• Home
  • About
  • Resume

UltraWebGridExcelExporter: Export more than 65536 rows

By , 05/14/2012 12:37 PM

When exporting data from Infragistics UltraWebGrid into Excel using UltraWebGridExcelExporter ordinary a very basic code similar to this is used:

'
' define Exporter "xMyExporter"
' define UltraWebGrid "xMyGrid" and load grid with data, then:
'
xMyExporter.ExportMode = UltraWebGrid.ExcelExport.ExportMode.Download
xMyExporter.Export(xMyGrid)

That’s it. But, if your data exceed 65536 rows you will get an error:

System.InvalidOperationException: The maximum number of rows in an excel worksheet with the current format is: 65536

This is not Excel Exporter limitation. In fact this is a limitation of Excel 2003 XLS format, which is a default output format of the Excel Exporter. The good news – this can be easily changed. Consider the following code:

'
' define Exporter "xMyExporter"
' define UltraWebGrid "xMyGrid" and load grid with data, then:
'
Dim oBook As New Excel.Workbook(Excel.WorkbookFormat.Excel2007)

xMyExporter.ExportMode = UltraWebGrid.ExcelExport.ExportMode.Download
xMyExporter.Export(xMyGrid, oBook)

In this version we first defined an Excel Workbook (Line 5) and gave it Excel 2007 format. And then used that workbook in actual export (Line 8). Which produces an Excel 2007 XLSX format capable of storing over 1 million rows.

Leave a Reply

Panorama Theme by Themocracy