CODEWITHSUNDEEP
asp.netvb.netexcelexport-to-excel
neeko
neeko

Reputation: 2000

System.OutOfMemoryException when exporting GridView to Excel

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

Upvotes: 2

Views: 4116

Answers (2)

Magnus
Magnus

Reputation: 46947

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

Upvotes: 1

alex.pulver
alex.pulver

Reputation: 2123

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.

Upvotes: 0

Related Questions