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.
I notice that you are using the indexer to get the row for each cell. This can be relatively slow. I assume you are exporting on a row by row basis, and for each row, setting the separate cells values in order. If so, you can store the row in a local variable at the start of each iteration of the loop for rows This way you are only asking for each row once. The same if true for getting the cells with the indexer. If you need to get the same cell multiple times, store it in a local variable so you are only using the indexer once. These indexers will get slower and slower as more data is added to the worksheet, so that might explain why you are noticing the slowdown later in the process.
Also, in 11.1, you will no longer need to get cells. The row has methods such as SetCellValue and GetCellFormat which you can use to increase performance. This prevents cell objects from needing to be created, because the data is actually stored on the rows.
This sounds good.