把DataTable匯出成為Excel的方法。

如何把一個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;
    }