Package org.apache.poi.ss.formula

Class WorkbookEvaluator

java.lang.Object

org.apache.poi.ss.formula.WorkbookEvaluator

@Internal
public final class WorkbookEvaluator
extends 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.

Nested Class Summary

Nested Classes

Modifier and Type	Class	Description
static interface	WorkbookEvaluator.CacheManager

Constructor Summary

Constructors

Constructor	Description
WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)
WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder, WorkbookEvaluator.CacheManager cacheManager)

Method Summary

Modifier and Type	Method	Description
void	clearAllCachedResultValues()	Should be called whenever there are changes to input cells in the evaluated workbook.
void	clearColumnCache(int sheetIndex, int start, int end)
static ValueEval	dereferenceResult(ValueEval evaluationResult, int srcRowNum, int srcColNum)	Dereferences a single value from any AreaEval or RefEval evaluation result.
void	disableCacheManager()
void	enableCacheManager()
ValueEval	evaluate(String formula, CellReference ref, int[] offset)	Evaluate a formula outside a cell value, e.g.
ValueEval	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.
ValueEval	evaluate(EvaluationCell srcCell)
ValueEval	evaluate(EvaluationCell srcCell, Object ref)
ValueEval	evaluate(EvaluationCell srcCell, Object ref, int[] offset)
ValueEval	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.
FreeRefFunction	findUserDefinedFunction(String functionName)
static Collection<String>	getNotSupportedFunctionNames()	Return a collection of functions that POI does not support
static Collection<String>	getSupportedFunctionNames()	Return a collection of functions that POI can evaluate
boolean	isDebugEvaluationOutputForNextEval()
boolean	isIgnoreMissingWorkbooks()
void	notifyClearIterationValue(EvaluationCell cell)
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(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	setDependencyTracker(DependencyTracker tracker)
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)

WorkbookEvaluator

public WorkbookEvaluator(EvaluationWorkbook workbook,
                         IStabilityClassifier stabilityClassifier,
                         UDFFinder udfFinder,
                         WorkbookEvaluator.CacheManager cacheManager)

Method Detail

setDependencyTracker

public void setDependencyTracker(DependencyTracker tracker)

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.

notifyClearIterationValue

public void notifyClearIterationValue(EvaluationCell cell)

notifyDeleteCell

public void notifyDeleteCell(EvaluationCell cell)

Should be called to tell the cell value cache that the specified cell has just been deleted.

clearColumnCache

public void clearColumnCache(int sheetIndex,
                             int start,
                             int end)

evaluate

public ValueEval evaluate(EvaluationCell srcCell,
                          Object ref)

evaluate

public ValueEval evaluate(EvaluationCell srcCell,
                          Object ref,
                          int[] offset)

evaluate

public ValueEval evaluate(EvaluationCell srcCell)

enableCacheManager

public void enableCacheManager()

disableCacheManager

public void disableCacheManager()

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

evaluate

public ValueEval evaluate(String formula,
                          CellReference ref,
                          int[] offset)

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

IllegalArgumentException - if target does not define a sheet name to evaluate the formula on.

evaluateList

public ValueEval 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. 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:

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 external workbooks referenced by formulas in the main workbook are not available. 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 Collection<String> getSupportedFunctionNames()

Return a collection of functions that POI can evaluate

Returns:

names of functions supported by POI

getNotSupportedFunctionNames

public static Collection<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(String name,
                                    FreeRefFunction func)

Register a ATP function in runtime.

Parameters:

name - the function name

func - the function to register

Throws:

IllegalArgumentException - if the function is unknown or already registered.

Since:

3.8 beta6

registerFunction

public static void registerFunction(String name,
                                    Function func)

Register a function in runtime.

Parameters:

name - the function name

func - the function 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()