Repository navigation
How DEFINE and FUNCEXEC Functions Work
The purpose of this document is to describe the workflow of DEFINE and FUNCEXEC functions and their implementation in Calculation Engine\POI.
There is a separate java class for DEFINE function called DefineFunction.java. It follows all the standards of how custom excel function should be created, but it does nothing. It’s evaluate() method just returns its name DEFINE. This is because DEFINE function should never be called from evaluate() perspective. It can only describe a model, not execute it. In other words it just contains some static metadata to be used by define functions extracter.
Question: DEFINE function is a meta-property of a workbook (DataModel), how these properties are loaded into POI\Calculation Engine Evaluator?
- DEFINE function could be extracted to separeted minimized IDataModel instance to save space and make evaluation faster. From program perspective there is no difference between any regular IDataModel and extracted to IDataModel DEFINE function.
- Since IDataModel contains only execution part of DEFINE function, but there is a need to store its meta information (like name, list of inputs, etc.). To save DEFINE function meta information there is a class called DefineFunctionMeta (extends generic FunctionMeta).
- To extract DEFINE function to list of executable IDataModel there is a util method in Converters class - toDataModels(). It gets the original Excel file (binary or IDataModel format) and returns parsed list of List. There is also another method in Converters - toMetaFunctions() which is a generic way to get DEFINE function with its DefineFunctionMeta information. The second method returns Map<T extends FunctionMeta, IDataModel> there T can be DefineFunctionMeta class. And it gets as one of arguments the class of function to extract (DefineFunctionMeta.class in this case).
- After the extraction IDataModels can be stored in DataModelAccessor (storage for data models) and FUNCEXEC function will have access to them, so they can be executed.
No, steps for loading define metadata are necessary for DEFINE functions only, because they are not like all other functions.
Code of FUNCEXEC function can be found in FuncexecFunction.java
- When there is a time to evaluate a cell that contains FUNCEXEC function in it, the first this that should be done is DEFINE function loading to DataModelAccessor. This can be done any time, but before the execution. Once everything is loaded into DataModelAccessor the call to IEvaluator.evaluate(CellAddress) or IEvaluator.evaluate() can be done.
- The evaluation begins in IEvaluator and almost immediately comes to POI WorkbookEvaluator.java class. POI evaluator does its standard evaluation steps and when it finds a FUNCEXEC function it calls to an instance of FuncexecFunction.java (evaluate() method).
- FuncexecFunction does some validation operations:
- First argument is a String (the name of the DEFINE function) or a reference to a Cell
- Such DEFINE function is registered in DataModelAccessor
- Number of input arguments of this FUNCEXEC equals to number of input arguments of appropriate DEFINE function
- FuncexecFunction resolves input arguments to its values. It is done with standard POI’s OperandResolver.getSingleValue(), since input arguments can be references to cells only.
- FuncexecFunction does create new instance of WorkbookEvaluator.java from converted IDataModel object (got by DEFINE name). Then it replaces all the input values in a copy of original IDataModel with provided values. And then FuncexecFunction does evaluate() from workbook evaluator for every output cell.
The diagram below illustrates the process: PDF