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  ConsoleApplication20
7.{
8.     //添加引用-COM-MicroSoft Excel 11.0 Object Libery
9.     class  Program
10.    {
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  MergeExcel
33.    {
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.Value
70.                , Missing.Value, Missing.Value, Missing.Value, Missing.Value
71.                , 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.

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索excel
， string
， missing
， value
， excel C# copy
， excel C# copy的问题
， void
表头
c站、c语言、cf、ch、c罗，以便于您获取更多的相关知识。