We use the UltraWebGridExcelExporter on just about all the grids that we have in a specific web project. However, there is one UltraWebGrid that we have that is 57 columns and can have anywhere from 50 rows to 30K rows. The export on this grid takes ~1m 10s per 10K rows to build the excel spreadsheet (this is only if I do it in 1000 row increments binding 1000 rows to the grid, exporting it and then binding the next 1000 and so on.).
So here is the test case.. 20k rows, 57 columns wide, doing an export in 1000 row increments (Any more than 1500 rows at a time the performance goes down logrithmically). Formatting has some background color and stringformatting and alignment. None of the cells contain more data than a char(50). Running IIS7 on a dl320 g6 rack server with 16gig of ram and 4 cores. The IIS app pool is set for unlimited ram usage and does not have any recycle triggers set. We are currently using the Infragistics35 dll set and just upgraded for the test to 2011.1.
When I run and profile the export, It takes almost 2 Gig of ram and 2min 24sec to build the workbook that ends up in a 6 meg excel file.
I am baffled at why it takes so much resources to translate this to excel. Yes, I know it is a lot of columns, we all know as software engineers that these things are not always under our control, client wants the columns, they get the columns. :-) We have known about the export speed as a problem since the 2007 version we first started using. As a result, in 2009, we decided to build a home grown excel exporter specifically to export huge amounts of data. It takes this same UltraWebGrid full of data and processes it into an excel formatted xml file that excel can open. The home grown version exports the 20k rows 57 cols wide in 4 seconds. But it produces a 70meg xml file that I have to transmit to the browser. (the down side)
Every time a new version of infragistics is released we find ourselves grabbing the new version and hoping that the performance has improved so that we can move this grid back to the infragistics exporter.
So my question is. Is infragistics dev staff looking into the speed of this exporter? We really like its ease of use but it is a resource animal. We are needing to move this grid to an xlsx export from the xls export that our home grown exporter does and would much rather use yours as opposed to writing another one because of speed issues.
Can you try exporting fake data and formatting to a workbook manually to cut the grid exporter out of the process? Use the same amount of rows and columns as in a typical use case as well as the same data types and formatting. That will indicate whether the exporter is the problem or the Excel library. There were many memory and performance improvements made to the Excel library in 11.1, so I'm surprised it is still using a large amount of resources.
Ok, I wrote an export method that builds the infragistics workbook and worksheets manually as opposed to using the infragistics exporter to push data to the workbook. This method cut the time down to 32 seconds per 10K rows and reduced the memory usage to about 400M (down from 2G). It is now doing a 20K row 57 column export in about 1M 4s. This same export was taking anywhere between 2M 24s to 2M 54s with the exporter. So I think the major culprit is the exporter.
This is definitely more acceptable in terms of time and memory. Here is the code:
private static bool AddDataToWorksheetManual(UltraWebGrid grid, Worksheet sheet, int StartRow, bool processColumns){ DisplayFormatList format = new DisplayFormatList(); int icols = 0; int irow = StartRow; WorksheetRow headerRow = null; if (processColumns) { foreach (UltraGridColumn col in grid.Columns) { if (irow == StartRow) headerRow = sheet.Rows[irow++]; WorksheetCell wscell = headerRow.Cells[col.Index]; wscell.Value = col.Header.Caption; wscell.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
WorksheetColumn wscol = sheet.Columns[icols++];
if (col.HasCellStyle) { if (!string.IsNullOrEmpty(col.Format)) wscol.CellFormat.FormatString = format.GetExcelFormat(col.Format);
if (col.CellStyle.HorizontalAlign != System.Web.UI.WebControls.HorizontalAlign.NotSet) wscol.CellFormat.Alignment = TranslateAlignment(col.CellStyle.HorizontalAlign); wscol.Width = (int)sheet.Workbook.PixelsToCharacterWidth256ths(col.Width.Value); } } }
//Loop through rows to set data and attributes foreach (UltraGridRow row in grid.Rows) { WorksheetRow wsrow = sheet.Rows[irow++]; if (row.HasStyle) { if (!row.Style.BackColor.IsEmpty) { wsrow.CellFormat.FillPattern = FillPatternStyle.Solid; wsrow.CellFormat.FillPatternForegroundColor = row.Style.BackColor; } }
int icell = 0;
//Loop through cells in the row setting the cell level attributes and values foreach (UltraGridCell cell in row.Cells) { WorksheetCell wscell = wsrow.Cells[icell++]; wscell.Value = cell.Value;
if (cell.HasStyle) { if (!cell.Style.BackColor.IsEmpty) { wscell.CellFormat.FillPattern = FillPatternStyle.Solid; wscell.CellFormat.FillPatternForegroundColor = cell.Style.BackColor; }
if (!string.IsNullOrEmpty(grid.Columns[icell - 1].Format)) wscell.CellFormat.FormatString = format.GetExcelFormat(grid.Columns[icell - 1].Format);
if(cell.Style.HorizontalAlign != System.Web.UI.WebControls.HorizontalAlign.NotSet) wscell.CellFormat.Alignment = TranslateAlignment(cell.Style.HorizontalAlign); else if(grid.Columns[icell -1].CellStyle.HorizontalAlign != System.Web.UI.WebControls.HorizontalAlign.NotSet) wscell.CellFormat.Alignment = TranslateAlignment(grid.Columns[icell -1].CellStyle.HorizontalAlign); } } } return true;}
private static HorizontalCellAlignment TranslateAlignment(System.Web.UI.WebControls.HorizontalAlign alignment){ switch (alignment) { case System.Web.UI.WebControls.HorizontalAlign.Center: return HorizontalCellAlignment.Center; case System.Web.UI.WebControls.HorizontalAlign.Justify: return HorizontalCellAlignment.Justify; case System.Web.UI.WebControls.HorizontalAlign.Left: return HorizontalCellAlignment.Left; case System.Web.UI.WebControls.HorizontalAlign.NotSet: return HorizontalCellAlignment.Default; case System.Web.UI.WebControls.HorizontalAlign.Right: return HorizontalCellAlignment.Right; default: return HorizontalCellAlignment.Default; }}