DataGrid 导出到 Excel 的帮助类
//=============================================================================== // // 从 DataGrid 或数据源中导出数据到 Excel 并提示下载的帮助类。 // // Author: Roger 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 } }
代码就是页面里这些了。不过忘记了写调用的例子，现在补上：
以下例子实际调用时需要在外面用 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();
