程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> C#入門知識 >> C# DataTable 導出 Excel 進階 多行表頭、合並單元格、中文文件名亂碼

C# DataTable 導出 Excel 進階 多行表頭、合並單元格、中文文件名亂碼

編輯:C#入門知識

例子是上一篇 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); 

以上代碼未經嚴格測試,或有錯漏,請引用或使用本文代碼的諸君注意。
導出效果如下圖:

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved