c# excel合并单元格（c#合并excel文件）

有多个结构一样的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(); } } }