I am creating an Excel 2007 workbook with 10,000 rows and 20 columns. When I first start assigning values to cell it takes about 8 - 10 ms to to assign the 20 columns for each row.
After adding 100 rows it then takes about 13 - 15 ms to assign the 20 columns.
After 500 rows it then takes about 25ms to assign the 20 columns.
After 2000 rows it then takes 80ms to assign the 20 columns.
After 5000 rows it then takes about 200ms to assign the 20 columns.
After 7000 rows it takes about 260ms to assign the 20 columns.
After 8000 rows it takes about 300ms to assign the 20 columns.
After 9000 rows it takes about 350ms to assign the 20 columns.
At 10000 rows it is taking about 400ms to assign the 20 columns.
Is this correct behavior? As the workbook grows it takes longer and longer to assign values to cells?
We are using version 9.2.20092.2119
It depends. What kind of data is being added to the cells? Are there formulas being applied to cells? Are you caching the current row in a local variable when assigning to the columns or are you getting the same row for each cell being populated with data? A snippet of the code you are using to populate the cells would help me provide assistance. Also, there were many performance and memory improvements in version 11.1, so upgrading to that version might help.
We were assigning the FormatString, Alignment and other Cellformat properties for each row. We moved that to assign those properties at the Column and then only for the cell if it needs to be different. In our teasting it reduced the time to create a worksheet from 5 minutes to 14 seconds. On some larger worksheets it reduced the time from 30 minutes to 2 minutes. Thanks for the response and we are moving to V11 in the next few months.
FOR EACH ttblItemAnalysisTotals:
/* add ProductNum */
ASSIGN
iColumn = iColumn + 1
/* now the actual data */
oWorksheet:Rows:
Item[iRow]:Cells:Item[iColumn]:Value = ttblItemAnalysisTotals.ProductNum
/* oWorksheet:Rows:Item[iRow]:Cells:Item[iColumn]:CellFormat:FormatString = "@"*/
/* oWorksheet:Rows:Item[iRow]:Cells:Item[iColumn]:CellFormat:Alignment = Infragistics.Excel.HorizontalCellAlignment:Center*/
.
END.