EXCEL 合并同样格式的多个excel文件

136 阅读 0 评论 90 点赞

我是靠谱客的博主文静手机，这篇文章主要介绍EXCEL 合并同样格式的多个excel文件，现在分享给大家，希望可以做个参考。

在日常工作中我们经常会遇到有很多个相同格式的excel文件并需要将他们合并到一个文件中去，要是文件少还好办手动搞一下就好了，但是当需要合并的文件是几千几百个的时候呢？这时我们就需要一个可以自动把相同格式的excel文件合并到一个文件中的工具！

复制代码

private void MergeExcel()
        {
            string TileLine = textBox1.Text;
            if(TileLine=="")
            {
                MessageBox.Show("请输入标题的总行数！", "标题总行数为空！");//需要输入标题的总行数，因为格式相同的文件只需要一个标题就行了，只合并内容
            }
            else
            {
            
                Microsoft.Office.Interop.Excel.Application excel = new ApplicationClass();
                excel.ScreenUpdating = false; //停止工作表刷新
                excel.DisplayAlerts = false;
                Workbook workbook = excel.Workbooks.Add(Type.Missing);//合并到此表
                Workbook workbook2;
                Worksheet worksheet = (Worksheet)workbook.Sheets[1];
                worksheet.Name = "合并后表格";

for (int i = 0; i < filename.Length; i++)
                {
                    workbook2 = excel.Workbooks.Open(filename[i]);
                    Worksheet worksheet2 = (Worksheet)workbook2.ActiveSheet;
                    if (i == 0)//标题只需要复制一遍
                    {
                        Range range;
                        range = (Range)worksheet.Cells[worksheet.Range["A65536", "A65536"].End[XlDirection.xlUp].Row, 1];
                        worksheet2.UsedRange.Copy(range);

}
                    else
                    {
                        Range range;
                        range = (Range)worksheet.Cells[worksheet.Range["A65536", "A65536"].End[XlDirection.xlUp].Row, 1];
                        worksheet2.UsedRange.Offset[TileLine].Copy(range);
                    }
                    workbook2.Close();
                   // PublicMethod.Kill(excel);//关闭excel进程
                }

SaveFileDialog saveExcelDialog = new SaveFileDialog();
                saveExcelDialog.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件|*.*"; 
                string saveExcelPath="";
                if(saveExcelDialog.ShowDialog()==DialogResult.OK)
                {
                    saveExcelPath = saveExcelDialog.FileName;
                    workbook.SaveAs(saveExcelPath);//保存新建的excel到指定的路径中去
                    workbook.Close();
                    excel.Quit();
                    PublicMethod.Kill(excel);//关闭excel进程
                    MessageBox.Show("合并表格成功！");
                }
                else
                {
                    MessageBox.Show("保存失败！");
                    this.Close();
                }
                
               
            }
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
		private void MergeExcel()
        {
            string TileLine = textBox1.Text;
            if(TileLine=="")
            {
                MessageBox.Show("请输入标题的总行数！", "标题总行数为空！");//需要输入标题的总行数，因为格式相同的文件只需要一个标题就行了，只合并内容
            }
            else
            {
            
                Microsoft.Office.Interop.Excel.Application excel = new ApplicationClass();
                excel.ScreenUpdating = false; //停止工作表刷新
                excel.DisplayAlerts = false;
                Workbook workbook = excel.Workbooks.Add(Type.Missing);//合并到此表
                Workbook workbook2;
                Worksheet worksheet = (Worksheet)workbook.Sheets[1];
                worksheet.Name = "合并后表格";

                for (int i = 0; i < filename.Length; i++)
                {
                    workbook2 = excel.Workbooks.Open(filename[i]);
                    Worksheet worksheet2 = (Worksheet)workbook2.ActiveSheet;
                    if (i == 0)//标题只需要复制一遍
                    {
                        Range range;
                        range = (Range)worksheet.Cells[worksheet.Range["A65536", "A65536"].End[XlDirection.xlUp].Row, 1];
                        worksheet2.UsedRange.Copy(range);

                    }
                    else
                    {
                        Range range;
                        range = (Range)worksheet.Cells[worksheet.Range["A65536", "A65536"].End[XlDirection.xlUp].Row, 1];
                        worksheet2.UsedRange.Offset[TileLine].Copy(range);
                    }
                    workbook2.Close();
                   // PublicMethod.Kill(excel);//关闭excel进程
                }

                SaveFileDialog saveExcelDialog = new SaveFileDialog();
                saveExcelDialog.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件|*.*"; 
                string saveExcelPath="";
                if(saveExcelDialog.ShowDialog()==DialogResult.OK)
                {
                    saveExcelPath = saveExcelDialog.FileName;
                    workbook.SaveAs(saveExcelPath);//保存新建的excel到指定的路径中去
                    workbook.Close();
                    excel.Quit();
                    PublicMethod.Kill(excel);//关闭excel进程
                    MessageBox.Show("合并表格成功！");
                }
                else
                {
                    MessageBox.Show("保存失败！");
                    this.Close();
                }
                
               
            }
        }

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 		private void SelectExcelFile()//打开对话框选取需要合并的文件
        {
            OpenFileDialog dialog = new OpenFileDialog();
            
            dialog.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件|*.*";
            dialog.Multiselect = true;
            
            if(dialog.ShowDialog()==DialogResult.OK)
            {
                filename = dialog.FileNames;
            }

        }

在程序完成后，打开合并表格的时候发现打不开，后面发现是程序运行时打开的excel进程还没有关闭，因此在程序结束时需要把进程关闭，以下关闭excel进程参考博客：https://blog.csdn.net/spw55381155/article/details/79891108depth_1utm_source=distribute.pc_relevant.nonetask&utm_source=distribute.pc_relevant.none-task

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
	public class PublicMethod//关闭excel进程
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]//导入User32.dll中的函数
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);//获取指定窗体的进程id
        public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
        {
            IntPtr t = new IntPtr(excel.Hwnd);//得到这个句柄，具体作用是得到这块内存入口 

            int k = 0;
            GetWindowThreadProcessId(t, out k);   //得到本进程唯一标志k
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);   //得到对进程k的引用
            p.Kill();     //关闭进程k
        }

    }