C#数据库数据导出Excel通用方法

http://blog.csdn.net/cpcpc/article/details/7029763

 

下面给出一个在网上下载的一个已经封装好的类

[csharp] view plaincopy
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI.WebControls;  
  6. using System.Web.UI;  
  7. using System.Data;  
  8. using System.Text;  
  9. using System.Globalization;  
  10. using System.IO;  
  11.   
  12. namespace VMS.Test.Classes  
  13. {  
  14.     public class ExcelHelper {   
  15.  
  16.         #region Fields   
  17.    
  18.         string _fileName;   
  19.         DataTable _dataSource;           
  20.         string[] _titles = null;   
  21.         string[] _fields = null;   
  22.         int _maxRecords = 1000;   
  23.   
  24.         #endregion   
  25.   
  26.         #region Properties   
  27.    
  28.         /**//// <summary>   
  29.         /// 限制输出到 Excel 的最大记录数。超出则抛出异常   
  30.         /// </summary>   
  31.         public int MaxRecords {   
  32.             set { _maxRecords = value; }   
  33.             get { return _maxRecords; }   
  34.         }   
  35.    
  36.         /**//// <summary>   
  37.         /// 输出到浏览器的 Excel 文件名   
  38.         /// </summary>   
  39.         public string FileName {   
  40.             set { _fileName = value; }   
  41.             get { return _fileName; }   
  42.         }   
  43.   
  44.         #endregion   
  45.   
  46.         #region .ctor   
  47.    
  48.         /**//// <summary>   
  49.         /// 构造函数   
  50.         /// </summary>   
  51.         /// <param name="titles">要输出到 Excel 的列标题的数组</param>   
  52.         /// <param name="fields">要输出到 Excel 的字段名称数组</param>   
  53.         /// <param name="dataSource">数据源</param>   
  54.         public ExcelHelper(string[] titles, string[] fields, DataTable dataSource): this(titles, dataSource)        {   
  55.             if (fields == null || fields.Length == 0)   
  56.                 throw new ArgumentNullException("fields");   
  57.    
  58.             if (titles.Length != fields.Length)   
  59.                 throw new ArgumentException("titles.Length != fields.Length", "fields");   
  60.                
  61.             _fields = fields;               
  62.         }   
  63.    
  64.         /**//// <summary>   
  65.         /// 构造函数   
  66.         /// </summary>   
  67.         /// <param name="titles">要输出到 Excel 的列标题的数组</param>   
  68.         /// <param name="dataSource">数据源</param>   
  69.         public ExcelHelper(string[] titles, DataTable dataSource): this(dataSource) {   
  70.             if (titles == null || titles.Length == 0)   
  71.                 throw new ArgumentNullException("titles");   
  72.             //if (titles.Length != dataSource.Columns.Count)   
  73.             //    throw new ArgumentException("titles.Length != dataSource.Columns.Count", "dataSource");   
  74.    
  75.             _titles = titles;               
  76.         }   
  77.    
  78.         /**//// <summary>   
  79.         /// 构造函数   
  80.         /// </summary>   
  81.         /// <param name="dataSource">数据源</param>   
  82.         public ExcelHelper(DataTable dataSource) {   
  83.             if (dataSource == null)   
  84.                 throw new ArgumentNullException("dataSource");   
  85.             // maybe more checks needed here (IEnumerable, IList, IListSource, ) ???   
  86.             // 很难判断，先简单的使用 DataTable   
  87.    
  88.             _dataSource = dataSource;   
  89.         }   
  90.            
  91.         public ExcelHelper() {}   
  92.   
  93.         #endregion   
  94.           
  95.         #region public Methods   
  96.            
  97.         /**//// <summary>   
  98.         /// 导出到 Excel 并提示下载   
  99.         /// </summary>   
  100.         /// <param name="dg">DataGrid</param>   
  101.         public void Export(DataGrid dg) {   
  102.             if (dg == null)   
  103.                 throw new ArgumentNullException("dg");   
  104.             if (dg.AllowPaging || dg.PageCount > 1)   
  105.                 throw new ArgumentException("paged DataGrid can't be exported.", "dg");   
  106.    
  107.             // 添加标题样式   
  108.             dg.HeaderStyle.Font.Bold = true;   
  109.             dg.HeaderStyle.BackColor = System.Drawing.Color.LightGray;   
  110.    
  111.             RenderExcel(dg);   
  112.         }   
  113.    
  114.         ///**//// <summary>   
  115.         ///// 导出到 Excel 并提示下载   
  116.         ///// </summary>   
  117.         ///// <param name="xgrid">ASPxGrid</param>   
  118.         //public void Export(DataGrid xgrid) {    
  119.         //    if (xgrid == null)   
  120.         //        throw new ArgumentNullException("xgrid");   
  121.         //    if (xgrid.PageCount > 1)   
  122.         //        throw new ArgumentException("paged xgird not can't be exported.", "xgrid");   
  123.    
  124.         //    // 添加标题样式   
  125.         //    xgrid.HeaderStyle.Font.Bold = true;   
  126.         //    xgrid.HeaderStyle.BackColor = System.Drawing.Color.LightGray;   
  127.    
  128.         //    RenderExcel(xgrid);   
  129.         //}   
  130.    
  131.         /**//// <summary>   
  132.         /// 导出到 Excel 并提示下载   
  133.         /// </summary>   
  134.         public void Export() {   
  135.             if (_dataSource == null)   
  136.                 throw new Exception("数据源尚未初始化");   
  137.    
  138.             if (_fields == null && _titles != null && _titles.Length != _dataSource.Columns.Count)    
  139.                 throw new Exception("_titles.Length != _dataSource.Columns.Count");   
  140.                
  141.             if (_dataSource.Rows.Count > _maxRecords)   
  142.                 throw new Exception("导出数据条数超过限制。请设置 MaxRecords 属性以定义导出的最多记录数。");   
  143.    
  144.             DataGrid dg = new DataGrid();   
  145.             dg.DataSource = _dataSource;   
  146.    
  147.             if (_titles == null) {   
  148.                 dg.AutoGenerateColumns = true;   
  149.             }    
  150.             else {   
  151.                 dg.AutoGenerateColumns = false;   
  152.                 int cnt = _titles.Length;   
  153.    
  154.                 System.Web.UI.WebControls.BoundColumn col;   
  155.    
  156.                 if (_fields == null) {   
  157.                     for (int i=0; i<cnt; i++) {   
  158.                         col = new System.Web.UI.WebControls.BoundColumn();   
  159.                         col.HeaderText = _titles[i];   
  160.                         col.DataField = _dataSource.Columns[i].ColumnName;   
  161.                         dg.Columns.Add(col);   
  162.                     }   
  163.                 }   
  164.                 else {   
  165.                     for (int i=0; i<cnt; i++) {   
  166.                         col = new System.Web.UI.WebControls.BoundColumn();   
  167.                         col.HeaderText = _titles[i];   
  168.                         col.DataField = _fields[i];   
  169.                         dg.Columns.Add(col);   
  170.                     }   
  171.                 }   
  172.             }   
  173.    
  174.             // 添加标题样式   
  175.             dg.HeaderStyle.Font.Bold = true;   
  176.             dg.HeaderStyle.BackColor = System.Drawing.Color.LightGray;   
  177.             dg.ItemDataBound += new DataGridItemEventHandler(DataGridItemDataBound);   
  178.    
  179.             dg.DataBind();   
  180.             RenderExcel(dg);   
  181.         }   
  182.   
  183.         #endregion   
  184.   
  185.         #region private Methods   
  186.            
  187.         private void RenderExcel(Control c) {   
  188.             // 确保有一个合法的输出文件名   
  189.             if (_fileName == null || _fileName == string.Empty || !(_fileName.ToLower().EndsWith(".xls")))   
  190.                 _fileName = GetRandomFileName();   
  191.    
  192.             HttpResponse response = HttpContext.Current.Response;   
  193.                
  194.             response.Charset = "GB2312";   
  195.             response.ContentEncoding = Encoding.GetEncoding("GB2312");   
  196.             response.ContentType = "application/ms-excel/msword";   
  197.             response.AppendHeader("Content-Disposition", "attachment;filename=" +    
  198.                 HttpUtility.UrlEncode(_fileName));   
  199.    
  200.             CultureInfo cult = new CultureInfo("zh-CN", true);   
  201.             StringWriter sw = new StringWriter(cult);               
  202.             HtmlTextWriter writer = new HtmlTextWriter(sw);   
  203.    
  204.             writer.WriteLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=GB2312\">");   
  205.    
  206.             DataGrid dg = c as DataGrid;   
  207.                
  208.             if (dg != null) {   
  209.                 dg.RenderControl(writer);   
  210.             }   
  211.             else {  
  212.                 DataGrid xgrid = c as DataGrid;   
  213.    
  214.                 if (xgrid != null)   
  215.                     xgrid.RenderControl(writer);   
  216.                 else   
  217.                     throw new ArgumentException("only supports DataGrid or ASPxGrid.", "c");       
  218.             }   
  219.             c.Dispose();   
  220.    
  221.             response.Write(sw.ToString());   
  222.             response.End();   
  223.         }   
  224.    
  225.    
  226.         /**//// <summary>   
  227.         /// 得到一个随意的文件名   
  228.         /// </summary>   
  229.         /// <returns></returns>   
  230.         private string GetRandomFileName() {   
  231.             Random rnd = new Random((int) (DateTime.Now.Ticks));   
  232.             string s = rnd.Next(Int32.MaxValue).ToString();   
  233.             return DateTime.Now.ToShortDateString() + "_" + s + ".xls";   
  234.         }   
  235.    
  236.         private void DataGridItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) {   
  237.             if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) {   
  238.                 e.Item.Attributes.Add("style", "vnd.ms-excel.numberformat:@");   
  239.                 //e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:￥#,###.00");   
  240.             }   
  241.         }   
  242.         #endregion   
  243.     }   
  244. }  

 

posted @ 2014-01-22 11:28  weikai  阅读(2059)  评论(0)  编辑  收藏  举报