有多个结构一样的excel，带复杂表头需要合并为一个，且去掉多余的表头数据，可以用com组件来读取每个excel表格的range来合并到一个新的表格中。样例如图
有很多相同格式的表格，合并代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
using system;using system.collections.generic;using system.text;using system.reflection;using excel = microsoft.office.interop.excel;namespace consoleapplication20{ //添加引用-com-microsoft excel 11.0 object libery class program { static void main( string [] args) { //m为表格宽度标志（excel中的第m列为最后一列），3为表头高度 mergeexcel.domerge( new string [] { @ "e:/excel/类型a/公司a.xls" , @ "e:/excel/类型a/公司b.xls" }, @ "e:/excel/类型a/合并测试.xls" , "m" , 3); mergeexcel.domerge( new string [] { @ "e:/excel/类型b/统计表a.xls" , @ "e:/excel/类型b/统计表b.xls" }, @ "e:/excel/类型b/合并测试.xls" , "i" , 4); } } public class mergeexcel { excel.application app = new microsoft.office.interop.excel.applicationclass(); //保存目标的对象 excel.workbook bookdest = null ; excel.worksheet sheetdest = null ; //读取数据的对象 excel.workbook booksource = null ; excel.worksheet sheetsource = null ; string [] _sourcefiles = null ; string _destfile = string .empty; string _columnend = string .empty; int _headerrowcount = 1; int _currentrowcount = 0; public mergeexcel( string [] sourcefiles, string destfile, string columnend, int headerrowcount) { bookdest = (excel.workbookclass)app.workbooks.add(missing.value); sheetdest = bookdest.worksheets.add(missing.value, missing.value, missing.value, missing.value) as excel.worksheet; sheetdest.name = "data" ; _sourcefiles = sourcefiles; _destfile = destfile; _columnend = columnend; _headerrowcount = headerrowcount; } /// <summary> /// 打开工作表 /// </summary> /// <param name="filename"></param> void openbook( string filename) { booksource = app.workbooks._open(filename, missing.value, missing.value, missing.value, missing.value , missing.value, missing.value, missing.value, missing.value , missing.value, missing.value, missing.value, missing.value); sheetsource = booksource.worksheets[1] as excel.worksheet; } /// <summary> /// 关闭工作表 /// </summary> void closebook() { booksource.close( false , missing.value, missing.value); } /// <summary> /// 复制表头 /// </summary> void copyheader() { excel.range range = sheetsource.get_range( "a1" , _columnend + _headerrowcount.tostring()); range.copy(sheetdest.get_range( "a1" ,missing.value)); _currentrowcount += _headerrowcount; } /// <summary> /// 复制数据 /// </summary> void copydata() { int sheetrowcount = sheetsource.usedrange.rows.count; excel.range range = sheetsource.get_range( string .format( "a{0}" , _headerrowcount + 1), _columnend + sheetrowcount.tostring()); range.copy(sheetdest.get_range( string .format( "a{0}" , _currentrowcount + 1), missing.value)); _currentrowcount += range.rows.count; } /// <summary> /// 保存结果 /// </summary> void save() { bookdest.saved = true ; bookdest.savecopyas(_destfile); } /// <summary> /// 退出进程 /// </summary> void quit() { app.quit(); } /// <summary> /// 合并 /// </summary> void domerge() { bool b = false ; foreach ( string strfile in _sourcefiles) { openbook(strfile); if (b == false ) { copyheader(); b = true ; } copydata(); closebook(); } save(); quit(); } /// <summary> /// 合并表格 /// </summary> /// <param name="sourcefiles">源文件</param> /// <param name="destfile">目标文件</param> /// <param name="columnend">最后一列标志</param> /// <param name="headerrowcount">表头行数</param> public static void domerge( string [] sourcefiles, string destfile, string columnend, int headerrowcount) { new mergeexcel(sourcefiles, destfile, columnend, headerrowcount).domerge(); } }} |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
