AutoFitColumn slow process

AutoFitColumn slow process

Posted by: davide.vago on 28 January 2022, 5:31 am EST

• 

  davide.vago

  • Post Options:
  • Link
    Copy

  Posted 28 January 2022, 5:31 am EST

  Dear support,

  I recently had some performance issues with Worksheet containing a big number of columns and the autoFitColumn method.

  What we are doing is:

  
  const colCount = sheet.getColumnCount()
  for (let i = 0; i < colCount; i++) {
      if (i > 0) {
        sheet.autoFitColumn(i)
      }
  }
  
  

  I’m wondering if we could apply the autoFitColumn for a CellRange and run it once like it could be done with other methods such as setStyle with the -1 values 'cause looping just to “set the width of each column” seems a bit too hardcoded.

  Just this operation takes around 5s (5 seconds is an average from a bunch of performance tests) so I’m wondering if you have some memory leak within the method itself or it’s just need some optimisation.

  I’m not able to build a sample for you as this link: https://demos.componentone.com/Spread/spreadSamplesEvalKey.js has been removed so there is no way to include a license in a sandbox:

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 31 January 2022, 6:01 am EST

  Hi,

  Currently, you can auto-fit columns only using autoFitColumn method. For incorporating a large number of changes, it is recommended to use the suspendPaint method and the resumePaint method. These methods can improve performance significantly.
  
  suspendPaint and resumePaint:
  
  https://www.grapecity.com/spreadjs/docs/v14/online/SpreadJS~GC.Spread.Sheets.Worksheet~autoFitColumn.html
  
  Please refer to the attached sample, and if you face any further issues, modify the sample so that we can assist you accordingly: https://jscodemine.grapecity.com/share/Jl_XaRgezkmwmSOCj2MUzw/
  
  Regards

• 

  davide.vago

  • Post Options:
  • Link
    Copy

  Posted 31 January 2022, 2:24 pm EST

  Hi Avinash,

  We already tried to use the suspendPaint method but the autoFitColumn method does struggle of a big number of data, we are talking about 5k+ rows and 100+ columns injected via the fromJSON method.

  Looking forward to hearing from you

  Please note, methods such as setStyle or setTag are not having the same slow performance.

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 1 February 2022, 11:17 pm EST

  Hi,

  autoFitColumn method iterates through every cell in that column, and then column width is adjusted based on the longest text width. Considering the number of rows and columns you are mentioning, the time taken to carry out this operation is expected(in your case 5sec).

  Regards.

• 

  davide.vago

  • Post Options:
  • Link
    Copy

  Posted 2 February 2022, 7:49 am EST

  Right,

  Would you please provide a proper solution to allow us to “automatically set the width of each column including the usage of cellPadding” that doesn’t require more than a bunch of ms, either by optimising the library code or providing a quick-win snippet?

  ~5 seconds it’s way too long and it’s not even threaded separately from the main thread meaning the whole rendering of the application in which we embed SpreadJS gets stuck (doesn’t respond).

  Unfortunately, setting manually width for each column from our side isn’t a solution either because when you set a width the cellPadding property stops working and you get overlapping issues with the filtering arrow applied to the columns by using the Filtering system.

  Looking forward to hearing from you,
  
  Regards

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 7 February 2022, 1:57 am EST

  Hi,

  Thanks for your recommendation. Since this is causing a large performance issue at your end, we are escalating this to the dev team if any further improvement is possible. We will let you know as soon as we get an update from the dev team. The internal ID for this issue will be SJS-11801

  Regards.

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 15 February 2022, 11:46 pm EST

  Hi,

  The devs informed us that it is a current limitation of auto fitting that many columns.
  
  When auto fit the column, it will calculate all the cell width in one column, no matter the cell is in visible area or not.

  Regards.