个人中心
gpt4 book ai didi

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

转载 作者：塔克拉玛干 更新时间：2023-11-03 02:59:45 27 4
gpt4 key购买 nike

我有一个工作簿，里面有一些数据。我正在使用该工作簿并根据另一个工作簿中的数据创建另一个工作簿，其中包含一个折线图。代码运行良好，但每当我打开图形文件时，我都会收到警告 We can't update some of the links in your workbook right now。如果我单击警告菜单中的 Edit Links... 按钮，它会显示找不到数据工作簿。如果我单击 Change Source...，然后选择适当的工作簿，它就可以正常工作。为什么是这样？ POI可以不保留两个文件之间的链接吗？

我的代码:

创建数据工作簿:

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

创建图形工作簿:

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

最佳答案

在 XSSF 中创建外部链接直到现在还没有很好地实现。有 ExternalLinksTable但如果你看看 Uses of this Class然后你会看到只提供读取那些外部链接，但不提供创建和写入。

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

只要两个工作簿都存储在同一目录中，以下内容就可以工作。

代码主要是您提供的代码，添加了用于创建指向另一个工作簿中工作表的外部链接的方法。这种方法使用的是底层对象，不是很通用，但应该能说明原理。

您的代码的其他更改也有注释。

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

}

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

该方法只需要链接工作簿和链接工作表的名称。它自己管理 ID。它返回 ExternalLinksTable 的 Id(作为 /xl/externalLinks/externalLink1.xml 中的 1。所以这个 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<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;
  }
 }
}

由于图表创建方面的变化，上述 2017 年的代码示例将无法使用当前的 apache poi 版本。但是使用 ExternalLinksTable 创建链接到具有图表数据的外部工作簿的图表的问题仍然存在。因此，我将提供可用于当前 apache poi 版本 4.x.y 和 5.0.0 的代码示例。它现在使用新的 XDDF 内容来创建图表。

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

关于java - 为什么我不能在 Apache POI 中将一个工作簿链接到另一个工作簿？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46508297/

27 4 0
文章推荐： algorithm - 我们如何有效地解决给定的场景？
文章推荐： algorithm - 如何判断两棵二叉树的内容是否相同？
文章推荐： java - Mockito 因使用无效参数名称异常启用的内联模拟而失败
文章推荐： java - Character.toUpperCase() 和 Character.toTitleCase() 有什么区别
塔克拉玛干
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com