为什么我不能在ApachePOI中将一个工作簿链接到另一个工作簿？

XSSF直到现在,创建外部链接还没有很好地实现.有ExternalLinksTable但是如果你看一下这个类的使用,那么你会看到只提供读取那些外部链接但不创建和写入.

所以我们需要使用低级对象.我们需要了解Office OpenXML *.xlsxZIP存档中此外部链接的内部依赖关系.

只要两个工作簿存储在同一目录中,以下工作就可以正常工作.

代码主要是您提供的代码,其中添加了一种方法,用于创建指向另一个工作簿中工作表的外部链接.这种方法使用的是低级对象,并不是很普遍可用,但它应该显示原理.

您的代码的其他更改也会被注释.

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 xData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 0, 0));
  ChartDataSource 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(
      ""
     +""
     +""
     +""
     +""
     );
     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  in /xl/workbook.xml
  workbook.getCTWorkbook().addNewExternalReferences().addNewExternalReference().setId(rIdExtLink);

 }

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

}

我的新代码提供了一个类MyXSSFWorkbook,该类XSSFWorkbook通过创建ExternalLinksTable链接工作簿和工作表的方法进行扩展.这段代码确实创建了一个ExternalLinksTable,它使用反射将其添加ExternalLinksTable到的ExternalLinksTables 列表中XSSFWorkbook.因此,它可以进一步使用工作簿.

该方法只需要链接工作簿和链接表的名称.它管理自己的ID.它返回的Id ExternalLinksTable(作为1 in /xl/externalLinks/externalLink1.xml.因此,此ID可用作公式中的外部工作簿引用(作为1 [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 xData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 0, 0));
  ChartDataSource 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 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(
     ""
    +""
    +""
    +""
    +""
   );
   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  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 externalLinks = (ArrayList)externalLinksField.get(this);
   if (externalLinks == null) {
    externalLinks = new ArrayList();
    externalLinks.add(externallinkstable);
    externalLinksField.set(this, externalLinks);
   } else {
    externalLinks.add(externallinkstable);
   }

   return extLinksId;
  }
 }
}