Custom fucntion evaluate isnt triggered for every cell

Posted by: lior.sharabi on 14 March 2022, 6:16 am EST

    • Post Options:
    • Link

    Posted 14 March 2022, 6:16 am EST - Updated 29 September 2022, 3:46 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

  • Posted 15 March 2022, 5:01 am 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

  • Posted 15 March 2022, 2:05 pm EST - Updated 29 September 2022, 3:46 pm 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

  • Posted 17 March 2022, 7:32 am 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.

  • Posted 20 March 2022, 11: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.

  • Posted 22 March 2022, 12:34 pm EST

    Hi

    thank you for the answer

    can we somehow disable the cache so it will call evaluate for every cell?

  • Posted 23 March 2022, 4:39 am EST

    Hello,

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

    Regards,

    Prabhat Sharma.

  • Posted 24 March 2022, 7:16 am 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