ASP.NET export 50,000 records to Excel & generate Pivot Table

Hi experts

I have been searching the site & the web all over and can't find a decent solution. Here's my situation:

I'm using a datagrid (Infragistics UltraWebGrid) to display data on a page. The amount of data can range from 1 to max 50,000 records. I use AJAX to load 100 records at a time. Of course if the user gets a lot of results, then it would not be suitable to display this amount of data in a webpage, hence the user can export the data for further analysis to Excel (currently Excel 2003 at our company).

The infragistics UltraWebGrid offers a Excel Exporter, however this works extremely slowly when exporting large amounts of data. Exporting 50,000 records can take longer than 10 minutes. So I used the below code to speed things up.

What I cannot seem to do, is to add a template or a macro or a pivot table. My final solution should generate an Excel File with a pivot table. Is there any way to get the data from my ASP.NET page into an Excel 2003 document and display it using a pivot table? And this with fast performance?

Any help would be much appreciated!


Sub ExportToExcel()
        Dim dv As System.Data.DataView = sds_Assets.Select(DataSourceSelectArguments.Empty)
        Dim dt As System.Data.DataTable = dv.ToTable
        Dim dgGrid As New DataGrid()

        dgGrid.DataSource = dt
        dgGrid.HeaderStyle.Font.Bold = True
        Dim tw As New System.IO.StringWriter()
        Dim hw As New System.Web.UI.HtmlTextWriter(tw)
        dgGrid.DataBind()
        dgGrid.RenderControl(hw)

        Dim attachment As String = "attachment; filename=FundTRX.xls"
        Response.ClearContent()
        Response.AddHeader("content-disposition", attachment)
        Response.ContentType = "application/vnd.ms-excel"
        Response.ContentEncoding = System.Text.Encoding.Default
        Response.Write(tw.ToString)
        Response.End()
    End Sub

Open in new window

riffrackAsked:
Who is Participating?
 
Amandeep Singh BhullarCommented:
0
 
riffrackAuthor Commented:
Thanks a lot for the link. That is a fantastic solution for retrieving lots of data from a data source. However the database data source can only be a table and does not work with a view or a stored procedure. This is unfortunate, as most production websites never access the tables directly.

If I connect the devexpress grid up with a normal sqldatasource, it is slightly faster, but still far too slow. It is only very fast using the devexpress xpo data source, which only works with tables :-(

Do you or anyone else have any further ideas?
0
 
riffrackAuthor Commented:
I managed to find a working solution. Using Aman's recommendation and combining it with the following blog, I was able to find a superfast way of navigating in large datasets within the browser.

http://community.devexpress.com/blogs/paulk/archive/2009/03/12/using-a-sql-view-with-persistent-classes-xpo.aspx

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.