例子是上一篇 DataTable 導出 Excel 的進階，除了上一篇提到的處理亂碼問題，本例還添加了處理多行表頭、合並單元格的功能及處理中文文件名亂碼問題，應該可以滿足日常開發的需要了。
廢話不多說了，直接上代碼：
[csharp]
using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Web.UI.WebControls;
using System.Text.RegularExpressions;
/// <summary>
/// Common 的摘要說明
/// 作者：李偉波
/// 時間：2012-10-18
/// </summary>
public class Common
{
public Common()
{
//
//TODO: 在此處添加構造函數邏輯
//
}
/// <summary>
/// 描述：把DataTable內容導出excel並返回客戶端
/// 作者：李偉波
/// 時間：2012-10-18
/// </summary>
/// <param name="dtData"></param>
/// <param name="header"></param>
/// <param name="fileName"></param>
/// <param name="mergeCellNums">要合並的列索引字典 格式：列索引-合並模式(合並模式 1 合並相同項、2 合並空項、3 合並相同項及空項)</param>
/// <param name="mergeKey">作為合並項的標記列索引</param>
public static void DataTable2Excel(System.Data.DataTable dtData, TableCell[] header, string fileName, Dictionary<int, int> mergeCellNums, int? mergeKey)
{
System.Web.UI.WebControls.GridView gvExport = null;
// 當前對話
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用於導出並返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
// 設置編碼和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.Charset = "gb2312";
if (!string.IsNullOrEmpty(fileName))
{
//處理中文名亂碼問題
fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + (fileName.ToLower().EndsWith(".xls") ? fileName : fileName + ".xls")));
}
// 導出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 重新定義一個無分頁的GridView
gvExport = new System.Web.UI.WebControls.GridView();
gvExport.DataSource = dtData.DefaultView;
gvExport.AllowPaging = false;
//優化導出數據顯示，如身份證、12-1等顯示異常問題
gvExport.RowDataBound += new System.Web.UI.WebControls.GridViewRowEventHandler(dgExport_RowDataBound);
gvExport.DataBind();
//處理表頭
if (header != null && header.Length > 0)
{
gvExport.HeaderRow.Cells.Clear();
gvExport.HeaderRow.Cells.AddRange(header);
}
//合並單元格
if (mergeCellNums != null && mergeCellNums.Count > 0)
{
foreach (int cellNum in mergeCellNums.Keys)
{
MergeRows(gvExport, cellNum, mergeCellNums[cellNum], mergeKey);
}
}
// 返回客戶端
gvExport.RenderControl(htmlWriter);
curContext.Response.Clear();
curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
curContext.Response.End();
}
}
/// <summary>
/// 描述：行綁定事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected static void dgExport_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
foreach (TableCell cell in e.Row.Cells)
{
//優化導出數據顯示，如身份證、12-1等顯示異常問題
if (Regex.IsMatch(cell.Text.Trim(), @"^\d{12,}$") || Regex.IsMatch(cell.Text.Trim(), @"^\d+[-]\d+$"))
{
cell.Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
}
}
}
/// <summary>
/// 描述：合並GridView列中相同的行
/// 作者：李偉波
/// 時間：2012-10-18
/// </summary>
/// <param name="gvExport">GridView對象</param>
/// <param name="cellNum">需要合並的列</param>
/// <param name="mergeMode">合並模式 1 合並相同項、2 合並空項、3 合並相同項及空項</param>
/// <param name="mergeKey">作為合並項的標記列索引</param>
public static void MergeRows(GridView gvExport, int cellNum, int mergeMode, int? mergeKey)
{
int i = 0, rowSpanNum = 1;
System.Drawing.Color alterColor = System.Drawing.Color.LightGray;
while (i < gvExport.Rows.Count - 1)
{
GridViewRow gvr = gvExport.Rows[i];
for (++i; i < gvExport.Rows.Count; i++)
{
GridViewRow gvrNext = gvExport.Rows[i];
if ((!mergeKey.HasValue || (mergeKey.HasValue && (gvr.Cells[mergeKey.Value].Text.Equals(gvrNext.Cells[mergeKey.Value].Text) || " ".Equals(gvrNext.Cells[mergeKey.Value].Text)))) && ((mergeMode == 1 && gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text) || (mergeMode == 2 && " ".Equals(gvrNext.Cells[cellNum].Text.Trim())) || (mergeMode == 3 && (gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text || " ".Equals(gvrNext.Cells[cellNum].Text.Trim())))))
{
gvrNext.Cells[cellNum].Visible = false;
rowSpanNum++;
gvrNext.BackColor = gvr.BackColor;
}
else
{
gvr.Cells[cellNum].RowSpan = rowSpanNum;
rowSpanNum = 1;
//間隔行加底色，便於閱讀
if (mergeKey.HasValue && cellNum == mergeKey.Value)
{
if (alterColor == System.Drawing.Color.White)
{
gvr.BackColor = System.Drawing.Color.LightGray;
alterColor = System.Drawing.Color.LightGray;
}
else
{
alterColor = System.Drawing.Color.White;
}
}
break;
}
if (i == gvExport.Rows.Count - 1)
{
gvr.Cells[cellNum].RowSpan = rowSpanNum;
if (mergeKey.HasValue && cellNum == mergeKey.Value)
{
if (alterColor == System.Drawing.Color.White)
gvr.BackColor = System.Drawing.Color.LightGray;
}
}
}
}
}
}
頁面調用如下：
[html]
TableCell[] header = new TableCell[29];
for (int i = 0; i < header.Length; i++)
{
header[i] = new TableHeaderCell();
}
header[0].ColumnSpan = 7;
header[0].Text = "訂單基本信息";
header[1].ColumnSpan = 4;
header[1].Text = "收貨人信息";
header[2].ColumnSpan = 4;
header[2].Text = "快遞信息";
header[3].ColumnSpan = 3;
header[3].Text = "支付信息";
header[4].ColumnSpan = 6;
header[4].Text = "商品信息</th></tr><tr>";
//第二行
header[5].Text = "訂單編號";
header[6].Text = "訂單類型";
header[7].Text = "訂單狀態";
header[8].Text = "下單時間";
header[9].Text = "支付時間";
header[10].Text = "發貨時間";
header[11].Text = "備注";
header[12].Text = "收貨人姓名";
header[13].Text = "地址";
header[14].Text = "手機號碼";
header[15].Text = "配送方式";
header[16].Text = "物流公司名稱";
header[17].Text = "物流發貨單";
header[18].Text = "運費收入";
header[19].Text = "實際配送費";
header[20].Text = "訂單總額";
header[21].Text = "支付方式";
header[22].Text = "訂單支付金額";
header[23].Text = "商品編號";
header[24].Text = "商品名稱";
header[25].Text = "商品價格";
header[26].Text = "購買數量";
header[27].Text = "商品總金額";
header[28].Text = "優惠金額</th>";
DataTable dt = Common.DbHelper.DBClass_GetDataToTable(sqlDHD, sqlParam, ref rMsg);
Dictionary<int, int> mergeCellNums = new Dictionary<int, int>();
for (int i = 0; i < dt.Columns.Count; i++)
{
mergeCellNums.Add(i, 2);
}
Common.DataTable2Excel(dt, header, "數據導出" + DateTime.Now.ToString("yyyyMMdd"), mergeCellNums, 0);
以上代碼未經嚴格測試，或有錯漏，請引用或使用本文代碼的諸君注意。
導出效果如下圖：
