有多個結構一樣的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.
