多个表格在不同下文件夹下面汇总到同一个表不同工作簿的方法

文件夹结构

--A  
    -1
    -2
    -3
--B
    -1
    -2
    -3

表格结构
--A ， --B
将文件夹里面的123 都汇总到字母AB 中去

import os
import xlwings as xw
import pandas as pd
path  = f"{os.path.split(os.path.realpath(__file__))[0]}/"

wb1 = xw.books("JD优惠券百补各店铺承担比例信息截止2024年1月.xlsx")
shop_list = [x for x in wb1.sheet_names][3:]


def  readsheet_to_sheet(sheetname):
    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)

    wb1.sheets[sheetname].range("A2").options(header = False , index = False).value  = all_df
    

if __name__ == "__main__":
    for i in shop_list:
        readsheet_to_sheet(sheetname=i)