Log in to start a topic

Custom Function in template

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

  • anil.kumar

    • Post Options:
    • Link
      Copy

    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

    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

    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

    Posted 29 November 2021, 1:21 am EST

    Please find attachment for same.

    code.zip

  • prabhat.sharma

    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

    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

    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.