public interface FormulaEvaluator
For performance reasons, this class keeps a cache of all previously calculated intermediate
cell values. Be sure to call clearAllCachedResultValues()
if any workbook cells are changed between
calls to evaluate~ methods on this class.
Modifier and Type | Method and Description |
---|---|
void |
clearAllCachedResultValues()
Should be called whenever there are changes to input cells in the evaluated workbook.
|
CellValue |
evaluate(Cell 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.
|
void |
evaluateAll()
Loops over all cells in all sheets of the associated workbook.
|
CellType |
evaluateFormulaCell(Cell cell)
If cell contains formula, it evaluates the formula,
and saves the result of the formula.
|
Cell |
evaluateInCell(Cell cell)
If cell contains formula, it evaluates the formula, and
puts the formula result back into the cell, in place
of the old formula.
|
void |
notifyDeleteCell(Cell cell)
Should be called to tell the cell value cache that the specified cell has just become a
formula cell, or the formula text has changed
|
void |
notifySetFormula(Cell cell)
Should be called to tell the cell value cache that the specified (value or formula) cell
has changed.
|
void |
notifyUpdateCell(Cell cell)
Should be called to tell the cell value cache that the specified (value or formula) cell
has changed.
|
void |
setDebugEvaluationOutputForNextEval(boolean value)
Perform detailed output of formula evaluation for next evaluation only?
Is for developer use only (also developers using POI for their XLS files).
|
void |
setIgnoreMissingWorkbooks(boolean ignore)
Whether to ignore missing references to external workbooks and
use cached formula results in the main workbook instead.
|
void |
setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks)
Sets up the Formula Evaluator to be able to reference and resolve
links to other workbooks, eg [Test.xls]Sheet1!A1.
|
void clearAllCachedResultValues()
void notifySetFormula(Cell cell)
void notifyDeleteCell(Cell cell)
void notifyUpdateCell(Cell cell)
void evaluateAll()
CellValue evaluate(Cell cell)
cell
- The Cell
to evaluateCellType evaluateFormulaCell(Cell cell)
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
evaluateInCell(Cell)
cell
- The cell to evaluateCellType.NUMERIC
, CellType.STRING
,
CellType.BOOLEAN
, CellType.ERROR
Note: the cell's type remains as CellType.FORMULA however.Cell evaluateInCell(Cell cell)
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
evaluateFormulaCell(Cell)
cell
- The Cell
to evaluate and modify.void setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks)
For a workbook referenced as [Test.xls]Sheet1!A1, you should supply a map containing the key Test.xls (no square brackets), and an open FormulaEvaluator onto that Workbook.
workbooks
- Map of workbook names (no square brackets) to an evaluator on that workbookvoid setIgnoreMissingWorkbooks(boolean ignore)
In some cases external workbooks referenced by formulas in the main workbook are not available. With this method you can control how POI handles such missing references:
CollaboratingWorkbooksEnvironment.WorkbookNotFoundException
if an external reference cannot be resolvedignore
- whether to ignore missing references to external workbooksvoid setDebugEvaluationOutputForNextEval(boolean value)
value
- whether to perform detailed outputCopyright 2021 The Apache Software Foundation or its licensors, as applicable.