2

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
Estefany Velez
  • 328
  • 4
  • 18
  • Same question posted and answered on Infragistics forums: http://news.infragistics.com/forums/p/70018/354551.aspx#354551 – roken Jun 12 '12 at 12:58
  • @roken - well thats me there, and there is no answer to the question. This is a limitation from Infragistics. – Estefany Velez Jun 12 '12 at 13:45
  • 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 Jul 08 '12 at 19:38
  • @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 Jul 09 '12 at 04:33
  • @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 Jul 09 '12 at 20:45

3 Answers3

3

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)

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
  • That is exactly what I said in my answer. Previous version of Excel Export control from infragistics were using Excel 97/2003 APIs to export data. Their newly launched controls handle this stuff very well now. My question was, how to achieve that in UltrawebgridExcelExporter. – Estefany Velez Jun 13 '12 at 06:52
  • 1
    I myself use UltrawebgridExcelExporter (currently also version 11.1) and it does support the line that you have commented in your code - so you can export XSLX using UltrawebgridExcelExporter – Yuriy Galanter Jun 13 '12 at 12:46
1

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 :)

Estefany Velez
  • 328
  • 4
  • 18
0
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.
    }
Jipson PJ
  • 1