Set cell value performance problem

Posted by: yyoshimura on 27 August 2020, 9:21 pm EST

  • Posted 27 August 2020, 9:21 pm EST

    Hello

    We encountered a performance problem when invoking lots of setting cell values(about 15000 calls in total that costs 8 seconds). To determine whether the problem exists in our code base or your library, we are using the performance testing code from herehttps://www.grapecity.com/blogs/performance-improvements-documents-for-excel.

    When we are using an empty Workbook object or a new workbook file, the performance of setting cell values is really great. Then we try to use a test workbook we created and insert an empty sheet as the test target, we noticed that the performance is terrible(several thousand loops of 1 million within 2 minutes). See the attachment for the test excel file and test program(notably the TestCellValues method).

    We doubt that this case has something to do with our performance problem in our code base. Hope that you can help us to find the problem of this issue.

    Regards
    Wenjin TestFile.zip
  • Replied 30 August 2020, 7:52 pm EST

    HI Yukinori,

    We too can observe some issues with the 3.1.5 version builds, whereas it is working fine with the latest builds i.e. 3.2.2 at our end. So I would request you to please test with the latest builds and if the issue still occurs then please share the screenshot of the output of both the condition: with Empty WorkBook and with existing Workbook.

    Also, share your environment details.

    Regards,
    Prabhat Sharma.
  • Replied 31 August 2020, 5:00 pm EST

    Hi

    Thank you for your support.
    We can confirm that after updating to version 3.2.2 the performance problem with previous test program is solved. However the performance problem in our code base still exist. So we make another test program to confirm the issue. Please refer the attachment for the latest test. We are setting 15000 or so cell values to the test excel file [101_Budget_1.xlsx] using the info from the [Info.log] file. In my development environment it costs 5 seconds or so which is too slow for our needs.
    Hope that you can take it a look and we'll really appreciate that.

    Test program and files
    gcexcelperformance.zip

    Test result


    Environment detail:


  • Replied 10 September 2020, 8:54 pm EST

    Hello,

    We have forwarded this to the developers and will let you know once we get any update on this from their end.
    [Internal Tracking Id: DOCXLS-3029]

    Regards,
    Prabhat Sharma.
  • Marked as Answer

    Replied 10 September 2020, 5:05 pm EST

    Hi Yukinori,

    As per the developers, the issue will be fixed in the future. Currently, you can cache worksheet instance during setting values to get better performance, like the below code:
     var stopwatch = new Stopwatch();
    stopwatch.Start();
    Dictionary<string, IWorksheet> worksheets = new Dictionary<string, IWorksheet>(); foreach (var addressAndValue in addressAndValues)
    {
    IWorksheet worksheet = null;
    worksheets.TryGetValue(addressAndValue.sheetName, out worksheet);
    if (worksheet == null)
    {
    worksheet = workbook.Worksheets[addressAndValue.sheetName];
    worksheets.Add(addressAndValue.sheetName, worksheet);
    }
    worksheet.Range[addressAndValue.rangeAddress].Value =
    addressAndValue.value;
    } stopwatch.Stop();


    Please refer to the modified file that implements the same.

    Regards,
    Prabhat Sharma.
    GcExcel.cs.zip
  • Replied 10 September 2020, 6:38 pm EST

    Hi

    Thanks for your kind support.
    The performance is much better using your code snippet.
    I'll mark this as answer.

    Regards
  • Replied 31 October 2020, 12:57 pm EST

    Hi Yukinori,

    You can upgrade to the latest builds for more enhanced performance using the code snippet given before.

    Regards,
    Prabhat Sharma.
  • Replied 3 November 2020, 10:49 am EST

    Hi

    Surely we'll upgrade and try the latest version.
    Thanks for letting us know that.
Need extra support?

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

Learn More

Forum Channels