Recently I was working on a report where I need to display data in Excel sheet instead of PDF etc. So I designed the query which was returning more than 80,000 records for a given date criteria.
I used the technique of exporting data from gridview to Excel. Since the process is slow but most effcient where you are not required to create worksheet object. Things are managed by ASP.NET because Gridview has inbuilt functionality of exporting data to Excel.
Note: I was having MS office 2003 installed on my PC. there Excel 2003
I used the below code
Public Sub ExportDataToExcel()
' A function that bind data to GridView
bindDataToGridViewControl()
Response.Clear()
Response.Buffer = True
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Response.Write("")
Response.Write(vbCr & vbLf)
Response.Write("")
Dim tw As New StringWriter
Dim hw As New HtmlTextWriter(tw)
' Gridview that have data
GridView1.RenderControl(hw)
Response.AppendHeader("content-disposition","attachment;filename=Excel.xls")
Response.Write(tw.ToString())
Response.End()
End Sub
Issues:I started getting Exception "System.OutOfMemoryException"
After doing lot is searching on Internet, I came to know that Excel 2003's sheet has a limitation where a sheet can stored maximum of 65,536 records in one sheet.
If client has office 2010 installed or later version, one single sheet can contain more than 10,00,0000 records.
So the solution would be better to breakdown your DataTable records into multiple sets, and bind each set of records with each sheet. For that you need to created Worksheet object and each and ever column will be defnied by you.
I used the technique of exporting data from gridview to Excel. Since the process is slow but most effcient where you are not required to create worksheet object. Things are managed by ASP.NET because Gridview has inbuilt functionality of exporting data to Excel.
Note: I was having MS office 2003 installed on my PC. there Excel 2003
I used the below code
Public Sub ExportDataToExcel()
' A function that bind data to GridView
bindDataToGridViewControl()
Response.Clear()
Response.Buffer = True
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Response.Write("")
Response.Write(vbCr & vbLf)
Response.Write("")
Dim tw As New StringWriter
Dim hw As New HtmlTextWriter(tw)
' Gridview that have data
GridView1.RenderControl(hw)
Response.AppendHeader("content-disposition","attachment;filename=Excel.xls")
Response.Write(tw.ToString())
Response.End()
End Sub
Issues:I started getting Exception "System.OutOfMemoryException"
After doing lot is searching on Internet, I came to know that Excel 2003's sheet has a limitation where a sheet can stored maximum of 65,536 records in one sheet.
If client has office 2010 installed or later version, one single sheet can contain more than 10,00,0000 records.
So the solution would be better to breakdown your DataTable records into multiple sets, and bind each set of records with each sheet. For that you need to created Worksheet object and each and ever column will be defnied by you.
