Custom Formula Functions"

From Documentation
m (correct highlight (via JWB))
 
(34 intermediate revisions by 2 users not shown)
Line 2: Line 2:
  
  
 +
__TOC__
  
 +
{{ZSS EE}}
 
= Overview=
 
= 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 <tt>=MYFORMULA(A1)</tt> 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.
+
Spreadsheet allows developers to implement their own custom functions and use these functions like built-in ones. You can just enter your custom function like <code>=MYFFUNCTION(A1)</code> and it works like other functions. 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:
+
Steps to add a custom function:
# Implement a custom formula with a public static method.
+
# Implement a custom function with a public static method.
# Declare it in a ZUL page.
+
# Declare a custom function in a ZUL page.
 
#: You could declare it with xel-method or custom tag library. Of course, the ZUL page should contain a Spreadsheet component.
 
#: You could declare it with xel-method or custom tag library. Of course, the ZUL page should contain a Spreadsheet component.
  
After completing above steps, you can use the custom formula in Spreadsheet.
+
After completing above steps, you can use the custom function 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==
+
= Only Work in ZK Spreadsheet =
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.  
+
Notice that a custom function is implemented with Java in your project. So the custom function will not be saved into an XLSX file. That means a custom function is only evaluated and recognized when you load a file into ZK Spreadsheet. If you open the exported file with a custom function by MS Excel (or other compatible editors), Excel cannot recognize the custom function and will show <code>#NAME?</code> (or might be other error results) in a cell.
  
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.
+
= Implement a Custom Function =
 +
Basically, to implement a function 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 Function==
 +
If your function 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 function.
 +
 
 +
Assume that we are going to create a money exchange function, <code>EXCHANGE()</code>, that accepts 2 double arguments and return exchange result. The first one is money, and the second one is exchange rate.
 
[[File:zss-essentials-customFormula-exchange.png | center]]
 
[[File:zss-essentials-customFormula-exchange.png | center]]
  
The custom formula can be implemented like:
+
The custom function can be implemented like:
 
<source lang='java'>
 
<source lang='java'>
public class MyCustomFormulas {
+
public class MyCustomFunctions {
  
 
public static double exchange(double money, double exchangeRate) {
 
public static double exchange(double money, double exchangeRate) {
Line 32: Line 39:
 
</source>
 
</source>
  
After declaring it in a ZUL page, you can use this formula like <tt>=EXCHANGE(10, 31.3)</tt> or <tt>=EXCHANGE(C3, D3)</tt>. Spreadsheet will evaluate each argument and pass them to <tt>exchange()</tt> method. If you pass a reference to a range of cells in this formula, e.g.  <tt>=EXCHANGE(C3:C4, D3)</tt>, only the first cell will be evaluated.
+
After declaring it in a ZUL page, you can use this function like <code>=EXCHANGE(10, 31.3)</code> or <code>=EXCHANGE(C3, D3)</code>. Spreadsheet will evaluate each argument and pass them to <code>exchange()</code> method. If you pass a reference to a range of cells in this function, e.g.  <code>=EXCHANGE(C3:C4, D3)</code>, only the first cell will be evaluated.
  
== Intermediate - Multiple Numeric Arguments Formula ==
+
== Intermediate - Multiple Numeric Arguments Function==
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:  
+
If your function needs to accept a range of cells with numeric value or variable number of numeric arguments, you should follow the steps below:  
# Create a class <tt>MyNumericFunction</tt> inherited from <javadoc>MultiOperandNumericFunction</javadoc> and override its <tt>evaluate(double[])</tt>.
+
# Create a class <code>MyNumericFunction</code> inherited from <javadoc directory="zss">org.zkoss.poi.ss.formula.functions.MultiOperandNumericFunction</javadoc> and override its <code>evaluate(double[])</code>.
#: <javadoc>MultiOperandNumericFunction</javadoc> 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 <javadoc>ValueEval</javadoc> by yourself.
+
#: <code>MultiOperandNumericFunction</code> 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 <javadoc directory="zss">org.zkoss.poi.ss.formula.eval.ValueEval</javadoc> by yourself.
# Create a public static method with the signature:
+
# Create a public static method with specific signature:
#; <tt>public static ValueEval yourFormulaName(ValueEval[] , int , int )</tt>
+
#; <code>public static ValueEval yourFunctionName(ValueEval[] , int , int )</code>
 
#: You should not change this signature because Spreadsheet recognize your method by the signature.
 
#: You should not change this signature because Spreadsheet recognize your method by the signature.
# In your static method (<tt>yourFormulaName()</tt>), invoke <tt>MyNumericFunction.evaluate(ValueEval[] , int , int)</tt> to calculate.
+
# In your static method (<code>yourFunctionName()</code>), invoke <code>MyNumericFunction.evaluate(ValueEval[] , int , int)</code> to calculate.
 +
 
  
Assume that we are going to create a custom formula that accepts variable number of numeric arguments and sums them all.
+
Assume that we are going to create a custom function, MYSUBTOTAL(), that accepts variable number of numeric arguments and sums them all.
 
[[File:zss-essentials-customFormula-mysubtotal.png | center]]
 
[[File:zss-essentials-customFormula-mysubtotal.png | center]]
  
This custom formula can be implemented like:
 
<source lang='java' high='4, 7, 17'>
 
public class MyCustomFormulas {
 
  
private static Function MY_SUBTOTAL =  
+
First, we create a class to implement my subtotal function:
new MultiOperandNumericFunction(false, false) {
+
<source lang='java' highlight='19'>
+
public class MySubtotal extends MultiOperandNumericFunction{
@Override
+
 
protected double evaluate(double[] values) throws EvaluationException {
+
protected MySubtotal() {
double sum = 0;
+
// the first parameter determines whether to evaluate boolean value. If
for (int i = 0 ; i < values.length ; i++){
+
// it's true, evaluator will evaluate boolean value to number. TRUE to 1
sum += values[i];
+
// and FALSE to 0.
}
+
// If it's false, boolean value is just ignored.
return sum;
+
// The second parameter determines whether to evaluate blank value.
 +
super(false, false);
 +
}
 +
 +
/**
 +
* inherited method, ValueEval evaluate(ValueEval[] args, int srcCellRow,
 +
* int srcCellCol), will call this overridden method This function depends
 +
* on MultiOperandNumericFunction that evaluates all arguments to double.
 +
* This function sums all double values in cells.
 +
*/
 +
@Override
 +
protected double evaluate(double[] values) throws EvaluationException {
 +
double sum = 0;
 +
for (int i = 0 ; i < values.length ; i++){
 +
sum += values[i];
 
}
 
}
};  
+
return sum;
 +
}
 +
}
 +
</source>
 +
* Line 19: The overloading <code>evaluate(ValueEval[], int, int)</code> it inherits from <code>MultiOperandNumericFunction</code> will process all arguments to a double array and pass it to your overridden method, <code>evaluate(double[])</code>. It can save your effort to deal with each argument. If you encounter a situation that you don't expect, please throw <javadoc directory="zss">org.zkoss.poi.ss.formula.eval.EvaluationException</javadoc>. Because Spreadsheet can handle the exception gracefully.
 +
 
 +
 
 +
Then, create a static method with previously-mentioned signature and delegate method calling to <code>MySubtotal</code>.
 +
<source lang='java' highlight='13'>
 +
public class MyCustomFunctions {
 +
 
 +
private static Function MY_SUBTOTAL = new MySubtotal();
 
 
 +
/**
 +
* This method delegates calling to MySubtotal which implements the function.
 +
* @param args evaluation of all arguments
 +
* @param srcCellRow row index of the cell containing the function under evaluation
 +
* @param srcCellCol column index of the cell containing the function under evaluation
 +
* @return function result
 +
*/
 
public static ValueEval mySubtotal(ValueEval[] args, int srcCellRow, int srcCellCol){
 
public static ValueEval mySubtotal(ValueEval[] args, int srcCellRow, int srcCellCol){
 
return MY_SUBTOTAL.evaluate(args, srcCellRow, srcCellCol);  
 
return MY_SUBTOTAL.evaluate(args, srcCellRow, srcCellCol);  
Line 68: Line 105:
 
}
 
}
 
</source>
 
</source>
* Line 4: <javadoc>MultiOperandNumericFunction</javadoc>'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 13: Delegate method calling to to <code>MySubtotal</code> which implements the function actually.
* Line 7: When another overloading <tt>evaluate()</tt> is invoked (line 17), <javadoc>MultiOperandNumericFunction</javadoc> can evaluate all arguments to double and pass them to your overridden method, <tt>evaluate()</tt>. It can save your effort to evaluate each argument. If you encounter a situation that you don't expect, please throw <javadoc>EvaluationException</javadoc>. Because Spreadsheet can handle the exception gracefully.
 
* Line 17: Delegate method calling to our subclass of <javadoc>MultiOperandNumericFunction</javadoc>.
 
  
== Advanced - Manually-Handled Arguments Formula ==
+
== Advanced - Manually-Handled Arguments Function==
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:
+
If your function needs to handle arguments by yourself instead of always evaluating them as numbers, you should create a public static method with the signature:
  
'''<tt>public static ValueEval yourFormulaName(ValueEval[] , int , int )</tt>'''
+
'''<code>public static ValueEval yourFunctionName(ValueEval[] , int , int )</code>'''
  
You should not change this signature because Spreadsheet recognizes your method by the signature. Spreadsheet will pass all arguments as an array of <javadoc>ValueEval</javadoc>, and you have to process each <javadoc>ValueEval</javadoc> to fulfill your formula's requirement.
+
You should not change this signature because Spreadsheet recognizes your method by the signature. Spreadsheet will pass all arguments as an array of <code>ValueEval</code>, and you have to process each <code>ValueEval</code> to fulfill your function's requirement.
  
  
Here, we demonstrate this approach with a formula that chains multiple text cells into one text. Below screenshot shows its use cases:
+
Here, we demonstrate this approach with a function, <code>CHAIN()</code>,  that chains multiple text cells into one text. Below screenshot shows its use cases:
 
[[File:zss-essentials-customFormula-chain.png | center]]
 
[[File:zss-essentials-customFormula-chain.png | center]]
  
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 <javadoc>ValueEval</javadoc>, and you should handle them in your formula method to make your custom formula support these use cases.
+
You can see that this function 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 <code>ValueEval</code>, and you should handle them in your function method to make your custom function support these use cases.
  
'''The formula to chain text '''
+
'''The function to chain text '''
<source lang='java' high='14, 19, 34, 41, 53, 54'>
+
<source lang='java' highlight='14, 19, 34, 41, 53, 54'>
public class MyCustomFormulas {
+
public class MyCustomFunctions {
  
/**
+
    /**
* Advanced - Manually-Handled Arguments Formula.  
+
    * Advanced - Manually-Handled Arguments Function.  
* This method demonstrates how to evaluate arguments manually.  
+
    * This method demonstrates how to evaluate arguments manually.  
* The method implements a formula that concatenates all texts in cells  
+
    * The method implements a function that concatenates all texts in cells  
* and ignores other types of value.
+
    * and ignores other types of value.
*  
+
    *  
* @param args the evaluated formula arguments
+
    * @param args the evaluated function arguments
* @param srcCellRow unused
+
    * @param srcCellRow unused
* @param srcCellCol unused
+
    * @param srcCellCol unused
* @return calculated result, a subclass of ValueEval
+
    * @return calculated result, a subclass of ValueEval
*/
+
    */
public static ValueEval chain(ValueEval[] args, int srcCellRow, int srcCellCol){
+
    public static ValueEval chain(ValueEval[] args, int srcCellRow, int srcCellCol){
  
List<StringEval> stringList = new LinkedList<StringEval>();
+
        List<StringEval> stringList = new LinkedList<StringEval>();
for (int i = 0 ; i < args.length ; i++){
+
        for (int i = 0 ; i < args.length ; i++){
//process an argument like A1:B2
+
            //process an argument like A1:B2
if (args[i] instanceof TwoDEval) {
+
            if (args[i] instanceof TwoDEval) {
TwoDEval twoDEval = (TwoDEval) args[i];
+
                TwoDEval twoDEval = (TwoDEval) args[i];
int width = twoDEval.getWidth();
+
                int width = twoDEval.getWidth();
int height = twoDEval.getHeight();
+
                int height = twoDEval.getHeight();
for (int rowIndex=0; rowIndex<height; rowIndex++) {
+
                for (int rowIndex=0; rowIndex<height; rowIndex++) {
for (int columnIndex=0; columnIndex<width; columnIndex++) {
+
                for (int columnIndex=0; columnIndex<width; columnIndex++){
ValueEval ve = twoDEval.getValue(rowIndex, columnIndex);
+
                    ValueEval ve = twoDEval.getValue(rowIndex, columnIndex);
if (ve instanceof StringEval){
+
                    if (ve instanceof StringEval){
stringList.add((StringEval)ve);
+
                        stringList.add((StringEval)ve);
}
+
                        }
}
+
                    }
}
+
                }
continue;
+
                continue;
}
+
            }
//process an argument like C18
+
            //process an argument like C18
if (args[i] instanceof RefEval){
+
            if (args[i] instanceof RefEval){
ValueEval valueEval = ((RefEval)args[i]).getInnerValueEval();
+
                ValueEval valueEval = ((RefEval)args[i]).getInnerValueEval();
if (valueEval instanceof StringEval){
+
                if (valueEval instanceof StringEval){
stringList.add((StringEval)valueEval);
+
                    stringList.add((StringEval)valueEval);
}
+
                }
continue;
+
                continue;
}
+
            }
if (args[i] instanceof StringEval){
+
            if (args[i] instanceof StringEval){
stringList.add((StringEval)args[i]);
+
                stringList.add((StringEval)args[i]);
continue;
+
                continue;
}
+
            }
  
}
+
        }
//chain all string value
+
        //chain all string value
StringBuffer result = new StringBuffer();
+
        StringBuffer result = new StringBuffer();
for (StringEval s: stringList){
+
        for (StringEval s: stringList){
result.append(s.getStringValue());
+
            result.append(s.getStringValue());
}
+
        }
  
//throw EvaluationException if encounter error conditions
+
        //throw EvaluationException if encounter error conditions
return new StringEval(result.toString());
+
        return new StringEval(result.toString());
}
+
    }
  
 
}
 
}
 
</source>
 
</source>
* Line 14: You should create a public static method with the same signature because Spreadsheet recognizes your formula method by the signature.
+
* Line 14: You should create a public static method with the same signature because Spreadsheet recognizes your function method by the signature.
* Line 19: <javadoc>TwoDEval</javadoc> 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 19: <javadoc directory="zss">org.zkoss.poi.ss.formula.TwoDEval</javadoc> is a common interface that represents a range of cells. Process it to make your function accepts an argument like A1:B2. In our example, we just get each text cell of it and ignore others.
* Line 34: <javadoc>RefEval</javadoc> represents an evaluation of a cell reference like "C18".  
+
* Line 34: <javadoc directory="zss">org.zkoss.poi.ss.formula.eval.RefEval</javadoc> represents an evaluation of a cell reference like "C18".  
* Line 41: <javadoc>StringEval</javadoc> is the evaluation result of a string like "abc".
+
* Line 41: <javadoc directory="zss">org.zkoss.poi.ss.formula.eval.StringEval</javadoc> is the evaluation result of a string like "abc".
* Line 53: We recommend you to throw an <javadoc>EvaluationException</javadoc> when you encounter an error condition. Because Spreadsheet will catch and handle it gracefully for you.
+
* Line 53: We recommend you to throw an <code>EvaluationException</code> when you encounter an error condition. Because Spreadsheet will catch and handle it gracefully for you.
* Line 54: Return an object of <javadoc>ValueEval</javadoc>'s subtype according to your result.
+
* Line 54: Return an object of <code>ValueEval</code>'s subtype according to your result.
  
= Declare Custom Formula on a ZUL Page =
+
= Declare a Custom Function in 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.
+
After implementing a method for a custom function, we can use one of the following ways to bring it in a ZUL page before using it in Spreadsheet.
  
 
== Using EL Method ==
 
== 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.
+
Use xel-method directive is quite straight out. Just write it on the ZUL page with prefix '''zss''' and that is done. Please refer to [[ZUML_Reference/ZUML/Processing_Instructions/xel-method]] for complete explanation of each attribute.
  
For our custom formula, we can write:
+
For our custom function, we can write:
<source lang='xml' >
+
<source lang='xml'  highlight='1'>
  
 
<?xel-method prefix="zss" name="EXCHANGE"
 
<?xel-method prefix="zss" name="EXCHANGE"
     class="org.zkoss.zss.essential.advanced.MyCustomFormulas"   
+
     class="org.zkoss.zss.essential.advanced.MyCustomFunctions"   
 
     signature="double exchange(double,double)"?>
 
     signature="double exchange(double,double)"?>
 
<?xel-method prefix="zss" name="MYSUBTOTAL"
 
<?xel-method prefix="zss" name="MYSUBTOTAL"
     class="org.zkoss.zss.essential.advanced.MyCustomFormulas"   
+
     class="org.zkoss.zss.essential.advanced.MyCustomFunctions"   
 
     signature="org.zkoss.poi.ss.formula.eval.ValueEval mySubtotal(org.zkoss.poi.ss.formula.eval.ValueEval[], int, int)"?>
 
     signature="org.zkoss.poi.ss.formula.eval.ValueEval mySubtotal(org.zkoss.poi.ss.formula.eval.ValueEval[], int, int)"?>
 
<?xel-method prefix="zss" name="CHAIN"
 
<?xel-method prefix="zss" name="CHAIN"
     class="org.zkoss.zss.essential.advanced.MyCustomFormulas"   
+
     class="org.zkoss.zss.essential.advanced.MyCustomFunctions"   
     signature="org.zkoss.poi.ss.formula.eval.ValueEval chain(org.zkoss.poi.ss.formula.eval.ValueEval[], int, int)"?>  
+
     signature="org.zkoss.poi.ss.formula.eval.ValueEval chain(org.zkoss.poi.ss.formula.eval.ValueEval[], int, int)"?>
 
+
<?taglib uri="/WEB-INF/tld/function.tld" prefix="zss" ?>   
 
<zk>
 
<zk>
 
<window title="ZK Spreadsheet" border="normal" height="100%">
 
<window title="ZK Spreadsheet" border="normal" height="100%">
<spreadsheet src="/WEB-INF/books/customFormula.xlsx"  
+
<spreadsheet src="/WEB-INF/books/customFunction.xlsx"  
 
maxVisibleRows="250" maxVisibleColumns="40" width="100%" height="100%"  
 
maxVisibleRows="250" maxVisibleColumns="40" width="100%" height="100%"  
 
showContextMenu="true" showSheetbar="true" showToolbar="true" />
 
showContextMenu="true" showSheetbar="true" showToolbar="true" />
Line 181: Line 216:
 
</zk>
 
</zk>
 
</source>
 
</source>
 +
* Line 1: Notice that "prefix" attribute must be set to "zss" for ZK Spreadsheet to find custom functions.
  
 
<!--  
 
<!--  
Line 186: Line 222:
 
-->
 
-->
  
After declaring them, you can use them in a cell like <tt>=EXCHANGE(10, 31.3)</tt>.
+
After declaring them, you can use them in a cell like <code>=EXCHANGE(10, 31.3)</code>.
  
 
==Using Tag Library ==
 
==Using Tag Library ==
To use taglib directive, we should create a taglib file and specify its file path in <tt>uri</tt> attribute of  taglib directive. Please refer to [[ZUML_Reference/ZUML/Processing_Instructions/taglib/Custom_Taglib]] for details. We list our sample configuration here:
+
To use taglib directive, we should create a taglib file and specify its file path in <code>uri</code> attribute of  taglib directive and set "prefix" to '''zss'''. Please refer to [[ZUML_Reference/ZUML/Processing_Instructions/taglib/Custom_Taglib]] for details. We list our sample configuration here:
  
'''formula.tld'''
+
'''function.tld'''
 
<source lang='xml'>
 
<source lang='xml'>
 +
 
<?xml version="1.0" encoding="UTF-8" ?>
 
<?xml version="1.0" encoding="UTF-8" ?>
 
<taglib>
 
<taglib>
Line 200: Line 237:
 
     </description>
 
     </description>
 
     <import>
 
     <import>
         <import-name>MyCustomFormulas</import-name>
+
         <import-name>MyCustomFunctions</import-name>
         <import-class>org.zkoss.zss.essential.advanced.MyCustomFormulas</import-class>
+
         <import-class>org.zkoss.zss.essential.advanced.MyCustomFunctions
 +
</import-class>
 
     </import>
 
     </import>
 
   
 
   
 
     <function>
 
     <function>
 
         <name>MYEXCHANGE</name>
 
         <name>MYEXCHANGE</name>
         <function-class>org.zkoss.zss.essential.advanced.MyCustomFormulas
+
         <function-class>
 +
org.zkoss.zss.essential.advanced.MyCustomFunctions
 
         </function-class>
 
         </function-class>
         <function-signature>double exchange(double,double);</function-signature>
+
         <function-signature>
 +
double exchange(double,double);
 +
</function-signature>
 
         <description>
 
         <description>
            Exchange one money to another one according to specified exchange rate.
+
        Exchange one money to another one according to specified exchange rate.
 
         </description>
 
         </description>
 
     </function>
 
     </function>
 
</taglib>
 
</taglib>
 +
 
</source>
 
</source>
  
  
 
Declare it in a ZUL page.
 
Declare it in a ZUL page.
<source lang='xml' high='1'>
+
<source lang='xml' highlight='1'>
<?taglib uri="/WEB-INF/tld/formula.tld" prefix="zss" ?>     
+
<?taglib uri="/WEB-INF/tld/function.tld" prefix="zss" ?>     
 +
<zk>
 +
<window title="ZK Spreadsheet" border="normal" height="100%">
 +
<spreadsheet src="/WEB-INF/books/customFunction.xlsx"
 +
maxVisibleRows="250" maxVisibleColumns="40" width="100%" height="100%"
 +
showContextMenu="true" showSheetbar="true" showToolbar="true" />
 +
</window>
 +
</zk>
 +
</source>
 +
* Line 1: Notice that "prefix" attribute must be set to "zss" for ZK Spreadsheet to find custom functions.
 +
 
 +
 
 +
After completing above steps, you can use this custom function in Spreadsheet like <code>=MYEXCHANGE(5, 31.1)</code>.
 +
 
 +
= Override Built-in Function =
 +
 
 +
If you give your customized function the same name as built-in function's, it will override Spreadsheet built-in functions. Your customized function will be invoked instead of built-in one.
 +
 
 +
 
 +
'''Override LEN()'''
 +
<source lang='xml' highlight='1, 3'>
 +
<?xel-method prefix="zss" name="LEN"
 +
    class="org.zkoss.zss.essential.advanced.MyCustomFunctions" 
 +
    signature="org.zkoss.poi.ss.formula.eval.ValueEval myLen(org.zkoss.poi.ss.formula.eval.ValueEval[], int, int)"?>
 
<zk>
 
<zk>
 
<window title="ZK Spreadsheet" border="normal" height="100%">
 
<window title="ZK Spreadsheet" border="normal" height="100%">
<spreadsheet src="/WEB-INF/books/customFormula.xlsx"  
+
<spreadsheet src="/WEB-INF/books/overrideFunction.xlsx"  
 
maxVisibleRows="250" maxVisibleColumns="40" width="100%" height="100%"  
 
maxVisibleRows="250" maxVisibleColumns="40" width="100%" height="100%"  
 
showContextMenu="true" showSheetbar="true" showToolbar="true" />
 
showContextMenu="true" showSheetbar="true" showToolbar="true" />
Line 228: Line 293:
 
</zk>
 
</zk>
 
</source>
 
</source>
 +
* In above page, if we use <code>LEN()</code> in a formula, <code>myLen()</code> will be invoked.
 +
 +
 +
  
After completing above steps, you can use this custom formula in Spreadsheet like <tt>=MYEXCHANGE(5, 31.1)</tt>.
+
{{ZKSpreadsheetEssentialsPageFooter}}

Latest revision as of 12:50, 19 January 2022


Custom Formula Functions




Available in ZK Spreadsheet EE only

Overview

Spreadsheet allows developers to implement their own custom functions and use these functions like built-in ones. You can just enter your custom function like =MYFFUNCTION(A1) and it works like other functions. 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 function:

  1. Implement a custom function with a public static method.
  2. Declare a custom function in a ZUL page.
    You could declare it with xel-method or custom tag library. Of course, the ZUL page should contain a Spreadsheet component.

After completing above steps, you can use the custom function in Spreadsheet.


Only Work in ZK Spreadsheet

Notice that a custom function is implemented with Java in your project. So the custom function will not be saved into an XLSX file. That means a custom function is only evaluated and recognized when you load a file into ZK Spreadsheet. If you open the exported file with a custom function by MS Excel (or other compatible editors), Excel cannot recognize the custom function and will show #NAME? (or might be other error results) in a cell.

Implement a Custom Function

Basically, to implement a function 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 Function

If your function 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 function.

Assume that we are going to create a money exchange function, EXCHANGE(), that accepts 2 double arguments and return exchange result. The first one is money, and the second one is exchange rate.

Zss-essentials-customFormula-exchange.png

The custom function can be implemented like:

public class MyCustomFunctions {

	public static double exchange(double money, double exchangeRate) {
		return money * exchangeRate;
	}	
}

After declaring it in a ZUL page, you can use this function 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 function, e.g. =EXCHANGE(C3:C4, D3), only the first cell will be evaluated.

Intermediate - Multiple Numeric Arguments Function

If your function 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 specific signature:
    public static ValueEval yourFunctionName(ValueEval[] , int , int )
    You should not change this signature because Spreadsheet recognize your method by the signature.
  3. In your static method (yourFunctionName()), invoke MyNumericFunction.evaluate(ValueEval[] , int , int) to calculate.


Assume that we are going to create a custom function, MYSUBTOTAL(), that accepts variable number of numeric arguments and sums them all.

Zss-essentials-customFormula-mysubtotal.png


First, we create a class to implement my subtotal function:

public class MySubtotal extends MultiOperandNumericFunction{

	protected MySubtotal() {
		// 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.
		super(false, false);
	}
	
	/**
	 * inherited method, ValueEval evaluate(ValueEval[] args, int srcCellRow,
	 * int srcCellCol), will call this overridden method This function depends
	 * on MultiOperandNumericFunction that evaluates all arguments to double.
	 * This function sums all double values in cells.
	 */
	@Override
	protected double evaluate(double[] values) throws EvaluationException {
		double sum = 0;
		for (int i = 0 ; i < values.length ; i++){
			sum += values[i];
		}
		return sum;
	}
}
  • Line 19: The overloading evaluate(ValueEval[], int, int) it inherits from MultiOperandNumericFunction will process all arguments to a double array and pass it to your overridden method, evaluate(double[]). It can save your effort to deal with each argument. If you encounter a situation that you don't expect, please throw EvaluationException. Because Spreadsheet can handle the exception gracefully.


Then, create a static method with previously-mentioned signature and delegate method calling to MySubtotal.

public class MyCustomFunctions {

	private static Function MY_SUBTOTAL = new MySubtotal();
	
	/**
	 * This method delegates calling to MySubtotal which implements the function.
	 * @param args evaluation of all arguments
	 * @param srcCellRow row index of the cell containing the function under evaluation
	 * @param srcCellCol column index of the cell containing the function under evaluation
	 * @return function result
	 */
	public static ValueEval mySubtotal(ValueEval[] args, int srcCellRow, int srcCellCol){
		return MY_SUBTOTAL.evaluate(args, srcCellRow, srcCellCol); 
	}
}
  • Line 13: Delegate method calling to to MySubtotal which implements the function actually.

Advanced - Manually-Handled Arguments Function

If your function 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 yourFunctionName(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 function's requirement.


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

Zss-essentials-customFormula-chain.png

You can see that this function 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 function method to make your custom function support these use cases.

The function to chain text

public class MyCustomFunctions {

    /**
     * Advanced - Manually-Handled Arguments Function. 
     * This method demonstrates how to evaluate arguments manually. 
     * The method implements a function that concatenates all texts in cells 
     * and ignores other types of value.
     * 
     * @param args the evaluated function 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 function method by the signature.
  • Line 19: TwoDEval is a common interface that represents a range of cells. Process it to make your function 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 a Custom Function in a ZUL Page

After implementing a method for a custom function, 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 prefix zss and that is done. Please refer to ZUML_Reference/ZUML/Processing_Instructions/xel-method for complete explanation of each attribute.

For our custom function, we can write:

<?xel-method prefix="zss" name="EXCHANGE"
    class="org.zkoss.zss.essential.advanced.MyCustomFunctions"  
    signature="double exchange(double,double)"?>
<?xel-method prefix="zss" name="MYSUBTOTAL"
    class="org.zkoss.zss.essential.advanced.MyCustomFunctions"  
    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.MyCustomFunctions"  
    signature="org.zkoss.poi.ss.formula.eval.ValueEval chain(org.zkoss.poi.ss.formula.eval.ValueEval[], int, int)"?>
<?taglib uri="/WEB-INF/tld/function.tld" prefix="zss" ?>    
<zk>
	<window title="ZK Spreadsheet" border="normal" height="100%">
		<spreadsheet src="/WEB-INF/books/customFunction.xlsx" 
		maxVisibleRows="250" maxVisibleColumns="40" width="100%" height="100%" 
		showContextMenu="true" showSheetbar="true" showToolbar="true" />
	</window>
</zk>
  • Line 1: Notice that "prefix" attribute must be set to "zss" for ZK Spreadsheet to find custom functions.


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

Using Tag Library

To use taglib directive, we should create a taglib file and specify its file path in uri attribute of taglib directive and set "prefix" to zss. Please refer to ZUML_Reference/ZUML/Processing_Instructions/taglib/Custom_Taglib for details. We list our sample configuration here:

function.tld

<?xml version="1.0" encoding="UTF-8" ?>
<taglib>
    <uri>http://www.zkoss.org/zss/essentials/custom</uri>
    <description>
        User defined functions.
    </description>
    <import>
        <import-name>MyCustomFunctions</import-name>
        <import-class>org.zkoss.zss.essential.advanced.MyCustomFunctions
		</import-class>
    </import>
 
    <function>
        <name>MYEXCHANGE</name>
        <function-class>
		org.zkoss.zss.essential.advanced.MyCustomFunctions
        </function-class>
        <function-signature>
		double exchange(double,double);
		</function-signature>
        <description>
        Exchange one money to another one according to specified exchange rate.
        </description>
    </function>
</taglib>


Declare it in a ZUL page.

<?taglib uri="/WEB-INF/tld/function.tld" prefix="zss" ?>    
<zk>
	<window title="ZK Spreadsheet" border="normal" height="100%">
		<spreadsheet src="/WEB-INF/books/customFunction.xlsx" 
		maxVisibleRows="250" maxVisibleColumns="40" width="100%" height="100%" 
		showContextMenu="true" showSheetbar="true" showToolbar="true" />
	</window>
</zk>
  • Line 1: Notice that "prefix" attribute must be set to "zss" for ZK Spreadsheet to find custom functions.


After completing above steps, you can use this custom function in Spreadsheet like =MYEXCHANGE(5, 31.1).

Override Built-in Function

If you give your customized function the same name as built-in function's, it will override Spreadsheet built-in functions. Your customized function will be invoked instead of built-in one.


Override LEN()

<?xel-method prefix="zss" name="LEN"
    class="org.zkoss.zss.essential.advanced.MyCustomFunctions"  
    signature="org.zkoss.poi.ss.formula.eval.ValueEval myLen(org.zkoss.poi.ss.formula.eval.ValueEval[], int, int)"?>
<zk>
	<window title="ZK Spreadsheet" border="normal" height="100%">
		<spreadsheet src="/WEB-INF/books/overrideFunction.xlsx" 
		maxVisibleRows="250" maxVisibleColumns="40" width="100%" height="100%" 
		showContextMenu="true" showSheetbar="true" showToolbar="true" />
	</window>
</zk>
  • In above page, if we use LEN() in a formula, myLen() will be invoked.



All source code listed in this book is at Github.


Last Update : 2022/01/19

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.