c#中合并excel表格的方法示例
程序员文章站
2022-03-26 08:41:32
有多个结构一样的excel,带复杂表头需要合并为一个,且去掉多余的表头数据,可以用com组件来读取每个excel表格的range来合并到一个新的表格中。样例如图...
有多个结构一样的excel,带复杂表头需要合并为一个,且去掉多余的表头数据,可以用com组件来读取每个excel表格的range来合并到一个新的表格中。样例如图
有很多相同格式的表格,合并代码如下:
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(); } } }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
上一篇: 知识面不广谁跟你聊啊