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

# -*- 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)

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

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

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