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

[[435418]]

一、前言

大家好，我是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，代码如下：

# -*- 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表，其返回的是一个字典，所有在后面遍历的时候，是以字典的形式进行取值的，效率比前面的方法都要高一些。

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

# -*- 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("合并完成!") 

六、效果展示

1、处理前Excel数据：

2、运行进度提示：

3、合并后的结果：

七、总结

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