把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;
}

 

转载于:https://www.cnblogs.com/sinianyufang/archive/2011/01/17/1937561.html