C#实现多个excel合并,并计算表格最后的统计数据。
注意:这里合并的表尾数据是原来单个excel里面本来就有的统计数据,这里只是将每个表格最后的统计数据取出来,再进行相加,最后将计算后的数据重新赋值; 没有统计数据的表格可以将计算统计的代码删除,只需要设置表头/表尾所占行数就可以;
首先要添加引用:
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Threading.Tasks;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
namespace mergeExcel
{
class ExcelUtils
{
Application app = new Application();
//保存目标的对象
Workbook bookDest = null;
Worksheet sheetDest = null;
//读取数据的对象
Workbook bookSource = null;
Worksheet sheetSource = null;
string[] _sourceFiles = null;
string _destFile = string.Empty;
string _columnEnd = string.Empty;
int _headerRowCount = 1;
int _currentRowCount = 0;
int _endInfoRowCount = 0;
int param1 = 0;
int param2 = 0;
int param3 = 0;
double param4 = 0;
int param5 = 0;
int param6 = 0;
public ExcelUtils(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount,int endInfoRowCount)
{
bookDest = (Workbook)app.Workbooks.Add(Missing.Value);
sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet;
sheetDest.Name = "Data";
_sourceFiles = sourceFiles;
_destFile = destFile;
_columnEnd = columnEnd;
_headerRowCount = headerRowCount;
_endInfoRowCount = endInfoRowCount;
}
/// <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 Worksheet;
}
/// <summary>
/// 关闭工作表
/// </summary>
void CloseBook()
{
bookSource.Close(false, Missing.Value, Missing.Value);
}
/// <summary>
/// 复制表头
/// </summary>
void CopyHeader()
{
Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
range.Copy(sheetDest.get_Range("A1", Missing.Value));
_currentRowCount += _headerRowCount;
}
/// <summary>
/// 复制表尾 处理表尾数据
/// </summary>
void CopyEndInfo()
{
int sheetRowCount = sheetSource.UsedRange.Rows.Count;
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 updateEndInfo() {
//设置某个单元格内容 需要改为自己需要计算的数据定位 行数/列数
sheetDest.Cells[_currentRowCount - _endInfoRowCount + 2, 2] = param1;
sheetDest.Cells[_currentRowCount - _endInfoRowCount + 2, 3] = param2;
sheetDest.Cells[_currentRowCount - _endInfoRowCount + 2, 8] = param3;
sheetDest.Cells[_currentRowCount - _endInfoRowCount + 2, 14] = param4;
sheetDest.Cells[_currentRowCount - _endInfoRowCount + 2, 16] = param5;
sheetDest.Cells[_currentRowCount - _endInfoRowCount + 2, 18] = param6;
}
/// <summary>
/// 复制数据
/// </summary>
void CopyData()
{
int sheetRowCount = sheetSource.UsedRange.Rows.Count;
Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount + 1), _columnEnd + (sheetRowCount-_endInfoRowCount).ToString());
range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount + 1), Missing.Value));
_currentRowCount += range.Rows.Count;
}
/// <summary>
/// 处理表尾数据
/// </summary>
void handleData(string fileName) {
//读取某个单元格内容 需要改为自己需要计算的数据定位 行数/列数
int sheetRowCount = sheetSource.UsedRange.Rows.Count;
param1 += int.Parse((((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount+2, 2]).Text) == "" ? "0" : (((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 2]).Text)) ;
param2 += int.Parse((((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 3]).Text) == "" ? "0" : (((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 3]).Text));
param3 += int.Parse((((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 8]).Text) == "" ? "0" : (((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 8]).Text));
param4 += double.Parse((((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 14]).Text) == "" ? "0" : (((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 14]).Text));
param5 += int.Parse((((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 16]).Text) == "" ? "0" : (((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 16]).Text));
param6 += int.Parse((((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 18]).Text) == "" ? "0" : (((Range)sheetSource.Cells[sheetRowCount - _endInfoRowCount + 2, 18]).Text));
}
/// <summary>
/// 保存结果
/// </summary>
void Save()
{
bookDest.Saved = false;
bookDest.SaveCopyAs(_destFile);
}
/// <summary>
/// 退出进程
/// </summary>
void Quit()
{
app.Quit();
}
/// <summary>
/// 合并
/// </summary>
void DoMerge()
{
bool b = false;
int i = 0;
foreach (string strFile in _sourceFiles)
{
i++;
//处理表尾数据
OpenBook(strFile);
handleData(strFile); //计算统计数据 不需要计算的可以删除该方法
if (b == false)
{
CopyHeader();
b = true;
}
//最后一条数据 添加表尾
if (_sourceFiles.Length <= i)
{
CopyEndInfo();
}
else {
CopyData();
}
CloseBook();
}
updateEndInfo(); //修改统计数据 不需要计算的可以删除该方法
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,int endInfoRowCount)
{
new ExcelUtils(sourceFiles, destFile, columnEnd, headerRowCount, endInfoRowCount).DoMerge();
}
}
}
在handleData(),updateEndInfo()中需要将计算的数据位置改为自己的表格位置,sheetSource.Cells[param1, param2]) ,param1:行数,param2:列数;
public partial class Form1 : Form
{
public String url = ""; //窗口展示的路径字符串
public String[] urls = { }; //选择的合并文件集合
public String targetUrl; //合并后的保存路径
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Multiselect = true;//该值确定是否可以选择多个文件
dialog.Title = "请选择文件夹";
dialog.Filter = "所有文件(*.*)|*.*";
if (dialog.ShowDialog() == DialogResult.OK)
{
urls = dialog.FileNames;
foreach (string filename in urls) {
url += filename + "\r\n";
}
this.label1.Text = url;
// do something
}
}
private void button3_Click(object sender, EventArgs e)
{
FolderBrowserDialog dialog = new FolderBrowserDialog();
dialog.Description = "请选择文件夹";
if (dialog.ShowDialog() == DialogResult.OK)
{
if (string.IsNullOrEmpty(dialog.SelectedPath))
{
MessageBox.Show("文件夹路径不能为空", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
return;
}
this.label2.Text = dialog.SelectedPath + "\\";
targetUrl = dialog.SelectedPath + "\\targetExcel.xlsx";
//do something
}
}
//点击合并
private void button2_Click(object sender, EventArgs e)
{
String param1 = "S"; //表格的最后一列
int param2= 9; //表头所占行数
int param3 = 10;//表尾所占行数
ExcelUtils.DoMerge(urls, targetUrl, param1, param2 ,param3);
}
private void button4_Click(object sender, EventArgs e)
{
this.label1.Text = "";
urls = new string[] { };
url = "";
}
}
完整项目连接:合并多个表格,计算表尾的统计数据(需要自己更改参数)