为什么我不能在Apache POI中将一个工作簿链接到另一个工作簿?

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?

我有一个工作簿，里面有一些数据。我将使用那个工作簿并根据另一个工作簿中的数据创建另一个工作簿，其中包含一个折线图。代码运行良好，但是每当我打开图形文件时，我就会得到警告，我们现在不能更新工作簿中的一些链接。如果我点击编辑链接…在警告菜单中的按钮，显示无法找到数据工作簿。如果我点击更改源…，然后选择合适的工作簿，它就可以正常工作了。这是为什么呢?POI不能保留两个文件之间的链接吗?

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;
}

1 个解决方案

#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.

在XSSF中创建外部链接直到现在还没有很好地实现。有外部性的，但是如果你看一下这个类的用法，你会发现只提供了外部链接，而不是创建和写作。

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.

所以我们需要处理低层次的对象。我们还需要了解Office OpenXML *中外部链接的内部依赖关系。xlsx zip归档。

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.

我的新代码提供了一个MyXSSFWorkbook类，它通过为链接的工作簿和工作表创建ExternalLinksTable的方法来扩展XSSFWorkbook。这段代码实际上创建了一个外部墨水稳定器，它使用反射将这个外部墨水稳定添加到XSSFWorkbook中的外部墨水稳定器列表中。因此，它将在进一步使用工作簿时获得。

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).

该方法只需要链接工作簿和链接表的名称。它管理id本身。它返回ExternalLinksTable的Id(作为1 in /xl/externalLinks/externalLink1.xml)。因此，这个Id可以用作公式中的外部工作簿引用(如[1]ChartDataSheet中的1 !$ 1:$ 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;
  }
 }
}