Custom Function in template

Custom Function in template

Posted by: anil.kumar on 23 November 2021, 8:47 am EST

• 

  anil.kumar

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 23 November 2021, 8:47 am EST

  How to use custom function which is written in excel template except set it directly in java code like example below.

  [i]worksheet.getRange("C1").setFormula("=MyConditionalSum(A1:A10)");[/i]
  

  https://www.grapecity.com/documents-api-excel-java/docs/online/ManageCustomFunctions.html

  And were we write code when some of function called before processTemplate and some of function will call after it.

  In below code I can able to achieve it with template string {{==textfun(“text1”)}} in template excel file.

  		FindOptions fo = new FindOptions();
  		fo.setLookIn(FindLookIn.Formulas);
  		IRange usedRange = activeSheet.getUsedRange();
  		IRange find = null;
  		do
  		{
  			find = usedRange.find(functionName, find, fo);
  			if(find!=null)
  				System.out.println(find.getValue());
  		}while(find!=null);
  

  Still I have problem here,

  • we need to define sheet.
  • If Used range cover maximum region then operation below slow
  • Above getValue() statement make it call custom function otherwise no call
  • Pre and post we need to scan usedRatio in our code to set value

  Can you help me how I achieve it?

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 24 November 2021, 12:01 am EST

  Hello,

  Apologies for the inconvenience caused.
  
  It would be helpful if you can provide us the Excel file with the specific range in which you are facing the performance issue so that we can replicate the same at our end and assist you further.

  Also, the requirement is not clear with the below-given statements so please describe a brief on this:

  [b]- Above getValue() statement make it call custom function otherwise no call

  • Pre and post we need to scan usedRatio in our code to set[/b]

  Regards,
  
  Prabhat Sharma.

• 

  anil.kumar

  • Post Options:
  • Link
    Copy

  Posted 27 November 2021, 8:53 pm EST

  Please find attachment for same. I am sharing code with template.

  Below are details of files of startup class and template.

  grapecitypocShare/src/main/java/com/java/beans/MyApp.java
  
  grapecitypocShare/src/main/resources/xlsx/sendforpoc.xlsx

  I am finding if template file is big around 12000~ rows and there we need to render data with custom function it is taking more than 12-15 sec.

• 

  anil.kumar

  • Post Options:
  • Link
    Copy

  Posted 29 November 2021, 1:21 am EST

  Please find attachment for same.
  
  code.zip

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 29 November 2021, 3:10 am EST

  Hello,

  Thank you for the attached sample.
  
  We are discussing this issue with the developers and will let you know the updates soon.
  
  [Internal Tracking ID: DOCXLS-5183]

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 30 November 2021, 10:53 pm EST

  Hello,

  Please find the developer’s comments on the issue:

  >If the Used range covers the maximum region then operation below slow
  
  We will improve the performance in the future, but now there no workaround.

  >Above getValue() statement make it call custom function otherwise no call
  
  
  
  Because GcExcel calculates formula on demand, the function only will be called when you get value or call the workbook.Calculate(). In this case, you should call Workbook.Calculate() after calling workbook.processTemplate() to calculate all the formulas.

  >Pre and post he needs to scan usedRatio in his code to set value
  
  Sorry, I can’t understand this sentence, please explain in more detail.

  Regards,
  
  Prabhat Sharma.

• 

  anil.kumar

  • Post Options:
  • Link
    Copy

  Posted 6 December 2021, 8:12 am EST - Updated 29 September 2022, 3:47 pm EST

  Hi Prabhat,
  
  I need to do some custom operation before and after method(processTemplate()) call. To do it, we need to again scan sheet ratio for it.

  I have another point, which I need to check with you. There are any other parallel processing method to render data fast compare to processTemplate method. In my template “processTemplate” method taking approx 6 sec. Please find attachment for same.

  

  907×420 86.5 KB

  Thanks,
  
  Anil Kumar

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 7 December 2021, 7:00 am EST

  Hello Anil,

  >>There are any other parallel processing method to render data fast compare to processTemplate method.
  
  Please share your stripped-down sample which is replicating the issue so that we can investigate and assist you further.

  Regards,
  
  Prabhat Sharma.

• 

  anil.kumar

  • Post Options:
  • Link
    Copy

  Posted 8 December 2021, 11:09 pm EST

  Hi Prabhat,
  
  Please find attachment sample. Only for few data render in a single sheet it is taking 3-sec.

  Need to consider below points:

  • The time completion get increase in processTemplate call, if workbook have many sheets that is not part of render. But processTemplate method maintain scope to scan entire sheet with rows/columns and increase time.
  • There is no control in data render, We have scenarios where we don’t want rows/cells should shift below. Means, I don’t find any template control for InsertShiftDirection for data render.

  Thanks,
  
  Anil

• 

  anil.kumar

  • Post Options:
  • Link
    Copy

  Posted 8 December 2021, 11:10 pm EST

  Attachment for same.
  
  tosendforpoc.zip

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 10 December 2021, 2:25 am EST

  Hello Anil,

  We have asked for the stripped-down sample because just going through the excel file will not be enough to reproduce and investigate this further. So please share the stripped-down sample with minimal code replicating the issue.

  Regards,
  
  Prabhat Sharma.

• 

  anil.kumar

  • Post Options:
  • Link
    Copy

  Posted 10 December 2021, 6:19 am EST - Updated 29 September 2022, 3:47 pm EST

  Hi Prabhat,
  
  Please find attachment for same.
  
  Thanks,
  
  Anil Kumar

  

  871×146 25.2 KB

  code-2.zip

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 13 December 2021, 7:49 am EST

  Hello Anil,

  Thank you for the attachments.
  
  We have forwarded your comments to the developers and will let you know as soon as we get the updates from their end.

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 21 March 2022, 7:08 am EST

  Hello Anil,

  Please find the developers’ comments on this issue:

  1: About the performance issue, your template file is too big(around 12000~ rows), which is a rare use case, not our design use case. We have considered this as an enhancement but there is no ETA yet.

  2: For the custom function call, you should call Workbook.Calculate() after calling workbook.processTemplate().

  3: You need to scan usedRatio in your code to set value
  
  If you call Workbook.Calculate(), you don’t need to scan the usedrange to set value.

  4: There is no control in data render
  
  Because the template language is a declarative syntax， we don’t allow users to control the data render process,

  Regards,
  
  Prabhat Sharma.