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