Hiding rows is very slow on some workbook

Hiding rows is very slow on some workbook

Posted by: yyoshimura on 7 September 2022, 11:33 am EST

• 

  yyoshimura

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 7 September 2022, 11:33 am EST

  Hi,

  We tried to hide rows in a workbook by using row.Hidden property but it performed very slowly. Each row.Hidden property value setting costs 2 or 3 seconds on our end(with 2000 rows it costs over one hour!). Please refer attachment for reproduction. We are using the latest V5.2.1 version.

  Please help to look at this issue. Thank you.

  Regards.

  gcexcelperformance.zip

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 7 September 2022, 6:31 pm EST

  Hello,

  Thank you for reporting this issue.
  
  We too can observe the issue and escalate this to the development team for investigation. We will let you know as soon as we get any update on this from their end.
  
  [Internal Tracking ID: DOCXLS-6588]

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 8 September 2022, 10:49 pm EST

  Hello,

  As per the developers, there are a lot of dynamic array formulas in your given file which caused the performance problem, so as a workaround you can disable the calcengine before changing the hidden of rows, when all the operations are completed, then enable the calcengine. Please see the attached code snippet for the same:

    workbook.EnableCalculation = false;
              var range = workbook.ActiveSheet.Range["FS9:FS1583"];
              for (var rowIndex = 0; rowIndex < range.RowCount; rowIndex++)
              {
                  stopwatch.Reset();
                  stopwatch.Start();
                     var row = range.Rows[rowIndex];
                  row.Hidden = false;
  
                  stopwatch.Stop();
  
                  Console.WriteLine($"row hidden costs: {stopwatch.ElapsedMilliseconds}ms");
              }
              workbook.EnableCalculation = true;
  

  Regards,
  
  Prabhat Sharma.

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 11 September 2022, 11:15 am EST

  Thank you for your quick reply. We tried your advice and it performed as expected!

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 3 November 2022, 5:12 pm EST

  Hello,

  We have made some performance improvements in the current release 5.2.4, please checkout the latest version to see the changes.
  
  https://www.nuget.org/packages/GrapeCity.Documents.Excel/

  Regards,

  Prabhat Sharma.

• 

  yyoshimura

  • Post Options:
  • Link
    Copy

  Posted 6 November 2022, 11:39 am EST

  Thank you for your reply. It is working much faster though not as fast as turning off the calculation. Appreciate for the performance improvement.