0

invoke custom functions

asked 2013-06-07 00:02:38 +0800

phasthal gravatar image phasthal
5 3

updated 2013-07-17 01:00:18 +0800

I have created a custom function (to add the values from the given range), but this method is never getting invoked and not even throws any exception. What could be the wrong?

public static double addRange(Range r1, Range r2){
    // logic goes here
    System.out.println("adding ranges");        
    return 1.00;
}

Where as, when I tried this below function, which is getting invoked and working fine.

public static int add(int l, int m){
        System.out.println("addition");
        return l*m;
}

and my zul file is

<?page title="ZSS" contentType="text/html;charset=UTF-8"?>
    <?xel-method prefix="zss" name="add"
        class="org.zkoss.zssessentials.functions.CustomFunction"  
        signature="int add(int, int)"?>
    <?xel-method prefix="zss" name="addRange"
        class="org.zkoss.zssessentials.functions.CustomFunction"  
        signature="double addRange(org.zkoss.zss.model.Range, org.zkoss.zss.model.Range)"?>         
    <zk>
        <window title="ZSS User Defined Functions as Java static method" border="normal"
            width="100%" height="100%">
            <spreadsheet width="800px" height="800px"
                src="/WEB-INF/customfunctions.xlsx" maxrows="20" maxcolumns="10">
            </spreadsheet>
        </window>
    </zk>
delete flag offensive retag edit

Is there any workaround or solution for this?

phasthal ( 2013-07-17 01:01:38 +0800 )edit

4 Answers

Sort by ยป oldest newest most voted
0

answered 2013-07-17 05:54:11 +0800

jimmyshiau gravatar image jimmyshiau
4921 5
http://www.zkoss.org/ ZK Team

Hi phasthal

Why do you need a range as a parameter? do you want to implement a function like this: sum(A1:A10)?

link publish delete flag offensive edit
0

answered 2013-07-17 17:42:50 +0800

phasthal gravatar image phasthal
5 3

Hi jimmyshiau,

Yes, I need to some math calculations based on given ranges.

link publish delete flag offensive edit

Comments

Hi jimmyshiau, Can you please let me know if this is feasible to do?

phasthal ( 2013-07-22 08:31:51 +0800 )edit
0

answered 2013-07-23 04:18:32 +0800

jimmyshiau gravatar image jimmyshiau
4921 5
http://www.zkoss.org/ ZK Team

Hi phasthal,

I have created a sample to calculate the sum of the range

import java.util.List;

import org.zkoss.poi.ss.formula.eval.ErrorEval;
import org.zkoss.poi.ss.formula.eval.EvaluationException;
import org.zkoss.poi.ss.formula.eval.ValueEval;
import org.zkoss.poi.ss.formula.functions.Function;
import org.zkoss.poi.ss.formula.functions.MultiOperandNumericFunction;
import org.zkoss.poi.ss.formula.functions.NumericFunction;
import org.zkoss.zssex.formula.fn.UtilFns;

/**
 * @author sam
 *
 */
public class CustomizeFormula {

    public static final ValueEval foo(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
        return FOO.evaluate(args, srcRowIndex, srcColumnIndex);
    }

    public static final ValueEval bar(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
        return BAR.evaluate(args, srcRowIndex, srcColumnIndex);
    }

    public static final Function FOO = new NumericFunction() {

        @Override
        public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

            final List ls = UtilFns.toList(args, srcRowIndex, srcColumnIndex);
            if (ls.isEmpty()) {
                throw new EvaluationException(ErrorEval.DIV_ZERO);
            }

            for (int i = 0; i < ls.size(); i++) {
                System.out.println(ls.get(i).toString());
            }

            return 3.1415;
        }
    };

    public static final Function BAR = new MultiOperandNumericFunction(false, false) {

        @Override
        protected double evaluate(double[] values) throws EvaluationException {
            for (int i = 0; i < values.length; i++) {
                System.out.println(values[i]);
            }

            return 3.1415;
        }
    };
}

zul page

<?page title="new page title" contentType="text/html;charset=UTF-8"?>
<?xel-method prefix="zss" name="foo"
        class="org.zkoss.zss.app.test.CustomizeFormula"
        signature="org.zkoss.poi.ss.formula.eval.ValueEval foo(org.zkoss.poi.ss.formula.eval.ValueEval[] args, int
            srcRowIndex, int srcColumnIndex)"?>
<?xel-method prefix="zss" name="bar"
        class="org.zkoss.zss.app.test.CustomizeFormula"
        signature="org.zkoss.poi.ss.formula.eval.ValueEval bar(org.zkoss.poi.ss.formula.eval.ValueEval[] args, int
            srcRowIndex, int srcColumnIndex)"?>
<zk>
<spreadsheet id="spreadsheet" src="/xls/empty.xlsx"
        maxrows="100" maxcolumns="20" width="100%" vflex="1">
</spreadsheet>
</zk>
link publish delete flag offensive edit
0

answered 2013-07-23 23:08:25 +0800

phasthal gravatar image phasthal
5 3

Thanks jimmyshiau,

Actually these functions (foo and bar) returning the values in a given range(s) into a single array. i.e. (ValueEval[] args) is having values from all the arguments.

For example: foo(B1:B2, C1:C2), this will return total four values into args[] and it is difficult to find a particular value belongs which range (either B or C).

Is there any method/API to get individual arguments, because I need to do calculation by getting value from each cell of the row (iterate) from the given range(s) :

i.e. (B1Value / 12) * (C1Value/100) + (B2Value / 12) * (C2Value/100) and so on..

link publish delete flag offensive edit
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!

[hide preview]

Question tools

Follow
4 followers

RSS

Stats

Asked: 2013-06-07 00:02:38 +0800

Seen: 39 times

Last updated: Jul 23 '13

Support Options
  • Email Support
  • Training
  • Consulting
  • Outsourcing
Learn More