[整理]表格数据/DataSet导出到Excel

项目里需要导出数据到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。

 

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

后面经常要设置输出流，所以干脆提取出来独立方法。

 

    /// <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头文档中 
    }

第一种方案：这种是比较常见的方法。

 

    /// <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可能存在没有关闭的情形）。

 

 

/// <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]);
            }
        }
    }

第二篇博文提到的方法，也是抄的代码。

 

    /// <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();
    } 

第三种方案：这个方法也是比较常见的。其实和第二种本质上差不多。

 

下面这部分短短代码才是我写的，哈，大家别见怪，俺是站在巨人肩膀上，也方便以后拿来就用。

    /// <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--