Uploaded image for project: 'Keikai'
  1. Keikai
  2. KEIKAI-441

Add a registration option in JAVA for user defined FreeRefFunction


    • Icon: New Feature New Feature
    • Resolution: Fixed
    • Icon: Normal Normal
    • 5.8.0
    • None
    • None
    • Security Level: Jimmy
    • None

      User Story

      As a developer, I want to register functions that access the Function evaluation context, in order to read sheet information. This is useful if the function need to provide a result based on the sheet structure itself. Excel includes such functions like "indirect" "cell" or the search functions.

      Additionally, I want to be able to register a function from Java code instead of relying on the ELEvalFunction resolver to find a function from zul / EL context.

      Acceptance Criteria

      A per-spreadsheet and / or a global way to register a FreeRefFunction into the function resolver.
      This could be used with an object or a lambda such as:
      in a local spreadsheet:
      spreadsheet.getFunctionResolver().putFunction(String name, FreeRefFunction function)
      in a global utility:
      FunctionResolvers.putFunction(String name, FreeRefFunction function)


      Put document or specification reference
      currently the org.zkoss.poi.ss.formula.udf.AggregatingUDFFinder will check for functions in the available _usedToolPacks when executing org.zkoss.poi.ss.formula.udf.AggregatingUDFFinder.findFunction(String)
      The custom functions could be added in a new toolpack for a global registration.

      The current UserDefined functions restrict the context passed to the function itself.
      io.keikaiex.formula.ELEvalFunction.evaluate(ValueEval[], OperationEvaluationContext)
      This method recieve the full EvaluationContext, but restrict it to only arguments, row and col when invoking the user defined function.

      Workaround - mockup

      The attached ZKUDFFinder class allow registration of a function from java code.
      It can be used from a composer to add a new function to the function finder, which have access to the evalution context.

      	private Spreadsheet kk;
      	public void doAfterCompose(Component comp) throws Exception {
      		if(ZKUDFFinder.instance.findFunction("TESTFN") == null){
      			FreeRefFunction fn = new FreeRefFunction() {
      				public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
      			((ZKUDFFinder) ZKUDFFinder.instance).putFunction("TESTFN", fn);

            DevChu DevChu
            MDuchemin MDuchemin
            0 Vote for this issue
            3 Start watching this issue