Custom Formula Functions"
Line 34: | Line 34: | ||
== Intermediate - Multiple Numeric Arguments Formula == | == 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 <javadoc>MultiOperandNumericFunction</javadoc>. | + | 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 <tt>MyNumericFunction</tt> inherited from <javadoc>MultiOperandNumericFunction</javadoc> and override its <tt>evaluate(double[])</tt>. | ||
+ | # Create a public static method with the signature <tt>public static ValueEval yourFormulaName(ValueEval[] , int , int )</tt> | ||
+ | #: 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. | ||
− | <source lang='java'> | + | Assume that we are going to create a custom formula named MYSUBTOTAL that accepts variable number of numeric arguments and sums them all. |
+ | |||
+ | <source lang='java' high='4, 7, 17'> | ||
public class MyCustomFormulas { | public class MyCustomFormulas { | ||
Line 57: | Line 63: | ||
} | } | ||
</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 7: <javadoc>MultiOperandNumericFunction</javadoc> can evaluate all arguments to double and pass them to your overridden method. It can save your effort to evaluate each argument. | ||
+ | * Line 17: Delegate method calling to our subclass of <javadoc>MultiOperandNumericFunction</javadoc>. | ||
Revision as of 10:13, 17 July 2013
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:
- Implement a public static method
- Declare it in a ZUL page with Spreadsheet component
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 MyNumericFunction inherited from MultiOperandNumericFunction and override its evaluate(double[]).
- 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.
- In your static method (yourFormulaName()), invoke MyNumericFunction.evaluate(ValueEval[] , int , int) to calculate.
Assume that we are going to create a custom formula named MYSUBTOTAL 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: MultiOperandNumericFunction can evaluate all arguments to double and pass them to your overridden method. It can save your effort to evaluate each argument.
- Line 17: Delegate method calling to our subclass of MultiOperandNumericFunction.
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());
}
}