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-MicroSoftExcel11.0ObjectLibery
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. }
31. }
32. public class MergeExcel
33. {
35. Excel.Applicationapp= new Microsoft.Office.Interop.Excel.ApplicationClass();
36. //保存目标的对象
37. Excel.WorkbookbookDest= null ;
38. Excel.WorksheetsheetDest= null ;
39. //读取数据的对象
40. Excel.WorkbookbookSource= null ;
41. Excel.WorksheetsheetSource= null ;
44. string []_sourceFiles= null ;
45. string _destFile= string .Empty;
46. string _columnEnd= string .Empty;
47. int _headerRowCount=1;
48. int _currentRowCount=0;
50. public MergeExcel( string []sourceFiles, string destFile, string columnEnd, int headerRowCount)
51. {
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" ;
57. _sourceFiles=sourceFiles;
58. _destFile=destFile;
59. _columnEnd=columnEnd;
60. _headerRowCount=headerRowCount;
62. }
63. ///<summary>
64. ///打开工作表
65. ///</summary>
66. ///<paramname="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.Rangerange=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.Rangerange=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. ///<paramname="sourceFiles">源文件</param>
139. ///<paramname="destFile">目标文件</param>
140. ///<paramname="columnEnd">最后一列标志</param>
141. ///<paramname="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. }
148. }