Pourquoi ne puis-je pas lier un classeur à un autre dans les points d’intérêt Apache?

J’ai un classeur qui contient des données. Je prends ce classeur et crée un autre classeur avec un graphique en courbes basé sur les données de l’autre classeur. Le code fonctionne correctement, mais chaque fois que j’ouvre le fichier graphique, je reçois l’avertissement We can't update some of the links in your workbook right now . Si je clique sur le bouton Edit Links... dans le menu d’avertissement, cela signifie que le classeur de données est introuvable. Si je clique sur Change Source... et que je sélectionne le bon classeur, cela fonctionne correctement. Pourquoi est-ce? Les points d’intérêt ne peuvent-ils pas conserver le lien entre les deux fichiers?

Mon code:

Pour créer le classeur de données:

 public static XSSFWorkbook createDataSpreadsheet(Ssortingng 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; }

Pour créer le classeur graphique:

 public static XSSFWorkbook createLineChart(Ssortingng name, XSSFWorkbook data) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(name); XSSFDrawing drawing = sheet.createDrawingPasortingarch(); 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 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 }); return workbook; }

La création de liens externes dans XSSF n’est pas bien implémentée jusqu’à présent. ExternalLinksTable existe, mais si vous examinez les utilisations de cette classe, vous verrez qu’il est uniquement fourni de lire ces liens externes, mais pas de créer ni d’écrire.

Nous avons donc besoin de travailler avec les objects de bas niveau. Et nous avons besoin de connaissances sur les dépendances internes de ces liens externes au sein de l’archive ZIP OpenXML *.xlsx Office.

Ce qui suit fonctionne tant que les deux classeurs sont stockés dans le même répertoire.

Le code est principalement votre code fourni, ajouté à une méthode de création d’un lien externe vers une feuille dans un autre classeur. Cette méthode utilise des objects de bas niveau et n’est pas très générale utilisable, mais elle devrait montrer le principe.

D’autres modifications de votre code sont également commentées.

 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 Ssortingng datawbname = "DataWB.xlsx"; private static Ssortingng 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(Ssortingng 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 Ssortingng 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); Ssortingng 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, Ssortingng wbname, Ssortingng sheetname, Ssortingng 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 Ssortingng 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(Ssortingng[] args) throws Exception { CreateExcelLineChartDataAnotherWorkbook mainObject = new CreateExcelLineChartDataAnotherWorkbook(); } }

Mon nouveau code fournit une classe MyXSSFWorkbook qui étend XSSFWorkbook par une méthode permettant de créer ExternalLinksTable pour un classeur et une feuille liés. Ce code crée vraiment un ExternalLinksTable et utilise la reflection pour l’append à la liste des ExternalLinksTable du XSSFWorkbook . Il sera donc possible d’utiliser le classeur ultérieurement.

La méthode nécessite uniquement les noms du classeur lié et de la feuille liée. Il gère les identifiants lui-même. Il renvoie l’identifiant de ExternalLinksTable (comme le 1 dans /xl/externalLinks/externalLink1.xml . Cet identifiant peut donc être utilisé comme référence de classeur externe dans les formules (comme le 1 dans [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 Ssortingng datawbname = "DataWB.xlsx"; private static Ssortingng 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(Ssortingng 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 Ssortingng 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); Ssortingng 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(Ssortingng[] 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(Ssortingng wbname, Ssortingng 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 Ssortingng 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; } } }