ZK Spreadsheet Essentials/Working with ZK Spreadsheet/Implement Your Own Formula Functions/As Simple as a Java Static Method

ZK Spreadsheet allows developers to write their own custom formula functions. One way of implementing them is as a Java static method & declaring using xel-directive and another one is to implement it as EL function and register it in a TLD. Here we describe the first method.

Purpose

Implement a custom formula function as a Java static method.

Implement formula function

Define your custom formula function as a normal Java static method. For example here is a static method to convert USD to TWD.

public class CurrencyFns {

	public static double toTWD(double usdNum, double twdRate) {
		return usdNum * twdRate;
	}
}

xel-method directive

To be able to use above defined toTWD static method as a custom ZK Spreadsheet formula fuction declare it in your ZUL page using xel-method directive[1] along with ZK Spreadsheet component as shown below

<?page title="ZSS" contentType="text/html;charset=UTF-8"?>
<?xel-method prefix="zss" name="toTWD"
    class="org.zkoss.zss.example.CurrencyFns"   
    signature="double toTWD(double,double)"?>
<zk>
	<window title="ZSS User Defined Functions" border="normal"
		width="100%" height="100%">
		<spreadsheet width="800px" height="800px"
			src="/test2/xls/elfn.xlsx" maxrows="20" maxcolumns="10">
		</spreadsheet>
	</window>
</zk>

Note that xel-method prefix attribute value must be set to "zss" for ZK Spreadsheet to invoke custom implementation for such custom formula function.

Usage

Once defined and declared above custom formula function can be used just like any other built-in functions of Excel.

=toTWD(A1,B1)

All source code listed in this book is at Github.