Export more than 100000 rows to Excel - Infragistics Excel exporter

2
votes

I am using ultrawebgrid v11.1 with xml LoadOnDemand and paging enabled to load more than 100,000 records.

I'd written BindGrid() functionality inside the InitializeDataSource event.

In every postback(Save,ExcelExport,Load..) and/or partial postback(Page navigation,Sorting,filtering..), I am fetching more than 100,000 records from the database and setting the Grid's DataSource.

Issue 1:

Each time querying large amount of data from database is taking more time. Is there a way to query for the data only once on load and subsequently when the data in the grid is modified.

Issue 2:

I am using UltrawebgridExcelExporter v11.1. On click of Export button, Grid's data source is initialized inside InitializeDataSource with more than 100,000 records fetched from database.

I have got to know that when xml LoadOnDemand is set, each time when I export to excel, I should set 

AllowPaging = False  

and

LoadOndemand = LoadOnDemand.NotSet

then bind the grid and Export.

Fetching huge data from database is taking long time and binding it is taking even longer. After binding, while exporting to excel it is throwing 'System Out of Memory' exception.

Issue 3:

I am not sure even after solving the issues above, will I be able to export more than 65535 rows in Excel 97-2003?

How to optimize the performance of all the operations of the grid with the xml LoadOnDemand.

Code:

Protected Sub UWGrid_InitializeDataSource(ByVal sender As Object, ByVal e As Infragistics.WebUI.UltraWebGrid.UltraGridEventArgs) Handles UWGrid.InitializeDataSource

UWGrid.DataSource = dsData 'record set with 100000 rows.

End Sub

Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click

UWGrid.DisplayLayout.Pager.AllowPaging = False

UWGrid.DisplayLayout.LoadOnDemand = LoadOnDemand.NotSet

UWGrid.DataBind()

'Dim wBook As New Excel.Workbook(Excel.WorkbookFormat.Excel2007)

UltraWebGridExcelExporter1.DownloadName = "ExportFile.xls"

UltraWebGridExcelExporter1.Export(UWGrid)

End Sub
3
export-to-excelinfragisticsultrawebgrid
Same question posted and answered on Infragistics forums: news.infragistics.com/forums/p/70018/354551.aspx#354551 - roken
@roken - well thats me there, and there is no answer to the question. This is a limitation from Infragistics. - Estefany Velez
What is in the call stack for #2 when you get the OutOfMemoryException? If it is happening when the exporter is writing the workbook to a memory stream, it may be possible to write the file to the file system instead when setting the ExportMode to Custom and then later read the file for download later. - alhalama
@alhalama : yes, it is when exporter is writing to a memory stream. Application architecture does not allow us to create a file. I have to do it in memory. For now, I am exporting to CSV as a temporary solution. - Estefany Velez
@EstefanyVelez Is it an option to create a temporary file, remove references to the excel exporter and the excel objects and then load the temporary file and send to the client? This should allow garbage collection of the objects and that would allow you to export more data. - alhalama

3 Answers

3
votes

To answer your Issue 3 - 65535 rows is not Infragistics limitation, it's a limitation of Excel 97/2003 format. The only way around it is to export in Excel 2007+ format (XLSX)

1
votes

Reply from Infragistics was to split the records across more than one workbooks. Because there is a limitation of not allowing more than 65535 rows.

Though Their new WebGrids resolved that limitation and I resolved mine by exporting to a CSV file.

So no definite answer to the question, only work around :)

0
votes
private void Form1_Shown(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Serial number");
        dt.Columns.Add("Name");
        dt.Columns.Add("Values");
        for (int i = 0; i < 500; i++)
        {
            dt.Columns.Add("Column" + i.ToString());
        }
        for (int i = 0; i < 100000; i++)
        {
            dt.Rows.Add(i.ToString(), "Jipson PJ", (i + 100).ToString());
        }

        ultraGrid1.DataSource = dt;
    }

    private void button1_Click(object sender, EventArgs e)
    {

        Workbook w = new Infragistics.Excel.Workbook();

        //w.CurrentFormat = WorkbookFormat.Excel2007;
        w.Worksheets.Add(this.Text);
        this.ultraGridExcelExporter1.FileLimitBehaviour = Infragistics.Win.UltraWinGrid.ExcelExport.FileLimitBehaviour.TruncateData;

        this.ultraGridExcelExporter1.Export(this.ultraGrid1, w.Worksheets[this.Text]);

        w.Save("C://Users//jipso//Documents//excel//1.xls"); // Save as .xlsx file name.
    }