[整理]表格数据/DataSet导出到Excel
时间：2010-09-30 来源：jes
项目里需要导出数据到Excel的功能，参考了网上的几篇文章，加上自己的整理，放在博客上，方便大家和自己查阅。有什么地方需要改进，或者有bug，均可以留言指出，不胜感激。
参考资料：
http://www.cnblogs.com/xiaotao823/archive/2008/09/26/1299364.html
这篇博文整理的很好，按照Winform和Web两种情况，将各种方法汇总，并且指出 WinForm 和Web两种情况下不同的实现原理：
“虽然都是实现导出excel的功能，但在asp.net和winform的程序中，实现的代码是各不相同的。在asp.net中，是在服务器端读取数据，在服务器端把数据以ms-excel的格式，以Response输出到浏览器（客户端)；而在winform中，是把数据读到客户端（因为winform运行端就是客户端），然后调用客户端安装的office组件，将读到的数据写在excel”（好像抄的有点多了，不过确实很精辟。）
http://www.cnblogs.com/stswordman/archive/2006/08/24/485641.html
这篇译文提供了另外一种将GridView数据导出到Excel的方法，而且将遇到的问题和解决方法都说清楚了，很好。
http://blog.csdn.net/tippointGmail/archive/2008/06/27/2591219.aspx
这篇文章是将DataSet的数据导出到Excel，解决了纯数字类型的问题。输出HTML代码的方式
DataSet直接导出到Excel，主要有一下几个缺点：
1.数字类型可能会出现2.01053E+15的格式，最后一位都变成0；或者开头的0丢失了；
2.DataSet中所有的列都导出了；
3.表头是数据库的字段名，用户不清楚具体含义；
而第三篇参考链接提供的方法，解决了第一个问题。而且式样也比较不错。如果GridView的数据项就够了，不需要的列的话，可以采用第二个链接提到的方法。这种方法和页面上看到的差不多，除了式样可能稍微差点外。
下面是我根据以上几篇参考整理的代码
导出文件类型
/// <summary>
导出文件类型
/// 导出文件类型
/// </summary>
public enum ExportFileType
{
/// <summary>
/// Excel格式
/// </summary>
EXCEL = 0,
/// <summary>
/// XML格式
/// </summary>
XML = 1
}
随便写的，主要还是导出到Excel。
子方法：Http流输出文件的设置
/// <summary>/// 子方法：Http流输出文件的设置
/// </summary>
/// <param name="resp"></param>
/// <param name="typeid"></param>
/// <param name="FileName"></param>
private static void SetHttpResponseFile(ref HttpResponse resp, ExportFileType typeid, string FileName)
{
resp.Clear();
resp.ClearContent();
resp.Buffer = true;
resp.Charset = "GB2312";
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
//NOTE: 文件名必须指定为Unicode，否则会出现乱码.System.Text.Encoding.UTF8
resp.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.ASCIIEncoding.UTF8));
switch (typeid)
{
default: //break;
case ExportFileType.EXCEL:
resp.ContentType = "application/vnd.MS-Excel";//设置输出文件类型为excel文件。
//if (!FileName.EndsWith(".xls"))
//{
// FileName += ".xls";
//}
break;
case ExportFileType.XML:
resp.ContentType = "application/XML";//TODO: 不知道正确否？
break;
}
}
后面经常要设置输出流，所以干脆提取出来独立方法。
将DataSet中的数据导出Excel（Web 版）
/// <summary>/// 将DataSet中的数据导出Excel（Web 版）
/// 用法: CreateExcel(page, ds, ExportFileType.EXCEL,"Data.xls");
/// </summary>
/// <param name="p"></param>
/// <param name="ds">参数ds应为填充有数据表的DataSet</param>
/// <param name="typeid">导出文件类型，默认是Excel</param>
/// <param name="FileName">文件名是全名，包括后缀名，如Excel2006.xls</param>
public static void ExportDataToFile(System.Web.UI.Page p, DataSet ds, ExportFileType typeid, string FileName)
{
//原理：要把DataSet中的数据导出，也就是把DataSet中的表中的各行信息，
//以ms-excel的格式输出（Response）到客户端（http流），这样就可以了。
//这种方法已知的缺点：1.数字类型可能会出现2.01053E+15的格式，最后一位都变成0；或者开头的0丢失了；
//2.DataSet中所有的列都导出了；
//3.表头是数据库的字段名，用户不清楚具体含义；
HttpResponse resp = p.Response;
if (ds == null || ds.Tables.Count == 0)
{
resp.Write(@"<script type='text/language'>window.alert('没有可导出的数据!');</script>");
return;
}
SetHttpResponseFile(ref resp, typeid, FileName);
//定义表对象和行对象，同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select("");//可以类似dt.Select("id>10")之形式达到数据筛选目的
switch (typeid)
{
default: //break;
case ExportFileType.EXCEL:
int ColCount = dt.Columns.Count;
int i = 0;
#region 表头
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
string colHeaders = "";
for (i = 0; i < ColCount; i++)
{
colHeaders += dt.Columns[i].Caption;
colHeaders += (i == ColCount - 1) ? "\n" : "\t";
}
resp.Write(colHeaders);//向HTTP输出流中写入取得的数据信息
#endregion
#region 逐行处理数据
string ls_item = string.Empty;
foreach (DataRow row in myRow)
{
//在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n
for (i = 0; i < ColCount; i++)
{
ls_item += row[i].ToString();
ls_item += (i == ColCount - 1) ? "\n" : "\t";
}
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item = string.Empty;
}
#endregion
break;
case ExportFileType.XML:
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(ds.GetXml());
break;
}
resp.End();//写缓冲区中的数据到HTTP头文档中
}
第一种方案：这种是比较常见的方法。
导出DataSet数据到Excel（HTML版）
/// <summary>/// 导出DataSet数据到Excel（HTML版）
/// </summary>
/// <param name="p"></param>
/// <param name="ds"></param>
/// <param name="fileName"></param>
public static void CreateExcel(System.Web.UI.Page p, DataSet ds, string fileName)
{
StringBuilder strb = new StringBuilder();
strb.Append(" <html xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
strb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
strb.Append("xmlns=\"http://www.w3.org/TR/REC-html40\"");
strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=gb2312'>");
strb.Append(" <style>");
strb.Append(".xl26");
strb.Append(" {mso-style-parent:style0;");
strb.Append(" font-family:\"Times New Roman\", serif;");
strb.Append(" mso-font-charset:0;");
strb.Append(" mso-number-format:\"@\";}");
strb.Append(" </style>");
strb.Append(" <xml>");
strb.Append(" <x:ExcelWorkbook>");
strb.Append(" <x:ExcelWorksheets>");
strb.Append(" <x:ExcelWorksheet>");
strb.Append(" <x:Name>Sheet1 </x:Name>");
strb.Append(" <x:WorksheetOptions>");
strb.Append(" <x:DefaultRowHeight>285 </x:DefaultRowHeight>");
strb.Append(" <x:Selected/>");
strb.Append(" <x:Panes>");
strb.Append(" <x:Pane>");
strb.Append(" <x:Number>3 </x:Number>");
strb.Append(" <x:ActiveCol>1 </x:ActiveCol>");
strb.Append(" </x:Pane>");
strb.Append(" </x:Panes>");
strb.Append(" <x:ProtectContents>False </x:ProtectContents>");
strb.Append(" <x:ProtectObjects>False </x:ProtectObjects>");
strb.Append(" <x:ProtectScenarios>False </x:ProtectScenarios>");
strb.Append(" </x:WorksheetOptions>");
strb.Append(" </x:ExcelWorksheet>");
strb.Append(" <x:WindowHeight>6750 </x:WindowHeight>");
strb.Append(" <x:WindowWidth>10620 </x:WindowWidth>");
strb.Append(" <x:WindowTopX>480 </x:WindowTopX>");
strb.Append(" <x:WindowTopY>75 </x:WindowTopY>");
strb.Append(" <x:ProtectStructure>False </x:ProtectStructure>");
strb.Append(" <x:ProtectWindows>False </x:ProtectWindows>");
strb.Append(" </x:ExcelWorkbook>");
strb.Append(" </xml>");
strb.Append("");
strb.Append(" </head> <body> <table align=\"center\" style='border-collapse:collapse;table-layout:fixed'>");
if (ds != null && ds.Tables.Count > 0)
{
#region 写列标题
int columncount = ds.Tables[0].Columns.Count;
strb.Append(" <tr>");
for (int columi = 0; columi < columncount; columi++)
{
strb.Append(" <td> <b>" + ds.Tables[0].Columns[columi] + " </b> </td>");
}
strb.Append(" </tr>");
#endregion
#region 写数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
strb.Append(" <tr>");
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
strb.Append(" <td class='xl26'>" + ds.Tables[0].Rows[i][j].ToString() + " </td>");
}
strb.Append(" </tr>");
}
#endregion
}
strb.Append("</table> </body> </html>");
HttpResponse resp = p.Response;
SetHttpResponseFile(ref resp, ExportFileType.EXCEL, fileName);
p.EnableViewState = false;//this.
p.Response.Write(strb);
p.Response.End();
}
第二种方案：就是第三篇博文提到的方法，代码是原作者写的，稍有修改（原来Tr和Table可能存在没有关闭的情形）。
子方法：用使循环的方法将linkbutton和dropdownlist替换成literal控件
/// <summary>/// 子方法：用使循环的方法将linkbutton和dropdownlist替换成literal控件
/// 使得包含下拉控件的表格更加美观
/// </summary>
/// <param name="gv"></param>
private static void DisableControls(Control gv)
{
LinkButton lb = new LinkButton();
Literal l = new Literal();
string name = String.Empty;
for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls[i].GetType() == typeof(LinkButton))
{
l.Text = (gv.Controls[i] as LinkButton).Text;
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
else if (gv.Controls[i].GetType() == typeof(DropDownList))
{
l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
if (gv.Controls[i].HasControls())
{
DisableControls(gv.Controls[i]);
}
}
}
第二篇博文提到的方法，也是抄的代码。
子方法：将控件写入Excel文件
/// <summary>/// 子方法：将控件写入Excel文件
/// </summary>
/// <param name="p"></param>
/// <param name="ctl"></param>
/// <param name="type"></param>
/// <param name="FileName"></param>
private static void RenderGridToExcel(System.Web.UI.Page p, System.Web.UI.Control ctl, ExportFileType type, string FileName)
{
//注意：使用本方法
//1.还需要override一下VerifyRenderingInServerForm方法(这一点非常重要，否则在点击按钮后会报错，译者注)，代码如下:
//public override void VerifyRenderingInServerForm(Control control)
//{
//}
//2.如果要导出的GridView中包含LinkButton或者分页(出现分页码时，译者注) 则将出现错误：
//通过修改页文件可以修正这个问题：EnableEventValidation = "false".
//为了使纯数字列（无论是字符串类型还是数字类型）正确显示，需要将其储存为文本格式。
//Excel中的文本格式表示为"mso-number-format:"\@"。
string style = @"<style> .text { mso-number-format:\@; } </script> ";
DisableControls(ctl);
HttpResponse resp = p.Response;
SetHttpResponseFile(ref resp, type, FileName);
p.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
ctl.RenderControl(htw);
resp.Write(style); // Style is added dynamically
resp.Write(sw.ToString());
resp.End();
}
第三种方案：这个方法也是比较常见的。其实和第二种本质上差不多。
下面这部分短短代码才是我写的，哈，大家别见怪，俺是站在巨人肩膀上，也方便以后拿来就用。
将GridView的数据通过画控件方式写入Excel
/// <summary>/// 将GridView的数据通过画控件方式写入Excel
/// </summary>
/// <param name="p"></param>
/// <param name="gv"></param>
/// <param name="ds"></param>
/// <param name="type"></param>
/// <param name="FileName"></param>
public static void RenderGridToExcel(System.Web.UI.Page p, System.Web.UI.WebControls.GridView gv, DataSet ds, ExportFileType type, string FileName)
{
System.Web.UI.WebControls.GridView ctl = new GridView();
ctl = gv;
ctl.AllowPaging = false;
ctl.HeaderStyle.BackColor = System.Drawing.Color.Gray;
//一定要在行绑定事件中添加属性才生效！
ctl.RowDataBound += new GridViewRowEventHandler(ctl_RowDataBound);
ctl.DataSource = ds;
ctl.DataBind();
RenderGridToExcel(p, ctl, type, FileName);
}
主要还是调用上面的子方法，不过需要主意的是，正如第二篇博文提到的，需要在数据绑定事件中将类型设置为文本，来解决数字型失真问题。这个方法用到了下面的事件：
给表格的每一个格子都设置为文本
/// <summary>/// 给表格的每一个格子都设置为文本
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected static void ctl_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
for (int j = 0; j < e.Row.Cells.Count; j++)
{
e.Row.Cells[j].Attributes.Add("class", "text");
}
}
}
如果采用第三种方案，还需要注意（第二篇博文有提到的） ：
1． 在要导出数据的页面，ASPX文件，
1.1在页面最顶端的声明语句中设置 EnableEventValidation = "false"。这步很重要！不然会出现异常。比如：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Query.aspx.cs" Inherits="Pages_StoreManage_Query" EnableEventValidation = "false" %>
2． 在要导出数据的页面，CS文件中放置以下代码：
2.1 override一下VerifyRenderingInServerForm方法(这一点非常重要，否则在点击按钮后会报错)
public override void VerifyRenderingInServerForm(Control control)
{ }
OK，将方法类放在一个文件，需要导出的页面的导出时间调用即可。本文不少代码基本上来自参考文章，对原作者表示感谢。
--END--
相关阅读 更多 +
