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

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

一、前言

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

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

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

盘点四种使用Python批量合并同一文件夹内所有子文件夹下的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，代码如下：

# -*- coding: utf-8 -*-
import pandas as pd
import datetime
import os
# define a starting point of time
start = datetime.datetime.now()
def Set_Work_Path(x):
try:
os.chdir(x)
route = os.getcwd()
print(route)
return route
except Exception:
print("No Result")
work_path = r"E:\\PythonCrawler\\python_crawler-master\\MergeExcelSheet\\file\\"
Set_Work_Path(work_path)
# define a function to get all the xlsx file names after deleting old file if there.
def Get_Dedicated_4Letter_File_List(x):
path = os.getcwd()
old_name = path + os.sep + "汇总数据" + ".xlsx" # dim a txt name
if os.path.exists(old_name):
os.remove(old_name)
files = os.listdir(path) # print(files) #check all files name in the path
current_list = []
for i in range(0, len(files), 1):
try:
if files[i][-4:] == x and files[i][:4] != "汇总数据":
current_list.append(files[i])
except Exception:
pass
return current_list
Current_Excel_list = Get_Dedicated_4Letter_File_List("xlsx")
print(Current_Excel_list)
# define a function to read all sheets one by one in excel file
def Get_All_Sheets_Excel(x):
file = pd.ExcelFile(x)
list_sht_name = file.sheet_names # get list of sheets' names
print(list_sht_name)
list_sht_data = [] # get all sheet data sets into a list
for i in range(0, len(list_sht_name), 1):
list_sht_data.append(pd.read_excel(x, header=0, sheet_name=list_sht_name[i], index_col=None))
# merge all data sets together
df = pd.concat(list_sht_data)
# delete blank data
df.dropna(axis=0, how="all", inplace=True)
print(df)
return df
# define a list to get all data from sheets from different excel files
data_list = []
for i in range(0, len(Current_Excel_list), 1):
# print(Current_Excel_list[i])
data_list.append(Get_All_Sheets_Excel(Current_Excel_list[i]))
data = pd.concat(data_list)
data.dropna(axis=0, how="all", inplace=True)
print(data)
# save the data into excel file
writer = pd.ExcelWriter("王宁大佬的汇总数据.xlsx")
data.to_excel(writer, encoding="utf_8_sig", sheet_name="DATA", index=False)
# get the target pivot datasets
writer.save()
end = datetime.datetime.now()
run_time = round((end-start).total_seconds()/60, 2)
show = "程序运行消耗时间为: %s 分钟" % run_time+",搞定!"
print(show)

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

2、方法二

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

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

3、方法三

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

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

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

4、方法四

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

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