多个表格在不同下文件夹下面汇总到同一个表不同工作簿的方法 """ #主文件夹 #sheet1name #name1.xlsx #name2.xlsx #name3.xlsx #sheet2name #name1.xlsx #name2.xlsx #name3.xlsx #workbook sheet1name sheet2name 这个工具的作用就是 将和sheetname名字相同的1个文件夹中的所有的excel文件合并成一个pandas件，并将数据写入到对应的sheet中。 """ import os import xlwings as xw import pandas as pd path = f"{os.path.split(os.path.realpath(__file__))[0]}/" def readsheet_to_sheet(workbook, sheetname): '''读取sheetname对应的文件夹中的所有excel文件，并将数据合并到一个pandas数据框中''' folder_path = path + sheetname all_df = pd.DataFrame() for filename in os.listdir(folder_path): if filename.endswith('.xlsx'): file_path = os.path.join(folder_path, filename) print(f"Reading sheets from {file_path}:") with xw.Book(file_path) as wb: for sheet in wb.sheets: # 读取工作表数据并跳过第一行 df = sheet.used_range.options(pd.DataFrame, index=False, header=1).value # 打印工作表名称和数据 print(f"\nSheet: {sheet.name}") print(df) all_df = pd.concat([all_df,df],ignore_index=True) workbook.sheets[sheetname].range("A2").options(header = False , index = False).value = all_df def main(book_name): ''' 打开excel文件''' wb1 = xw.books(book_name) ########## #这里做了切片, 因为我前面2个工作表是不需要收集的 shop_list = [x for x in wb1.sheet_names][3:] for i in shop_list: readsheet_to_sheet(workbook=wb1,sheetname=i) if __name__ == "__main__": main("file_name.xlsx") 5月7日更新""" #主文件夹 #sheet1name #name1.xlsx #name2.xlsx #name3.xlsx #sheet2name #name1.xlsx #name2.xlsx #name3.xlsx #workbook sheet1name sheet2name 这个工具的作用就是 将和sheetname名字相同的1个文件夹中的所有的excel文件合并成一个pandas件，并将数据写入到对应的sheet中。 """ import os import pandas as pd import xlwings as xw path = f"{os.path.split(os.path.realpath(__file__))[0]}/" def readsheet_to_sheet(sheetname): """读取sheetname对应的文件夹中的所有excel文件，并将数据合并到一个pandas数据框中""" folder_path = path + sheetname all_df = pd.DataFrame() for filename in os.listdir(folder_path): if filename.endswith(".csv"): file_path = os.path.join(folder_path, filename) print(f"Reading sheets from {file_path}:") with xw.Book(file_path) as wb: for sheet in wb.sheets: # 读取工作表数据并跳过第一行 df = sheet.used_range.options( pd.DataFrame, index=False, header=1 ).value # 打印工作表名称和数据 print(f"\nSheet: {sheet.name}") all_df = pd.concat([all_df, df], ignore_index=True) all_df["店铺名称"] = sheetname # all_df["月份"] = pd.to_datetime(df["订单下单时间"]) return all_df # workbook.sheets[sheetname].range("A2").options(header = False , index = False).value = all_df def main(): """打开excel文件""" wb = xw.books.active shop_list = [ "JD-技嘉旗舰店", "JD-技嘉DIY旗舰店", "JD-AMD旗舰店", "JD-AORUS旗舰店", "JD-嘉志硕", "JD-图迈", "JD-优卡", "JD-XPG旗舰店", "JD-七彩虹旗舰店", ] df = pd.DataFrame() for i in shop_list: df = pd.concat([df, readsheet_to_sheet(i)], ignore_index=True) print(df) wb.sheets["综合优惠信息"].tables["综合优惠信息"].update(df, index=False) if __name__ == "__main__": main()