Set cell value performance problem

Set cell value performance problem

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

• 

  yyoshimura

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 31 August 2020, 5:00 pm EST - Updated 29 September 2022, 6:37 am 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
  
  

  

  1062×677 13.6 KB

  Environment detail:
  
  

  

  1710×1296 79.9 KB

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 3 November 2020, 10:49 am EST

  Hi

  Surely we’ll upgrade and try the latest version.
  
  Thanks for letting us know that.