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

The custom formula can be implemented like:

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 follow the steps below:

1. Create a class MyNumericFunction inherited from MultiOperandNumericFunction and override its evaluate(double[]).

   MultiOperandNumericFunction can evaluate various arguments to double including a range of cells, string, and boolean etc. You can benefit from this behavior instead of handling various ValueEval by yourself.

2. Create a public static method with the signature:

   public static ValueEval yourFormulaName(ValueEval[] , int , int )

   You should not change this signature because Spreadsheet recognize your method by the signature.

3. In your static method (yourFormulaName()), invoke MyNumericFunction.evaluate(ValueEval[] , int , int) to calculate.

Assume that we are going to create a custom formula that accepts variable number of numeric arguments and sums them all.

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); 
	}
}

• Line 4: MultiOperandNumericFunction's constructor has 2 arguments. The first parameter determines whether to evaluate boolean value. If it's true, evaluator will evaluate boolean value to number. TRUE to 1 and FALSE to 0. If it's false, boolean value is just ignored. The second parameter determines whether to evaluate blank value. Blank value will be evaluate to 0.
• Line 7: When another overloading evaluate() is invoked (line 17), MultiOperandNumericFunction can evaluate all arguments to double and pass them to your overridden method, evaluate(). It can save your effort to evaluate each argument. If you encounter a situation that you don't expect, please throw EvaluationException. Because Spreadsheet can handle the exception gracefully.
• Line 17: Delegate method calling to our subclass of MultiOperandNumericFunction.

Advanced - Manually-Handled Arguments Formula

If your formula needs to handle arguments by yourself instead of always evaluating them as numbers, you should create a public static method with the signature:

public static ValueEval yourFormulaName(ValueEval[] , int , int )

You should not change this signature because Spreadsheet recognizes your method by the signature. Spreadsheet will pass all arguments as an array of ValueEval, and you have to process each ValueEval to fulfill your formula's requirement.

Here, we demonstrate this approach with a formula that chains multiple text cells into one text. Below screenshot shows its use cases:

You can see that this formula can accept various arguments including string, a range of cells, and multiple cell references. Different kind of arguments will be evaluated to different subclass of ValueEval, and you should handle them in your formula method to make your custom formula support these use cases.

The formula to chain text

public class MyCustomFormulas {

	/**
	 * Advanced - Manually-Handled Arguments Formula. 
	 * This method demonstrates how to evaluate arguments manually. 
	 * The method implements a formula that concatenates all texts in cells 
	 * and ignores other types of value.
	 * 
	 * @param args the evaluated formula arguments
	 * @param srcCellRow unused
	 * @param srcCellCol unused
	 * @return calculated result, a subclass of ValueEval
	 */
	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());
		}

		//throw EvaluationException if encounter error conditions
		return new StringEval(result.toString());
	}

}

• Line 14: You should create a public static method with the same signature because Spreadsheet recognizes your formula method by the signature.
• Line 19: TwoDEval is a common interface that represents a range of cells. Process it to make your formula accepts an argument like A1:B2. In our example, we just get each text cell of it and ignore others.
• Line 34: RefEval represents an evaluation of a cell reference like "C18".
• Line 41: StringEval is the evaluation result of a string like "abc".
• Line 53: We recommend you to throw an EvaluationException when you encounter an error condition. Because Spreadsheet will catch and handle it gracefully for you.
• Line 54: Return an object of ValueEval's subtype according to your result.

Declare Custom Formula on a ZUL Page

After implementing a method for a custom formula, we can use one of the following ways to bring it in a ZUL page before using it in Spreadsheet.

Using EL Method

Use xel-method directive is quite straight out. Just write it on the ZUL page with Spreadsheet component, and that is it. Please refer to ZUML_Reference/ZUML/Processing_Instructions/xel-method for complete explanation of each attribute.

For our custom formula, we can write:

<?xel-method prefix="zss" name="EXCHANGE"
    class="org.zkoss.zss.essential.advanced.MyCustomFormulas"  
    signature="double exchange(double,double)"?>
<?xel-method prefix="zss" name="MYSUBTOTAL"
    class="org.zkoss.zss.essential.advanced.MyCustomFormulas"  
    signature="org.zkoss.poi.ss.formula.eval.ValueEval mySubtotal(org.zkoss.poi.ss.formula.eval.ValueEval[], int, int)"?>
<?xel-method prefix="zss" name="CHAIN"
    class="org.zkoss.zss.essential.advanced.MyCustomFormulas"  
    signature="org.zkoss.poi.ss.formula.eval.ValueEval chain(org.zkoss.poi.ss.formula.eval.ValueEval[], int, int)"?> 

<zk>
	<window title="ZK Spreadsheet" border="normal" height="100%">
		<spreadsheet src="/WEB-INF/books/customFormula.xlsx" 
		maxVisibleRows="250" maxVisibleColumns="40" width="100%" height="100%" 
		showContextMenu="true" showSheetbar="true" showToolbar="true" />
	</window>
</zk>

After declaring them, you can use them in a cell like =EXCHANGE(10, 31.3).