C# 两张Excel 合并到新建的一张Excel

一、调用代码：

private void simpleButton1_Click(object sender, EventArgs e)
        {
            try
            {
                //M为表格宽度标志（Excel中的第M列为最后一列），3为表头高度
                MergeExcel.DoMerge(new string[]
                {
                    @"D:/WORKSPACE/limis.config/JSYD_ZD/Kcdj/Kcdj/bin/Debug/ExcelTemplate/类型A/土地勘测定界情况审核表(供地).xlt",
                    @"D:/WORKSPACE/limis.config/JSYD_ZD/Kcdj/Kcdj/bin/Debug/ExcelTemplate/类型A/附表.xlt"
                },

                    @"D:/WORKSPACE/limis.config/JSYD_ZD/Kcdj/Kcdj/bin/Debug/ExcelTemplate/类型A/合并测试.xls", "M", 3);
                MergeExcel.DoMerge(new string[]
                {
                    @"D:/WORKSPACE/limis.config/JSYD_ZD/Kcdj/Kcdj/bin/Debug/ExcelTemplate/类型B/土地勘测定界情况审核表(供地).xlt",
                    @"D:/WORKSPACE/limis.config/JSYD_ZD/Kcdj/Kcdj/bin/Debug/ExcelTemplate/类型B/附表.xlt"
                },

                @"D:/WORKSPACE/limis.config/JSYD_ZD/Kcdj/Kcdj/bin/Debug/ExcelTemplate/类型B/合并测试.xls", "I", 4);
            }
            catch (Exception ex)
            {
               
            }
        }

 

 

 

二、被调用代码：

using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1
{
    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), _columnEnd + sheetRowCount.ToString());
            range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount), 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();
        }
    }
}