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
Did you also try storing the row so you don't have to get it for each cell in that row? That is the major thing that I can think of which might help the degrading performance you are experiencing. I just mentioned storing the cell because it is a similar fix which also saves some time.
I tried as you suggested storing the cell in a local variable. It works just fine but no improvement in performance. I think our issue was definitely related to using the Cellformat on each cell.
oWorksheetCell = oWorksheet:Rows:Item[iRow]:Cells:Item[iColumn].
oWorksheetCell:Value = "Column " + STRING (iColumn) + " Row " + STRING (iRow).
I will give this a shot. Thanks!!
This sounds good.
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.