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

点击上方“Python爬虫与数据挖掘”，进行关注

回复“书籍”即可获赠Python从入门到进阶共10本电子书

今

日

鸡

汤

春潮带雨晚来急，野渡无人舟自横。

一、前言

大家好，我是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 pdimport datetimeimport os

# define a starting point of timestart = 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 filedef 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 filesdata_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 filewriter = pd.ExcelWriter("王宁大佬的汇总数据.xlsx")data.to_excel(writer, encoding="utf_8_sig", sheet_name="DATA", index=False)# get the target pivot datasetswriter.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 osimport pandas as pdresult = []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 globimport pandas as pdpath = "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 globimport pandas as pdpath = 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("合并完成!")