Overview | Package | Class | Use | Tree | Deprecated | Index | Help
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

## org.apache.poi.ss.formula
Class WorkbookEvaluator

    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
`WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)`

Method Summary
`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.
`protected boolean` | `adjustRegionRelativeReference(Ptg[] ptgs, int deltaRow, int deltaColumn)` Adjust the formula relative cell references by a given delta
`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` -
    `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` -
    `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.

  Parameters:
    `ptgs` -
    `target` - cell within the region to use.
    `region` - containing the 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.

### adjustRegionRelativeReference

    protected boolean adjustRegionRelativeReference(Ptg[] ptgs,
                                                    int deltaRow,
                                                    int deltaColumn)

  Adjust the formula relative cell references by a given delta

  Parameters:
    `ptgs` -
    `deltaRow` - target row offset from the top left cell of a region
    `deltaColumn` - target column offset from the top left cell of a region
  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 either of the deltas are negative, as the assumption is we are shifting formulas relative to the top left cell of a 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()

Overview | Package | Class | Use | Tree | Deprecated | Index | Help
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

Copyright 2017 The Apache Software Foundation or its licensors, as applicable.