盘点四种使用Python批量合并同一文件夹内所有子文件夹下的Excel文件内所有Sheet数据

一、前言

大家好，我是Python进阶者。前一阵子给大家分享了Python自动化文章：手把手教你利用Python轻松拆分Excel为多个CSV文件，手把手教你4种方法用Python批量实现多Excel多Sheet合并，而后在Python进阶交流群里边有个叫【扮猫】的粉丝遇到一个问题，她有很多个Excel表格，而且多个excel里多个sheet，现在需要对这些Excel文件进行合并。

用上面链接对应的方法进行合并，发现只能够合并Sheet，其他的就合并不了，这确实是个问题。

诚然，一个一个打开复制粘贴固然可行，但是该方法费时费力，还容易出错，几个文件还可以手动处理，要是几十个甚至上百个，你就抓瞎了，不过这问题对Python来说，so easy，一起来看看吧!

二、项目目标

用Python实现多Excel、多Sheet的合并处理，针对现实中的切确需求，使用Python批量合并同一文件夹内所有子文件夹下的Excel文件内所有Sheet数据，这个需求在现实生活中还是挺常见的，所有比较实用。

三、项目准备

软件：PyCharm

需要的库：pandas，os，glob

四、项目分析

1)如何选择要合并的Excel文件?

利用os和glob，获取所有要合并的Excel文件。

2)如何选择要合并的Sheet?

利用pandas库进行Excel读取，获取要合并的Sheet名。

3)如何合并?

利用pandas库，对所有Sheet名逐一循环打开，通过concat()函数进行数据追加合并即可。

4)如何保存文件?

利用to_excel保存数据，得到最终合并后的目标文件。

五、项目实现

这里提供4种方法给大家，一个比一个简洁，其中后面三个方法都是来自【小小明大佬】提供的，确实太强了。

1、方法一

这个方法是来自【王宁】大佬的分享，代码确实有点多，不过也是手把手教程，非常详细，也有注释，详情可以戳这篇文章：文科生自学Python-批量汇总同一路径内所有Excel文件内所有Sheet数据-基础知识1.41，代码如下：

1. # -*- coding: utf-8 -*-
2. import pandas as pd
3. import datetime
4. import os
7. # define a starting point of time
8. start = datetime.datetime.now()
11. def Set_Work_Path(x):
12. try:
13. os.chdir(x)
14. route = os.getcwd()
15. print(route)
16. return route
17. except Exception:
18. print("No Result")
21. work_path = r"E:\\PythonCrawler\\python_crawler-master\\MergeExcelSheet\\file\\"
22. Set_Work_Path(work_path)
25. # define a function to get all the xlsx file names after deleting old file if there.
26. def Get_Dedicated_4Letter_File_List(x):
27. path = os.getcwd()
28. old_name = path + os.sep + "汇总数据" + ".xlsx" # dim a txt name
29. if os.path.exists(old_name):
30. os.remove(old_name)
31. files = os.listdir(path) # print(files) #check all files name in the path
32. current_list = []
33. for i in range(0, len(files), 1):
34. try:
35. if files[i][-4:] == x and files[i][:4] != "汇总数据":
36. current_list.append(files[i])
37. except Exception:
38. pass
39. return current_list
42. Current_Excel_list = Get_Dedicated_4Letter_File_List("xlsx")
43. print(Current_Excel_list)
46. # define a function to read all sheets one by one in excel file
47. def Get_All_Sheets_Excel(x):
48. file = pd.ExcelFile(x)
49. list_sht_name = file.sheet_names # get list of sheets' names
50. print(list_sht_name)
51. list_sht_data = [] # get all sheet data sets into a list
52. for i in range(0, len(list_sht_name), 1):
53. list_sht_data.append(pd.read_excel(x, header=0, sheet_name=list_sht_name[i], index_col=None))
54. # merge all data sets together
55. df = pd.concat(list_sht_data)
56. # delete blank data
57. df.dropna(axis=0, how="all", inplace=True)
58. print(df)
59. return df
62. # define a list to get all data from sheets from different excel files
63. data_list = []
64. for i in range(0, len(Current_Excel_list), 1):
65. # print(Current_Excel_list[i])
66. data_list.append(Get_All_Sheets_Excel(Current_Excel_list[i]))
67. data = pd.concat(data_list)
68. data.dropna(axis=0, how="all", inplace=True)
69. print(data)
71. # save the data into excel file
72. writer = pd.ExcelWriter("王宁大佬的汇总数据.xlsx")
73. data.to_excel(writer, encoding="utf_8_sig", sheet_name="DATA", index=False)
74. # get the target pivot datasets
75. writer.save()
77. end = datetime.datetime.now()
78. run_time = round((end-start).total_seconds()/60, 2)
79. show = "程序运行消耗时间为: %s 分钟" % run_time+",搞定!"
80. print(show)

上面这个代码对原始数据要求比较苛刻，前提条件：所有数据都是规范的数据源且字段名和数据结构是一样的。这样看来，还是有些受限的。不过不要慌，接下来【小小明大佬】的这三个方法，就没有这个限制，下面一起来看看吧!

2、方法二

下面这个代码是基于【小小明大佬】提供的单Sheet表合并代码改进所得到的，关键点在于将sheet_name=None这个参数带上，代表获取Excel文件中的所有sheet表，其返回的是一个字典，所有在后面遍历的时候，是以字典的形式进行取值的，之后在15行的地方，需要注意使用的是extend()方法进行追加，如果使用append()方法，得到的就只有最后一个表格的合并结果，这个坑小编亲自踩过，感兴趣的小伙伴也可以踩下坑。

1. # -*- coding: utf-8 -*-
2. import os
3. import pandas as pd
4. result = []
5. path = r"E:\\PythonCrawler\\python_crawler-master\\MergeExcelSheet\\testfile\\file"
6. for root, dirs, files in os.walk(path, topdown=False):
7. for name in files:
8. if name.endswith(".xls") or name.endswith(".xlsx"):
9. df = pd.read_excel(os.path.join(root, name), sheet_name=None)
10. result.append(df)
12. data_list = []
13. for data in result:
14. # print(data.values())
15. data_list.extend(data.values()) # 注意这里是extend()函数而不是append()函数
17. df = pd.concat(data_list)
18. df.to_excel("testfile所有表合并.xlsx", index=False)
19. print("合并完成!")

3、方法三

下面这个代码是【小小明大佬】手撸的一个代码，使用了列表append()方法，效率虽说会低一些，但是处理上百上千个文件，仍然不在话下。

需要注意的是代码中的第6行和第7行，获取文件路径，其中**代表的是文件夹下的子文件递归。另外就是.xls*了，这个是正则写法，表示的是既可以处理xls格式，也可以处理xlsx格式的Excel文件，真是妙哉!

1. # -*- coding: utf-8 -*-
2. import glob
3. import pandas as pd
4. path = "E:\\PythonCrawler\\python_crawler-master\\MergeExcelSheet\\file\\"
5. data = []
6. for excel_file in glob.glob(f'{path}/**/[!~]*.xls*'):
7. # for excel_file in glob.glob(f'{path}/[!~]*.xlsx'):
8. excel = pd.ExcelFile(excel_file)
9. for sheet_name in excel.sheet_names:
10. df = excel.parse(sheet_name)
11. data.append(df)
12. # print(data)
14. df = pd.concat(data, ignore_index=True)
15. df.to_excel("小小明提供的代码(合并多表)--glob和pandas库列表append方法--所有表合并.xlsx", index=False)
16. print("合并完成!")

4、方法四

下面这个代码是【小小明大佬】手撸的另外一个代码，使用了sheet_name=None和列表extend()方法，将sheet_name=None这个参数带上，代表获取Excel文件中的所有sheet表，其返回的是一个字典，所有在后面遍历的时候，是以字典的形式进行取值的，效率比前面的方法都要高一些。

需要注意的是代码中的第6行和第7行，获取文件路径，其中**代表的是文件夹下的子文件递归。另外就是.xls*了，这个是正则写法，表示的是既可以处理xls格式，也可以处理xlsx格式的Excel文件，真是妙哉!

1. # -*- coding: utf-8 -*-
2. import glob
3. import pandas as pd
4. path = r"E:\PythonCrawler\python_crawler-master\MergeExcelSheet\file"
5. data = []
6. # for excel_file in glob.glob(f'{path}/**/[!~]*.xlsx'):
7. for excel_file in glob.glob(f'{path}/[!~]*.xlsx'):
8. dfs = pd.read_excel(excel_file, sheet_name=None).values()
9. data.extend(dfs)
10. # print(data)
12. df = pd.concat(data, ignore_index=True)
13. df.to_excel("小小明提供的代码(合并多表)--glob和pandas库列表extend方法--简洁--所有表合并.xlsx", index=False)
14. print("合并完成!")

六、效果展示

1、处理前Excel数据：

2、运行进度提示：

3、合并后的结果：

七、总结

本文从实际工作出发，基于Python编程，介绍了4种方法，实现批量合并同一文件夹内所有子文件夹下的Excel文件内所有Sheet数据，为大家减少了很多复制粘贴的麻烦，省时省力，还不容易出错。代码不多，循环追加有点绕，想想也就明白了，大家一起学习进步。

原文链接：https://mp.weixin.qq.com/s/MxHK0jMk_hSTIcz8vkXk7w