Register as a TLD

ZK Spreadsheet allows developers to write their own custom formula functions. One way of implementing them is as a Java static method and another one to implement it as EL function and register it in a TLD.

Purpose

Implement a custom formula function as an EL function and register it in a TLD.

Define EL function

Define your custom formula function as an EL function. For example here is an EL function to convert USD to TWD.

public class CurrencyFns {

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

Register and config as a TLD

Register above defined toTWD EL function in a TLD as shown below.

<?xml version="1.0" encoding="UTF-8" ?>
<taglib>
	<uri>http://www.zkoss.org/zss/example/custom</uri>
	<description>
		User defined functions.
	</description>
	<import>
		<import-name>CurrencyFns</import-name>
		<import-class>org.zkoss.zss.example.CurrencyFns</import-class>
	</import>

	<function>
		<name>toTWD</name>
		<function-class>org.zkoss.zss.example.CurrencyFns
		</function-class>
		<function-signature>double toTWD(double,double);</function-signature>
		<description>
			Returns equivalent amount in TWD for the given amount in USD.
		</description>
	</function>
</taglib>

Configure this TLD in /metainfo/tld/config.xml which can be find in the classpath. For example, you could put it under WEB-INF/classes/metainfo/tld/config.xml, or as part of a JAR file.

<config>
	<taglib>
		<taglib-uri>http://www.zkoss.org/zss/example/custom</taglib-uri>
		<taglib-location>/web/WEB-INF/tld/custom.tld</taglib-location>
	</taglib>
</config>

taglib directive

To be able to use above defined toTWD EL Function as a custom ZK Spreadsheet formula fuction declare its registed TLD in your ZUL page using taglib directive [1].

<?page title="ZSS" contentType="text/html;charset=UTF-8"?>
<?taglib uri="http://www.zkoss.org/zss/example/custom" prefix="zss" ?>
<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>

Usage

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

=toTWD(A1,B1)