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

作者：Gausst松鼠会 | 2024-03-26 15:02:16

踩

python合并指定文件夹里所有xlsx文件的每个sheet页,并将它们复制到新的excel工作

点击上方“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 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("合并完成!")