ASP.net(C#)匯出Excel—-普通表頭，多層表頭。

在工作中，使用ASP.net（C#）從頁面上匯出Excel檔案。要求是複雜表頭

所以就試了幾種方法，列出來供大家參考：

第一種方法呢，主要針對簡單的Excel的匯出，只有一行列名和資料。

在頁面上，有一個匯出的按鈕。下面唯寫出按鈕事件的代碼：

            try            {                System.Data.DataTable table = bindData("Trdate desc");                //將DataTable中添加一行，合并所有列                String[] arr = new String[10] { "卡印刷號", "流水號", "商戶名稱", "商戶號", "POS編號", "日期時間", "發生額", "操作員", "類型", "備忘" };                for (int i = 0; i < table.Columns.Count; i++)                {                    table.Columns[i].ColumnName = arr[i].ToString();                }                DataView dv1 = table.DefaultView;                dv1.Sort = "Trdate desc";//將DataTable排序，按POS編號（分店）排序，日期時間按倒序排列                table = dv1.ToTable();                ExcelHelper.DataTable1Excel(table);            }            catch            {                Response.Write("<script >function window.onload() {alert('匯出失敗！');}</script>");            }

ExcelHelper類中：

  public static void DataTable1Excel(System.Data.DataTable dtData)        {            try             {             GridView gvExport = null;             HttpContext curContext = HttpContext.Current;             StringWriter strWriter = null;            HtmlTextWriter htmlWriter = null;            if (dtData != null)            {                curContext.Response.ContentType = "application/vnd.ms-excel";                curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");                curContext.Response.Charset = "utf-8";                strWriter = new StringWriter();                htmlWriter = new HtmlTextWriter(strWriter);                gvExport = new GridView();                gvExport.DataSource = dtData.DefaultView;                gvExport.AllowPaging = false;                gvExport.DataBind();                gvExport.RenderControl(htmlWriter);                curContext.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=gb2312\"/>" + strWriter.ToString());                curContext.Response.End();            }            }            catch (Exception e)            {                 throw e;            }        }

方法2：需要引入MyXls的引用。org.in2bits.MyXls

 try            {                org.in2bits.MyXls.XlsDocument tz = new org.in2bits.MyXls.XlsDocument();                tz.FileName = DateTime.Now.ToString().Replace("-", "").Replace(":", "").Replace(" ", "") + ".xls";//excel檔案名稱                org.in2bits.MyXls.Worksheet sheet = tz.Workbook.Worksheets.AddNamed("36524儲值明細");//Excel工作表名稱                org.in2bits.MyXls.Cells cells = sheet.Cells;                int colnum = GridView_Data.Columns.Count; //擷取gridview列數                for (int i = 0; i < colnum; i++)                {                    cells.AddValueCell(1, (i + 1), GridView_Data.Columns[i].HeaderText);//匯出gridView列名                }                DataTable table = bindData("Trdate desc");                for (int i = 0; i < table.Rows.Count; i++)                {                    for (int j = 0; j < colnum; j++)                    {                        cells.AddValueCell((i + 2), (j + 1), table.Rows[i][j].ToString());                    }                }                tz.Send();//把寫好的excel檔案輸出到用戶端            }            catch { }

方法3：-------------------複雜表頭

DataTable table = bindData("Trdate desc");            String[] arr = new String[10] { "卡印刷號", "流水號", "商戶名稱", "商戶號", "POS編號", "日期時間", "發生額", "操作員", "類型", "備忘" };            for (int i = 0; i < table.Columns.Count; i++)            {                table.Columns[i].ColumnName = arr[i].ToString();            }            DataView dv1 = table.DefaultView;            dv1.Sort = "Trdate desc";//將DataTable排序，按POS編號（分店）排序，日期時間按倒序排列            table = dv1.ToTable();            string summary = LabelSumLS.Text;            exportBu36524czmx("儲值明細", summary, table);

  #region 匯出Excel--方法三        protected void exportBu36524czmx(string titleName,string summary,DataTable table)        {            try            {                string temp_path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);//產生的檔案存放路徑                ExcelOperate excelOperate = new ExcelOperate();                //建立一個Excel.Application的新進程                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();                if (app == null)                {                    return;                }                app.Visible = false;                app.UserControl = true;                Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;                Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//這裡的Add方法裡的參數就相當於繼承了一個空模板(暫這樣理解吧)                Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;                Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);                if (worksheet == null)                {                    return;                }                worksheet.Rows.NumberFormatLocal = "@"; //設定所有儲存格為文字格式設定                 //第一行的標題                worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, table.Columns.Count]).Merge(Missing.Value); //橫向合并                worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = titleName;                //設定格式                excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//置中                excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 12);//字型大小                worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, table.Columns.Count]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色連續邊框                //第二行的綜述                worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, table.Columns.Count]).Merge(Missing.Value); //橫向合并                worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 1]).Value2 = summary;                //設定格式                 excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 1]);//置中                 excelOperate.SetFontSize(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 1], 9);//字型大小                 worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[1, table.Columns.Count]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色連續邊框                for (int i = 0; i < table.Columns.Count; i++)                {                    worksheet.Cells[3, i + 1] = table.Columns[i].ColumnName.ToString();                    excelOperate.SetFontSize(worksheet, worksheet.Cells[3, i + 1], worksheet.Cells[3, i + 1], 9);//字型大小                    excelOperate.SetBold(worksheet, worksheet.Cells[3, i + 1], worksheet.Cells[3, i + 1]); //黑體                }                worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, table.Columns.Count]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);                for (int i = 0; i < table.Rows.Count; i++)                {                    for (int j = 0; j < table.Columns.Count; j++)                    {                        string data = table.Rows[i][j].ToString();                        worksheet.Cells[4 + i, j + 1] = data;                        excelOperate.SetFontSize(worksheet, worksheet.Cells[4 + i, j + 1], worksheet.Cells[4 + i, j + 1], 9);//字型大小                        //if (j == 6)                         //{                         //    worksheet.Cells[4 + i, j + 1].                        //}                    }                    worksheet.get_Range(worksheet.Cells[4 + i, 1], worksheet.Cells[4 + i, 10]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//設定邊框顏色，不然預覽列印，會非常不雅觀                }                worksheet.Name = titleName;                worksheet.Columns.EntireColumn.AutoFit();//列寬自適應                String tick = DateTime.Now.ToString().Replace("-", "").Replace(":", "").Replace(" ", "") + ".xls";//excel檔案名稱                String save_path = temp_path + "\\" + tick + ".xls";                workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                excelOperate.Dispose(worksheet, workbook, app);//關閉Excel進程                Response.Write("<script >function window.onload() {alert('匯出Excel檔案到案頭成功！');}</script>");            }            catch            {                Response.Write("<script >function window.onload() {alert('匯出失敗！');}</script>");            }                    }        #endregion 

結果：

方法4------------------------複雜表頭，和方法三的結果一樣。---------------------------我最後採用的第4種方法。

  string excelTile = Label1.Text;            string summary = LabelSumLS.Text;            TableCell[] header = new TableCell[12];            for (int i = 0; i < header.Length; i++)            {                header[i] = new TableHeaderCell();            }            //第一行表頭            header[0].ColumnSpan = 10;            header[0].Text = excelTile + "</th></tr><tr>";            //第二行表頭            header[1].ColumnSpan = 10;            header[1].Text = summary + "</th></tr><tr>";            String[] arr = new String[10] { "卡印刷號", "流水號", "商戶名稱", "商戶號", "POS編號", "日期時間", "發生額", "操作員", "類型", "備忘" };            for (int i = 0; i < arr.Length; i++)            {                header[i + 2].Text = arr[i];                if (i + 2 == 11)                {                    header[i + 2].Text = arr[i] + "</th>";                }            }            DataTable dt = bindData("Trdate desc");//根據檢索條件查詢出DataTable            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, excelTile + DateTime.Now.ToString("yyyyMMdd"), mergeCellNums, 0);

Common類的代碼如下:

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;namespace ContractSystem.WebUI.DataAnalyse{    /// <summary>      /// Common 的摘要說明      /// 作者：李偉波      /// 時間：2012-10-18      /// </summary>      public class Common    {        public Common()        {            //              //TODO: 在此處添加建構函式邏輯              //          }        /// <summary>          /// 描述：把DataTable內容匯出excel並返回用戶端           /// </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)                {                    //最佳化匯出資料顯示，如大資料的字串：卡號等顯示異常問題                      if (Regex.IsMatch(cell.Text.Trim(), @"^\d{12,}$"))                    {                        cell.Attributes.Add("style", "vnd.ms-excel.numberformat:@");                    }                }            }        }        /// <summary>             /// 描述：合并GridView列中相同的行          /// </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;                        //}                    }                }            }        }    }}

第4中方法參考連結如下：{我的程式中，將行間隔的顏色去掉了，在Common中注釋掉的語句是每間隔一行，將該行的背景置灰}

http://blog.csdn.net/ranbolwb/article/details/8083983