DataGrid 导出到 Excel 的帮助类

DataGrid 导出到 Excel 的帮助类

 

// ===============================================================================  //   //  从 DataGrid    或数据源中导出数据到 Excel 并提示下载的帮助类。  //   //  Author:    Neil Chen (木野狐)  //  Date:    2005-1-27  //  Version: 1.22  //  History:  //             v1.00    使用静态方法的形式实现该类，提供多种重载方式。  //             v1.01    添加了对 DevExpress.Web.ASPxGrid.ASPxGrid 的直接导出支持。  //             v1.20    改写为实体类。 减少了重复代码。  //             v1.21    2005-2-1      //                     修改了一个构造函数的重载形式中异常检测的代码。延迟到 Export() 方法。  //             v1.22    2005-2-3      //                     1. 修正了 Export() 方法中缺少 _titles != null 判断的 bug.  //                     2. 修正了长的数字被 Excel 自动转换为科学计数法的毛病。  //                         (修改的办法来自  http://dotnet.aspx.cc )  //   // ===============================================================================  namespace  RChen.Demos {      using  System;      using  System.IO;      using  System.Data;      using  System.Web;      using  System.Web.UI;      using  System.Web.UI.WebControls;      using  System.Text;      using  System.Globalization;      using  System.Collections;      using  DevExpress.Web.ASPxGrid;       public   class  ExcelHelper {          #region  Fields            string  _fileName;         DataTable _dataSource;                  string [] _titles  =   null ;          string [] _fields  =   null ;          int  _maxRecords  =   1000 ;           #endregion             #region  Properties            ///   <summary>           ///  限制输出到 Excel 的最大记录数。超出则抛出异常          ///   </summary>            public   int  MaxRecords {              set  { _maxRecords  =  value; }              get  {  return  _maxRecords; }         }           ///   <summary>           ///  输出到浏览器的 Excel 文件名          ///   </summary>            public   string  FileName {              set  { _fileName  =  value; }              get  {  return  _fileName; }         }           #endregion             #region  .ctor            ///   <summary>           ///  构造函数          ///   </summary>           ///   <param name="titles"> 要输出到 Excel 的列标题的数组 </param>           ///   <param name="fields"> 要输出到 Excel 的字段名称数组 </param>           ///   <param name="dataSource"> 数据源 </param>            public  ExcelHelper( string [] titles,  string [] fields, DataTable dataSource):  this (titles, dataSource)        {              if  (fields  ==   null   ||  fields.Length  ==   0 )                  throw   new  ArgumentNullException( " fields " );               if  (titles.Length  !=  fields.Length)                  throw   new  ArgumentException( " titles.Length != fields.Length " ,  " fields " );                          _fields  =  fields;                     }           ///   <summary>           ///  构造函数          ///   </summary>           ///   <param name="titles"> 要输出到 Excel 的列标题的数组 </param>           ///   <param name="dataSource"> 数据源 </param>            public  ExcelHelper( string [] titles, DataTable dataSource):  this (dataSource) {              if  (titles  ==   null   ||  titles.Length  ==   0 )                  throw   new  ArgumentNullException( " titles " );              // if (titles.Length != dataSource.Columns.Count)              //     throw new ArgumentException("titles.Length != dataSource.Columns.Count", "dataSource");               _titles  =  titles;                     }           ///   <summary>           ///  构造函数          ///   </summary>           ///   <param name="dataSource"> 数据源 </param>            public  ExcelHelper(DataTable dataSource) {              if  (dataSource  ==   null )                  throw   new  ArgumentNullException( " dataSource " );              //  maybe more checks needed here (IEnumerable, IList, IListSource, ) ???              //  很难判断，先简单的使用 DataTable               _dataSource  =  dataSource;         }                   public  ExcelHelper() {}           #endregion                     #region  public Methods                    ///   <summary>           ///  导出到 Excel 并提示下载          ///   </summary>           ///   <param name="dg"> DataGrid </param>            public   void  Export(DataGrid dg) {              if  (dg  ==   null )                  throw   new  ArgumentNullException( " dg " );              if  (dg.AllowPaging  ||  dg.PageCount  >   1 )                  throw   new  ArgumentException( " paged DataGrid can't be exported. " ,  " dg " );               //  添加标题样式              dg.HeaderStyle.Font.Bold  =   true ;             dg.HeaderStyle.BackColor  =  System.Drawing.Color.LightGray;              RenderExcel(dg);         }           ///   <summary>           ///  导出到 Excel 并提示下载          ///   </summary>           ///   <param name="xgrid"> ASPxGrid </param>            public   void  Export(DevExpress.Web.ASPxGrid.ASPxGrid xgrid) {               if  (xgrid  ==   null )                  throw   new  ArgumentNullException( " xgrid " );              if  (xgrid.PageCount  >   1 )                  throw   new  ArgumentException( " paged xgird not can't be exported. " ,  " xgrid " );               //  添加标题样式              xgrid.HeaderStyle.Font.Bold  =   true ;             xgrid.HeaderStyle.BackColor  =  System.Drawing.Color.LightGray;              RenderExcel(xgrid);         }           ///   <summary>           ///  导出到 Excel 并提示下载          ///   </summary>            public   void  Export() {              if  (_dataSource  ==   null )                  throw   new  Exception( " 数据源尚未初始化 " );               if  (_fields  ==   null   &&  _titles  !=   null   &&  _titles.Length  !=  _dataSource.Columns.Count)                   throw   new  Exception( " _titles.Length != _dataSource.Columns.Count " );                           if  (_dataSource.Rows.Count  >  _maxRecords)                  throw   new  Exception( " 导出数据条数超过限制。请设置 MaxRecords 属性以定义导出的最多记录数。 " );              DataGrid dg  =   new  DataGrid();             dg.DataSource  =  _dataSource;               if  (_titles  ==   null ) {                 dg.AutoGenerateColumns  =   true ;             }               else  {                 dg.AutoGenerateColumns  =   false ;                  int  cnt  =  _titles.Length;                  System.Web.UI.WebControls.BoundColumn col;                   if  (_fields  ==   null ) {                      for  ( int  i = 0 ; i < cnt; i ++ ) {                         col  =   new  System.Web.UI.WebControls.BoundColumn();                         col.HeaderText  =  _titles[i];                         col.DataField  =  _dataSource.Columns[i].ColumnName;                         dg.Columns.Add(col);                     }                 }                  else  {                      for  ( int  i = 0 ; i < cnt; i ++ ) {                         col  =   new  System.Web.UI.WebControls.BoundColumn();                         col.HeaderText  =  _titles[i];                         col.DataField  =  _fields[i];                         dg.Columns.Add(col);                     }                 }             }               //  添加标题样式              dg.HeaderStyle.Font.Bold  =   true ;             dg.HeaderStyle.BackColor  =  System.Drawing.Color.LightGray;             dg.ItemDataBound  +=   new  DataGridItemEventHandler(DataGridItemDataBound);              dg.DataBind();             RenderExcel(dg);         }           #endregion             #region  private Methods                    private   void  RenderExcel(Control c) {              //  确保有一个合法的输出文件名               if  (_fileName  ==   null   ||  _fileName  ==   string .Empty  ||   ! (_fileName.ToLower().EndsWith( " .xls " )))                 _fileName  =  GetRandomFileName();              HttpResponse response  =  HttpContext.Current.Response;                          response.Charset  =   " GB2312 " ;             response.ContentEncoding  =  Encoding.GetEncoding( " GB2312 " );             response.ContentType  =   " application/ms-excel/msword " ;             response.AppendHeader( " Content-Disposition " ,  " attachment;filename= "   +                   HttpUtility.UrlEncode(_fileName));              CultureInfo cult  =   new  CultureInfo( " zh-CN " ,  true );             StringWriter sw  =   new  StringWriter(cult);                         HtmlTextWriter writer  =   new  HtmlTextWriter(sw);              writer.WriteLine( " <meta http-equiv=\ " Content - Type\ "  content=\ " text / html;charset = GB2312\ " > " );              DataGrid dg  =  c  as  DataGrid;                           if  (dg  !=   null ) {                 dg.RenderControl(writer);             }              else  {                 ASPxGrid xgrid  =  c  as  ASPxGrid;                   if  (xgrid  !=   null )                     xgrid.RenderControl(writer);                  else                       throw   new  ArgumentException( " only supports DataGrid or ASPxGrid. " ,  " c " );                 }             c.Dispose();              response.Write(sw.ToString());             response.End();         }            ///   <summary>           ///  得到一个随意的文件名          ///   </summary>           ///   <returns></returns>            private   string  GetRandomFileName() {             Random rnd  =   new  Random(( int ) (DateTime.Now.Ticks));              string  s  =  rnd.Next(Int32.MaxValue).ToString();              return  DateTime.Now.ToShortDateString()  +   " _ "   +  s  +   " .xls " ;         }           private   void  DataGridItemDataBound( object  sender, System.Web.UI.WebControls.DataGridItemEventArgs e) {              if (e.Item.ItemType  ==  ListItemType.Item  ||  e.Item.ItemType  ==  ListItemType.AlternatingItem) {                 e.Item.Attributes.Add( " style " ,  " vnd.ms-excel.numberformat:@ " );                  // e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:￥#,###.00");              }         }           #endregion       } }  0 0 0 (请您对文章做出评价)

posted on 2005-02-02 00:16 木野狐(Neil Chen) 阅读(10278) 评论(60)  编辑 收藏 网摘 所属分类: .NET

<!-- <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:trackback="http://madskills.com/public/xml/rss/module/trackback/"> <rdf:Description rdf:about="http://www.cnblogs.com/RChen/articles/100649.html" dc:identifier="http://www.cnblogs.com/RChen/articles/100649.html" dc:title="" trackback:ping="http://www.cnblogs.com/RChen/services/trackbacks/100649.aspx" /> </rdf:RDF> -->

评论

1183477

2005-02-02 09:03  

呵呵，根本看不到你的代码呀           

211.158.23.* 2005-02-02 09:36

hehe, expand the tree         

[楼主] 2005-02-02 10:25  

jackyrong, 代码就是页面里这些了。不过忘记了写调用的例子，现在补上： 以下例子实际调用时需要在外面用 try 块捕获并处理异常。可能抛出的异常有 ParameterException, ParameterNullException, Exception 等。   // 1. 导出一个不分页的 DataGrid 到 Excel.ExcelHelper helper = new ExcelHelper(); // 设置文件名(可省。省略则自动生成一个随机的文件名)helper.FileName = "xxx.xls"; helper.Export(dg); // 2. 导出分页的 DataGrid，需要同时指定他的数据源(DataTable)DataTable dt = ; ExcelHelper helper = new ExcelHelper(dt); // 最大导出条数(可省)helper.MaxRecords = 2000; helper.Export(); // 3. 如果要指定列标题，这样调用：DataTable dt = ; ExcelHelper helper = new ExcelHelper(     new string[] {"列标题1", "列标题2",  },      dt); helper.Export(); // 4. 如果还要指定字段名称， 这样调用（因为输出的字段名称不一定要和 DataTable 里字段名称的次序相同。DataTable dt = ; ExcelHelper helper = new ExcelHelper(     new string[] {"列标题1", "列标题2",  },      new string[] {"字段名1", "字段名2",  },     dt); helper.Export();           

 

203.207.189.* 2005-02-02 13:25

本方法的技术实质是另存HTML为EXCEL文件.如果需要套用一个EXCEL的标准格式,那又该如何实现呢?         

61.171.96.* 2005-02-02 13:37

这个类有点小问题。 DataTable dt = ...; ExcelHelper helper = new ExcelHelper(dt); helper.MaxRecords = 2000; helper.Export(); 如果用这种方法 类中的 if (_fields == null && _titles.Length != _dataSource.Columns.Count) throw new Exception("_titles.Length != _dataSource.Columns.Count"); 由于_titles 设有初始化而就使用 Length 属性

相关资源：easyui datagrid 导出到Excel js实现 跨浏览器