I have a dataset with 10,000 records and I am using the Infragistics.Excel to create a worksheet to present the data. I am looping through the records and write the values to each cell but this is taking 10 minutes for 10,000 rows. Is there a quicker/better way to do this?
I don't see any areas for improvement in your code. As a test, try exporting the values to individual cells instead of merged cell regions and tell me if you see a noticeable improvement there. It's possible the creation of large numbers of merged cell regions is causing a significant slowdown.
Just looping through a table. When is the V11.1 release going to be available?
FOR EACH ttblItemReorderTotals NO-LOCK:
iRow = iRow + 1
iColumn =
0
oItemRegion = oWorksheet:MergedCellsRegions:Add(iRow,0,iRow,1).
oItemRegion:VALUE = "ItemNum: " + ttblItemReorderTotals.ItemNum.
oItemRegion:CellFormat:SetFormatting(oMergedCellFormat).
oProductRegion = oWorksheet:MergedCellsRegions:Add(iRow,2,iRow,4).
oProductRegion:VALUE = "ProductNum: " + ttblItemReorderTotals.ProductNum.
oProductRegion:CellFormat:SetFormatting(oMergedCellFormat).
oUpcRegion = oWorksheet:MergedCellsRegions:Add(iRow,5,iRow,6).
oUpcRegion:VALUE = "UpcNum: " + ttblItemReorderTotals.UpcNum.
oUpcRegion:CellFormat:SetFormatting(oMergedCellFormat).
oLastReceiptRegion = oWorksheet:MergedCellsRegions:Add(iRow,7,iRow,8).
oLastReceiptRegion:VALUE = "Last Receipt: " + ttblItemReorderTotals.LastReceived.
oLastReceiptRegion:CellFormat:SetFormatting(oMergedCellFormat).
Etc.
END
There have been many performance and memory enhancements recently, so try upgrading the latest SR and see if you still have the problem. That is even truer with the 11.1 release, so when that is released, upgrading to that version will also help.
Can I see how you are populating the data? It is possible there are ways to make improvements to your performance by changing the way you set the data.
You can use the UltraGridExcelExporter. Be aware there is a known issue on it exporting "large" amounts of data. Large is yet to be defined, so use cautiously. It does work well and fast when the datagrid is within it's limits.