Author: nick
Date: Tue Nov 4 21:35:01 2014
New Revision: 1636742
URL: http://svn.apache.org/r1636742
Log:
Partial HSSF support for adding new external workbook formula references for
#57184
Modified:
poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
Modified: poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java?rev=1636742&r1=1636741&r2=1636742&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java
(original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/InternalWorkbook.java Tue Nov
4 21:35:01 2014
@@ -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
Modified: poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java?rev=1636742&r1=1636741&r2=1636742&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/LinkTable.java Tue Nov 4
21:35:01 2014
@@ -37,6 +37,7 @@ import org.apache.poi.ss.formula.ptg.Err
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);
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java?rev=1636742&r1=1636741&r2=1636742&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
(original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java Tue Nov
4 21:35:01 2014
@@ -79,6 +79,7 @@ import org.apache.poi.ss.formula.udf.Agg
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
}
/**
+ * 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() {
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java?rev=1636742&r1=1636741&r2=1636742&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java Tue Nov 4
21:35:01 2014
@@ -407,6 +407,22 @@ public interface Workbook extends Closea
*/
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>
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java?rev=1636742&r1=1636741&r2=1636742&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java
(original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java
Tue Nov 4 21:35:01 2014
@@ -1174,6 +1174,20 @@ public class SXSSFWorkbook implements Wo
{
_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.
*
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java?rev=1636742&r1=1636741&r2=1636742&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
(original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
Tue Nov 4 21:35:01 2014
@@ -1704,6 +1704,18 @@ public class XSSFWorkbook extends POIXML
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/>
Modified:
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java?rev=1636742&r1=1636741&r2=1636742&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
Tue Nov 4 21:35:01 2014
@@ -231,14 +231,35 @@ public final class TestHSSFFormulaEvalua
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 TestHSSFFormulaEvalua
} 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 TestHSSFFormulaEvalua
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(){
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
