Custom Formula Functions
Overview
Spreadsheet allows developers to implement their own custom formulas and use these formulas like built-in ones. You can just enter your custom formula like =MYFORMULA(A1) and it works like other formulas. To implement such a custom method is easy: just create a static method and declare it in a ZUL page with EL method or tag library.
Steps to add a custom formula:
- Implement a public static method
- Declare it in a ZUL page with Spreadsheet component
After completing above steps, you can use the custom formula in Spreadsheet.
Implement a Formula
Basically, to implement a formula is just like implementing a static Java method, but depending on your requirements there are several ways to do it. We will introduce them from easy to complicated one.
Basic - Simple Formula
If your formula accepts fixed number of arguments and each argument is a single value (or a reference to a single cell), you can use this way to implement your custom formula.
Assume that we are going to create a money exchange formula that accepts 2 double arguments and return exchange result. The first one is money, and the second one is exchange rate.
public class MyCustomFormulas {
public static double exchange(double money, double exchangeRate) {
return money * exchangeRate;
}
}
After declaring it in a ZUL page, you can use this formula like =EXCHANGE(10, 31.3) or =EXCHANGE(C3, D3). Spreadsheet will evaluate each argument and pass them to exchange() method. If you pass a reference to a range of cells in this formula, e.g. =EXCHANGE(C3:C4, D3), only the first cell will be evaluated.
Intermediate - Multiple Numeric Arguments Formula
If your formula needs to accept a range of cells with numeric value or variable number of numeric arguments, you should use this way: Create a class inherited from MultiOperandNumericFunction.
public class MyCustomFormulas {
private static Function MY_SUBTOTAL =
new MultiOperandNumericFunction(false, false) {
@Override
protected double evaluate(double[] values) throws EvaluationException {
double sum = 0;
for (int i = 0 ; i < values.length ; i++){
sum += values[i];
}
return sum;
}
};
public static ValueEval mySubtotal(ValueEval[] args, int srcCellRow, int srcCellCol){
return MY_SUBTOTAL.evaluate(args, srcCellRow, srcCellCol);
}
}
Advanced - Manually-Handled Arguments Formula
public class MyCustomFormulas {
public static ValueEval chain(ValueEval[] args, int srcCellRow, int srcCellCol){
List<StringEval> stringList = new LinkedList<StringEval>();
for (int i = 0 ; i < args.length ; i++){
//process an argument like A1:B2
if (args[i] instanceof TwoDEval) {
TwoDEval twoDEval = (TwoDEval) args[i];
int width = twoDEval.getWidth();
int height = twoDEval.getHeight();
for (int rowIndex=0; rowIndex<height; rowIndex++) {
for (int columnIndex=0; columnIndex<width; columnIndex++) {
ValueEval ve = twoDEval.getValue(rowIndex, columnIndex);
if (ve instanceof StringEval){
stringList.add((StringEval)ve);
}
}
}
continue;
}
//process an argument like C18
if (args[i] instanceof RefEval){
ValueEval valueEval = ((RefEval)args[i]).getInnerValueEval();
if (valueEval instanceof StringEval){
stringList.add((StringEval)valueEval);
}
continue;
}
if (args[i] instanceof StringEval){
stringList.add((StringEval)args[i]);
continue;
}
}
//chain all string value
StringBuffer result = new StringBuffer();
for (StringEval s: stringList){
result.append(s.getStringValue());
}
return new StringEval(result.toString());
}
}