From 03a1b6e3f68258bb099ec1bc139674b6123bad91 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sat, 19 Jul 2014 11:27:46 +0000 Subject: [PATCH] There can be more than one linked ExternalLinks table for a workbook #56744 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1611890 13f79535-47bb-0310-9956-ffa450edef68 --- .../opc/PackageRelationshipTypes.java | 5 ++ .../poi/xssf/model/ExternalLinksTable.java | 34 ++++++++++ .../poi/xssf/usermodel/XSSFWorkbook.java | 37 +++++++++-- .../xssf/model/TestExternalLinksTable.java | 65 +++++++++++++++++-- 4 files changed, 128 insertions(+), 13 deletions(-) diff --git a/src/ooxml/java/org/apache/poi/openxml4j/opc/PackageRelationshipTypes.java b/src/ooxml/java/org/apache/poi/openxml4j/opc/PackageRelationshipTypes.java index 9344a64abf..134e6a9fa3 100644 --- a/src/ooxml/java/org/apache/poi/openxml4j/opc/PackageRelationshipTypes.java +++ b/src/ooxml/java/org/apache/poi/openxml4j/opc/PackageRelationshipTypes.java @@ -99,4 +99,9 @@ public interface PackageRelationshipTypes { * Style type. */ String STYLE_PART = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"; + + /** + * External Link to another Document + */ + String EXTERNAL_LINK_PATH = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath"; } diff --git a/src/ooxml/java/org/apache/poi/xssf/model/ExternalLinksTable.java b/src/ooxml/java/org/apache/poi/xssf/model/ExternalLinksTable.java index 22a0de9c45..9d66b6fa48 100644 --- a/src/ooxml/java/org/apache/poi/xssf/model/ExternalLinksTable.java +++ b/src/ooxml/java/org/apache/poi/xssf/model/ExternalLinksTable.java @@ -25,6 +25,8 @@ import java.util.List; import org.apache.poi.POIXMLDocumentPart; import org.apache.poi.openxml4j.opc.PackagePart; import org.apache.poi.openxml4j.opc.PackageRelationship; +import org.apache.poi.openxml4j.opc.PackageRelationshipTypes; +import org.apache.poi.openxml4j.opc.TargetMode; import org.apache.poi.ss.usermodel.Name; import org.apache.xmlbeans.XmlException; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExternalDefinedName; @@ -78,6 +80,38 @@ public class ExternalLinksTable extends POIXMLDocumentPart { public CTExternalLink getCTExternalLink(){ return link; } + + /** + * Returns the last recorded name of the file that this + * is linked to + */ + public String getLinkedFileName() { + String rId = link.getExternalBook().getId(); + PackageRelationship rel = getPackagePart().getRelationship(rId); + if (rel != null && rel.getTargetMode() == TargetMode.EXTERNAL) { + return rel.getTargetURI().toString(); + } else { + return null; + } + } + /** + * Updates the last recorded name for the file that this links to + */ + public void setLinkedFileName(String target) { + String rId = link.getExternalBook().getId(); + + if (rId == null || rId.isEmpty()) { + // We're a new External Link Table, so nothing to remove + } else { + // Relationships can't be changed, so remove the old one + getPackagePart().removeRelationship(rId); + } + + // Have a new one added + PackageRelationship newRel = getPackagePart().addExternalRelationship( + target, PackageRelationshipTypes.EXTERNAL_LINK_PATH); + link.getExternalBook().setId(newRel.getId()); + } @SuppressWarnings("deprecation") public List getSheetNames() { diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index f1025bce74..84e118233c 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -79,6 +79,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCalcPr; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedNames; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExternalReference; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheets; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook; @@ -157,9 +158,9 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable externalLinks; /** * A collection of custom XML mappings @@ -284,16 +285,19 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable shIdMap = new HashMap(); + Map elIdMap = new HashMap(); for(POIXMLDocumentPart p : getRelations()){ if(p instanceof SharedStringsTable) sharedStringSource = (SharedStringsTable)p; else if(p instanceof StylesTable) stylesSource = (StylesTable)p; else if(p instanceof ThemesTable) theme = (ThemesTable)p; else if(p instanceof CalculationChain) calcChain = (CalculationChain)p; - else if(p instanceof ExternalLinksTable) externalLinks = (ExternalLinksTable)p; else if(p instanceof MapInfo) mapInfo = (MapInfo)p; else if (p instanceof XSSFSheet) { shIdMap.put(p.getPackageRelationship().getId(), (XSSFSheet)p); } + else if (p instanceof ExternalLinksTable) { + elIdMap.put(p.getPackageRelationship().getId(), (ExternalLinksTable)p); + } } if (stylesSource == null) { @@ -307,7 +311,8 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable(shIdMap.size()); for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) { XSSFSheet sh = shIdMap.get(ctSheet.getId()); @@ -319,6 +324,20 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable(elIdMap.size()); + if (this.workbook.isSetExternalReferences()) { + for (CTExternalReference er : this.workbook.getExternalReferences().getExternalReferenceArray()) { + ExternalLinksTable el = elIdMap.get(er.getId()); + if(el == null) { + logger.log(POILogger.WARN, "ExternalLinksTable with r:id " + er.getId()+ " was defined, but didn't exist in package, skipping"); + continue; + } + externalLinks.add(el); + } + } // Process the named ranges reprocessNamedRanges(); @@ -1611,16 +1630,20 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, IterableThe external links table specifies details of named ranges etc * that are referenced from other workbooks, along with the last seen * values of what they point to.

* - * @return the ExternalLinksTable object or null if not defined + *

Note that Excel uses index 0 for the current workbook, so the first + * External Links in a formula would be '[1]Foo' which corresponds to + * entry 0 in this list.

+ + * @return the ExternalLinksTable list, which may be empty */ @Internal - public ExternalLinksTable getExternalLinksTable() { + public List getExternalLinksTable() { return externalLinks; } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/model/TestExternalLinksTable.java b/src/ooxml/testcases/org/apache/poi/xssf/model/TestExternalLinksTable.java index 612924ad5f..1fbf86a335 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/model/TestExternalLinksTable.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/model/TestExternalLinksTable.java @@ -29,7 +29,8 @@ public final class TestExternalLinksTable { @Test public void none() { XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("SampleSS.xlsx"); - assertEquals(null, wb.getExternalLinksTable()); + assertNotNull(wb.getExternalLinksTable()); + assertEquals(0, wb.getExternalLinksTable().size()); } @Test @@ -37,8 +38,10 @@ public final class TestExternalLinksTable { XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("ref-56737.xlsx"); assertNotNull(wb.getExternalLinksTable()); Name name = null; - - ExternalLinksTable links = wb.getExternalLinksTable(); + + assertEquals(1, wb.getExternalLinksTable().size()); + + ExternalLinksTable links = wb.getExternalLinksTable().get(0); assertEquals(3, links.getSheetNames().size()); assertEquals(2, links.getDefinedNames().size()); @@ -57,17 +60,20 @@ public final class TestExternalLinksTable { assertEquals(1, name.getSheetIndex()); assertEquals("Defines", name.getSheetName()); assertEquals("'Defines'!$A$1", name.getRefersToFormula()); + + assertEquals("56737.xlsx", links.getLinkedFileName()); } @Test public void basicReadWriteRead() { XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("ref-56737.xlsx"); - Name name = wb.getExternalLinksTable().getDefinedNames().get(1); + Name name = wb.getExternalLinksTable().get(0).getDefinedNames().get(1); name.setNameName("Testing"); name.setRefersToFormula("$A$1"); wb = XSSFTestDataSamples.writeOutAndReadBack(wb); - ExternalLinksTable links = wb.getExternalLinksTable(); + assertEquals(1, wb.getExternalLinksTable().size()); + ExternalLinksTable links = wb.getExternalLinksTable().get(0); name = links.getDefinedNames().get(0); assertEquals("NR_Global_B2", name.getNameName()); @@ -86,6 +92,53 @@ public final class TestExternalLinksTable { public void readWithReferencesToTwoExternalBooks() { XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("ref2-56737.xlsx"); - // TODO Fix so we can see both of them... + assertNotNull(wb.getExternalLinksTable()); + Name name = null; + + assertEquals(2, wb.getExternalLinksTable().size()); + + // Check the first one, links to 56737.xlsx + ExternalLinksTable links = wb.getExternalLinksTable().get(0); + assertEquals("56737.xlsx", links.getLinkedFileName()); + assertEquals(3, links.getSheetNames().size()); + assertEquals(2, links.getDefinedNames().size()); + + assertEquals("Uses", links.getSheetNames().get(0)); + assertEquals("Defines", links.getSheetNames().get(1)); + assertEquals("56737", links.getSheetNames().get(2)); + + name = links.getDefinedNames().get(0); + assertEquals("NR_Global_B2", name.getNameName()); + assertEquals(-1, name.getSheetIndex()); + assertEquals(null, name.getSheetName()); + assertEquals("'Defines'!$B$2", name.getRefersToFormula()); + + name = links.getDefinedNames().get(1); + assertEquals("NR_To_A1", name.getNameName()); + assertEquals(1, name.getSheetIndex()); + assertEquals("Defines", name.getSheetName()); + assertEquals("'Defines'!$A$1", name.getRefersToFormula()); + + + // Check the second one, links to 56737.xls, slightly differently + links = wb.getExternalLinksTable().get(1); + assertEquals("56737.xls", links.getLinkedFileName()); + assertEquals(2, links.getSheetNames().size()); + assertEquals(2, links.getDefinedNames().size()); + + assertEquals("Uses", links.getSheetNames().get(0)); + assertEquals("Defines", links.getSheetNames().get(1)); + + name = links.getDefinedNames().get(0); + assertEquals("NR_Global_B2", name.getNameName()); + assertEquals(-1, name.getSheetIndex()); + assertEquals(null, name.getSheetName()); + assertEquals("'Defines'!$B$2", name.getRefersToFormula()); + + name = links.getDefinedNames().get(1); + assertEquals("NR_To_A1", name.getNameName()); + assertEquals(1, name.getSheetIndex()); + assertEquals("Defines", name.getSheetName()); + assertEquals("'Defines'!$A$1", name.getRefersToFormula()); } } -- 2.39.5