Custom fucntion evaluate isnt triggered for every cell

Posted by: lior.sharabi on 13 March 2022, 9:16 pm EST

  • Posted 13 March 2022, 9:16 pm EST

    Hi everyone

    we created custom function and implement the evaluate function



    public class PyramidFunction extends CustomFunction {

    public PyramidFunction() {
    super("PA.Pyramid", FunctionValueType.Text, createParameters());
    }

    static Parameter[] createParameters() {
    }

    @Override
    public Object evaluate(Object[] objects, ICalcContext iCalcContext) {
    }
    }


    we put 2 cells with the pyramid function, both of them on the same column:



    when i'm putting a breakpoint on the evaluate function it's pausing only for the first cell!.

    when i'm moving the second cell to be in another column it will call evaluate also for him.

    after i played with it, it's seems like cells with same syntax that are on the same column will call evaluate function only once.

    is this on purpose?
    how can we avoid it?
    thanks
  • Replied 14 March 2022, 8:01 pm EST

    Hello,

    We do not face the issue at our end with the attached sample.
    The same custom formula is put in the C1 and C2 cells and the evaluate method is called for both.

    If you are doing anything else then please let us know and modify the sample accordingly.

    customFormula.zip
  • Replied 15 March 2022, 5:05 am EST

    Hi

    thanks for the reply.

    I did a small project for reproducing the issue,
    please have a look

    testSameCol_2.zip

    i made 2 json strings, one with function cells in the same column and one without.
    when you switch between them you can see that the evaluate called once for the first json and twice for the second


  • Replied 16 March 2022, 10:32 pm EST

    Hello,

    Thank you for the sample attached.
    We can observe the difference and escalated this to the developers for investigation.
    [Internal Tracking ID: DOCXLS-5712]

    Regards,
    Prabhat Sharma.
  • Replied 20 March 2022, 2:13 pm EST

    Hello,

    As per the developers, the reason for the different evaluation times between the same and different columns is "calc engine cache". GcExcel would store the cache of the formulas in the same column which has the same parameters.

    And we would not create cache if they are in the same row. So in SameCol, we call evaluate only once and in DifferentCol, we call evaluate twice. That's due to the cache of formula design.

    Regards,
    Prabhat Sharma.
  • Replied 22 March 2022, 3:34 am EST

    Hi

    thank you for the answer
    can we somehow disable the cache so it will call evaluate for every cell?
  • Replied 22 March 2022, 7:39 pm EST

    Hello,

    We are discussing this with the developers and will let you know the updates soon.

    Regards,
    Prabhat Sharma.
  • Marked as Answer

    Replied 23 March 2022, 10:16 pm EST

    Hello,

    As per the developers, there is no way to disable the cache and it is not recommended too. It would cause bad performance when calculating.
    Could you explain why you want to disable it when the formula are exact the same?

    Regards,
    Prabhat Sharma.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels