使用GridView控件导出数据源的数据为Excel、Word或Text

[源码下载]

扩展GridView控件(8) - 导出数据源的数据为Excel、Word或Text

作者： webabcd

/* 正式版的实现 开始 */

介绍
扩展GridView控件：
导出数据源的数据为Excel、Word或Text（应保证数据源的类型为DataTable或DataSet）

使用方法：
为SmartGridView添加的方法
Export(string fileName)
Export(string fileName, ExportFormat exportFormat)
Export(string fileName, ExportFormat exportFormat, Encoding encoding)
Export(string fileName, int[] columnIndexList, ExportFormat exportFormat, Encoding encoding)
Export(string fileName, int[] columnIndexList, string[] headers, ExportFormat exportFormat, Encoding encoding)
Export(string fileName, string[] columnNameList, ExportFormat exportFormat, Encoding encoding)
Export(string fileName, string[] columnNameList, string[] headers, ExportFormat exportFormat, Encoding encoding)


关键代码

using  System;
using  System.Collections.Generic;
using  System.Text;

using  System.Data;
using  System.Web.UI.WebControls;

namespace  YYControls
{
    /**//// <summary>
    /// SmartGridView类的属性部分
    /// </summary>
    public partial class SmartGridView
    {
        /**//// <summary>
        /// 导出SmartGridView的数据源的数据
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="exportFormat">导出文件的格式</param>
        /// <param name="encoding">编码</param>
        public void Export(string fileName, ExportFormat exportFormat, Encoding encoding)
        {
            DataTable dt = GetDataTable();
            Helper.Common.Export(dt, exportFormat, fileName, encoding);
        }

        /**//// <summary>
        /// 导出SmartGridView的数据源的数据
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="exportFormat">导出文件的格式</param>
        public void Export(string fileName, ExportFormat exportFormat)
        {
            Export(fileName, exportFormat, Encoding.GetEncoding("GB2312"));
        }

        /**//// <summary>
        /// 导出SmartGridView的数据源的数据为Excel
        /// </summary>
        /// <param name="fileName">文件名</param>
        public void Export(string fileName)
        {
            Export(fileName, ExportFormat.CSV);
        }

        /**//// <summary>
        /// 导出SmartGridView的数据源的数据
        /// </summary>
        /// <param name="fileName">输出文件名</param>
        /// <param name="columnIndexList">导出的列索引数组</param>
        /// <param name="exportFormat">导出文件的格式</param>
        /// <param name="encoding">编码</param>
        public void Export(string fileName,int[] columnIndexList, ExportFormat exportFormat, Encoding encoding)
        {
            DataTable dt = GetDataTable();

            Helper.Common.Export(dt, columnIndexList, exportFormat, fileName, encoding);
        }

        /**//// <summary>
        /// 导出SmartGridView的数据源的数据
        /// </summary>
        /// <param name="fileName">输出文件名</param>
        /// <param name="columnNameList">导出的列的列名数组</param>
        /// <param name="exportFormat">导出文件的格式</param>
        /// <param name="encoding">编码</param>
        public void Export(string fileName, string[] columnNameList, ExportFormat exportFormat, Encoding encoding)
        {
            DataTable dt = GetDataTable();

            Helper.Common.Export(dt, columnNameList, exportFormat, fileName, encoding);
        }

        /**//// <summary>
        /// 导出SmartGridView的数据源的数据
        /// </summary>
        /// <param name="fileName">输出文件名</param>
        /// <param name="columnIndexList">导出的列索引数组</param>
        /// <param name="headers">导出的列标题数组</param>
        /// <param name="exportFormat">导出文件的格式</param>
        /// <param name="encoding">编码</param>
        public void Export(string fileName, int[] columnIndexList, string[] headers, ExportFormat exportFormat, Encoding encoding)
        {
            DataTable dt = GetDataTable();

            Helper.Common.Export(dt, columnIndexList, headers, exportFormat, fileName, encoding);
        }

        /**//// <summary>
        /// 导出SmartGridView的数据源的数据
        /// </summary>
        /// <param name="fileName">输出文件名</param>
        /// <param name="columnNameList">导出的列的列名数组</param>
        /// <param name="headers">导出的列标题数组</param>
        /// <param name="exportFormat">导出文件的格式</param>
        /// <param name="encoding">编码</param>
        public void Export(string fileName, string[] columnNameList, string[] headers, ExportFormat exportFormat, Encoding encoding)
        {
            DataTable dt = GetDataTable();

            Helper.Common.Export(dt, columnNameList, headers, exportFormat, fileName, encoding);
        }

        /**//// <summary>
        /// 获取数据源（DataTable）
        /// </summary>
        private DataTable GetDataTable()
        {
            DataTable dt = null;

            if (this._dataSourceObject is DataTable)
                dt = (DataTable)this._dataSourceObject;
            else if (this._dataSourceObject is DataSet)
                dt = ((DataSet)this._dataSourceObject).Tables[0];
            else
                throw new InvalidCastException("若要导出SmartGridView，应保证其数据源为DataTable或DataSet类型");

            if (dt == null)
                throw new ArgumentNullException("数据源", "数据源不能为NULL");

            return dt;
        }
    }
}

/* 正式版的实现 结束 */

/* 测试版的实现 开始 */

介绍
把GridView导出为一个Excel文件算是一个经常要用到的功能，也比较简单，我们来扩展一个GridView以实现这样的功能。


控件开发
1、新建一个继承自GridView的类。

/**/ /// <summary>
/// 继承自GridView
/// </summary>
[ToolboxData( @" <{0}:SmartGridView runat='server'></{0}:SmartGridView> " )]
public   class  SmartGridView : GridView
{
}

2、重写OnRowCommand，以实现把GridView导出为Excel的功能

         /**/ /// <summary>
        /// OnRowCommand
        /// </summary>
        /// <param name="e"></param>
         protected   override   void  OnRowCommand(GridViewCommandEventArgs e)
         {
            if (e.CommandName.ToLower() == "exporttoexcel")
            {
                System.Web.HttpContext.Current.Response.ClearContent();
                // e.CommandArgument用“;”隔开两部分，左边的部分为导出Excel的文件名称
                System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + e.CommandArgument.ToString().Split(';')[0] + ".xls");
                System.Web.HttpContext.Current.Response.ContentType = "application/excel";

                System.IO.StringWriter sw = new System.IO.StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);

                // e.CommandArgument用“;”隔开两部分，右边的部分为需要隐藏的列的索引（列索引用“,”分开）
                if (e.CommandArgument.ToString().Split(';').Length > 1)
                {
                    foreach (string s in e.CommandArgument.ToString().Split(';')[1].Split(','))
                    {
                        int i;

                        if (!Int32.TryParse(s, out i))
                        {
                            throw new ArgumentException("需要隐藏的列的索引不是整数"); 
                        }

                        if (i > this.Columns.Count)
                        {
                            throw new ArgumentOutOfRangeException("需要隐藏的列的索引超出范围");
                        }

                        this.Columns[i].Visible = false;
                    }
                }

                // 隐藏“导出Excel”按钮
                ((Control)e.CommandSource).Visible = false;

                // 如果HeaderRow里的控件是button的话，则把它替换成文本
                foreach (TableCell tc in this.HeaderRow.Cells)
                {
                    // TableCell里的每个Control
                    foreach (Control c in tc.Controls)
                    {
                        // 如果控件继承自接口IButtonControl
                        if (c.GetType().GetInterface("IButtonControl") != null && c.GetType().GetInterface("IButtonControl").Equals(typeof(IButtonControl)))
                        {
                            // 如果该控件不是“导出Excel”按钮则把button转换成文本
                            if (!c.Equals(e.CommandSource))
                            {
                                tc.Controls.Clear();
                                tc.Text = ((IButtonControl)c).Text;
                            }
                        }
                    }
                }

                // 将服务器控件的内容输出到所提供的 System.Web.UI.HtmlTextWriter 对象中
                this.RenderControl(htw);

                System.Web.HttpContext.Current.Response.Write(sw.ToString());
                System.Web.HttpContext.Current.Response.End();
            }

            base.OnRowCommand(e);
        }

控件使用
添加这个控件到工具箱里，然后拖拽到webform上，在GridView内加一个按钮，把CommandName属性设置为“ExportToExcel”，CommandArgument属性的值用“;”做分隔符分为两部分，左边的部分为导出Excel的文件名称，右边的部分为需要隐藏的列的索引（列索引用“,”分开）
ObjData.cs

using  System;
using  System.Data;
using  System.Configuration;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;

using  System.ComponentModel;

/**/ /// <summary>
/// OjbData 的摘要说明
/// </summary>
public   class  OjbData
{
    public OjbData()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }

    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataTable Select()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("no", typeof(string));
        dt.Columns.Add("name", typeof(string));

        for (int i = 0; i < 30; i++)
        {
            DataRow dr = dt.NewRow();
            dr[0] = "no" + i.ToString().PadLeft(2, '0');
            dr[1] = "name" + i.ToString().PadLeft(2, '0');

            dt.Rows.Add(dr);
        }

        return dt;
    }
}

Default.aspx

<% @ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default"  %>

<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
< html  xmlns ="http://www.w3.org/1999/xhtml" >
< head  runat ="server" >
     < title > SmartGridView测试 </ title >
</ head >
< body >
     < form  id ="form1"  runat ="server" >
         < div >
             < yyc:SmartGridView  ID ="SmartGridView1"  runat ="server"  AutoGenerateColumns ="False"
                DataSourceID ="ObjectDataSource1" >
                 < Columns >
                     < asp:TemplateField  ItemStyle-Width ="50px" >
                         < headertemplate >
                             < asp:Button  id ="btnExportToExcel"  runat ="server"  Text ="Excel"  CommandName ="ExportToExcel"  CommandArgument ="ExcelFileName;5,6"   />
                         </ headertemplate >
                         < itemtemplate >
                             <% # Container.DataItemIndex + 1  %>
                         </ itemtemplate >
                     </ asp:TemplateField >
                     < asp:BoundField  DataField ="no"  HeaderText ="序号"  SortExpression ="no"  ItemStyle-Width ="100px"   />
                     < asp:BoundField  DataField ="name"  HeaderText ="名称"  SortExpression ="name"  ItemStyle-Width ="100px"   />
                     < asp:BoundField  DataField ="no"  HeaderText ="序号"  SortExpression ="no"  ItemStyle-Width ="100px"   />
                     < asp:BoundField  DataField ="name"  HeaderText ="名称"  SortExpression ="name"  ItemStyle-Width ="100px"   />
                     < asp:BoundField  DataField ="no"  HeaderText ="序号"  SortExpression ="no"  ItemStyle-Width ="100px"   />
                     < asp:BoundField  DataField ="name"  HeaderText ="名称"  SortExpression ="name"  ItemStyle-Width ="100px"   />
                 </ Columns >
             </ yyc:SmartGridView >
             < asp:ObjectDataSource  ID ="ObjectDataSource1"  runat ="server"  SelectMethod ="Select"
                TypeName ="OjbData" ></ asp:ObjectDataSource >
         </ div >
     </ form >
</ body >
</ html >

注：为了防止出错要在.cs代码中加上下面这句

public   override   void  VerifyRenderingInServerForm(Control control)
     {

    }

另外，如果你的GridView中含有命令按钮的话要在.aspx页面的头部中加上下面这个属性

EnableEventValidation="false"

/* 测试版的实现 结束 */

OK
[源码下载]

原文地址: http://www.cnblogs.com/webabcd/archive/2008/06/27/632863.html#1238389