diff options
Diffstat (limited to 'src')
7 files changed, 209 insertions, 58 deletions
diff --git a/src/java/org/apache/poi/hssf/model/InternalWorkbook.java b/src/java/org/apache/poi/hssf/model/InternalWorkbook.java index 580d48d025..3c3b91b148 100644 --- a/src/java/org/apache/poi/hssf/model/InternalWorkbook.java +++ b/src/java/org/apache/poi/hssf/model/InternalWorkbook.java @@ -94,6 +94,7 @@ import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.formula.ptg.Ref3DPtg; import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.BuiltinFormats; +import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.util.Internal; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; @@ -1803,6 +1804,10 @@ public final class InternalWorkbook { } return linkTable; } + + public int linkExternalWorkbook(String name, Workbook externalWorkbook) { + return getOrCreateLinkTable().linkExternalWorkbook(name, externalWorkbook); + } /** * Finds the first sheet name by his extern sheet index diff --git a/src/java/org/apache/poi/hssf/model/LinkTable.java b/src/java/org/apache/poi/hssf/model/LinkTable.java index 7277da2e6c..cc228b3dfd 100644 --- a/src/java/org/apache/poi/hssf/model/LinkTable.java +++ b/src/java/org/apache/poi/hssf/model/LinkTable.java @@ -37,6 +37,7 @@ import org.apache.poi.ss.formula.ptg.ErrPtg; import org.apache.poi.ss.formula.ptg.NameXPtg; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.formula.ptg.Ref3DPtg; +import org.apache.poi.ss.usermodel.Workbook; /** * Link Table (OOO pdf reference: 4.10.3 ) <p/> @@ -110,31 +111,39 @@ final class LinkTable { _crnBlocks = new CRNBlock[temp.size()]; temp.toArray(_crnBlocks); } - - /** - * Create a new block for internal references. It is called when constructing a new LinkTable. - * - * @see org.apache.poi.hssf.model.LinkTable#LinkTable(int, WorkbookRecordList) - */ - public ExternalBookBlock(int numberOfSheets) { - _externalBookRecord = SupBookRecord.createInternalReferences((short)numberOfSheets); - _externalNameRecords = new ExternalNameRecord[0]; - _crnBlocks = new CRNBlock[0]; - } - - /** - * Create a new block for registering add-in functions - * - * @see org.apache.poi.hssf.model.LinkTable#addNameXPtg(String) - */ - public ExternalBookBlock() { - _externalBookRecord = SupBookRecord.createAddInFunctions(); - _externalNameRecords = new ExternalNameRecord[0]; - _crnBlocks = new CRNBlock[0]; - } + + /** + * Create a new block for external references. + */ + public ExternalBookBlock(String url, String[] sheetNames) { + _externalBookRecord = SupBookRecord.createExternalReferences(url, sheetNames); + _crnBlocks = new CRNBlock[0]; + } + + /** + * Create a new block for internal references. It is called when constructing a new LinkTable. + * + * @see org.apache.poi.hssf.model.LinkTable#LinkTable(int, WorkbookRecordList) + */ + public ExternalBookBlock(int numberOfSheets) { + _externalBookRecord = SupBookRecord.createInternalReferences((short)numberOfSheets); + _externalNameRecords = new ExternalNameRecord[0]; + _crnBlocks = new CRNBlock[0]; + } + + /** + * Create a new block for registering add-in functions + * + * @see org.apache.poi.hssf.model.LinkTable#addNameXPtg(String) + */ + public ExternalBookBlock() { + _externalBookRecord = SupBookRecord.createAddInFunctions(); + _externalNameRecords = new ExternalNameRecord[0]; + _crnBlocks = new CRNBlock[0]; + } public SupBookRecord getExternalBookRecord() { - return _externalBookRecord; + return _externalBookRecord; } public String getNameText(int definedNameIndex) { @@ -382,31 +391,68 @@ final class LinkTable { }; } } + + private int getExternalWorkbookIndex(String workbookName) { + for (int i=0; i<_externalBookBlocks.length; i++) { + SupBookRecord ebr = _externalBookBlocks[i].getExternalBookRecord(); + if (!ebr.isExternalReferences()) { + continue; + } + if (workbookName.equals(ebr.getURL())) { // not sure if 'equals()' works when url has a directory + return i; + } + } + return -1; + } + + public int linkExternalWorkbook(String name, Workbook externalWorkbook) { + int extBookIndex = getExternalWorkbookIndex(name); + if (extBookIndex != -1) { + // Already linked! + return extBookIndex; + } + + // Create a new SupBookRecord + String[] sheetNames = new String[externalWorkbook.getNumberOfSheets()]; + for (int sn=0; sn<sheetNames.length; sn++) { + sheetNames[sn] = externalWorkbook.getSheetName(sn); + } + String url = "\000" + name; + ExternalBookBlock block = new ExternalBookBlock(url, sheetNames); + + // Add it into the list + records + extBookIndex = extendExternalBookBlocks(block); + + // add the created SupBookRecord before ExternSheetRecord + int idx = findFirstRecordLocBySid(ExternSheetRecord.sid); + if (idx == -1) { + idx = _workbookRecordList.size(); + } + _workbookRecordList.add(idx, block.getExternalBookRecord()); + + // Setup links for the sheets + for (int sn=0; sn<sheetNames.length; sn++) { + _externSheetRecord.addRef(extBookIndex, sn, sn); + } + + // Report where it went + return extBookIndex; + } public int getExternalSheetIndex(String workbookName, String firstSheetName, String lastSheetName) { - SupBookRecord ebrTarget = null; - int externalBookIndex = -1; - for (int i=0; i<_externalBookBlocks.length; i++) { - SupBookRecord ebr = _externalBookBlocks[i].getExternalBookRecord(); - if (!ebr.isExternalReferences()) { - continue; - } - if (workbookName.equals(ebr.getURL())) { // not sure if 'equals()' works when url has a directory - ebrTarget = ebr; - externalBookIndex = i; - break; - } - } - if (ebrTarget == null) { - throw new RuntimeException("No external workbook with name '" + workbookName + "'"); - } + int externalBookIndex = getExternalWorkbookIndex(workbookName); + if (externalBookIndex == -1) { + throw new RuntimeException("No external workbook with name '" + workbookName + "'"); + } + SupBookRecord ebrTarget = _externalBookBlocks[externalBookIndex].getExternalBookRecord(); + int firstSheetIndex = getSheetIndex(ebrTarget.getSheetNames(), firstSheetName); int lastSheetIndex = getSheetIndex(ebrTarget.getSheetNames(), lastSheetName); + // Find or add the external sheet record definition for this int result = _externSheetRecord.getRefIxForSheet(externalBookIndex, firstSheetIndex, lastSheetIndex); if (result < 0) { - throw new RuntimeException("ExternSheetRecord does not contain combination (" - + externalBookIndex + ", " + firstSheetIndex + ", " + lastSheetIndex + ")"); + result = _externSheetRecord.addRef(externalBookIndex, firstSheetIndex, lastSheetIndex); } return result; } @@ -580,13 +626,7 @@ final class LinkTable { // An ExternalBlock for Add-In functions was not found. Create a new one. if (extBlock == null) { extBlock = new ExternalBookBlock(); - - ExternalBookBlock[] tmp = new ExternalBookBlock[_externalBookBlocks.length + 1]; - System.arraycopy(_externalBookBlocks, 0, tmp, 0, _externalBookBlocks.length); - tmp[tmp.length - 1] = extBlock; - _externalBookBlocks = tmp; - - extBlockIndex = _externalBookBlocks.length - 1; + extBlockIndex = extendExternalBookBlocks(extBlock); // add the created SupBookRecord before ExternSheetRecord int idx = findFirstRecordLocBySid(ExternSheetRecord.sid); @@ -620,6 +660,14 @@ final class LinkTable { int ix = _externSheetRecord.getRefIxForSheet(extBlockIndex, fakeSheetIdx, fakeSheetIdx); return new NameXPtg(ix, nameIndex); } + private int extendExternalBookBlocks(ExternalBookBlock newBlock) { + ExternalBookBlock[] tmp = new ExternalBookBlock[_externalBookBlocks.length + 1]; + System.arraycopy(_externalBookBlocks, 0, tmp, 0, _externalBookBlocks.length); + tmp[tmp.length - 1] = newBlock; + _externalBookBlocks = tmp; + + return (_externalBookBlocks.length - 1); + } private int findRefIndexFromExtBookIndex(int extBookIndex) { return _externSheetRecord.findRefIndexFromExtBookIndex(extBookIndex); diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index 3f3bedfd01..da741d4944 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -79,6 +79,7 @@ import org.apache.poi.ss.formula.udf.AggregatingUDFFinder; import org.apache.poi.ss.formula.udf.IndexedUDFFinder; import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; +import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.WorkbookUtil; import org.apache.poi.util.Configurator; @@ -1851,6 +1852,19 @@ public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss } /** + * Adds the LinkTable records required to allow formulas referencing + * the specified external workbook to be added to this one. Allows + * formulas such as "[MyOtherWorkbook]Sheet3!$A$5" to be added to the + * file, for workbooks not already referenced. + * + * @param name The name the workbook will be referenced as in formulas + * @param workbook The open workbook to fetch the link required information from + */ + public int linkExternalWorkbook(String name, Workbook workbook) { + return this.workbook.linkExternalWorkbook(name, workbook); + } + + /** * Is the workbook protected with a password (not encrypted)? */ public boolean isWriteProtected() { diff --git a/src/java/org/apache/poi/ss/usermodel/Workbook.java b/src/java/org/apache/poi/ss/usermodel/Workbook.java index 0ed1a6605d..9d0566c13f 100644 --- a/src/java/org/apache/poi/ss/usermodel/Workbook.java +++ b/src/java/org/apache/poi/ss/usermodel/Workbook.java @@ -407,6 +407,22 @@ public interface Workbook extends Closeable { */ void removeName(String name); + /** + * Adds the linking required to allow formulas referencing + * the specified external workbook to be added to this one. + * <p>In order for formulas such as "[MyOtherWorkbook]Sheet3!$A$5" + * to be added to the file, some linking information must first + * be recorded. Once a given external workbook has been linked, + * then formulas using it can added. Each workbook needs linking + * only once. + * <p>This linking only applies for writing formulas. To link things + * for evaluation, see {@link FormulaEvaluator#setupReferencedWorkbooks(java.util.Map)} + * + * @param name The name the workbook will be referenced as in formulas + * @param workbook The open workbook to fetch the link required information from + */ + int linkExternalWorkbook(String name, Workbook workbook); + /** * Sets the printarea for the sheet provided * <p> diff --git a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java index 112c1ff814..ce56b6a6d6 100644 --- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java @@ -1174,6 +1174,20 @@ public class SXSSFWorkbook implements Workbook { _wb.setSheetHidden(sheetIx,hidden); } + + /** + * Adds the LinkTable records required to allow formulas referencing + * the specified external workbook to be added to this one. Allows + * formulas such as "[MyOtherWorkbook]Sheet3!$A$5" to be added to the + * file, for workbooks not already referenced. + * + * @param name The name the workbook will be referenced as in formulas + * @param workbook The open workbook to fetch the link required information from + */ + public int linkExternalWorkbook(String name, Workbook workbook) { + throw new RuntimeException("NotImplemented"); + } + /** * Register a new toolpack in this workbook. * 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 a51b40675f..7164534d3b 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -1704,6 +1704,18 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X return mapInfo; } + /** + * Adds the LinkTable records required to allow formulas 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 referenced. + * + * @param name The name the workbook will be referenced as in formulas + * @param workbook The open workbook to fetch the link required information from + */ + public int linkExternalWorkbook(String name, Workbook workbook) { + throw new RuntimeException("NotImplemented"); + } /** * Specifies a boolean value that indicates whether structure of workbook is locked. <br/> diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java index 22e7ca5493..2c2fe81da4 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java @@ -231,14 +231,35 @@ public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator { assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCachedFormulaResultType()); assertEquals(36.90, cell.getNumericCellValue(), 0.0001); -/* - // Now add a formula that refers to yet another (different) workbook - cell = wb.getSheetAt(0).getRow(1).createCell(42); - cell.setCellFormula("[alt.xls]Sheet1!$A$1"); - // Check it - TODO Is this correct? Or should it become [2]Sheet1!$A$1 ? - assertEquals("[alt.xls]Sheet1!$A$1", cell.getCellFormula()); + // Add a formula that refers to one of the existing external workbooks + cell = wb.getSheetAt(0).getRow(1).createCell(40); + cell.setCellFormula("Cost*[XRefCalcData.xls]MarkupSheet!$B$1"); + // Check is was stored correctly + assertEquals("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.getCellFormula()); + + // Check it evaluates correctly + eval.evaluateFormulaCell(cell); + assertEquals(24.60*1.8, cell.getNumericCellValue()); + + + // Try to add a formula for a new external workbook, won't be allowed to start + try { + cell = wb.getSheetAt(0).getRow(1).createCell(42); + cell.setCellFormula("[alt.xls]Sheet0!$A$1"); + fail("New workbook not linked, shouldn't be able to add"); + } catch(Exception e) {} + + // Link our new workbook + HSSFWorkbook alt = new HSSFWorkbook(); + alt.createSheet().createRow(0).createCell(0).setCellValue("In another workbook"); + wb.linkExternalWorkbook("alt.xls", alt); + + // Now add a formula that refers to our new workbook + cell.setCellFormula("[alt.xls]Sheet0!$A$1"); + assertEquals("[alt.xls]Sheet0!$A$1", cell.getCellFormula()); + // Evaluate it, without a link to that workbook try { eval.evaluate(cell); @@ -246,8 +267,6 @@ public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator { } catch(Exception e) {} // Add a link, check it does - HSSFWorkbook alt = new HSSFWorkbook(); - alt.createSheet().createRow(0).createCell(0).setCellValue("In another workbook"); HSSFFormulaEvaluator.setupEnvironment( new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" }, new HSSFFormulaEvaluator[] { @@ -256,10 +275,33 @@ public final class TestHSSFFormulaEvaluator extends BaseTestFormulaEvaluator { new HSSFFormulaEvaluator(alt) } ); + eval.evaluateFormulaCell(cell); + assertEquals("In another workbook", cell.getStringCellValue()); + + + // Save and re-load + wb = HSSFTestDataSamples.writeOutAndReadBack(wb); + eval = new HSSFFormulaEvaluator(wb); + HSSFFormulaEvaluator.setupEnvironment( + new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" }, + new HSSFFormulaEvaluator[] { + eval, + new HSSFFormulaEvaluator(wbData), + new HSSFFormulaEvaluator(alt) + } + ); + + // Check the one referring to the previously existing workbook behaves + cell = wb.getSheetAt(0).getRow(1).getCell(40); + assertEquals("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.getCellFormula()); + eval.evaluateFormulaCell(cell); + assertEquals(24.60*1.8, cell.getNumericCellValue()); - eval.evaluate(cell); + // Now check the newly added reference + cell = wb.getSheetAt(0).getRow(1).getCell(42); + assertEquals("[alt.xls]Sheet0!$A$1", cell.getCellFormula()); + eval.evaluateFormulaCell(cell); assertEquals("In another workbook", cell.getStringCellValue()); -*/ } public void testSharedFormulas(){ |