C# 合并多个excel并合并表尾的统计数据

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 = "";
        }
    }

完整项目连接：合并多个表格，计算表尾的统计数据（需要自己更改参数）