C#WinFormDataTable Excelの複数行ヘッダーのエクスポート、セルのマージ


今回のコードは従来のasp.NetDataTable 2 Excelクラスを修正し、最適化、使用方法とasp.Net版は同じです.
この例ではMicrosoftを用いた.Office.Interop.Excel.dll、バージョン12.0.4518.1014
asp.NetDataTable 2 Excel類の記事リンク:C#DataTable Excel進級複数行ヘッダー、連結セル、中国語ファイル名文字化け
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;
using System.IO;
using System.Text;
using System.Web.UI;

namespace DataTable2Excel
{
    /// 
    /// Common      
    ///   :   
    ///   :2016-4-8
    /// 
    public class Common
    {
        public Common()
        {
            //
            //TODO:            
            //
        }

        /// 
        ///   : DataTable    excel       
        ///   :   
        ///   :2016-4-8
        /// 
        /// 
        /// 
        /// 
        ///             :   -    (     1      、2     、3         )
        ///            
        public static void DataTable2Excel(System.Data.DataTable dtData, TableCell[] header, string fileName, Dictionary mergeCellNums, int? mergeKey)
        {
            System.Web.UI.WebControls.GridView gvExport = null;
            // IO       excel   
            StringWriter strWriter = null;
            HtmlTextWriter htmlWriter = null;

            if (dtData != null)
            {
                //   excel   
                strWriter = new StringWriter();
                htmlWriter = new 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);

                StreamWriter sw = new StreamWriter(fileName, false, Encoding.GetEncoding("gb2312"));
                sw.Write("" + strWriter.ToString());
                sw.Flush();
                sw.Close();
                sw.Dispose();

                strWriter.Close();
                strWriter.Dispose();
                //           Excel  , Excel            ,       ,   Excel2003  。
                SaveAsExcel2003(fileName);
            }
        }
        /// 
        ///   :     
        /// 
        /// 
        /// 
        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:@");
                    }
                }
            }
        }

        ///    
        ///   :  GridView      
        ///   :   
        ///   :2016-4-8
        ///    
        /// GridView     
        ///          
        ///      1      、2     、3         
        ///            
        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;
                        if (alterColor == System.Drawing.Color.White)
                            gvrNext.BackColor = System.Drawing.Color.LightGray;
                    }
                    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;
                        }
                    }
                }
            }
        }
        /// 
        ///    2003  Excel
        /// 
        /// 
        public static void SaveAsExcel2003(string filePath)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            app.DisplayAlerts = false; //        
            Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //   Excel2003  
            wb.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            wb.Close(Type.Missing, Type.Missing, Type.Missing);
            wbs.Close();
            app.Quit();
            wb = null;
            wbs = null;
            app = null;
            GC.Collect();
        }
    }
}