.NET Export large gridview data to excel

0
votes

I'm having trouble using exporting a very large amount of data to excel. I have a .net 4.0 web page that binds a datatable to a gridview and then attempts to render the gridview in excel. I have a very large SQL table with over 200 columns that I need to export to excel. The user is allowed to enter a date range which limits the amount of rows that are returned. When a date range spans a few across a few months, no data is displayed and the screen freezes. I believe my issue is related to the amount of columns(over 200) that are bound to the gridview. Is there another way to do this? How can I successfully export a large amount of data to excel?

I'm using the code below to render the grid -

Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition","attachment;filename=Test.csv")
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Using sw As New StringWriter()
 Using htw As New HtmlTextWriter(sw)
  Dim gv As As new GridView
  gv.DataSource = dt
  gv.DataBind()
  gv.RenderControl(htw)
  Response.Write(sw.ToString())
  Response.End()
1
.netexcelexportexport-to-excel

1 Answers

0
votes

You can use an Excel library like EasyXLS. EasyXLS hadles very well large volume of data.
Check this code sample about how to export the gridview to Excel: http://www.easyxls.com/manual/FAQ/export-gridview-to-excel.html