aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--poi-integration/build.gradle3
-rw-r--r--poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java22
-rw-r--r--poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java49
-rw-r--r--poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java41
-rw-r--r--test-data/spreadsheet/link-external-workbook-a.xlsxbin0 -> 3274 bytes
-rw-r--r--test-data/spreadsheet/link-external-workbook-b.xlsxbin0 -> 4080 bytes
6 files changed, 104 insertions, 11 deletions
diff --git a/poi-integration/build.gradle b/poi-integration/build.gradle
index 3bc58132ef..1c01901e59 100644
--- a/poi-integration/build.gradle
+++ b/poi-integration/build.gradle
@@ -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 {
diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java
index fedbc8d3dd..3adb47b8b1 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java
@@ -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 implements CreationHelper {
@Internal
public XSSFCreationHelper(XSSFWorkbook wb) {
workbook = wb;
+ referencedWorkbooks = new HashMap<>();
}
/**
@@ -74,7 +81,12 @@ public class XSSFCreationHelper implements CreationHelper {
*/
@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 implements CreationHelper {
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);
+ }
}
diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
index 38daa8d85f..fef5bece7b 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
@@ -468,6 +468,8 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Date1904Su
namedRangesByName = new ArrayListValuedHashMap<>();
sheets = new ArrayList<>();
pivotTables = new ArrayList<>();
+
+ externalLinks = new ArrayList<>();
}
private void setBookViewsIfMissing() {
@@ -1971,18 +1973,53 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Date1904Su
* 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;
+
}
/**
diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
index bef3a36519..e8c7e5ad14 100644
--- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
+++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
@@ -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 extends BaseTestXWorkbook {
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);
+ }
+ */
+ }
+ }
+
}
diff --git a/test-data/spreadsheet/link-external-workbook-a.xlsx b/test-data/spreadsheet/link-external-workbook-a.xlsx
new file mode 100644
index 0000000000..fc983ccb08
--- /dev/null
+++ b/test-data/spreadsheet/link-external-workbook-a.xlsx
Binary files differ
diff --git a/test-data/spreadsheet/link-external-workbook-b.xlsx b/test-data/spreadsheet/link-external-workbook-b.xlsx
new file mode 100644
index 0000000000..30d08f310c
--- /dev/null
+++ b/test-data/spreadsheet/link-external-workbook-b.xlsx
Binary files differ
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-11 11:30:13 +0000