有多个结构一样的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();
}
}
}
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
dy("nrwz");
查看更多关于c#中合并excel表格的方法示例的详细内容...