1，目的:

• 将多个Excel文件合并至指定Excel文件中。

2，主要知识点:

• Microsoft.Office.Interop.Excel 的应用。
• 驱动Excel的SQL语句应用。
• 解决使用app.Quit()语句后，Excel进程无法退出的问题。

3，效果呈现:

• 应用界面

• 选择多个源文件与目的的文件

• 点击合并，合并成功

• 点击呈现按钮，自动打开合并后的Excel 工作簿

• 4，源代码：

 public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        List<string> list = new List<string>();
        Excel.Application app;
        private void btnSourceSelect_Click(object sender, EventArgs e)
        {
            using(OpenFileDialog ofd=new OpenFileDialog())
            {
                ofd.Multiselect = true;
                ofd.Filter = ".xlsx文件|*.xlsx|.xls文件|*.xls";
                if(ofd.ShowDialog()== DialogResult.OK)
                {
                    list.Clear();
                    listBox1.Items.Clear();
                    foreach (string  filename in ofd.FileNames)
                    {
                        list.Add(filename);
                        listBox1.Items.Add(Path.GetFileName(filename));
                    }
                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog())
            {
                ofd.Multiselect = false;
                ofd.Filter = ".xlsx文件|*.xlsx|.xls文件|*.xls";
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    textBox1.Text = ofd.FileName;
                }
            }
        }

        private void btnMerge_Click(object sender, EventArgs e)
        {
            if(list.Count<1 || string.IsNullOrEmpty(textBox1.Text))
            {
                MessageBox.Show("请先选择源文件和目的文件","错误提示" , MessageBoxButtons.OK , MessageBoxIcon.Error);
                return;
            }
            app = new Excel.Application();
            //打开目的工作簿
        Excel.Workbook targetBook=    app.Workbooks.Open(textBox1.Text);
            
            //创建新工作表
            Excel.Worksheet targetSheet = targetBook.Sheets.Count>0?targetBook.Sheets[1]:targetBook.Worksheets.Add();
          //  targetSheet.Name = "合并";
            //源文件加载
            foreach (string  excelFilePath in list)
            {
                List<string> sheetNames = GetAllSheetName(excelFilePath);
                Excel.Workbook tempWorkBook = app.Workbooks.Open(excelFilePath);
                foreach (var item in sheetNames)
                {
                    Excel.Worksheet tempSheet = tempWorkBook.Sheets[item];
                    //注意Before与After不能同时有值
                    //如果是空值则只能是System.Reflection.Missing.Value 不是null
                    tempSheet.Copy(System.Reflection.Missing.Value, targetSheet);
                    
                }
                tempWorkBook.Close();
            }
            targetBook.Save();
            app.Quit();
            MessageBox.Show("合并完成","", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }
        /// <summary>
        /// 获取对应Exel工作簿中所有的Sheet名
        /// </summary>
        /// <param name="workBookFilePath"></param>
        /// <returns></returns>
        List<string > GetAllSheetName(string workBookFilePath)
        {
            //这里使用连接查询
            string constr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={workBookFilePath};Extended Properties="Excel 12.0;HDR=yes;IMEX=1;"";
            List<string> tableNames = new List<string>();
            using (OleDbConnection con = new OleDbConnection(constr))
            {
                con.Open();
                DataTable dt = con.GetSchema("Tables");
                con.Close();
                //获取字段TABLE_NAME值(Sheet名格式:SheetName$,名后面有个$)
                if (dt == null) return tableNames;
                foreach (DataRow row in dt.Rows)
                {
                    string sheetName = row["TABLE_NAME"].ToString().TrimEnd('$');
                    tableNames.Add(sheetName);
                }
            }
               
            return tableNames;

        }

        private void btnFinish_Click(object sender, EventArgs e)
        {
            //结束Excel进程
            if (app == null) return;
            //保存当前文档
            //app.ActiveWorkbook.Save();
            IntPtr hwd = new IntPtr(app.Hwnd);
            int id;
            //根据app 句柄获取该进程id
            GetWindowThreadProcessId(hwd, out id);
            System.Diagnostics.Process.GetProcessById(id)?.Kill();
            MessageBox.Show("已结束进程", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        [ System.Runtime.InteropServices. DllImport("User32.dll", CharSet = System.Runtime.InteropServices.CharSet.Auto)]    //调用API函数,目的获取新增EXCEL进程的进程ID
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        private void btnDisplay_Click(object sender, EventArgs e)
        {
           // app = new Excel.Application();
            app.Visible = true;
            //打开目的工作簿
            Excel.Workbook targetBook = app.Workbooks.Open(textBox1.Text);
        }
    }