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!
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)
Dim attachment As String = "attachment; filename=FundTRX.xls"
Response.ContentType = "application/vnd.ms-excel"
Response.ContentEncoding = System.Text.Encoding.Default