Python迭代读取Excel工作表名拆分后新增列并回存的实现方法

前置依赖 #

先安装所需第三方库：

pip install pandas openpyxl

完整实现代码 #

import os
import pandas as pd

base_dir = './'
file_list = os.listdir(base_dir)

for file in file_list:
    # 筛选xlsx格式文件，跳过临时文件和已处理的文件避免重复执行
    if file.endswith('.xlsx') and not file.startswith('~$') and not file.startswith('processed_'):
        # 拼接文件完整路径
        file_path = os.path.join(base_dir, file)
        # 读取文件所有工作表，返回「工作表名:DataFrame」格式的字典
        sheet_dict = pd.read_excel(file_path, sheet_name=None, engine='openpyxl')
        
        processed_sheet = {}
        for sheet_name, df in sheet_dict.items():
            # 拆分工作表名获取对应字段
            year, quarter, city = sheet_name.split('_')
            # 新增三列统一赋值
            df['year'] = year
            df['quarter'] = quarter
            df['city'] = city
            processed_sheet[sheet_name] = df
        
        # 回存为新的多工作表Excel，默认不覆盖原文件
        output_path = os.path.join(base_dir, f'processed_{file}')
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            for sheet_name, df in processed_sheet.items():
                df.to_excel(writer, sheet_name=sheet_name, index=False)

注意事项 #

• 如果存在表名不符合年_季度_城市三段拆分规则的情况，可以在拆分逻辑处加try...except捕获异常跳过异常工作表，避免程序崩溃
• 代码默认生成加processed_前缀的新文件，若需要直接覆盖原文件，将output_path改为原file_path即可，操作前建议备份原文件
• to_excel方法的index=False参数表示导出时不保留pandas的自动行索引，若业务需要保留可删除该参数

内容的提问来源于stack exchange，提问作者ah bon