快捷導(dǎo)航

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

更新時(shí)間：2016年10月25日 16:12:32 作者：紅葉飄飛落

本篇文章主要介紹了c#中合并excel表格的方法，就是將excel表格結(jié)構(gòu)一樣的合并到一起，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下。

有多個(gè)結(jié)構(gòu)一樣的Excel，帶復(fù)雜表頭需要合并為一個(gè)，且去掉多余的表頭數(shù)據(jù)，可以用COM組件來讀取每個(gè)Excel表格的Range來合并到一個(gè)新的表格中。樣例如圖

有很多相同格式的表格，合并代碼如下：

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為表格寬度標(biāo)志（Excel中的第M列為最后一列），3為表頭高度
      MergeExcel.DoMerge( new  string [] 
      {
        @ "E:/excel/類型A/公司A.xls" , 
        @ "E:/excel/類型A/公司B.xls" 
      },
        @ "E:/excel/類型A/合并測(cè)試.xls" , "M" , 3);
      MergeExcel.DoMerge( new  string [] 
      {
        @ "E:/excel/類型B/統(tǒng)計(jì)表A.xls" , 
        @ "E:/excel/類型B/統(tǒng)計(jì)表B.xls" 
      },
        @ "E:/excel/類型B/合并測(cè)試.xls" , "I" , 4);
    }
    
  }
   public  class MergeExcel
  {
    
    Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
     //保存目標(biāo)的對(duì)象
    Excel.Workbook bookDest = null ;
    Excel.Worksheet sheetDest = null ;
     //讀取數(shù)據(jù)的對(duì)象 
    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>
     /// 關(guān)閉工作表
     /// </summary>
     void CloseBook()
    {
      bookSource.Close( false , Missing.Value, Missing.Value);
    }
     /// <summary>
     /// 復(fù)制表頭
     /// </summary>
     void CopyHeader()
    {
      Excel.Range range = sheetSource.get_Range( "A1" , _columnEnd + _headerRowCount.ToString());
      range.Copy(sheetDest.get_Range( "A1" ,Missing.Value));
      _currentRowCount += _headerRowCount;
    }
     /// <summary>
     /// 復(fù)制數(shù)據(jù)
     /// </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>
     /// 保存結(jié)果
     /// </summary>
     void Save()
    {
      bookDest.Saved = true ;
      bookDest.SaveCopyAs(_destFile);
    }
     /// <summary>
     /// 退出進(jìn)程
     /// </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">目標(biāo)文件</param>
     /// <param name="columnEnd">最后一列標(biāo)志</param>
     /// <param name="headerRowCount">表頭行數(shù)</param>
     public  static  void DoMerge( string [] sourceFiles, string destFile, string columnEnd, int headerRowCount)
    {
       new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
    }
  }
}

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: