如何用Python为Excel所有工作表添加列名并新增日期列

问题1：为Excel所有工作表添加提取自文件名的日期列 #

原代码仅处理第一个工作表的原因是pd.read_excel()默认仅读取第一个工作表。要覆盖所有工作表，需通过sheet_name=None读取全部工作表，得到一个以工作表名为键、DataFrame为值的字典，再逐个处理后批量写回文件。

修改后的代码如下：

import os
import pandas as pd

path = 'XXXX'

# 遍历目录获取所有xlsx文件
for roots, dirs, files in os.walk(path):
    xlsfile = [f for f in files if f.endswith('.xlsx')]

for xlsf in xlsfile:
    print(f"处理文件: {xlsf}")
    file_path = os.path.join(roots, xlsf)
    
    # 读取所有工作表，返回字典{sheet_name: DataFrame}
    all_sheets = pd.read_excel(file_path, sheet_name=None)
    
    # 从文件名提取日期（格式需根据你的实际文件名调整）
    extracted_date = xlsf.split("-")[-2]
    
    # 逐个处理每个工作表
    updated_sheets = {}
    for sheet_name, df in all_sheets.items():
        # 添加日期列
        df['提取日期'] = extracted_date
        updated_sheets[sheet_name] = df
    
    # 将所有修改后的工作表写回原文件
    with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
        for sheet_name, df in updated_sheets.items():
            df.to_excel(writer, sheet_name=sheet_name, index=False)

关键说明：

• sheet_name=None：读取Excel文件的所有工作表，返回字典结构。
• pd.ExcelWriter：用于批量写入多个工作表，避免覆盖原文件的其他工作表。
• 日期提取逻辑xlsf.split("-")[-2]需根据实际文件名格式调整，比如文件名是data_20240520_report.xlsx，就改成xlsf.split("_")[1]。

问题2：为所有工作表添加列名 #

分两种场景处理：

场景1：工作表无列名（第一行是数据） #

如果工作表原本没有列名，读取时指定header=None，再为DataFrame设置列名：

import os
import pandas as pd

path = 'XXXX'

for roots, dirs, files in os.walk(path):
    xlsfile = [f for f in files if f.endswith('.xlsx')]

for xlsf in xlsfile:
    file_path = os.path.join(roots, xlsf)
    all_sheets = pd.read_excel(file_path, sheet_name=None, header=None)
    
    # 定义新列名，数量要和数据列数匹配
    new_columns = ['列名1', '列名2', '列名3', '提取日期']  # 按需调整
    
    updated_sheets = {}
    for sheet_name, df in all_sheets.items():
        # 设置列名（避免列名数量不匹配）
        df.columns = new_columns[:len(df.columns)]
        # 同时添加日期列（如果需要）
        df['提取日期'] = xlsf.split("-")[-2]
        updated_sheets[sheet_name] = df
    
    with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
        for sheet_name, df in updated_sheets.items():
            df.to_excel(writer, sheet_name=sheet_name, index=False, header=True)

场景2：修改现有列名 #

如果工作表已有列名，直接通过映射替换：

import os
import pandas as pd

path = 'XXXX'

for roots, dirs, files in os.walk(path):
    xlsfile = [f for f in files if f.endswith('.xlsx')]

for xlsf in xlsfile:
    file_path = os.path.join(roots, xlsf)
    all_sheets = pd.read_excel(file_path, sheet_name=None)
    
    # 定义列名映射：原列名 -> 新列名
    column_mapping = {
        '原列名1': '新列名1',
        '原列名2': '新列名2',
        # 按需添加更多映射
    }
    
    updated_sheets = {}
    for sheet_name, df in all_sheets.items():
        # 重命名列
        df.rename(columns=column_mapping, inplace=True)
        # 添加日期列
        df['提取日期'] = xlsf.split("-")[-2]
        updated_sheets[sheet_name] = df
    
    with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
        for sheet_name, df in updated_sheets.items():
            df.to_excel(writer, sheet_name=sheet_name, index=False)

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