WorkbookEvaluator (POI API Documentation)

java.lang.Object
- org.apache.poi.ss.formula.WorkbookEvaluator

```
@Internal
public final class WorkbookEvaluator
extends java.lang.Object
```
Evaluates formula cells.
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.
For POI internal use only

Author:

Josh Micich, Thies Wellpott (debug output enhancements)

Constructor Summary

Constructors
Constructor and Description

WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)

Constructors
Constructor and Description
`WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)`

Method Summary

All Methods Static Methods Instance Methods Concrete Methods
Modifier and Type	Method and Description
`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.
`void`	`clearAllCachedResultValues()` 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.
`ValueEval`	`evaluate(EvaluationCell srcCell)`
`ValueEval`	`evaluate(java.lang.String formula, CellReference ref)` Evaluate a formula outside a cell value, e.g.
`ValueEval`	`evaluate(java.lang.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.
`ValueEval`	`evaluateList(java.lang.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.
`FreeRefFunction`	`findUserDefinedFunction(java.lang.String functionName)`
`static java.util.Collection<java.lang.String>`	`getNotSupportedFunctionNames()` Return a collection of functions that POI does not support
`static java.util.Collection<java.lang.String>`	`getSupportedFunctionNames()` Return a collection of functions that POI can evaluate
`boolean`	`isDebugEvaluationOutputForNextEval()`
`boolean`	`isIgnoreMissingWorkbooks()`
`void`	`notifyDeleteCell(EvaluationCell cell)` Should be called to tell the cell value cache that the specified cell has just been deleted.
`void`	`notifyUpdateCell(EvaluationCell cell)` Should be called to tell the cell value cache that the specified (value or formula) cell has changed.
`static void`	`registerFunction(java.lang.String name, FreeRefFunction func)` Register a ATP function in runtime.
`static void`	`registerFunction(java.lang.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.

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

- Constructor Detail
  - WorkbookEvaluator
```
public WorkbookEvaluator(EvaluationWorkbook workbook,
                         IStabilityClassifier stabilityClassifier,
                         UDFFinder udfFinder)
```
    Parameters:
    
    udfFinder - pass null for default (AnalysisToolPak only)
- Method Detail
  - 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
```
public void notifyUpdateCell(EvaluationCell cell)
```
    Should be called to tell the cell value cache that the specified (value or formula) cell has changed.
  - notifyDeleteCell
```
public void notifyDeleteCell(EvaluationCell cell)
```
    Should be called to tell the cell value cache that the specified cell has just been deleted.
  - evaluate
```
public ValueEval evaluate(EvaluationCell srcCell)
```
  - dereferenceResult
```
public static ValueEval dereferenceResult(ValueEval evaluationResult,
                                          int srcRowNum,
                                          int srcColNum)
```
    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, or ErrorEval. Never null. BlankEval is converted to NumberEval.ZERO
  - findUserDefinedFunction
```
public FreeRefFunction findUserDefinedFunction(java.lang.String functionName)
```
  - evaluate
```
public ValueEval evaluate(java.lang.String formula,
                          CellReference ref)
```
    Evaluate a formula outside a cell value, e.g. conditional format rules or data validation expressions
    
    Parameters:
    
    formula - to evaluate
    
    ref - defines the optional sheet and row/column base for the formula, if it is relative
    
    Returns:
    
    value
  - evaluate
```
public ValueEval evaluate(java.lang.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. 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 - The formula to evaluate
    
    target - cell context for the operation
    
    region - containing the cell
    
    Returns:
    
    value
    
    Throws:
    
    java.lang.IllegalArgumentException - if target does not define a sheet name to evaluate the formula on.
  - evaluateList
```
public ValueEval evaluateList(java.lang.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. 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 - The formula to evaluate
    
    target - cell context for the operation
    
    region - containing the cell
    
    Returns:
    
    ValueEval for one or more values
    
    Throws:
    
    java.lang.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 to
```
formula ref + current cell ref
```
    Parameters:
    
    ptgs -
    
    target - cell within the region to use.
    
    region - containing the cell, OR, for conditional format rules with multiple ranges, the region with the top-left-most cell
    
    Returns:
    
    true if any Ptg references were shifted
    
    Throws:
    
    java.lang.IndexOutOfBoundsException - if the resulting shifted row/column indexes are over the document format limits
    
    java.lang.IllegalArgumentException - 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:
    
    Bug 52575 for details
  - isIgnoreMissingWorkbooks
```
public boolean isIgnoreMissingWorkbooks()
```
  - getSupportedFunctionNames
```
public static java.util.Collection<java.lang.String> getSupportedFunctionNames()
```
    Return a collection of functions that POI can evaluate
    
    Returns:
    
    names of functions supported by POI
  - getNotSupportedFunctionNames
```
public static java.util.Collection<java.lang.String> getNotSupportedFunctionNames()
```
    Return a collection of functions that POI does not support
    
    Returns:
    
    names of functions NOT supported by POI
  - registerFunction
```
public static void registerFunction(java.lang.String name,
                                    FreeRefFunction func)
```
    Register a ATP function in runtime.
    
    Parameters:
    
    name - the function name
    
    func - the functoin to register
    
    Throws:
    
    java.lang.IllegalArgumentException - if the function is unknown or already registered.
    
    Since:
    
    3.8 beta6
  - registerFunction
```
public static void registerFunction(java.lang.String name,
                                    Function func)
```
    Register a function in runtime.
    
    Parameters:
    
    name - the function name
    
    func - the functoin to register
    
    Throws:
    
    java.lang.IllegalArgumentException - if the function is unknown or already registered.
    
    Since:
    
    3.8 beta6
  - setDebugEvaluationOutputForNextEval
```
public void setDebugEvaluationOutputForNextEval(boolean value)
```
  - isDebugEvaluationOutputForNextEval
```
public boolean isDebugEvaluationOutputForNextEval()
```

Class WorkbookEvaluator

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Constructor Detail

WorkbookEvaluator

Method Detail

clearAllCachedResultValues

notifyUpdateCell

notifyDeleteCell

evaluate

dereferenceResult

findUserDefinedFunction

evaluate

evaluate

evaluateList

adjustRegionRelativeReference

setIgnoreMissingWorkbooks

isIgnoreMissingWorkbooks

getSupportedFunctionNames

getNotSupportedFunctionNames

registerFunction

registerFunction

setDebugEvaluationOutputForNextEval

isDebugEvaluationOutputForNextEval