Custom Function in template

Posted by: anil.kumar on 22 November 2021, 11:47 pm EST

  • Posted 22 November 2021, 11:47 pm EST

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

    worksheet.getRange("C1").setFormula("=MyConditionalSum(A1:A10)");


    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?
  • Replied 23 November 2021, 3:01 pm 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:

    - Above getValue() statement make it call custom function otherwise no call
    - Pre and post we need to scan usedRatio in our code to set



    Regards,
    Prabhat Sharma.
  • Replied 27 November 2021, 11:53 am 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.
  • Replied 28 November 2021, 4:21 pm EST

    Please find attachment for same.
    code.zip
  • Replied 28 November 2021, 6:10 pm 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.
  • Replied 30 November 2021, 1: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.
  • Replied 5 December 2021, 11:12 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.



    Thanks,
    Anil Kumar
  • Replied 6 December 2021, 10:00 pm 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.
  • Replied 8 December 2021, 2: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
  • Replied 8 December 2021, 2:10 pm EST

    Attachment for same.
    tosendforpoc.zip
  • Replied 9 December 2021, 5:25 pm 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.
  • Replied 9 December 2021, 9:19 pm EST

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




    code-2.zip
  • Replied 12 December 2021, 10:49 pm 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.
  • Marked as Answer

    Replied 20 March 2022, 10:08 pm 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.
Need extra support?

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

Learn More

Forum Channels