aspnet中40w行数据怎么导出Excel

    根据我这几天的折腾,最终给用户的方案是,4w行以下数据可以在系统中导出,用openxml sdk生成xlsx文件,生成时占内存200m左右(每行内容不多),在可以接受的范围之内.超过4w行还是我直接从数据库中导出吧...

    不知道大家有没有什么好的方案?

    试过以下方法:

    1 此方法根本不能导出40w行

public static void ToExcel(System.Web.UI.Control ctl, string FileName)
{
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.Charset = "utf-8";
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
    HttpContext.Current.Response.ContentType = "application/ms-excel";
    HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=xxxx.xls");
    ctl.Page.EnableViewState = false;
    System.IO.StringWriter tw = new System.IO.StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(tw);
    ctl.RenderControl(hw);
    HttpContext.Current.Response.Write(tw.ToString());
    HttpContext.Current.Response.End();
}

    2 此方法可以很快导出40w行,占内存也很少(80m),导出的文件60m左右,但是基本没法打开

Stream str = Response.OutputStream;
StreamWriter sw = new StreamWriter(str);
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=xxxx.xls");
string tmp1 = "<td>";
string tmp2 = "</td>";
string tmp3 = "<tr>";
string tmp4 = "</tr>";
if (reader.HasRows)
{
    int dccount = reader.FieldCount;
    sw.WriteLine("<table border=\"1\">");
    sw.Write(tmp3);
    for (int i = 0; i < dccount; i++)
    {
        sw.Write(tmp1);
        sw.Write(reader.GetName(i));
        sw.Write(tmp2);
    }
    sw.WriteLine(tmp4);

    while (reader.Read())
    {
        sw.Write(tmp3);
        for (int k = 0; k < dccount; k++)
        {
            sw.Write(tmp1);
            sw.Write(reader.GetValue(k).ToString());
            sw.Write(tmp2);
        }
        sw.WriteLine(tmp4);
    }
    sw.WriteLine("</table>");
}
reader.Close();
sw.Flush();
sw.Close();
str.Close();

    3 用openxml sdk2.5版本生成标准xlsx文件,内存占用很大,4w行的时候生成文件1.6m左右,解压后18m左右,内存占用200m左右.40w行我试了占内存将近1.8G...

Response.ContentType = "application/vnd.ms-excel";
string filename = name;
if (Request.UserAgent.ToLower().IndexOf("msie") != -1) filename = System.Web.HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8);
Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xlsx");

string cmdstr = Session["search_sql"].ToString();
SqlParameter[] paras = (SqlParameter[])Session["search_para"];
SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, cmdstr, paras);
DataTable datatable = reader.GetSchemaTable();

int dccount = datatable.Rows.Count;
int i;
UInt32 rowindex = 1;

MemoryStream ms = new MemoryStream();
SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);

WorkbookPart workbookpart = spreadSheet.AddWorkbookPart();
workbookpart.Workbook = new Workbook();

WorksheetPart worksheetpart = workbookpart.AddNewPart<WorksheetPart>();
worksheetpart.Worksheet = new Worksheet(new SheetData());

Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

Sheet sheet = new Sheet() { Id = spreadSheet.WorkbookPart.GetIdOfPart(worksheetpart), SheetId = 1, Name = name };
sheets.Append(sheet);

SheetData sheetdata = worksheetpart.Worksheet.GetFirstChild<SheetData>();

Row headerRow = new Row() { RowIndex = rowindex };
Cell cell;

Hashtable columnname = new Hashtable();
char[] tmp = ("ABCDEFGHIJKLMNOPQRSTUVWXYZ").ToCharArray();
for (i = 0; i < dccount; i++)
{
    string tmp1 = "";
    int tmp2 = i;
    int tmp3;
    do
    {
        tmp3 = tmp2 % 26;
        tmp1 = tmp[tmp3] + tmp1;
        tmp2 = (tmp2 - tmp3) / 26 - 1;

    }
    while (tmp2 > -1);
    columnname.Add(i, tmp1);

}
EnumValue<CellValues> celldatatype = new EnumValue<CellValues>(CellValues.String);            

for (i = 0; i < dccount; i++)
{
    cell = new Cell() { CellReference = columnname[i].ToString() + rowindex.ToString(), CellValue = new CellValue(datatable.Rows[i]["ColumnName"].ToString()), DataType = celldatatype };
    headerRow.InsertAt(cell, i);
}
sheetdata.Append(headerRow);
rowindex += 1;

if (reader.HasRows)
{
    while (reader.Read())
    {
        Row row = new Row() { RowIndex = rowindex };
        for (i = 0; i < dccount; i++)
        {
            cell = new Cell() { CellReference = columnname[i].ToString() + rowindex.ToString(), CellValue = new CellValue(reader.GetValue(i).ToString()), DataType = celldatatype };
            row.InsertAt(cell, i);
        }
        rowindex += 1;
        sheetdata.Append(row);
    }
}

reader.Close();

workbookpart.Workbook.Save();
spreadSheet.Close();

ms.Position = 0;
byte[] buffer = new byte[4096];
int k = 0;
long length = 0;
while (length < ms.Length)
{
    k = ms.Read(buffer, 0, 4096);
    Response.OutputStream.Write(buffer, 0, k);
    length += k;
};
ms.Dispose();
Response.Flush();
Response.End();

    不知道大家有没有好的解决方案,或者是优化上面代码的方法.

    拜谢~