'System.OutOfMemoryException' was thrown when Exporting Data in Excel from .NET code

Question

I have a code to debug where I am attempting to export data in Excel. It works fine for smaller data but when data size increases to several thousands, I get 'Out of Memory exception'. My application is running on IIS 6.0. Any recommendations?

PS: The process usually fails when it takes more than 1.2 GB of memory (looking at the Task Manager)

Protected Overrides Sub Render(ByVal writer As System.Web.UI.HtmlTextWriter)
  If (IsExportToCSV) Then
  Response.Clear()
        Response.ContentType = "application/vnd.ms-excel"

        Response.ContentType = "a"
        'Remove the charset from the Content-Type header.
        Response.Charset = ""
        'Turn off the view state.
        Page.EnableViewState = False
        Dim tw As System.IO.StringWriter
        tw = New System.IO.StringWriter
        Dim hw As HtmlTextWriter = New HtmlTextWriter(tw)
        'Get the HTML for the control.
        Me.GridView1.AllowPaging = False
        Me.GridView1.DataBind()
        Me.GridView1.EnableViewState = False
        Me.GridView1.AutoGenerateEditButton = False
        Me.GridView1.RenderControl(hw)
        Response.Write(tw.ToString)
        Response.Flush()
        Response.End()

    Else
        MyBase.Render(writer)
    End If

End Sub

It works fine when it is less than 10,000. It is failing when record count goes up. I have tested it for 60,000 and above it fails there. — Farhan Haque
– Farhan Haque, Commented Aug 28, 2012 at 14:34

Marcel · Accepted Answer · 2014-10-30 07:12:09Z

Perfect Solution!

I've been fighting this one for a while and the solution is so simple: Don't build a string to pass to response write as the string exist in memory, instead put each line directly into the response using multiple Response.Write calls

example for DataTable converted to HTML table and being sent to client as excel...

string contentType = "Reports.xls";
string fileName = "application/msexcel";

Response.Clear();
Response.Buffer = true;
Response.ContentType = contentType;
string attachment = string.Format("attachment; filename=\"{0}\"", fileName);
Response.AddHeader("Content-Disposition: ", attachment);
byte[] preamble = Encoding.UTF8.GetPreamble();
Response.BinaryWrite(preamble);

string strFontSize = "12";

Response.Write("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" \r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\" \r\n xmlns=\"http://www.w3.org/TR/REC-html40\">");
Response.Write(@"<head>");
Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=windows-1252\">");
Response.Write("<meta name=ProgId content=Excel.Sheet>");
Response.Write("<meta name=Generator content=\"Microsoft Excel 11\">");
Response.Write(@"</head>");
Response.Write(@"<body>");

Response.Write(@"<table style='width: 100%; font-size:" + strFontSize + ";' border='1'>");

foreach (DataColumn column in dt.Columns)
    {
        Response.Write(@"<td><b>" + column.ColumnName + "</b></td>");
    }
Response.Write(@"</tr>");

int intCell = 0;
string strCell = "";
foreach (DataRow row in dt.Rows)
    {
        intCell = 0;
        Response.Write(@"<tr>");
        foreach (DataColumn column in dt.Columns)
        {
            strCell = row.ItemArray[dt.Columns[column.ColumnName].Ordinal].ToString().Trim();
            Response.Write(@"<td>" + strCell + "</td>");
        }
        Response.Write(@"</tr>");
    }
Response.Write(@"</table>");
Response.Write(@"</body>");
Response.Write(@"</html>");
Response.End();

Felice Pollano · Accepted Answer · 2012-08-28 14:29:47Z

1

write each line directly info the response stream instead of passing trough the StringWriter

answered Aug 28, 2012 at 14:29

Felice Pollano

33.4k10 gold badges77 silver badges125 bronze badges

1 Comment

Farhan Haque Over a year ago

Thanks, Felice. Could you provide me an example?

Collectives™ on Stack Overflow

'System.OutOfMemoryException' was thrown when Exporting Data in Excel from .NET code

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related