//=============================================================================== // // 從 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 自動轉換為科學計數法的毛病。 // (修改的辦法來自 ) // //=============================================================================== 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

///

/// 限制輸出到 Excel 的最大記錄數。超出則丟擲異常
/// public int MaxRecords {
set { _maxRecords = value; }
get { return _maxRecords; }
}

///

/// 輸出到瀏覽器的 Excel 檔名
/// public string FileName {
set { _fileName = value; }
get { return _fileName; }
}

#endregion

#region .ctor

///

/// 建構函式
///
/// 要輸出到 Excel 的列標題的陣列
/// 要輸出到 Excel 的欄位名稱陣列
/// 資料來源 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;
}

///

/// 建構函式
///
/// 要輸出到 Excel 的列標題的陣列
/// 資料來源 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;
}

///

/// 建構函式
///
/// 資料來源 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

///

/// 匯出到 Excel 並提示下載
///
/// DataGrid 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);
}

///

/// 匯出到 Excel 並提示下載
///
/// ASPxGrid 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);
}

///

/// 匯出到 Excel 並提示下載
/// 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(""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();
}

///

/// 得到一個隨意的檔名
///
/// 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
}
} [@more@]

DataGrid 匯出到 Excel 的幫助類

相關文章