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:

1. Implement a public static method
2. Declare in a ZUL page with Spreadsheet component

   You could use ZUML_Reference/ZUML/Processing_Instructions/xel-method or ZUML_Reference/ZUML/Processing_Instructions/taglib/Custom_Taglib

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