学步园

如何把一个DataTable中的内容变成Excel然后输出出来。本方法解决了两个问题：

1、利用response.write写出文件后，文件名为乱码

2、数字会自动格式化。如会把00.1自动格式化成0.1，有时候你的确需要00.1，总之，你希望输出的是一个文本，而不是excel默认的东西。

public static void GetExcel(System.Web.HttpResponse response, DataTable dtExcelDataSource, bool isNeedFormat,string fileName="Excel")
    {
        System.Web.UI.WebControls.DataGrid dgExcel = new System.Web.UI.WebControls.DataGrid();
        dgExcel.AutoGenerateColumns = true;
        dgExcel.DataSource = dtExcelDataSource;
        dgExcel.DataBind();
//response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312>");
        response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
        response.AppendHeader("content-disposition", string.Format("attachment;filename={0}.xls", System.Web.HttpUtility.UrlEncode(fileName, Encoding.UTF8)));
//response.ContentEncoding = Encoding.GetEncoding("GB2312");
        response.ContentType = "application/ms-excel";
if (!isNeedFormat)
        {
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            dgExcel.RenderControl(hw);
            response.Write(tw.ToString());
        }
else
        {
            response.Write(ExportTable(dtExcelDataSource));
        }
        response.End();
    }

/// <summary>
/// 将每一列格式化为字符串
/// </summary>
/// <param name="tb"></param>
/// <returns></returns>
    public static string ExportTable(DataTable tb)
    {

//data += tb.TableName + "\n";
        string data = string.Empty;
        data += "<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">";
//写出列名
        data += "<tr style=\"font-weight: bold; white-space: nowrap;\">";
foreach (DataColumn column in tb.Columns)
        {
            data += "<td>" + column.Caption + "</td>";
        }
        data += "</tr>";

//写出数据
        foreach (DataRow row in tb.Rows)
        {
            data += "<tr>";
foreach (DataColumn column in tb.Columns)
            {
                data += "<td style=\"vnd.ms-excel.numberformat:@\">" + row[column].ToString() + "</td>";
            }
            data += "</tr>";
        }
        data += "</table>";


return data;
    }