ZK Spreadsheet Essentials/Working with Spreadsheet/Advanced/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.

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