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.
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).
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.
Mike,
Is there an efficient way to put a box around a range of cells? I was creating 4 CellFormats and then applying the appropriate one to the cell with SetFormatting. However, this increased the time to create the worksheet by 12 times. I looked into a Range but the control does not support that. I looked at the WorksheetRegion but that only appears to allow us to set a formula. Basically, I want to put a box around a group of cells. They all happen to be in the same column.
oWorksheetCell:CellFormat:SetFormatting(oCellformat4)
/* create our cellformat we will use for borders */
oCellFormat1 = oWorkbook:CreateNewWorksheetCellFormat().
oCellFormat1:LeftBorderStyle = CellBorderLineStyle:Double
oCellFormat1:RightBorderStyle = CellBorderLineStyle:
DOUBLE.
oCellFormat2 = oWorkbook:CreateNewWorksheetCellFormat().
oCellFormat2:LeftBorderStyle = CellBorderLineStyle:Double
oCellFormat2:RightBorderStyle = CellBorderLineStyle:
DOUBLE
oCellformat2:TopBorderStyle = CellBorderLineStyle:
oCellFormat3 = oWorkbook:CreateNewWorksheetCellFormat().
oCellFormat3:LeftBorderStyle = CellBorderLineStyle:Double
oCellFormat3:RightBorderStyle = CellBorderLineStyle:
oCellformat3:BottomBorderStyle = CellBorderLineStyle:
oCellFormat4 = oWorkbook:CreateNewWorksheetCellFormat().
oCellFormat4:LeftBorderStyle = CellBorderLineStyle:Double
oCellFormat4:RightBorderStyle = CellBorderLineStyle:
oCellformat4:BottomBorderStyle = CellBorderLineStyle:
Thanks for the info...I will do so.
No there is currently no way to do that. You can submit a feature request for this.