System.OutOfMemoryException when exporting GridView to Excel

Question

I have a sub that exports a ASP Gridview to excel, it works fine, however, when there are a large amount of rows I get this error:

Exception of type 'System.OutOfMemoryException' was thrown.

Any ideas how to solve this? Here is my export to excel sub:

Protected Sub btnExportMonthlyUK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportMonth.Click
    Dim title As String
    title = "MonthlyReportUK"

    Response.Clear()
    Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.xls", title))
    Response.Charset = ""
    Response.ContentType = "application/vnd.xls"
    Response.ContentEncoding = Encoding.Unicode
    Response.BinaryWrite(Encoding.Unicode.GetPreamble())
    Dim strWr As New StringWriter()
    Dim HtmlWr As New HtmlTextWriter(strWr)
    monthlyReportsIE.AllowPaging = False
    monthlyReportsIE.DataBind()
    monthlyReportsIE.RenderControl(HtmlWr)
    Response.Write(strWr.ToString())
    Response.End()
End Sub

Magnus · Accepted Answer · 2014-05-28 09:31:30Z

1

You can try rendering the control directly to the output stream by using a StreamWriter and avoid creating a large string in memory.
You can also try setting Response.Buffer to False and the server will send the output to the client directly as it is processed.

Protected Sub btnExportMonthlyUK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportMonth.Click
    Dim title As String
    title = "MonthlyReportUK"

    Response.Clear()
    Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.xls", title))
    Response.Charset = ""
    Response.ContentType = "application/vnd.xls"
    Response.ContentEncoding = Encoding.Unicode
    Response.BinaryWrite(Encoding.Unicode.GetPreamble())
    Response.Buffer = False

    monthlyReportsIE.AllowPaging = False
    monthlyReportsIE.DataBind()

    Using strWr As new StreamWriter(response.OutputStream)
       Using htmlWr As new HtmlTextWriter(strWr)
           monthlyReportsIE.RenderControl(htmlWr)
       End Using 
    End Using 

    Response.End()
End Sub

edited May 28, 2014 at 9:31

answered May 28, 2014 at 9:26

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

neeko Over a year ago

thank you so much for your reply! this looks like it works, except the excel sheet opens in what looks like chinese characters, any way to fix this?

Magnus Over a year ago

@neeko Try specifying the desired encoding to the StreamWriter constructor

Community · Accepted Answer · 2017-05-23 11:48:29Z

0

If this answer is not valid in your case, then you should consider an external library to do the job, because exporting large Excel files as HTML is memory consuming.

Check this sample about how to export the datatable of gridview.

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered May 28, 2014 at 8:54

alex.pulver

2,1232 gold badges34 silver badges33 bronze badges

Collectives™ on Stack Overflow

System.OutOfMemoryException when exporting GridView to Excel

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related