Package org.apache.poi.ss.formula

Class 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

    • 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.

      • 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.

      • 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:

        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()