You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2021/09/29 16:16:33 UTC
svn commit: r1893728 - in /poi/trunk: poi-integration/
poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/
poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/ test-data/spreadsheet/
Author: fanningpj
Date: Wed Sep 29 16:16:32 2021
New Revision: 1893728
URL: http://svn.apache.org/viewvc?rev=1893728&view=rev
Log:
[github-254] Implement XSSFWorkbook linkExternalWorkbook. Thanks to @aspojo. This closes #254
Added:
poi/trunk/test-data/spreadsheet/link-external-workbook-a.xlsx (with props)
poi/trunk/test-data/spreadsheet/link-external-workbook-b.xlsx (with props)
Modified:
poi/trunk/poi-integration/build.gradle
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
Modified: poi/trunk/poi-integration/build.gradle
URL: http://svn.apache.org/viewvc/poi/trunk/poi-integration/build.gradle?rev=1893728&r1=1893727&r2=1893728&view=diff
==============================================================================
--- poi/trunk/poi-integration/build.gradle (original)
+++ poi/trunk/poi-integration/build.gradle Wed Sep 29 16:16:32 2021
@@ -44,7 +44,8 @@ dependencies {
testImplementation 'org.apache.ant:ant:1.10.11'
testImplementation 'org.apache.commons:commons-collections4:4.4'
testImplementation 'com.google.guava:guava:31.0.1-jre'
- testRuntimeOnly 'org.slf4j:slf4j-api:1.7.32'
+ testRuntimeOnly "org.apache.logging.log4j:log4j-core:${log4jVersion}"
+ testRuntimeOnly "org.apache.logging.log4j:log4j-slf4j-impl:${log4jVersion}"
misc(project(':poi-ooxml')) {
capabilities {
Modified: poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java?rev=1893728&r1=1893727&r2=1893728&view=diff
==============================================================================
--- poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java (original)
+++ poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java Wed Sep 29 16:16:32 2021
@@ -18,14 +18,20 @@ package org.apache.poi.xssf.usermodel;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.CreationHelper;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Hyperlink;
+import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.Internal;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor;
+import java.util.HashMap;
+import java.util.Map;
+
public class XSSFCreationHelper implements CreationHelper {
private final XSSFWorkbook workbook;
+ private final Map<String, Workbook> referencedWorkbooks;
/**
* Should only be called by {@link XSSFWorkbook#getCreationHelper()}
@@ -35,6 +41,7 @@ public class XSSFCreationHelper implemen
@Internal
public XSSFCreationHelper(XSSFWorkbook wb) {
workbook = wb;
+ referencedWorkbooks = new HashMap<>();
}
/**
@@ -74,7 +81,12 @@ public class XSSFCreationHelper implemen
*/
@Override
public XSSFFormulaEvaluator createFormulaEvaluator() {
- return new XSSFFormulaEvaluator(workbook);
+ XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
+ Map<String, FormulaEvaluator> evaluatorMap = new HashMap<>();
+ evaluatorMap.put("", evaluator);
+ this.referencedWorkbooks.forEach((name,otherWorkbook)->evaluatorMap.put(name,otherWorkbook.getCreationHelper().createFormulaEvaluator()));
+ evaluator.setupReferencedWorkbooks(evaluatorMap);
+ return evaluator;
}
/**
@@ -104,4 +116,12 @@ public class XSSFCreationHelper implemen
public AreaReference createAreaReference(CellReference topLeft, CellReference bottomRight) {
return new AreaReference(topLeft, bottomRight, workbook.getSpreadsheetVersion());
}
+
+ protected Map<String, Workbook> getReferencedWorkbooks() {
+ return referencedWorkbooks;
+ }
+
+ protected void addExternalWorkbook(String name, Workbook workbook) {
+ this.referencedWorkbooks.put(name,workbook);
+ }
}
Modified: poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java?rev=1893728&r1=1893727&r2=1893728&view=diff
==============================================================================
--- poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (original)
+++ poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java Wed Sep 29 16:16:32 2021
@@ -468,6 +468,8 @@ public class XSSFWorkbook extends POIXML
namedRangesByName = new ArrayListValuedHashMap<>();
sheets = new ArrayList<>();
pivotTables = new ArrayList<>();
+
+ externalLinks = new ArrayList<>();
}
private void setBookViewsIfMissing() {
@@ -1971,18 +1973,53 @@ public class XSSFWorkbook extends POIXML
* referencing the specified external workbook to be added to this one. Allows
* formulas such as "[MyOtherWorkbook.xlsx]Sheet3!$A$5" to be added to the
* file, for workbooks not already linked / referenced.
- *
- * Note: this is not implemented and thus currently throws an Exception stating this.
+ * <p>
+ * This support is still regarded as in beta and may change
+ * <p>
+ * see https://bz.apache.org/bugzilla/show_bug.cgi?id=57184
*
* @param name The name the workbook will be referenced as in formulas
* @param workbook The open workbook to fetch the link required information from
- *
- * @throws RuntimeException stating that this method is not implemented yet.
+ * @return index position for external workbook
+ * @since POI 5.1.0
*/
+ @Beta
@Override
- @NotImplemented
public int linkExternalWorkbook(String name, Workbook workbook) {
- throw new RuntimeException("Not Implemented - see bug #57184");
+ int externalLinkIdx=-1;
+ if (!getCreationHelper().getReferencedWorkbooks().containsKey(name)){
+ externalLinkIdx = this.getNextPartNumber(XSSFRelation.EXTERNAL_LINKS,
+ this.getPackagePart().getPackage().getPartsByContentType(XSSFRelation.EXTERNAL_LINKS.getContentType()).size());
+ POIXMLDocumentPart.RelationPart rp = this.createRelationship(XSSFRelation.EXTERNAL_LINKS, XSSFFactory.getInstance(), externalLinkIdx, false);
+ ExternalLinksTable linksTable = rp.getDocumentPart();
+ linksTable.setLinkedFileName(name);
+ this.getExternalLinksTable().add(linksTable);
+
+ CTExternalReference ctExternalReference = this.getCTWorkbook().addNewExternalReferences().addNewExternalReference();
+ ctExternalReference.setId(rp.getRelationship().getId());
+
+ } else {
+ List<RelationPart> relationParts = getRelationParts();
+ for (RelationPart relationPart : relationParts) {
+ if (relationPart.getDocumentPart() instanceof ExternalLinksTable) {
+ ExternalLinksTable linksTable = relationPart.getDocumentPart();
+ String linkedFileName = linksTable.getLinkedFileName();
+ if(linkedFileName.equals(name)){
+ String s = relationPart.getRelationship().getTargetURI().toString();
+ String s2 = XSSFRelation.EXTERNAL_LINKS.getDefaultFileName();
+ String numStr = s.substring(s2.indexOf('#'), s2.indexOf('.'));
+ externalLinkIdx = Integer.parseInt(numStr);
+ break;
+ }
+ }
+ }
+ }
+
+ XSSFCreationHelper creationHelper = getCreationHelper();
+ creationHelper.addExternalWorkbook(name,workbook);
+
+ return externalLinkIdx;
+
}
/**
Modified: poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java?rev=1893728&r1=1893727&r2=1893728&view=diff
==============================================================================
--- poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java (original)
+++ poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java Wed Sep 29 16:16:32 2021
@@ -19,9 +19,7 @@ package org.apache.poi.xssf.usermodel;
import static org.apache.commons.io.output.NullOutputStream.NULL_OUTPUT_STREAM;
import static org.apache.poi.hssf.HSSFTestDataSamples.openSampleFileStream;
-import static org.apache.poi.xssf.XSSFTestDataSamples.openSampleWorkbook;
-import static org.apache.poi.xssf.XSSFTestDataSamples.writeOut;
-import static org.apache.poi.xssf.XSSFTestDataSamples.writeOutAndReadBack;
+import static org.apache.poi.xssf.XSSFTestDataSamples.*;
import static org.junit.jupiter.api.Assertions.assertArrayEquals;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
@@ -36,6 +34,7 @@ import java.nio.charset.StandardCharsets
import java.util.Date;
import java.util.Iterator;
import java.util.List;
+import java.util.function.Consumer;
import java.util.zip.CRC32;
import org.apache.poi.POIDataSamples;
@@ -66,6 +65,7 @@ import org.apache.poi.util.TempFile;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xssf.XSSFITestDataProvider;
+import org.apache.poi.xssf.XSSFTestDataSamples;
import org.apache.poi.xssf.model.StylesTable;
import org.junit.jupiter.api.Test;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCalcPr;
@@ -1215,4 +1215,39 @@ public final class TestXSSFWorkbook exte
private static String ref(Cell cell) {
return new CellReference(cell).formatAsString();
}
+
+ @Test
+ void testLinkExternalWorkbook() throws Exception {
+ String nameA = "link-external-workbook-a.xlsx";
+
+ try (
+ XSSFWorkbook a = new XSSFWorkbook();
+ XSSFWorkbook b = new XSSFWorkbook()
+ ) {
+ XSSFRow row1 = a.createSheet().createRow(0);
+ row1.createCell(0).setCellValue(10);
+ row1.createCell(1).setCellValue(20);
+
+ XSSFRow row2 = b.createSheet().createRow(0);
+ XSSFCell cell = row2.createCell(0);
+
+ b.linkExternalWorkbook(nameA, a);
+ String formula = String.format("SUM('[%s]Sheet0'!A1:B1)", nameA);
+ cell.setCellFormula(formula);
+ XSSFFormulaEvaluator evaluator = b.getCreationHelper().createFormulaEvaluator();
+ evaluator.evaluateFormulaCell(cell);
+ double cellValue = cell.getNumericCellValue();
+ assertEquals(cellValue,30.0);
+ /*
+ try (FileOutputStream out = new FileOutputStream(getSampleFile(nameA))) {
+ a.write(out);
+ }
+ String nameB = "link-external-workbook-b.xlsx";
+ try (FileOutputStream out = new FileOutputStream(getSampleFile(nameB))) {
+ b.write(out);
+ }
+ */
+ }
+ }
+
}
Added: poi/trunk/test-data/spreadsheet/link-external-workbook-a.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/link-external-workbook-a.xlsx?rev=1893728&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/link-external-workbook-a.xlsx
------------------------------------------------------------------------------
--- svn:mime-type (added)
+++ svn:mime-type Wed Sep 29 16:16:32 2021
@@ -0,0 +1 @@
+application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Added: poi/trunk/test-data/spreadsheet/link-external-workbook-b.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/link-external-workbook-b.xlsx?rev=1893728&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/link-external-workbook-b.xlsx
------------------------------------------------------------------------------
--- svn:mime-type (added)
+++ svn:mime-type Wed Sep 29 16:16:32 2021
@@ -0,0 +1 @@
+application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org