从.NET code在Excel导出数据时'System.OutOfMemoryException的'被抛出 [英] 'System.OutOfMemoryException' was thrown when Exporting Data in Excel from .NET code

问题描述

我有一个code调试在那里我试图数据在Excel导出。它工作正常的更小的数据，但是当数据量增大到几千，我得到内存异常。我的应用程序在IIS 6.0上运行。任何建议？

PS：当它超过1.2 GB的内存（看任务管理器）的过程通常失败

 受保护的覆盖分渲染（BYVAL作家作为System.Web.UI.HtmlTextWriter）
  如果（IsExportToCSV）然后
  Response.Clear（）
        Response.ContentType =应用程序/ vnd.ms-EXCEL        Response.ContentType =一个
        取下Content-Type头的字符集。
        Response.Charset的=
        关闭的视图状态。
        Page.EnableViewState =假
        昏暗的总重量为System.IO.StringWriter
        TW =新System.IO.StringWriter
        昏暗汉·王充的HtmlTextWriter =新的HtmlTextWriter（TW）
        获取该控件的HTML。
        Me.GridView1.AllowPaging =假
        Me.GridView1.DataBind（）
        Me.GridView1.EnableViewState =假
        Me.GridView1.AutoGenerateEditButton = FALSE
        Me.GridView1.RenderControl（HW）
        的Response.Write（tw.ToString）
        Response.Flush（）
        到Response.End（）    其他
        MyBase.Render（作家）
    万一结束小组
 

解决方案

完美的解决方案！

我一直在争取这一个了一段时间，该解决方案是如此简单：不要建一个字符串作为字符串存在于内存中传递给反应写，而是把每行直接插入使用多个响应的响应。写调用

例如，对于数据表转换成HTML表格并发送到客户端为Excel ...

 字符串的contentType =Reports.xls;
字符串文件名=应用程序/ msexcel的;Response.Clear（）;
将Response.Buffer =真;
Response.ContentType = contentType中;
带安装=的String.Format（附件;文件名= \\{0} \\，文件名）;
Response.AddHeader（内容处置，附件）;
字节[] preamble = Encoding.UTF8.Get preamble（）;
Response.BinaryWrite（preamble）;串strFontSize =12;的Response.Write（＆LT; HTML的xmlns：O = \\瓮：架构 - 微软-COM：办公室：办公\\\\ r \\ n的xmlns：X = \\瓮：架构 - 微软-COM：办公室：EXCEL \\ \\ r \\ n的xmlns = \\HTTP：//www.w3.org/TR/REC-html40 \\＆gt;中）;
的Response.Write（@＆LT; HEAD＆gt;中）;
的Response.Write（＆LT; META HTTP-EQUIV =内容类型内容= \\text / html的;字符集=窗口1252 \\＆gt;中）;
的Response.Write（＆LT; META NAME =的ProgId含量= Excel.Sheet＆GT;）;
的Response.Write（＆LT; META NAME =生成内容= \\Micr​​osoft Excel中11 \\＆gt;中）;
的Response.Write（@＆LT; /头＆GT;）;
的Response.Write（@＆LT;身体GT;）;的Response.Write（@＆LT;表样式=宽度：100％; FONT-SIZE：+ strFontSize +，边界='1'＆gt;中）;的foreach（在dt.Columns的DataColumn列）
    {
        的Response.Write（@＆LT; TD＆GT;＆LT; B＆gt;中+ column.ColumnName +＆LT; / B＆GT;＆LT; / TD＆gt;中）;
    }
的Response.Write（@＆LT; / TR＆gt;中）;INT intCell = 0;
字符串strCell =;
的foreach（在dt.Rows的DataRow行）
    {
        intCell = 0;
        的Response.Write（@＆LT; TR＆gt;中）;
        的foreach（在dt.Columns的DataColumn列）
        {
            strCell = row.ItemArray [dt.Columns [column.ColumnName] .Ordinal]的ToString（）修剪（）。
            的Response.Write（@＆LT; TD＆gt;中+ strCell +＆LT; / TD＆gt;中）;
        }
        的Response.Write（@＆LT; / TR＆gt;中）;
    }
的Response.Write（@＆LT; /表＆gt;）;
的Response.Write（@＆LT; /身体GT;）;
的Response.Write（@＆LT; / HTML＆gt;中）;
到Response.End（）;
 

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

解决方案

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();

这篇关于从.NET code在Excel导出数据时'System.OutOfMemoryException的'被抛出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！