C#合并多個結構一樣的Excel

有多個結構一樣的Excel，帶複雜表頭需要合并為一個，且去掉多餘的表頭資料，可以用COM組件來讀取每個Excel表格的Range來合并到一個新的表格中。範例如圖

有很多相同格式的表格，合并代碼如下：

1.using  System;2.using  System.Collections.Generic;3.using  System.Text;4.using  System.Reflection;5.using  Excel = Microsoft.Office.Interop.Excel;6.namespace  ConsoleApplication207.{8.     //添加引用-COM-MicroSoft Excel 11.0 Object Libery9.     class  Program10.    {11.         static   void  Main( string [] args)12.        {13.             //M為表格寬度標誌（Excel中的第M列為最後一列），3為表頭高度14.            MergeExcel.DoMerge( new   string []15.            {16.                @ "E:\excel\類型A\公司A.xls" ,17.                @ "E:\excel\類型A\公司B.xls"18.            },19.                @ "E:\excel\類型A\合并測試.xls" ,  "M" , 3);20.            MergeExcel.DoMerge( new   string []21.            {22.                @ "E:\excel\類型B\統計表A.xls" ,23.                @ "E:\excel\類型B\統計表B.xls"24.            },25.                @ "E:\excel\類型B\合并測試.xls" ,  "I" , 4);26.        }27.28.29.30.31.    }32.     public   class  MergeExcel33.    {34.35.        Excel.Application app =  new  Microsoft.Office.Interop.Excel.ApplicationClass();36.         //儲存目標的對象37.        Excel.Workbook bookDest =  null ;38.        Excel.Worksheet sheetDest =  null ;39.         //讀取資料的對象40.        Excel.Workbook bookSource =  null ;41.        Excel.Worksheet sheetSource =  null ;42.43.44.         string [] _sourceFiles =  null ;45.         string  _destFile =  string .Empty;46.         string  _columnEnd =  string .Empty;47.         int  _headerRowCount = 1;48.         int  _currentRowCount = 0;49.50.         public  MergeExcel( string [] sourceFiles, string  destFile, string  columnEnd, int  headerRowCount)51.        {52.53.            bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);54.            sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value)  as  Excel.Worksheet;55.            sheetDest.Name =  "Data" ;56.57.            _sourceFiles = sourceFiles;58.            _destFile = destFile;59.            _columnEnd = columnEnd;60.            _headerRowCount = headerRowCount;61.62.        }63.         /// <summary>64.         /// 開啟工作表65.         /// </summary>66.         /// <param name="fileName"></param>67.         void  OpenBook( string  fileName)68.        {69.            bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value70.                , Missing.Value, Missing.Value, Missing.Value, Missing.Value71.                , Missing.Value, Missing.Value, Missing.Value, Missing.Value);72.            sheetSource = bookSource.Worksheets[1]  as  Excel.Worksheet;73.        }74.         /// <summary>75.         /// 關閉工作表76.         /// </summary>77.         void  CloseBook()78.        {79.            bookSource.Close( false , Missing.Value, Missing.Value);80.        }81.         /// <summary>82.         /// 複製表頭83.         /// </summary>84.         void  CopyHeader()85.        {86.            Excel.Range range = sheetSource.get_Range( "A1" , _columnEnd + _headerRowCount.ToString());87.            range.Copy(sheetDest.get_Range( "A1" ,Missing.Value));88.            _currentRowCount += _headerRowCount;89.        }90.         /// <summary>91.         /// 複製資料92.         /// </summary>93.         void  CopyData()94.        {95.             int  sheetRowCount = sheetSource.UsedRange.Rows.Count;96.            Excel.Range range = sheetSource.get_Range( string .Format( "A{0}" , _headerRowCount + 1), _columnEnd + sheetRowCount.ToString());97.            range.Copy(sheetDest.get_Range( string .Format( "A{0}" , _currentRowCount + 1), Missing.Value));98.            _currentRowCount += range.Rows.Count;99.        }100.         /// <summary>101.         /// 儲存結果102.         /// </summary>103.         void  Save()104.        {105.            bookDest.Saved =  true ;106.            bookDest.SaveCopyAs(_destFile);107.        }108.         /// <summary>109.         /// 退出進程110.         /// </summary>111.         void  Quit()112.        {113.            app.Quit();114.        }115.         /// <summary>116.         /// 合并117.         /// </summary>118.         void  DoMerge()119.        {120.             bool  b =  false ;121.             foreach  ( string  strFile  in  _sourceFiles)122.            {123.                OpenBook(strFile);124.                 if  (b ==  false )125.                {126.                    CopyHeader();127.                    b =  true ;128.                }129.                CopyData();130.                CloseBook();131.            }132.            Save();133.            Quit();134.        }135.         /// <summary>136.         /// 合并表格137.         /// </summary>138.         /// <param name="sourceFiles">源檔案</param>139.         /// <param name="destFile">目標檔案</param>140.         /// <param name="columnEnd">最後一列標誌</param>141.         /// <param name="headerRowCount">表頭行數</param>142.         public   static   void  DoMerge( string [] sourceFiles,  string  destFile,  string  columnEnd,  int  headerRowCount)143.        {144.             new  MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();145.        }146.    }147.148.}149.