C#导出DataTable到Excel两种方案

项目，需要从查询结果里把数据导出到客户端，之前的方案是通过Excel的组件，在服务器生成excel文件，然后下载下来。可以分sheet，但是因为耗内存严重，导出很慢，六万行10列的数据，听说导出要40分钟，我没试过。后来经过改进得到以下两个方案。这两个方案都可以在几秒内生成上面的数据流。

方案一：

因为office2007以下版本，只支持65535行，超过后excel就无法创建或打开。所以需要进行分sheet进行

private static string BuildExportHTML(System.Data.DataTable dt)
    {
        string result = string.Empty;
        int readCnt = dt.Rows.Count;
        int colCount = dt.Columns.Count;

        int pagerecords = 50000;
        result = "<?xml version=\"1.0\" encoding=\"gb2312\"?>";
        result += "<?mso-application progid=\"Excel.Sheet\"?>";
        result += "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ";
        result += "xmlns:o=\"urn:schemas-microsoft-com:office:office\" ";
        result += "xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ";
        result += "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ";
        result += "xmlns:html=\"http://www.w3.org/TR/REC-html40\"> ";
        //以下两部分是可选的
        //result += "<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">";
        //result += "<Author>User</Author>";
        //result += "<LastAuthor>User</LastAuthor>";
        //result += "<Created>2009-03-20T02:15:12Z</Created>";
        //result += "<Company>Microsoft</Company>";
        //result += "<Version>12.00</Version>";
        //result += "</DocumentProperties>";

        //result += "<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">";
        //result += "<WindowHeight>7815</WindowHeight>";
        //result += "<WindowWidth>14880</WindowWidth>";
        //result += "<WindowTopX>240</WindowTopX>";
        //result += "<WindowTopY>75</WindowTopY>";
        //result += "<ProtectStructure>False</ProtectStructure>";
        //result += "<ProtectWindows>False</ProtectWindows>";
        //result += "</ExcelWorkbook>";
        string strTitleRow = "";

       //设置每行的标题行
        strTitleRow = "<Row ss:AutoFitHeight='0'>";
        for (int j = 0; j < colCount; j++)
        {
            strTitleRow += "<Cell><Data ss:Type=\"String\">" + dt.Columns[j].ColumnName + "</Data></Cell>";
        }
        strTitleRow += "</Row>";

        StringBuilder strRows = new StringBuilder();    

        //在变长的字符操作方面stringbuilder的效率比string高得多
        int page = 1;    //分成的sheet数
        int cnt = 1;        //输入的记录数
        int sheetcolnum = 0;        //每个sheet的行数，其实就等于cnt+1
        for (int i = 0; i < readCnt; i++)
        {
            strRows.Append("<Row ss:AutoFitHeight=\"0\">");
            for (int j = 0; j < colCount; j++)
            {

                if (dt.Columns[j].DataType.Name == "DateTime" || dt.Columns[j].DataType.Name == "SmallDateTime")
                {
                    if (dt.Rows[i][j].ToString() != string.Empty)
                    {
                        strRows.Append("<Cell><Data ss:Type=\"String\">" + Convert.ToDateTime(dt.Rows[i][j].ToString()).ToString("yyyy年MM月dd日") + "</Data></Cell>");
                    }
                    else
                        strRows.Append("<Cell><Data ss:Type=\"String\"></Data></Cell>");
                }
                else
                {
                    strRows.Append("<Cell><Data ss:Type=\"String\">" + dt.Rows[i][j].ToString().Trim() + "</Data></Cell>");
                }
            }
            strRows.Append("</Row>");
            cnt++;

             //到设定行数时，要输出一页，防止office打不开，同时要注意string和stringbuilder的长度限制
            if (cnt >= pagerecords+1)
            {
                sheetcolnum = cnt + 1;
                result += "<Worksheet ss:Name=\"Sheet" + page.ToString() + "\"><Table ss:ExpandedColumnCount=\"" + colCount.ToString() + "\" ss:ExpandedRowCount=\"" + sheetcolnum.ToString() + "\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultColumnWidth=\"104\" ss:DefaultRowHeight=\"13.5\">" + strTitleRow.ToString() + strRows.ToString() + "</Table></Worksheet>";
                strRows.Remove(0, strRows.Length);
                cnt = 1;                     //下一个sheet重新计数
                page++;

            }
        }
        sheetcolnum = cnt + 1;
        result = result + "<Worksheet ss:Name='Sheet" + page.ToString() + "'><Table ss:ExpandedColumnCount='" + colCount.ToString() + "' ss:ExpandedRowCount='" + sheetcolnum.ToString() + "' x:FullColumns='1' x:FullRows='1' ss:DefaultColumnWidth='104' ss:DefaultRowHeight='13.5'>" + strTitleRow.ToString() + strRows.ToString() + "</Table></Worksheet></Workbook>";
        return result;
    }

问题：因为这一种方案是以xml格式生成的excel，所以生成的文件特别大，六万条10列的数据大约有30M，对网络传输不利，在局域网内用还是不错的。

第二种方案：采用格式少的方式生成数据，数据量少，网络下载传输时比较方便，同样以上面六万条10列的数据，只有6M左右。缺点是，超过65535，在office2003上就打不开了。

    private static string BuildExportHTML(System.Data.DataTable dt)
    {
        string result = string.Empty;
        int readCnt = dt.Rows.Count;
        int colCount = dt.Columns.Count;

        int pagerecords = 5000;
        string strTitleRow = "";
        for (int j = 0; j < colCount; j++)
        {
            strTitleRow += dt.Columns[j].ColumnName + "\t";
        }
        strTitleRow += "\r\n";

        StringBuilder strRows = new StringBuilder();
        int cnt = 1;
        for (int i = 0; i < readCnt; i++)
        {
            //strRows.Append("");
            for (int j = 0; j < colCount; j++)
            {
                if (dt.Columns[j].DataType.Name == "DateTime" || dt.Columns[j].DataType.Name == "SmallDateTime")
                {
                    if (dt.Rows[i][j].ToString() != string.Empty)
                    {
                        strRows.Append(Convert.ToDateTime(dt.Rows[i][j].ToString()).ToString("yyyy年MM月dd日") + "\t");
                    }
                    else
                        strRows.Append("\t");
                }
                else
                {
                    strRows.Append(dt.Rows[i][j].ToString().Trim() + "\t");
                }
            }
            strRows.Append("\r\n");
            cnt++;
            if (cnt >= pagerecords)
            {
                result += strRows.ToString();
                strRows.Remove(0, strRows.Length);
                cnt = 1;
            }
        }
        result = strTitleRow + result + strRows.ToString();
        return result;
    }

如果谁能把两种方案结合起来的话，告诉我一声，谢谢哦。

送一个调用的方法，可以输出到客户端哦

    public void DataTable2Excel(DataTable dt)
    {
        string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";//设置导出文件的名称
        HttpContext curContext = System.Web.HttpContext.Current;
        curContext.Response.ContentType = "application/vnd.ms-excel";
        curContext.Response.ContentEncoding = System.Text.Encoding.Default;
        curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + fileName));
        curContext.Response.Charset = "";

        curContext.Response.Write(BuildExportHTML(dt));
        curContext.Response.Flush();
        curContext.Response.End();
    }