Package org.apache.poi.ss.formula
Class WorkbookEvaluator
java.lang.Object
org.apache.poi.ss.formula.WorkbookEvaluator
Evaluates formula cells.
For performance reasons, this class keeps a cache of all previously calculated intermediate
cell values. Be sure to call
For POI internal use only
clearAllCachedResultValues()
if any workbook cells are changed between
calls to evaluate~ methods on this class.For POI internal use only
- Author:
- Josh Micich, Thies Wellpott (debug output enhancements)
-
Constructor Summary
ConstructorsConstructorDescriptionWorkbookEvaluator
(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) -
Method Summary
Modifier and TypeMethodDescriptionprotected boolean
adjustRegionRelativeReference
(Ptg[] ptgs, CellReference target, CellRangeAddressBase region) Adjust formula relative references by the offset between the start of the given region and the given target cell.void
Should be called whenever there are changes to input cells in the evaluated workbook.static ValueEval
dereferenceResult
(ValueEval evaluationResult, int srcRowNum, int srcColNum) Dereferences a single value from any AreaEval or RefEval evaluation result.evaluate
(String formula, CellReference ref) Evaluate a formula outside a cell value, e.g.evaluate
(String formula, CellReference target, CellRangeAddressBase region) Some expressions need to be evaluated in terms of an offset from the top left corner of a region, such as some data validation and conditional format expressions, when those constraints apply to contiguous cells.evaluate
(EvaluationCell srcCell) evaluateList
(String formula, CellReference target, CellRangeAddressBase region) Some expressions need to be evaluated in terms of an offset from the top left corner of a region, such as some data validation and conditional format expressions, when those constraints apply to contiguous cells.findUserDefinedFunction
(String functionName) static Collection<String>
Return a collection of functions that POI does not supportstatic Collection<String>
Return a collection of functions that POI can evaluateboolean
boolean
void
Should be called to tell the cell value cache that the specified cell has just been deleted.void
Should be called to tell the cell value cache that the specified (value or formula) cell has changed.static void
registerFunction
(String name, FreeRefFunction func) Register a ATP function in runtime.static void
registerFunction
(String name, Function func) Register a function in runtime.void
setDebugEvaluationOutputForNextEval
(boolean value) void
setIgnoreMissingWorkbooks
(boolean ignore) Whether to ignore missing references to external workbooks and use cached formula results in the main workbook instead.
-
Constructor Details
-
WorkbookEvaluator
public WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) - Parameters:
udfFinder
- passnull
for default (AnalysisToolPak only)
-
-
Method Details
-
clearAllCachedResultValues
public void clearAllCachedResultValues()Should be called whenever there are changes to input cells in the evaluated workbook. Failure to call this method after changing cell values will cause incorrect behaviour of the evaluate~ methods of this class -
notifyUpdateCell
Should be called to tell the cell value cache that the specified (value or formula) cell has changed. -
notifyDeleteCell
Should be called to tell the cell value cache that the specified cell has just been deleted. -
evaluate
-
dereferenceResult
Dereferences a single value from any AreaEval or RefEval evaluation result. If the supplied evaluationResult is just a plain value, it is returned as-is.- Returns:
- a
NumberEval
,StringEval
,BoolEval
, orErrorEval
. Nevernull
.BlankEval
is converted toNumberEval.ZERO
-
findUserDefinedFunction
-
evaluate
Evaluate a formula outside a cell value, e.g. conditional format rules or data validation expressions- Parameters:
formula
- to evaluateref
- defines the optional sheet and row/column base for the formula, if it is relative- Returns:
- value
-
evaluate
Some expressions need to be evaluated in terms of an offset from the top left corner of a region, such as some data validation and conditional format expressions, when those constraints apply to contiguous cells. When a relative formula is used, it must be evaluated by shifting by the target offset position relative to the top left of the range.Returns a single value e.g. a cell formula result or boolean value for conditional formatting.
- Parameters:
formula
-target
- cell context for the operationregion
- containing the cell- Returns:
- value
- Throws:
IllegalArgumentException
- if target does not define a sheet name to evaluate the formula on.
-
evaluateList
Some expressions need to be evaluated in terms of an offset from the top left corner of a region, such as some data validation and conditional format expressions, when those constraints apply to contiguous cells. When a relative formula is used, it must be evaluated by shifting by the target offset position relative to the top left of the range.Returns a ValueEval that may be one or more values, such as the allowed values for a data validation constraint.
- Parameters:
formula
-target
- cell context for the operationregion
- containing the cell- Returns:
- ValueEval for one or more values
- Throws:
IllegalArgumentException
- if target does not define a sheet name to evaluate the formula on.
-
adjustRegionRelativeReference
protected boolean adjustRegionRelativeReference(Ptg[] ptgs, CellReference target, CellRangeAddressBase region) Adjust formula relative references by the offset between the start of the given region and the given target cell. That is, treat the region top-left cell as "A1" for the purposes of evaluating relative reference components (row and/or column), and further move references by the position of the target within the region.formula ref + range top-left + current cell range offset
which simplifies toformula ref + current cell ref
- Parameters:
ptgs
-target
- cell within the region to use.region
- containing the cell- Returns:
- true if any Ptg references were shifted
- Throws:
IndexOutOfBoundsException
- if the resulting shifted row/column indexes are over the document format limitsIllegalArgumentException
- if target is not within region.
-
setIgnoreMissingWorkbooks
public void setIgnoreMissingWorkbooks(boolean ignore) Whether to ignore missing references to external workbooks and use cached formula results in the main workbook instead.In some cases exetrnal workbooks referenced by formulas in the main workbook are not avaiable. With this method you can control how POI handles such missing references:
- by default ignoreMissingWorkbooks=false and POI throws
CollaboratingWorkbooksEnvironment.WorkbookNotFoundException
if an external reference cannot be resolved - if ignoreMissingWorkbooks=true then POI uses cached formula result that already exists in the main workbook
- Parameters:
ignore
- whether to ignore missing references to external workbooks- See Also:
- by default ignoreMissingWorkbooks=false and POI throws
-
isIgnoreMissingWorkbooks
public boolean isIgnoreMissingWorkbooks() -
getSupportedFunctionNames
Return a collection of functions that POI can evaluate- Returns:
- names of functions supported by POI
-
getNotSupportedFunctionNames
Return a collection of functions that POI does not support- Returns:
- names of functions NOT supported by POI
-
registerFunction
Register a ATP function in runtime.- Parameters:
name
- the function namefunc
- the functoin to register- Throws:
IllegalArgumentException
- if the function is unknown or already registered.- Since:
- 3.8 beta6
-
registerFunction
Register a function in runtime.- Parameters:
name
- the function namefunc
- the functoin to register- Throws:
IllegalArgumentException
- if the function is unknown or already registered.- Since:
- 3.8 beta6
-
setDebugEvaluationOutputForNextEval
public void setDebugEvaluationOutputForNextEval(boolean value) -
isDebugEvaluationOutputForNextEval
public boolean isDebugEvaluationOutputForNextEval()
-