You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2008/11/13 21:22:17 UTC
svn commit: r713811 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/usermodel/ java/org/apache/poi/ss/formula/
ooxml/java/org/apache/poi/xssf/usermodel/
testcases/org/apache/poi/ss/formula/
Author: josh
Date: Thu Nov 13 12:22:17 2008
New Revision: 713811
URL: http://svn.apache.org/viewvc?rev=713811&view=rev
Log:
Changes to formula evaluation allowing for reduced memory usage
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
poi/trunk/src/java/org/apache/poi/ss/formula/FormulaCellCacheEntry.java
poi/trunk/src/java/org/apache/poi/ss/formula/FormulaUsedBlankCellSet.java
poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/WorkbookEvaluatorTestHelper.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=713811&r1=713810&r2=713811&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Thu Nov 13 12:22:17 2008
@@ -37,6 +37,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.5-beta4" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">Changes to formula evaluation allowing for reduced memory usage</action>
<action dev="POI-DEVELOPERS" type="fix">45290 - Support odd files where the POIFS header block comes after the data blocks, and is on the data blocks list</header>
<action dev="POI-DEVELOPERS" type="fix">46184 - More odd escaped date formats</action>
<action dev="POI-DEVELOPERS" type="add">Include the sheet number in the output of XLS2CSVmra</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=713811&r1=713810&r2=713811&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Thu Nov 13 12:22:17 2008
@@ -34,6 +34,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.5-beta4" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">Changes to formula evaluation allowing for reduced memory usage</action>
<action dev="POI-DEVELOPERS" type="fix">45290 - Support odd files where the POIFS header block comes after the data blocks, and is on the data blocks list</header>
<action dev="POI-DEVELOPERS" type="fix">46184 - More odd escaped date formats</action>
<action dev="POI-DEVELOPERS" type="add">Include the sheet number in the output of XLS2CSVmra</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java?rev=713811&r1=713810&r2=713811&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java Thu Nov 13 12:22:17 2008
@@ -25,6 +25,7 @@
import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment;
+import org.apache.poi.ss.formula.IStabilityClassifier;
import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
@@ -42,266 +43,273 @@
*/
public class HSSFFormulaEvaluator implements FormulaEvaluator {
- private WorkbookEvaluator _bookEvaluator;
+ private WorkbookEvaluator _bookEvaluator;
- /**
- * @deprecated (Sep 2008) HSSFSheet parameter is ignored
- */
- public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) {
- this(workbook);
- if (false) {
- sheet.toString(); // suppress unused parameter compiler warning
- }
- }
- public HSSFFormulaEvaluator(HSSFWorkbook workbook) {
- _bookEvaluator = new WorkbookEvaluator(HSSFEvaluationWorkbook.create(workbook));
- }
-
- /**
- * Coordinates several formula evaluators together so that formulas that involve external
- * references can be evaluated.
- * @param workbookNames the simple file names used to identify the workbooks in formulas
- * with external links (for example "MyData.xls" as used in a formula "[MyData.xls]Sheet1!A1")
- * @param evaluators all evaluators for the full set of workbooks required by the formulas.
- */
- public static void setupEnvironment(String[] workbookNames, HSSFFormulaEvaluator[] evaluators) {
- WorkbookEvaluator[] wbEvals = new WorkbookEvaluator[evaluators.length];
- for (int i = 0; i < wbEvals.length; i++) {
- wbEvals[i] = evaluators[i]._bookEvaluator;
- }
- CollaboratingWorkbooksEnvironment.setup(workbookNames, wbEvals);
- }
-
- /**
- * Does nothing
- * @deprecated (Aug 2008) - not needed, since the current row can be derived from the cell
- */
- public void setCurrentRow(HSSFRow row) {
- // do nothing
- if (false) {
- row.getClass(); // suppress unused parameter compiler warning
- }
- }
-
- /**
- * Should be called whenever there are major changes (e.g. moving sheets) to input cells
- * in the evaluated workbook. If performance is not critical, a single call to this method
- * may be used instead of many specific calls to the notify~ methods.
- *
- * Failure to call this method after changing cell values will cause incorrect behaviour
- * of the evaluate~ methods of this class
- */
- public void clearAllCachedResultValues() {
- _bookEvaluator.clearAllCachedResultValues();
- }
- /**
- * Should be called to tell the cell value cache that the specified (value or formula) cell
- * has changed.
- * Failure to call this method after changing cell values will cause incorrect behaviour
- * of the evaluate~ methods of this class
- */
- public void notifyUpdateCell(HSSFCell cell) {
- _bookEvaluator.notifyUpdateCell(new HSSFEvaluationCell(cell));
- }
- /**
- * Should be called to tell the cell value cache that the specified cell has just been
- * deleted.
- * Failure to call this method after changing cell values will cause incorrect behaviour
- * of the evaluate~ methods of this class
- */
- public void notifyDeleteCell(HSSFCell cell) {
- _bookEvaluator.notifyDeleteCell(new HSSFEvaluationCell(cell));
- }
- public void notifyDeleteCell(Cell cell) {
- _bookEvaluator.notifyDeleteCell(new HSSFEvaluationCell((HSSFCell)cell));
- }
-
- /**
- * Should be called to tell the cell value cache that the specified (value or formula) cell
- * has changed.
- * Failure to call this method after changing cell values will cause incorrect behaviour
- * of the evaluate~ methods of this class
- */
- public void notifySetFormula(Cell cell) {
- _bookEvaluator.notifyUpdateCell(new HSSFEvaluationCell((HSSFCell)cell));
- }
-
- /**
- * If cell contains a formula, the formula is evaluated and returned,
- * else the CellValue simply copies the appropriate cell value from
- * the cell and also its cell type. This method should be preferred over
- * evaluateInCell() when the call should not modify the contents of the
- * original cell.
- *
- * @param cell may be <code>null</code> signifying that the cell is not present (or blank)
- * @return <code>null</code> if the supplied cell is <code>null</code> or blank
- */
- public CellValue evaluate(Cell cell) {
- if (cell == null) {
- return null;
- }
-
- switch (cell.getCellType()) {
- case HSSFCell.CELL_TYPE_BOOLEAN:
- return CellValue.valueOf(cell.getBooleanCellValue());
- case HSSFCell.CELL_TYPE_ERROR:
- return CellValue.getError(cell.getErrorCellValue());
- case HSSFCell.CELL_TYPE_FORMULA:
- return evaluateFormulaCellValue(cell);
- case HSSFCell.CELL_TYPE_NUMERIC:
- return new CellValue(cell.getNumericCellValue());
- case HSSFCell.CELL_TYPE_STRING:
- return new CellValue(cell.getRichStringCellValue().getString());
- case HSSFCell.CELL_TYPE_BLANK:
- return null;
- }
- throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")");
- }
-
-
- /**
- * If cell contains formula, it evaluates the formula, and saves the result of the formula. The
- * cell remains as a formula cell. If the cell does not contain formula, this method returns -1
- * and leaves the cell unchanged.
- *
- * Note that the type of the <em>formula result</em> is returned, so you know what kind of
- * cached formula result is also stored with the formula.
- * <pre>
- * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
- * </pre>
- * Be aware that your cell will hold both the formula, and the result. If you want the cell
- * replaced with the result of the formula, use {@link #evaluateInCell(org.apache.poi.ss.usermodel.Cell)}
- * @param cell The cell to evaluate
- * @return -1 for non-formula cells, or the type of the <em>formula result</em>
- */
- public int evaluateFormulaCell(Cell cell) {
- if (cell == null || cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
- return -1;
- }
- CellValue cv = evaluateFormulaCellValue(cell);
- // cell remains a formula cell, but the cached value is changed
- setCellValue(cell, cv);
- return cv.getCellType();
- }
-
- /**
- * If cell contains formula, it evaluates the formula, and
- * puts the formula result back into the cell, in place
- * of the old formula.
- * Else if cell does not contain formula, this method leaves
- * the cell unchanged.
- * Note that the same instance of HSSFCell is returned to
- * allow chained calls like:
- * <pre>
- * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
- * </pre>
- * Be aware that your cell value will be changed to hold the
- * result of the formula. If you simply want the formula
- * value computed for you, use {@link #evaluateFormulaCell(org.apache.poi.ss.usermodel.Cell)}}
- * @param cell
- */
- public HSSFCell evaluateInCell(Cell cell) {
- if (cell == null) {
- return null;
- }
- HSSFCell result = (HSSFCell) cell;
- if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
- CellValue cv = evaluateFormulaCellValue(cell);
- setCellType(cell, cv); // cell will no longer be a formula cell
- setCellValue(cell, cv);
- }
- return result;
- }
- private static void setCellType(Cell cell, CellValue cv) {
- int cellType = cv.getCellType();
- switch (cellType) {
- case HSSFCell.CELL_TYPE_BOOLEAN:
- case HSSFCell.CELL_TYPE_ERROR:
- case HSSFCell.CELL_TYPE_NUMERIC:
- case HSSFCell.CELL_TYPE_STRING:
- cell.setCellType(cellType);
- return;
- case HSSFCell.CELL_TYPE_BLANK:
- // never happens - blanks eventually get translated to zero
- case HSSFCell.CELL_TYPE_FORMULA:
- // this will never happen, we have already evaluated the formula
- }
- throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
- }
-
- private static void setCellValue(Cell cell, CellValue cv) {
- int cellType = cv.getCellType();
- switch (cellType) {
- case HSSFCell.CELL_TYPE_BOOLEAN:
- cell.setCellValue(cv.getBooleanValue());
- break;
- case HSSFCell.CELL_TYPE_ERROR:
- cell.setCellErrorValue(cv.getErrorValue());
- break;
- case HSSFCell.CELL_TYPE_NUMERIC:
- cell.setCellValue(cv.getNumberValue());
- break;
- case HSSFCell.CELL_TYPE_STRING:
- cell.setCellValue(new HSSFRichTextString(cv.getStringValue()));
- break;
- case HSSFCell.CELL_TYPE_BLANK:
- // never happens - blanks eventually get translated to zero
- case HSSFCell.CELL_TYPE_FORMULA:
- // this will never happen, we have already evaluated the formula
- default:
- throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
- }
- }
-
- /**
- * Loops over all cells in all sheets of the supplied
- * workbook.
- * For cells that contain formulas, their formulas are
- * evaluated, and the results are saved. These cells
- * remain as formula cells.
- * For cells that do not contain formulas, no changes
- * are made.
- * This is a helpful wrapper around looping over all
- * cells, and calling evaluateFormulaCell on each one.
- */
- public static void evaluateAllFormulaCells(HSSFWorkbook wb) {
- HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
- for(int i=0; i<wb.getNumberOfSheets(); i++) {
- HSSFSheet sheet = wb.getSheetAt(i);
-
- for (Iterator rit = sheet.rowIterator(); rit.hasNext();) {
- HSSFRow r = (HSSFRow)rit.next();
-
- for (Iterator cit = r.cellIterator(); cit.hasNext();) {
- HSSFCell c = (HSSFCell)cit.next();
- if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
- evaluator.evaluateFormulaCell(c);
- }
- }
- }
- }
-
- /**
- * Returns a CellValue wrapper around the supplied ValueEval instance.
- * @param eval
- */
- private CellValue evaluateFormulaCellValue(Cell cell) {
- ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell));
- if (eval instanceof NumberEval) {
- NumberEval ne = (NumberEval) eval;
- return new CellValue(ne.getNumberValue());
- }
- if (eval instanceof BoolEval) {
- BoolEval be = (BoolEval) eval;
- return CellValue.valueOf(be.getBooleanValue());
- }
- if (eval instanceof StringEval) {
- StringEval ne = (StringEval) eval;
- return new CellValue(ne.getStringValue());
- }
- if (eval instanceof ErrorEval) {
- return CellValue.getError(((ErrorEval)eval).getErrorCode());
- }
- throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
- }
+ /**
+ * @deprecated (Sep 2008) HSSFSheet parameter is ignored
+ */
+ public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) {
+ this(workbook);
+ if (false) {
+ sheet.toString(); // suppress unused parameter compiler warning
+ }
+ }
+ public HSSFFormulaEvaluator(HSSFWorkbook workbook) {
+ this(workbook, null);
+ }
+ /**
+ * @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>
+ * for the (conservative) assumption that any cell may have its definition changed after
+ * evaluation begins.
+ */
+ public HSSFFormulaEvaluator(HSSFWorkbook workbook, IStabilityClassifier stabilityClassifier) {
+ _bookEvaluator = new WorkbookEvaluator(HSSFEvaluationWorkbook.create(workbook), stabilityClassifier);
+ }
+
+ /**
+ * Coordinates several formula evaluators together so that formulas that involve external
+ * references can be evaluated.
+ * @param workbookNames the simple file names used to identify the workbooks in formulas
+ * with external links (for example "MyData.xls" as used in a formula "[MyData.xls]Sheet1!A1")
+ * @param evaluators all evaluators for the full set of workbooks required by the formulas.
+ */
+ public static void setupEnvironment(String[] workbookNames, HSSFFormulaEvaluator[] evaluators) {
+ WorkbookEvaluator[] wbEvals = new WorkbookEvaluator[evaluators.length];
+ for (int i = 0; i < wbEvals.length; i++) {
+ wbEvals[i] = evaluators[i]._bookEvaluator;
+ }
+ CollaboratingWorkbooksEnvironment.setup(workbookNames, wbEvals);
+ }
+
+ /**
+ * Does nothing
+ * @deprecated (Aug 2008) - not needed, since the current row can be derived from the cell
+ */
+ public void setCurrentRow(HSSFRow row) {
+ // do nothing
+ if (false) {
+ row.getClass(); // suppress unused parameter compiler warning
+ }
+ }
+
+ /**
+ * Should be called whenever there are major changes (e.g. moving sheets) to input cells
+ * in the evaluated workbook. If performance is not critical, a single call to this method
+ * may be used instead of many specific calls to the notify~ methods.
+ *
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void clearAllCachedResultValues() {
+ _bookEvaluator.clearAllCachedResultValues();
+ }
+ /**
+ * Should be called to tell the cell value cache that the specified (value or formula) cell
+ * has changed.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void notifyUpdateCell(HSSFCell cell) {
+ _bookEvaluator.notifyUpdateCell(new HSSFEvaluationCell(cell));
+ }
+ /**
+ * Should be called to tell the cell value cache that the specified cell has just been
+ * deleted.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void notifyDeleteCell(HSSFCell cell) {
+ _bookEvaluator.notifyDeleteCell(new HSSFEvaluationCell(cell));
+ }
+ public void notifyDeleteCell(Cell cell) {
+ _bookEvaluator.notifyDeleteCell(new HSSFEvaluationCell((HSSFCell)cell));
+ }
+
+ /**
+ * Should be called to tell the cell value cache that the specified (value or formula) cell
+ * has changed.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void notifySetFormula(Cell cell) {
+ _bookEvaluator.notifyUpdateCell(new HSSFEvaluationCell((HSSFCell)cell));
+ }
+
+ /**
+ * If cell contains a formula, the formula is evaluated and returned,
+ * else the CellValue simply copies the appropriate cell value from
+ * the cell and also its cell type. This method should be preferred over
+ * evaluateInCell() when the call should not modify the contents of the
+ * original cell.
+ *
+ * @param cell may be <code>null</code> signifying that the cell is not present (or blank)
+ * @return <code>null</code> if the supplied cell is <code>null</code> or blank
+ */
+ public CellValue evaluate(Cell cell) {
+ if (cell == null) {
+ return null;
+ }
+
+ switch (cell.getCellType()) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ return CellValue.valueOf(cell.getBooleanCellValue());
+ case HSSFCell.CELL_TYPE_ERROR:
+ return CellValue.getError(cell.getErrorCellValue());
+ case HSSFCell.CELL_TYPE_FORMULA:
+ return evaluateFormulaCellValue(cell);
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ return new CellValue(cell.getNumericCellValue());
+ case HSSFCell.CELL_TYPE_STRING:
+ return new CellValue(cell.getRichStringCellValue().getString());
+ case HSSFCell.CELL_TYPE_BLANK:
+ return null;
+ }
+ throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")");
+ }
+
+
+ /**
+ * If cell contains formula, it evaluates the formula, and saves the result of the formula. The
+ * cell remains as a formula cell. If the cell does not contain formula, this method returns -1
+ * and leaves the cell unchanged.
+ *
+ * Note that the type of the <em>formula result</em> is returned, so you know what kind of
+ * cached formula result is also stored with the formula.
+ * <pre>
+ * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
+ * </pre>
+ * Be aware that your cell will hold both the formula, and the result. If you want the cell
+ * replaced with the result of the formula, use {@link #evaluateInCell(org.apache.poi.ss.usermodel.Cell)}
+ * @param cell The cell to evaluate
+ * @return -1 for non-formula cells, or the type of the <em>formula result</em>
+ */
+ public int evaluateFormulaCell(Cell cell) {
+ if (cell == null || cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
+ return -1;
+ }
+ CellValue cv = evaluateFormulaCellValue(cell);
+ // cell remains a formula cell, but the cached value is changed
+ setCellValue(cell, cv);
+ return cv.getCellType();
+ }
+
+ /**
+ * If cell contains formula, it evaluates the formula, and
+ * puts the formula result back into the cell, in place
+ * of the old formula.
+ * Else if cell does not contain formula, this method leaves
+ * the cell unchanged.
+ * Note that the same instance of HSSFCell is returned to
+ * allow chained calls like:
+ * <pre>
+ * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
+ * </pre>
+ * Be aware that your cell value will be changed to hold the
+ * result of the formula. If you simply want the formula
+ * value computed for you, use {@link #evaluateFormulaCell(Cell)}}
+ */
+ public HSSFCell evaluateInCell(Cell cell) {
+ if (cell == null) {
+ return null;
+ }
+ HSSFCell result = (HSSFCell) cell;
+ if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
+ CellValue cv = evaluateFormulaCellValue(cell);
+ setCellType(cell, cv); // cell will no longer be a formula cell
+ setCellValue(cell, cv);
+ }
+ return result;
+ }
+ private static void setCellType(Cell cell, CellValue cv) {
+ int cellType = cv.getCellType();
+ switch (cellType) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ case HSSFCell.CELL_TYPE_ERROR:
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ case HSSFCell.CELL_TYPE_STRING:
+ cell.setCellType(cellType);
+ return;
+ case HSSFCell.CELL_TYPE_BLANK:
+ // never happens - blanks eventually get translated to zero
+ case HSSFCell.CELL_TYPE_FORMULA:
+ // this will never happen, we have already evaluated the formula
+ }
+ throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
+ }
+
+ private static void setCellValue(Cell cell, CellValue cv) {
+ int cellType = cv.getCellType();
+ switch (cellType) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ cell.setCellValue(cv.getBooleanValue());
+ break;
+ case HSSFCell.CELL_TYPE_ERROR:
+ cell.setCellErrorValue(cv.getErrorValue());
+ break;
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ cell.setCellValue(cv.getNumberValue());
+ break;
+ case HSSFCell.CELL_TYPE_STRING:
+ cell.setCellValue(new HSSFRichTextString(cv.getStringValue()));
+ break;
+ case HSSFCell.CELL_TYPE_BLANK:
+ // never happens - blanks eventually get translated to zero
+ case HSSFCell.CELL_TYPE_FORMULA:
+ // this will never happen, we have already evaluated the formula
+ default:
+ throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
+ }
+ }
+
+ /**
+ * Loops over all cells in all sheets of the supplied
+ * workbook.
+ * For cells that contain formulas, their formulas are
+ * evaluated, and the results are saved. These cells
+ * remain as formula cells.
+ * For cells that do not contain formulas, no changes
+ * are made.
+ * This is a helpful wrapper around looping over all
+ * cells, and calling evaluateFormulaCell on each one.
+ */
+ public static void evaluateAllFormulaCells(HSSFWorkbook wb) {
+ HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
+ for(int i=0; i<wb.getNumberOfSheets(); i++) {
+ HSSFSheet sheet = wb.getSheetAt(i);
+
+ for (Iterator rit = sheet.rowIterator(); rit.hasNext();) {
+ HSSFRow r = (HSSFRow)rit.next();
+
+ for (Iterator cit = r.cellIterator(); cit.hasNext();) {
+ HSSFCell c = (HSSFCell)cit.next();
+ if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
+ evaluator.evaluateFormulaCell(c);
+ }
+ }
+ }
+ }
+
+ /**
+ * Returns a CellValue wrapper around the supplied ValueEval instance.
+ * @param eval
+ */
+ private CellValue evaluateFormulaCellValue(Cell cell) {
+ ValueEval eval = _bookEvaluator.evaluate(new HSSFEvaluationCell((HSSFCell)cell));
+ if (eval instanceof NumberEval) {
+ NumberEval ne = (NumberEval) eval;
+ return new CellValue(ne.getNumberValue());
+ }
+ if (eval instanceof BoolEval) {
+ BoolEval be = (BoolEval) eval;
+ return CellValue.valueOf(be.getBooleanValue());
+ }
+ if (eval instanceof StringEval) {
+ StringEval ne = (StringEval) eval;
+ return new CellValue(ne.getStringValue());
+ }
+ if (eval instanceof ErrorEval) {
+ return CellValue.getError(((ErrorEval)eval).getErrorCode());
+ }
+ throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/FormulaCellCacheEntry.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaCellCacheEntry.java?rev=713811&r1=713810&r2=713811&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/FormulaCellCacheEntry.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/FormulaCellCacheEntry.java Thu Nov 13 12:22:17 2008
@@ -45,6 +45,15 @@
public FormulaCellCacheEntry() {
}
+
+ public boolean isInputSensitive() {
+ if (_sensitiveInputCells != null) {
+ if (_sensitiveInputCells.length > 0 ) {
+ return true;
+ }
+ }
+ return _usedBlankCellGroup == null ? false : !_usedBlankCellGroup.isEmpty();
+ }
public void setSensitiveInputCells(CellCacheEntry[] sensitiveInputCells) {
// need to tell all cells that were previously used, but no longer are,
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/FormulaUsedBlankCellSet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaUsedBlankCellSet.java?rev=713811&r1=713810&r2=713811&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/FormulaUsedBlankCellSet.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/FormulaUsedBlankCellSet.java Thu Nov 13 12:22:17 2008
@@ -186,4 +186,8 @@
}
return bcsg.containsCell(rowIndex, columnIndex);
}
+
+ public boolean isEmpty() {
+ return _sheetGroupsByBookSheet.isEmpty();
+ }
}
Added: poi/trunk/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java?rev=713811&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java Thu Nov 13 12:22:17 2008
@@ -0,0 +1,84 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula;
+
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+
+/**
+ * Used to help optimise cell evaluation result caching by allowing applications to specify which
+ * parts of a workbook are <em>final</em>.<br/>
+ * The term <b>final</b> is introduced here to denote immutability or 'having constant definition'.
+ * This classification refers to potential actions (on the evaluated workbook) by the evaluating
+ * application. It does not refer to operations performed by the evaluator ({@link
+ * WorkbookEvaluator}).<br/>
+ * <br/>
+ * <b>General guidelines</b>:
+ * <ul>
+ * <li>a plain value cell can be marked as 'final' if it will not be changed after the first call
+ * to {@link WorkbookEvaluator#evaluate(EvaluationCell)}.
+ * </li>
+ * <li>a formula cell can be marked as 'final' if its formula will not be changed after the first
+ * call to {@link WorkbookEvaluator#evaluate(EvaluationCell)}. This remains true even if changes
+ * in dependent values may cause the evaluated value to change.</li>
+ * <li>plain value cells should be marked as 'not final' if their plain value value may change.
+ * </li>
+ * <li>formula cells should be marked as 'not final' if their formula definition may change.</li>
+ * <li>cells which may switch between plain value and formula should also be marked as 'not final'.
+ * </li>
+ * </ul>
+ * <b>Notes</b>:
+ * <ul>
+ * <li>If none of the spreadsheet cells is expected to have its definition changed after evaluation
+ * begins, every cell can be marked as 'final'. This is the most efficient / least resource
+ * intensive option.</li>
+ * <li>To retain freedom to change any cell definition at any time, an application may classify all
+ * cells as 'not final'. This freedom comes at the expense of greater memory consumption.</li>
+ * <li>For the purpose of these classifications, setting the cached formula result of a cell (for
+ * example in {@link HSSFFormulaEvaluator#evaluateFormulaCell(org.apache.poi.ss.usermodel.Cell)})
+ * does not constitute changing the definition of the cell.</li>
+ * <li>Updating cells which have been classified as 'final' will cause the evaluator to behave
+ * unpredictably (typically ignoring the update).</li>
+ * </ul>
+ *
+ * @author Josh Micich
+ */
+public interface IStabilityClassifier {
+
+ /**
+ * Convenience implementation for situations where all cell definitions remain fixed after
+ * evaluation begins.
+ */
+ IStabilityClassifier TOTALLY_IMMUTABLE = new IStabilityClassifier() {
+ public boolean isCellFinal(int sheetIndex, int rowIndex, int columnIndex) {
+ return true;
+ }
+ };
+
+ /**
+ * Checks if a cell's value(/formula) is fixed - in other words - not expected to be modified
+ * between calls to the evaluator. (Note - this is an independent concept from whether a
+ * formula cell's evaluated value may change during successive calls to the evaluator).
+ *
+ * @param sheetIndex zero based index into workbook sheet list
+ * @param rowIndex zero based row index of cell
+ * @param columnIndex zero based column index of cell
+ * @return <code>false</code> if the evaluating application may need to modify the specified
+ * cell between calls to the evaluator.
+ */
+ boolean isCellFinal(int sheetIndex, int rowIndex, int columnIndex);
+}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java?rev=713811&r1=713810&r2=713811&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java Thu Nov 13 12:22:17 2008
@@ -82,19 +82,22 @@
private int _workbookIx;
private final IEvaluationListener _evaluationListener;
- private final Map _sheetIndexesBySheet;
+ private final Map<EvaluationSheet, Integer> _sheetIndexesBySheet;
private CollaboratingWorkbooksEnvironment _collaboratingWorkbookEnvironment;
+ private final IStabilityClassifier _stabilityClassifier;
- public WorkbookEvaluator(EvaluationWorkbook workbook) {
- this (workbook, null);
+ public WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier) {
+ this (workbook, null, stabilityClassifier);
}
- /* package */ WorkbookEvaluator(EvaluationWorkbook workbook, IEvaluationListener evaluationListener) {
+ /* package */ WorkbookEvaluator(EvaluationWorkbook workbook, IEvaluationListener evaluationListener,
+ IStabilityClassifier stabilityClassifier) {
_workbook = workbook;
_evaluationListener = evaluationListener;
_cache = new EvaluationCache(evaluationListener);
- _sheetIndexesBySheet = new IdentityHashMap();
+ _sheetIndexesBySheet = new IdentityHashMap<EvaluationSheet, Integer>();
_collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY;
_workbookIx = 0;
+ _stabilityClassifier = stabilityClassifier;
}
/**
@@ -141,7 +144,7 @@
}
/**
- * Should be called to tell the cell value cache that the specified (value or formula) cell
+ * Should be called to tell the cell value cache that the specified (value or formula) cell
* has changed.
*/
public void notifyUpdateCell(EvaluationCell cell) {
@@ -150,7 +153,7 @@
}
/**
* Should be called to tell the cell value cache that the specified cell has just been
- * deleted.
+ * deleted.
*/
public void notifyDeleteCell(EvaluationCell cell) {
int sheetIndex = getSheetIndex(cell.getSheet());
@@ -158,7 +161,7 @@
}
private int getSheetIndex(EvaluationSheet sheet) {
- Integer result = (Integer) _sheetIndexesBySheet.get(sheet);
+ Integer result = _sheetIndexesBySheet.get(sheet);
if (result == null) {
int sheetIndex = _workbook.getSheetIndex(sheet);
if (sheetIndex < 0) {
@@ -182,14 +185,21 @@
private ValueEval evaluateAny(EvaluationCell srcCell, int sheetIndex,
int rowIndex, int columnIndex, EvaluationTracker tracker) {
+ // avoid tracking dependencies for cells that have constant definition
+ boolean shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true
+ : !_stabilityClassifier.isCellFinal(sheetIndex, rowIndex, columnIndex);
if (srcCell == null || srcCell.getCellType() != Cell.CELL_TYPE_FORMULA) {
ValueEval result = getValueFromNonFormulaCell(srcCell);
- tracker.acceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
+ if (shouldCellDependencyBeRecorded) {
+ tracker.acceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
+ }
return result;
}
FormulaCellCacheEntry cce = _cache.getOrCreateFormulaCellEntry(srcCell);
- tracker.acceptFormulaDependency(cce);
+ if (shouldCellDependencyBeRecorded || cce.isInputSensitive()) {
+ tracker.acceptFormulaDependency(cce);
+ }
IEvaluationListener evalListener = _evaluationListener;
if (cce.getValue() == null) {
if (!tracker.startEvaluate(cce)) {
@@ -252,7 +262,7 @@
// visibility raised for testing
/* package */ ValueEval evaluateFormula(int sheetIndex, int srcRowNum, int srcColNum, Ptg[] ptgs, EvaluationTracker tracker) {
- Stack stack = new Stack();
+ Stack<Eval> stack = new Stack<Eval>();
for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
// since we don't know how to handle these yet :(
@@ -289,7 +299,7 @@
// storing the ops in reverse order since they are popping
for (int j = numops - 1; j >= 0; j--) {
- Eval p = (Eval) stack.pop();
+ Eval p = stack.pop();
ops[j] = p;
}
// logDebug("invoke " + operation + " (nAgs=" + numops + ")");
@@ -307,7 +317,7 @@
stack.push(opResult);
}
- ValueEval value = ((ValueEval) stack.pop());
+ ValueEval value = (ValueEval) stack.pop();
if (!stack.isEmpty()) {
throw new IllegalStateException("evaluation stack not empty");
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java?rev=713811&r1=713810&r2=713811&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java Thu Nov 13 12:22:17 2008
@@ -24,6 +24,7 @@
import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.IStabilityClassifier;
import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
@@ -46,7 +47,15 @@
private WorkbookEvaluator _bookEvaluator;
public XSSFFormulaEvaluator(XSSFWorkbook workbook) {
- _bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook));
+ this(workbook, null);
+ }
+ /**
+ * @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>
+ * for the (conservative) assumption that any cell may have its definition changed after
+ * evaluation begins.
+ */
+ public XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier) {
+ _bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook), stabilityClassifier);
}
/**
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java?rev=713811&r1=713810&r2=713811&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java Thu Nov 13 12:22:17 2008
@@ -42,6 +42,10 @@
*/
public class TestWorkbookEvaluator extends TestCase {
+ private static WorkbookEvaluator createEvaluator() {
+ return new WorkbookEvaluator(null, null);
+ }
+
/**
* Make sure that the evaluator can directly handle tAttrSum (instead of relying on re-parsing
* the whole formula which converts tAttrSum to tFuncVar("SUM") )
@@ -53,7 +57,7 @@
AttrPtg.SUM,
};
- ValueEval result = new WorkbookEvaluator(null).evaluateFormula(0, 0, 0, ptgs, null);
+ ValueEval result = createEvaluator().evaluateFormula(0, 0, 0, ptgs, null);
assertEquals(42, ((NumberEval)result).getNumberValue(), 0.0);
}
@@ -74,7 +78,7 @@
ptg,
};
- ValueEval result = new WorkbookEvaluator(null).evaluateFormula(0, 0, 0, ptgs, null);
+ ValueEval result = createEvaluator().evaluateFormula(0, 0, 0, ptgs, null);
assertEquals(ErrorEval.REF_INVALID, result);
}
@@ -89,7 +93,7 @@
AttrPtg.SUM,
};
- ValueEval result = new WorkbookEvaluator(null).evaluateFormula(0, 0, 0, ptgs, null);
+ ValueEval result = createEvaluator().evaluateFormula(0, 0, 0, ptgs, null);
assertEquals(42, ((NumberEval)result).getNumberValue(), 0.0);
}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/WorkbookEvaluatorTestHelper.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/WorkbookEvaluatorTestHelper.java?rev=713811&r1=713810&r2=713811&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/WorkbookEvaluatorTestHelper.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/WorkbookEvaluatorTestHelper.java Thu Nov 13 12:22:17 2008
@@ -32,6 +32,6 @@
}
public static WorkbookEvaluator createEvaluator(HSSFWorkbook wb, EvaluationListener listener) {
- return new WorkbookEvaluator(HSSFEvaluationWorkbook.create(wb), listener);
+ return new WorkbookEvaluator(HSSFEvaluationWorkbook.create(wb), listener, null);
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org