From e2d8007f664e1fd8332bd739f0335a2784e6e0a3 Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Thu, 13 Nov 2008 20:22:17 +0000 Subject: [PATCH] Changes to formula evaluation allowing for reduced memory usage git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@713811 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../hssf/usermodel/HSSFFormulaEvaluator.java | 502 +++++++++--------- .../poi/ss/formula/FormulaCellCacheEntry.java | 9 + .../ss/formula/FormulaUsedBlankCellSet.java | 4 + .../poi/ss/formula/IStabilityClassifier.java | 84 +++ .../poi/ss/formula/WorkbookEvaluator.java | 36 +- .../xssf/usermodel/XSSFFormulaEvaluator.java | 11 +- .../poi/ss/formula/TestWorkbookEvaluator.java | 10 +- .../formula/WorkbookEvaluatorTestHelper.java | 2 +- 10 files changed, 395 insertions(+), 265 deletions(-) create mode 100644 src/java/org/apache/poi/ss/formula/IStabilityClassifier.java diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index a0e36343a7..c1740afad4 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -37,6 +37,7 @@ + Changes to formula evaluation allowing for reduced memory usage 45290 - Support odd files where the POIFS header block comes after the data blocks, and is on the data blocks list 46184 - More odd escaped date formats Include the sheet number in the output of XLS2CSVmra diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 4d2b1a9974..57fec14dfa 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + Changes to formula evaluation allowing for reduced memory usage 45290 - Support odd files where the POIFS header block comes after the data blocks, and is on the data blocks list 46184 - More odd escaped date formats Include the sheet number in the output of XLS2CSVmra diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java index 845284c456..1f65588caa 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java @@ -25,6 +25,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.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 @@ import org.apache.poi.ss.usermodel.FormulaEvaluator; */ 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)); - } + /** + * @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 null + * 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); - } + /** + * 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 - } - } + /** + * 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 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)); - } + /** + * 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 null signifying that the cell is not present (or blank) - * @return null if the supplied cell is null or blank - */ - public CellValue evaluate(Cell cell) { - if (cell == null) { - return null; - } + /** + * 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 null signifying that the cell is not present (or blank) + * @return null if the supplied cell is null 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() + ")"); - } + 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 formula result is returned, so you know what kind of - * cached formula result is also stored with the formula. - * 
-     * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
-     *
- * 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 formula result - */ - 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 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 formula result is returned, so you know what kind of + * cached formula result is also stored with the formula. + * 
+	 * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
+	 *
+ * 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 formula result + */ + 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: - * 
-     * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
-     *
- * 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 + ")"); - } + /** + * 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: + * 
+	 * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
+	 *
+ * 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 + ")"); - } - } + 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 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, diff --git a/src/java/org/apache/poi/ss/formula/FormulaUsedBlankCellSet.java b/src/java/org/apache/poi/ss/formula/FormulaUsedBlankCellSet.java index 7447edca0f..d96df2e903 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaUsedBlankCellSet.java +++ b/src/java/org/apache/poi/ss/formula/FormulaUsedBlankCellSet.java @@ -186,4 +186,8 @@ final class FormulaUsedBlankCellSet { } return bcsg.containsCell(rowIndex, columnIndex); } + + public boolean isEmpty() { + return _sheetGroupsByBookSheet.isEmpty(); + } } diff --git a/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java b/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java new file mode 100644 index 0000000000..a7d2d3f478 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/IStabilityClassifier.java @@ -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 final.
+ * The term final 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}).
+ *
+ * General guidelines: + *
    + *
  • a plain value cell can be marked as 'final' if it will not be changed after the first call + * to {@link WorkbookEvaluator#evaluate(EvaluationCell)}. + *
    • + *
  • 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.
    • + *
  • plain value cells should be marked as 'not final' if their plain value value may change. + *
    • + *
  • formula cells should be marked as 'not final' if their formula definition may change.
    • + *
  • cells which may switch between plain value and formula should also be marked as 'not final'. + *
    • + *
+ * Notes: + *
    + *
  • 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.
    • + *
  • 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.
    • + *
  • 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.
    • + *
  • Updating cells which have been classified as 'final' will cause the evaluator to behave + * unpredictably (typically ignoring the update).
    • + *
+ * + * @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 false if the evaluating application may need to modify the specified + * cell between calls to the evaluator. + */ + boolean isCellFinal(int sheetIndex, int rowIndex, int columnIndex); +} diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index d3955f8796..a2a3b97149 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -82,19 +82,22 @@ public final class WorkbookEvaluator { private int _workbookIx; private final IEvaluationListener _evaluationListener; - private final Map _sheetIndexesBySheet; + private final Map _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(); _collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY; _workbookIx = 0; + _stabilityClassifier = stabilityClassifier; } /** @@ -141,7 +144,7 @@ public final class WorkbookEvaluator { } /** - * 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 @@ public final class WorkbookEvaluator { } /** * 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 @@ public final class WorkbookEvaluator { } 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 @@ public final class WorkbookEvaluator { 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 @@ public final class WorkbookEvaluator { // visibility raised for testing /* package */ ValueEval evaluateFormula(int sheetIndex, int srcRowNum, int srcColNum, Ptg[] ptgs, EvaluationTracker tracker) { - Stack stack = new Stack(); + Stack stack = new Stack(); for (int i = 0, iSize = ptgs.length; i < iSize; i++) { // since we don't know how to handle these yet :( @@ -289,7 +299,7 @@ public final class WorkbookEvaluator { // 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 @@ public final class WorkbookEvaluator { stack.push(opResult); } - ValueEval value = ((ValueEval) stack.pop()); + ValueEval value = (ValueEval) stack.pop(); if (!stack.isEmpty()) { throw new IllegalStateException("evaluation stack not empty"); } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java index cd20997c87..8780ae23f3 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java @@ -24,6 +24,7 @@ import org.apache.poi.hssf.record.formula.eval.ErrorEval; 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 @@ public class XSSFFormulaEvaluator implements FormulaEvaluator { private WorkbookEvaluator _bookEvaluator; public XSSFFormulaEvaluator(XSSFWorkbook workbook) { - _bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook)); + this(workbook, null); + } + /** + * @param stabilityClassifier used to optimise caching performance. Pass null + * 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); } /** diff --git a/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java b/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java index 47ceb2686f..81dad57d16 100644 --- a/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java +++ b/src/testcases/org/apache/poi/ss/formula/TestWorkbookEvaluator.java @@ -42,6 +42,10 @@ import org.apache.poi.hssf.usermodel.HSSFWorkbook; */ 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 @@ public class TestWorkbookEvaluator extends TestCase { 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 @@ public class TestWorkbookEvaluator extends TestCase { 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 @@ public class TestWorkbookEvaluator extends TestCase { 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); } diff --git a/src/testcases/org/apache/poi/ss/formula/WorkbookEvaluatorTestHelper.java b/src/testcases/org/apache/poi/ss/formula/WorkbookEvaluatorTestHelper.java index 2111cf4e85..b8b5de991e 100644 --- a/src/testcases/org/apache/poi/ss/formula/WorkbookEvaluatorTestHelper.java +++ b/src/testcases/org/apache/poi/ss/formula/WorkbookEvaluatorTestHelper.java @@ -32,6 +32,6 @@ public final class WorkbookEvaluatorTestHelper { } public static WorkbookEvaluator createEvaluator(HSSFWorkbook wb, EvaluationListener listener) { - return new WorkbookEvaluator(HSSFEvaluationWorkbook.create(wb), listener); + return new WorkbookEvaluator(HSSFEvaluationWorkbook.create(wb), listener, null); } } -- 2.39.5