c#中合并excel表格的方法示例

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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。