欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-26 08:41:32
有多个结构一样的excel,带复杂表头需要合并为一个,且去掉多余的表头数据,可以用com组件来读取每个excel表格的range来合并到一个新的表格中。样例如图...

有多个结构一样的excel,带复杂表头需要合并为一个,且去掉多余的表头数据,可以用com组件来读取每个excel表格的range来合并到一个新的表格中。样例如图

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

有很多相同格式的表格,合并代码如下:

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();
    }
  }
}

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