Custom Formula Functions"
Line 33: | Line 33: | ||
− | == Intermediate - Multiple Arguments | + | == 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>. | ||
− | = | + | <source lang='java'> |
+ | 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); | ||
+ | } | ||
+ | } | ||
+ | </source> | ||
+ | |||
+ | == Advanced - Manually-Handled Arguments Formula == | ||
+ | |||
+ | <source lang='java'> | ||
+ | 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()); | ||
+ | } | ||
+ | |||
+ | } | ||
+ | </source> | ||
= Declare Custom Formula on a ZUL Page = | = Declare Custom Formula on a ZUL Page = |
Revision as of 09:32, 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 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());
}
}