import os |
import pandas as pd |
# 设置文件夹路径 |
folder_path = r "path/to/folder" |
# 获取文件夹中所有的Excel文件名 |
excel_files = [ file for file in os.listdir(folder_path) if file .endswith( '.xlsx' )] |
# 读取每个Excel文件中的所有工作表 |
df_list = [] |
for file in excel_files: |
file_path = os.path.join(folder_path, file ) |
excel_data = pd.read_excel(file_path, sheet_name = None ) |
for sheet_name, df in excel_data.items(): |
df[ 'Source File' ] = file # 添加一列,记录每个工作表的来源文件 |
df[ 'Sheet Name' ] = sheet_name # 添加一列,记录每个工作表的名称 |
df_list.append(df) |
# 将所有工作表合并成一个DataFrame |
merged_df = pd.concat(df_list, ignore_index = True , sort = False ) |
# 保存合并后的DataFrame到一个新的Excel文件中 |
output_file_path = os.path.join(folder_path, "merged_output.xlsx" ) |
merged_df.to_excel(output_file_path, index = False ) |