Custom fucntion evaluate isnt triggered for every cell

Custom fucntion evaluate isnt triggered for every cell

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

• 

  lior.sharabi

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  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

• 

  lior.sharabi

  • Post Options:
  • Link
    Copy

  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

  

  931×442 37.7 KB

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  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.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  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.

• 

  lior.sharabi

  • Post Options:
  • Link
    Copy

  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?

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  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.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  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.