Fill Spreadsheet 1 row at a time instead of one cell at a time for performance

Fill Spreadsheet 1 row at a time instead of one cell at a time for performance

Posted by: abhishek1991rana on 17 August 2020, 9:23 am EST

• 

  abhishek1991rana

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 17 August 2020, 9:23 am EST

  Hello,
  
  I have a spreadsheet which can have ‘N’ number of rows and upto 15 columns.
  
  Right now We are populating the data into spreadsheet 1 cell at a time as follows:

  ActiveSheet.SetActiveCell(0,0) ;
  
  ActiveSheet.ActiveCell.ForeColor = SystemColors.GrayText;
  
  ActiveSheet.ActiveCell.Text = “some text”;

  And this continues in a while loop for all cells in the spreadsheet until the entire data is populated.

  But now, i want to populate 1 row at a time to get better spreadsheet performance.

  I am willing to try the following approach, select the active row and then fill the data as:
  
  ActiveSheet.ColumnCount = 15;
  
  ActiveSheet.AddSelection(lCurRow, 0, 1, ActiveSheet.ColumnCount);

  and then somehow try to fill the entire row with data in 1 go inside a for loop for all rows.
  
  But how can we fill the complete data at one go in the row and not set cell by cell???

  Thanks,

• 

  Ruchir.Agarwal

  

  • Post Options:
  • Link
    Copy

  Posted 18 August 2020, 6:32 am EST

  Hi,

  To improve performance, you can try to use the SuspendLayout method and the ResumeLayout methods (of FpSpread class) while adding data to each cell? This will help improve the performance by reducing the loading time. The control does not offer setting row-wise data.
  
  I notice that you are using the Text property to set the data in a cell, to improve performance please use the Value property instead and if you assigning celltypes to alot of cells, see if there is a way to set the celltype to the entire column or row if possible.
  
  Additionally, please turn off the AutoUpdateNotes and AutoCalculation.

  Regards,
  
  Ruchir