Why can't I link one workbook to another in Apache POI?

Question

I have one workbook that has some data in it. I am taking that workbook and creating another workbook with a line chart in it based off of the data in the other workbook. The code runs fine, but whenever I open up the graph file, I get the warning We can't update some of the links in your workbook right now. If I click the Edit Links... button in the warning menu, it shows that the data workbook cannot be found. If I click on Change Source..., and select the proper workbook, it then works fine. Why is this? Can POI not retain the link between the two files?

My code:

To create the data workbook:

public static XSSFWorkbook createDataSpreadsheet(String name, long[] data) {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet(name);

    int rowNumber = 0;
    for(int i = 1; i < data.length + 1; i++) {
        Row row = sheet.createRow(rowNumber++);

        int columnNumber = 0;
        row.createCell(columnNumber++).setCellValue(i);
        row.createCell(columnNumber++).setCellValue(data[i - 1]);
    }

    return workbook;
}

To create the graph workbook:

public static XSSFWorkbook createLineChart(String name, XSSFWorkbook data) {
    XSSFWorkbook workbook = new XSSFWorkbook();

    XSSFSheet sheet = workbook.createSheet(name);

    XSSFDrawing drawing = sheet.createDrawingPatriarch();
    XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 15, 15);
    XSSFChart lineChart = drawing.createChart(anchor);

    XSSFChartLegend legend = lineChart.getOrCreateLegend();
    legend.setPosition(LegendPosition.BOTTOM); 

    LineChartData chartData = lineChart.getChartDataFactory().createLineChartData();     
    ChartAxis bottomAxis = lineChart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
    ValueAxis leftAxis = lineChart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
    leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

    XSSFSheet dataSheet = data.getSheetAt(0);
    ChartDataSource<Number> xData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 0, 0));
    ChartDataSource<Number> yData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 1, 1));

    LineChartSeries chartSeries = chartData.addSeries(xData, yData);
    chartSeries.setTitle("A title");

    lineChart.plot(chartData, new ChartAxis[] { bottomAxis, leftAxis });

    return workbook;
}

Answer 1

Creation of external links in XSSF is not well implemented until now. There is ExternalLinksTable but if you look at the Uses of this Class then you will see that there is only provided reading those external links but not creating and writing.

So we need working with the low level objects. And we need knowledge about the internal dependencies of this external links within the Office OpenXML *.xlsx ZIP-archive.

The following works as long both workbooks are stored in the same directory.

The code is mainly your provided code added with a method for creating a external link to a sheet in another workbook. This method is using low level objects and is not very general usable, but it should show the principle.

Other changings to your code are commented as well.

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.model.ExternalLinksTable;

import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.POIXMLDocumentPart;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.ExternalLinkDocument;

import static org.apache.poi.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;

public class CreateExcelLineChartDataAnotherWorkbook {

 private static String datawbname = "DataWB.xlsx";
 private static String chartwbname = "ChartWB.xlsx";

 public CreateExcelLineChartDataAnotherWorkbook() throws Exception {
  Workbook datawb = createDataSpreadsheet("ChartDataSheet");
  saveWorkbook(datawb, "/home/axel/Dokumente/"+datawbname);

  Workbook chartwb = createLineChart("ChartSheet", (XSSFWorkbook)datawb);
  saveWorkbook(chartwb, "/home/axel/Dokumente/"+chartwbname);
 }

 //your method only partially changed to have sample data
 public XSSFWorkbook createDataSpreadsheet(String name) {
  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet(name);

  int rowNumber = 0;
  for(int i = 0; i < 20; i++) {
   Row row = sheet.createRow(rowNumber++);

   int columnNumber = 0;
   row.createCell(columnNumber++).setCellValue(Math.PI*i/10*2);
   row.createCell(columnNumber++).setCellValue(Math.sin(Math.PI*i/10*2));
  }

  return (XSSFWorkbook)workbook;
 }

 //method for saving the workbooks
 public void saveWorkbook(Workbook wb, String path) throws Exception {
  wb.write(new FileOutputStream(path));
  wb.close();
 }

 //your method changes are commented
 public XSSFWorkbook createLineChart(String name, XSSFWorkbook data) throws Exception {
  Workbook workbook = new XSSFWorkbook();

  //create the external link to datawbname
  int extwbid = 1;
  createExternalLinkToWorksheet((XSSFWorkbook)workbook, datawbname, "ChartDataSheet", "rId"+extwbid);

  Sheet sheet = workbook.createSheet(name);

  Drawing drawing = sheet.createDrawingPatriarch();
  ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 15, 15);
  Chart lineChart = drawing.createChart(anchor);

  ChartLegend legend = lineChart.getOrCreateLegend();
  legend.setPosition(LegendPosition.BOTTOM); 

  LineChartData chartData = lineChart.getChartDataFactory().createLineChartData();     
  ChartAxis bottomAxis = lineChart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
  ValueAxis leftAxis = lineChart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
  leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

  Sheet dataSheet = data.getSheetAt(0);
  ChartDataSource<Number> xData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 0, 0));
  ChartDataSource<Number> yData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 1, 1));

  LineChartSeries chartSeries = chartData.addSeries(xData, yData);
  chartSeries.setTitle("A title");

  lineChart.plot(chartData, new ChartAxis[] { bottomAxis, leftAxis });

  //since dataSheet is an external sheet, the formula in the org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef
  //must be prefixed with [1], where 1 is the Id of the linked workbook 
  String catref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().getF();
  ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().setF("[" + extwbid + "]" + catref);
  String valref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().getF();
  ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().setF("[" + extwbid + "]" + valref);

  return (XSSFWorkbook)workbook;
 }

 //method for creating a external link to a sheet in another workbook
 public void createExternalLinkToWorksheet(XSSFWorkbook workbook, String wbname, String sheetname, String rIdExtWb) throws Exception {
  OPCPackage opcpackage = workbook.getPackage();

  //creating /xl/externalLinks/externalLink1.xml having link to externalBook with external sheetName
  PackagePartName partname = PackagingURIHelper.createPartName("/xl/externalLinks/externalLink1.xml");
  PackagePart part = opcpackage.createPart(partname, "application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml");
  POIXMLDocumentPart externallinkstable = new POIXMLDocumentPart(part) {
   @Override
   protected void commit() throws IOException {
    PackagePart part = getPackagePart();
    OutputStream out = part.getOutputStream();
    try {
     ExternalLinkDocument doc = ExternalLinkDocument.Factory.parse(
      "<externalLink xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"
     +"<externalBook xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:id=\""+ rIdExtWb + "\">"
     +"<sheetNames><sheetName val=\"" + sheetname + "\"/></sheetNames>"
     +"</externalBook>"
     +"</externalLink>"
     );
     doc.save(out, DEFAULT_XML_OPTIONS);
     out.close();
    } catch (Exception ex) {
     ex.printStackTrace();
    }; 
   }
  };
  //creating the relation to the external workbook in /xl/externalLinks/_rels/externalLink1.xml.rels
  PackageRelationship packrelship = part.addRelationship(new java.net.URI(wbname), TargetMode.EXTERNAL, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath", rIdExtWb);

  //creating the relation to /xl/externalLinks/externalLink1.xml in /xl/_rels/workbook.xml.rels
  String rIdExtLink = "rId" + (workbook.getRelationParts().size()+1);
  workbook.addRelation(rIdExtLink, XSSFRelation.EXTERNAL_LINKS, externallinkstable);

  //creating the <externalReferences><externalReference .../> in /xl/workbook.xml
  workbook.getCTWorkbook().addNewExternalReferences().addNewExternalReference().setId(rIdExtLink);

 }

 public static void main(String[] args) throws Exception {
  CreateExcelLineChartDataAnotherWorkbook mainObject = new CreateExcelLineChartDataAnotherWorkbook();
 }

}

---

My new code provides a class MyXSSFWorkbook which extends XSSFWorkbook by a method for creating ExternalLinksTable for linked workbook and sheet. This code really creates a ExternalLinksTable and it uses reflection for adding this ExternalLinksTable to the list of ExternalLinksTables in the XSSFWorkbook. So it will be getable in further using the workbook.

The method only needs the names of the linked workbook and the linked sheet. It manages Ids itself. It returns the Id of the ExternalLinksTable (as the 1 in /xl/externalLinks/externalLink1.xml. So this Id can be used as external workbook reference in formulas (as the 1 in [1]ChartDataSheet!$A$1:$A$20).

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.model.ExternalLinksTable;

import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.POIXMLDocumentPart;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.ExternalLinkDocument;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExternalReferences;

import static org.apache.poi.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;

import java.lang.reflect.Field;

import java.util.List;
import java.util.ArrayList;

public class CreateExcelLineChartExternalLinksTable {

 private static String datawbname = "DataWB.xlsx";
 private static String chartwbname = "ChartWB.xlsx";

 public CreateExcelLineChartExternalLinksTable() throws Exception {
  Workbook datawb = createDataSpreadsheet("ChartDataSheet");
  saveWorkbook(datawb, "/home/axel/Dokumente/"+datawbname);

  Workbook chartwb = createLineChart("ChartSheet", (XSSFWorkbook)datawb);
  saveWorkbook(chartwb, "/home/axel/Dokumente/"+chartwbname);
 }

 //your method only partially changed to have sample data
 public XSSFWorkbook createDataSpreadsheet(String name) {
  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet(name);

  int rowNumber = 0;
  for(int i = 0; i < 20; i++) {
   Row row = sheet.createRow(rowNumber++);

   int columnNumber = 0;
   row.createCell(columnNumber++).setCellValue(Math.PI*i/10*2);
   row.createCell(columnNumber++).setCellValue(Math.sin(Math.PI*i/10*2));
  }

  return (XSSFWorkbook)workbook;
 }

 //method for saving the workbooks
 public void saveWorkbook(Workbook wb, String path) throws Exception {
  wb.write(new FileOutputStream(path));
  wb.close();
 }

 //your method changes are commented
 public XSSFWorkbook createLineChart(String name, XSSFWorkbook data) throws Exception {
  Workbook workbook = new MyXSSFWorkbook();

  Sheet sheet = workbook.createSheet(name);

  Drawing drawing = sheet.createDrawingPatriarch();
  ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 15, 15);
  Chart lineChart = drawing.createChart(anchor);

  ChartLegend legend = lineChart.getOrCreateLegend();
  legend.setPosition(LegendPosition.BOTTOM); 

  LineChartData chartData = lineChart.getChartDataFactory().createLineChartData();     
  ChartAxis bottomAxis = lineChart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
  ValueAxis leftAxis = lineChart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
  leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

  Sheet dataSheet = data.getSheetAt(0);
  ChartDataSource<Number> xData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 0, 0));
  ChartDataSource<Number> yData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 1, 1));

  LineChartSeries chartSeries = chartData.addSeries(xData, yData);
  chartSeries.setTitle("A title");

  lineChart.plot(chartData, new ChartAxis[] { bottomAxis, leftAxis });

  //create the ExternalLinksTable for the linked workbook and sheet
  int extLinksId = ((MyXSSFWorkbook)workbook).createExternalLinksTableWbSheet(datawbname, "ChartDataSheet");
System.out.println(((XSSFWorkbook)workbook).getExternalLinksTable());

  //since dataSheet is an external sheet, the formula in the org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef
  //must be prefixed with [1], where 1 is the Id of the linked workbook 
  String catref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().getF();
  ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().setF("["+extLinksId+"]" + catref);
  String valref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().getF();
  ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().setF("["+extLinksId+"]" + valref);

  return (XSSFWorkbook)workbook;
 }

 public static void main(String[] args) throws Exception {
  CreateExcelLineChartExternalLinksTable mainObject = new CreateExcelLineChartExternalLinksTable();
 }

 //class which extends XSSFWorkbook and provides a method for creating ExternalLinksTable for linked workbook and sheet
 private class MyXSSFWorkbook extends XSSFWorkbook {

  //method for creating ExternalLinksTable for linked workbook and sheet
  //returns the Id of this ExternalLinksTable
  int createExternalLinksTableWbSheet(String wbname, String sheetname) throws Exception {

   List<ExternalLinksTable> elternallinkstablelist = getExternalLinksTable();
   int extLinksId = 1;
   if (elternallinkstablelist != null) extLinksId = elternallinkstablelist.size()+1;

   OPCPackage opcpackage = getPackage();

   //creating /xl/externalLinks/externalLink1.xml having link to externalBook with external sheetName
   PackagePartName partname = PackagingURIHelper.createPartName("/xl/externalLinks/externalLink"+extLinksId+".xml");
   PackagePart part = opcpackage.createPart(partname, "application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml");

   OutputStream out = part.getOutputStream();
   ExternalLinkDocument doc = ExternalLinkDocument.Factory.parse(
     "<externalLink xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"
    +"<externalBook xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:id=\"rId1\">"
    +"<sheetNames><sheetName val=\"" + sheetname + "\"/></sheetNames>"
    +"</externalBook>"
    +"</externalLink>"
   );
   doc.save(out, DEFAULT_XML_OPTIONS);
   out.close();

   //creating the relation to the external workbook in /xl/externalLinks/_rels/externalLink1.xml.rels
   PackageRelationship packrelship = part.addRelationship(new java.net.URI(wbname), TargetMode.EXTERNAL, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath", "rId1");

   ExternalLinksTable externallinkstable = new ExternalLinksTable(part);
 
   //creating the relation to /xl/externalLinks/externalLink1.xml in /xl/_rels/workbook.xml.rels
   String rIdExtLink = "rId" + (getRelationParts().size()+1);
   addRelation(rIdExtLink, XSSFRelation.EXTERNAL_LINKS, externallinkstable);

   //creating the <externalReferences><externalReference .../> in /xl/workbook.xml
   CTExternalReferences externalreferences = getCTWorkbook().getExternalReferences();
   if (externalreferences == null) externalreferences = getCTWorkbook().addNewExternalReferences();
   externalreferences.addNewExternalReference().setId(rIdExtLink);

   Field externalLinksField = XSSFWorkbook.class.getDeclaredField("externalLinks"); 
   externalLinksField.setAccessible(true);
   @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
   List<ExternalLinksTable> externalLinks = (ArrayList<ExternalLinksTable>)externalLinksField.get(this);
   if (externalLinks == null) {
    externalLinks = new ArrayList<ExternalLinksTable>();
    externalLinks.add(externallinkstable);
    externalLinksField.set(this, externalLinks);
   } else {
    externalLinks.add(externallinkstable);
   }

   return extLinksId;
  }
 }
}

---

The above code samples from 2017 will not work using current apache poi versions because of changes in chart creation. But the problem of using ExternalLinksTable to create a chart linked to an external workbook having the chart data persists. So I will provide a code sample useable with current apache poi versions 4.x.y and 5.0.0. It uses the new XDDF stuff now for chart creation.

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.model.ExternalLinksTable;

import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;

import org.apache.poi.openxml4j.opc.*;
//import org.apache.poi.POIXMLDocumentPart; // up to apache poi 3.17
import org.apache.poi.ooxml.POIXMLDocumentPart; // since apache poi 4.0.0

import org.openxmlformats.schemas.spreadsheetml.x2006.main.ExternalLinkDocument;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExternalReferences;

//import static org.apache.poi.POIXMLTypeLoader.DEFAULT_XML_OPTIONS; // up to apache poi 3.17
import static org.apache.poi.ooxml.POIXMLTypeLoader.DEFAULT_XML_OPTIONS; // since apache poi 4.0.0

import java.lang.reflect.Field;

import java.util.List;
import java.util.ArrayList;

public class CreateExcelLineChartExternalLinksTable {

 private static String datawbname = "DataWB.xlsx";
 private static String chartwbname = "ChartWB.xlsx";

 public CreateExcelLineChartExternalLinksTable() throws Exception {
  Workbook datawb = createDataSpreadsheet("ChartDataSheet");
  saveWorkbook(datawb, "./"+datawbname);

  Workbook chartwb = createLineChart("ChartSheet", (XSSFWorkbook)datawb);
  saveWorkbook(chartwb, "./"+chartwbname);
 }

 //your method only partially changed to have sample data
 public XSSFWorkbook createDataSpreadsheet(String name) {
  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet(name);

  int rowNumber = 0;
  for(int i = 0; i < 20; i++) {
   Row row = sheet.createRow(rowNumber++);

   int columnNumber = 0;
   row.createCell(columnNumber++).setCellValue(Math.PI*i/10*2);
   row.createCell(columnNumber++).setCellValue(Math.sin(Math.PI*i/10*2));
  }

  return (XSSFWorkbook)workbook;
 }

 //method for saving the workbooks
 public void saveWorkbook(Workbook wb, String path) throws Exception {
  wb.write(new FileOutputStream(path));
  wb.close();
 }

 //your method changes are commented
 public XSSFWorkbook createLineChart(String name, XSSFWorkbook data) throws Exception {
  XSSFWorkbook workbook = new MyXSSFWorkbook();

  XSSFSheet sheet = workbook.createSheet(name);

  XSSFDrawing drawing = sheet.createDrawingPatriarch();
  XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 15, 15);
  XSSFChart lineChart = drawing.createChart(anchor);

  XDDFChartLegend legend = lineChart.getOrAddLegend();
  legend.setPosition(LegendPosition.BOTTOM); 

  XDDFCategoryAxis bottomAxis = lineChart.createCategoryAxis(AxisPosition.BOTTOM);
  XDDFValueAxis leftAxis = lineChart.createValueAxis(AxisPosition.LEFT);
  leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

  XSSFSheet dataSheet = data.getSheetAt(0);

  XDDFDataSource<Double> xData = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 0, 0));
  XDDFNumericalDataSource<Double> yData = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 1, 1));
 
  XDDFLineChartData chartData = (XDDFLineChartData) lineChart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
  XDDFLineChartData.Series series = (XDDFLineChartData.Series) chartData.addSeries(xData, yData);
  
  series.setTitle("A Title", null);

  lineChart.plot(chartData);

  //create the ExternalLinksTable for the linked workbook and sheet
  int extLinksId = ((MyXSSFWorkbook)workbook).createExternalLinksTableWbSheet(datawbname, "ChartDataSheet");
System.out.println(((XSSFWorkbook)workbook).getExternalLinksTable());

  //since dataSheet is an external sheet, the formula in the org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef
  //must be prefixed with [1], where 1 is the Id of the linked workbook 
  String catref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().getF();
  ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().setF("["+extLinksId+"]" + catref);
  String valref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().getF();
  ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().setF("["+extLinksId+"]" + valref);

  return (XSSFWorkbook)workbook;
 }

 public static void main(String[] args) throws Exception {
  CreateExcelLineChartExternalLinksTable mainObject = new CreateExcelLineChartExternalLinksTable();
 }

 //class which extends XSSFWorkbook and provides a method for creating ExternalLinksTable for linked workbook and sheet
 private class MyXSSFWorkbook extends XSSFWorkbook {

  //method for creating ExternalLinksTable for linked workbook and sheet
  //returns the Id of this ExternalLinksTable
  int createExternalLinksTableWbSheet(String wbname, String sheetname) throws Exception {

   List<ExternalLinksTable> elternallinkstablelist = getExternalLinksTable();
   int extLinksId = 1;
   if (elternallinkstablelist != null) extLinksId = elternallinkstablelist.size()+1;

   OPCPackage opcpackage = getPackage();

   //creating /xl/externalLinks/externalLink1.xml having link to externalBook with external sheetName
   PackagePartName partname = PackagingURIHelper.createPartName("/xl/externalLinks/externalLink"+extLinksId+".xml");
   PackagePart part = opcpackage.createPart(partname, "application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml");

   OutputStream out = part.getOutputStream();
   ExternalLinkDocument doc = ExternalLinkDocument.Factory.parse(
     "<externalLink xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"
    +"<externalBook xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:id=\"rId1\">"
    +"<sheetNames><sheetName val=\"" + sheetname + "\"/></sheetNames>"
    +"</externalBook>"
    +"</externalLink>"
   );
   doc.save(out, DEFAULT_XML_OPTIONS);
   out.close();

   //creating the relation to the external workbook in /xl/externalLinks/_rels/externalLink1.xml.rels
   PackageRelationship packrelship = part.addRelationship(new java.net.URI(wbname), TargetMode.EXTERNAL, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath", "rId1");

   ExternalLinksTable externallinkstable = new ExternalLinksTable(part);

   //creating the relation to /xl/externalLinks/externalLink1.xml in /xl/_rels/workbook.xml.rels
   String rIdExtLink = "rId" + (getRelationParts().size()+1);
   addRelation(rIdExtLink, XSSFRelation.EXTERNAL_LINKS, externallinkstable);

   //creating the <externalReferences><externalReference .../> in /xl/workbook.xml
   CTExternalReferences externalreferences = getCTWorkbook().getExternalReferences();
   if (externalreferences == null) externalreferences = getCTWorkbook().addNewExternalReferences();
   externalreferences.addNewExternalReference().setId(rIdExtLink);

   Field externalLinksField = XSSFWorkbook.class.getDeclaredField("externalLinks"); 
   externalLinksField.setAccessible(true);
   @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
   List<ExternalLinksTable> externalLinks = (ArrayList<ExternalLinksTable>)externalLinksField.get(this);
   if (externalLinks == null) {
    externalLinks = new ArrayList<ExternalLinksTable>();
    externalLinks.add(externallinkstable);
    externalLinksField.set(this, externalLinks);
   } else {
    externalLinks.add(externallinkstable);
   }

   return extLinksId;
  }
 }
}