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