How To Create Custom Function

How To Create Custom Function

Purpose of this document

The purpose of this document is to describe all steps of creating custom (new) function (excel-function) and register it in POI engine. This document is for Java developers that are familiar with Apache POI and some of its internal implementations. This document also describes and uses some terms from Spreadsheet Analytics Calculation Engine library.

Problem

Need to create a new excel function (to be used from Excel spreadsheet). This function should be visible to POI, it can be evaluated, it can be invoked in such way so GraphBuilder component can trace all the evaluation steps. This function should have access to any data objects from Spreadsheet Analytics Data package.

Solution

Components

This diagram describes all necessary components and their relationships:

• Part in brown represents engine-functions package
• Part in green represents engine-data package
• Part in orange represents forked POI library
  

Package engine-functions

There are two places in engine-functions to put new functions to - Data and Math. They have appropriate java packages in project structure:

1. com.dataart.spreadsheetanalytics.functions.poi.data for any Data functions
2. com.dataart.spreadsheetanalytics.functions.poi.math for any Math functions

• Data function usually get the data from some source and just return that data back to original caller. They may perform some operations with data depending on the nature of function, but the idea is that they need some external data sources to pull data from. Such external gateway for all functions is ExternalServices.java class, this is a singleton and can be used in any of the functions in a way like ExternalServices.INSTANCE and it provides access to other interfaces.
• Math functions are just some mathematical (but not only) operations for arguments. They do not require external data source to calculate the result.
• CustomFunctionMeta.java – is a special annotation (Java @interface) used to provide some additional static properties of a function (such as function name or number of arguments or return type). Every new custom function must be marked with this annotation to be included in functions scan part. The only required parameter is function name (upper case).
• CustomFunction.java is an empty interface (marker interface) to mark custom functions. It extends FreeRefFunction (required by POI) and contains one method – evaluate() with all the function arguments and POI evaluation context. Every new custom function must implement this interface to be included in function scan.

Creating of a new function

Simple steps to create new function called PLUS2 with one input argument:

1. It will be a Math function, so create a new class in math package:
   
2. This is how empty function looks like:
   
3. Then we add actual code to evaluate() method to do plus 2 operation:
   
4. After you have your function code completed, you need to register this function in POI. Luckily there is no need for additional actions, everything will be loaded with SpreadsheetEvaluator class creation (static part):
5. This is it. Now you can create excel file with =PLUS2(2) and you will have 4 as a result in this cell.