本文旨在解决使用Python Pandas库批量为Excel文件中多个Sheet添加相同列名的问题。通过遍历Excel文件的所有Sheet，并利用Pandas的ExcelWriter对象，将文件名作为新列添加到每个Sheet中，从而实现数据的批量处理。本文提供详细代码示例，并解释关键步骤，帮助读者快速掌握该技巧。
在使用Python处理Excel数据时，经常会遇到需要在多个Sheet中添加相同列的情况。例如，需要将文件名作为一列添加到每个Sheet中，以便于后续的数据分析和处理。 Pandas库提供了强大的Excel读写功能，结合ExcelWriter可以方便地实现批量操作。
以下代码展示了如何使用Pandas遍历Excel文件的所有Sheet，并将文件名作为新列添加到每个Sheet中：
import os
import pandas as pd
path = 'XXXX' # 请替换为你的Excel文件所在目录
for roots, dirs, files in os.walk(path):
xlsfile = [f for f in files if f.endswith('.xlsx')]
for xlsf in xlsfile:
print(xlsf)
excel_path = os.path.join(roots, xlsf)
xls = pd.ExcelFile(excel_path)
with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
for sheet_name in xls.sheet_names:
df = pd.read_excel(xls, sheet_name)
df['filename'] = xlsf.split("-")[-2]
df.to_excel(writer, sheet_name=sheet_name, index=False)代码解释：
立即学习“Python免费学习笔记（深入）”；
- 导入必要的库: os 用于文件路径操作，pandas 用于数据处理。
- 设置文件路径: 将path变量设置为包含Excel文件的目录。 注意：需要将XXXX替换为你实际的路径。
- 遍历文件: 使用os.walk遍历指定目录下的所有文件。
- 筛选Excel文件: 使用列表推导式[f for f in files if f.endswith('.xlsx')]筛选出所有以.xlsx结尾的文件。
-
循环处理每个Excel文件:
- 使用pd.ExcelFile(excel_path)读取Excel文件，获取ExcelFile对象。
- 使用pd.ExcelWriter(excel_path, engine='openpyxl')创建一个ExcelWriter对象，用于将修改后的数据写回Excel文件。 engine='openpyxl'指定使用openpyxl引擎，支持写入多个Sheet。
- 循环遍历xls.sheet_names，获取每个Sheet的名称。
- 使用pd.read_excel(xls, sheet_name)读取指定Sheet的数据到DataFrame。
- 添加新的列filename，其值为文件名经过处理后的结果。 在这里，文件名通过xlsf.split("-")[-2]进行分割，取倒数第二个部分。 注意：这个分割方式是基于你的文件名格式，需要根据实际情况进行调整。
- 使用df.to_excel(writer, sheet_name=sheet_name, index=False)将修改后的DataFrame写回Excel文件，index=False表示不写入索引列。
- 保存修改: with语句会自动关闭ExcelWriter对象，从而保存所有修改。
注意事项：
- 文件名分割: xlsf.split("-")[-2]这部分代码是根据文件名格式进行分割的，需要根据实际的文件名格式进行调整。 例如，如果文件名是data_2023-10-26.xlsx，并且你想要提取日期2023-10-26作为filename列的值，那么这段代码是适用的。 如果文件名格式不同，则需要修改分割符和索引值。
- 文件路径: 确保path变量指向的目录是正确的，并且包含需要处理的Excel文件。
- 引擎选择: engine='openpyxl' 是一个重要的参数，用于指定使用的Excel写入引擎。 openpyxl 引擎支持写入多个Sheet，因此是批量处理Excel文件的必要选择。 如果没有指定引擎，可能会出现写入错误。
- 覆盖原文件: 这段代码会直接覆盖原Excel文件。 在运行代码之前，建议备份原始文件，以防数据丢失。
- 依赖安装: 确保已经安装了pandas和openpyxl库。 可以使用pip install pandas openpyxl命令进行安装。
总结：
通过结合pd.ExcelFile和pd.ExcelWriter，可以方便地实现批量处理Excel文件的需求。 本文提供了一个简单的示例，展示了如何批量为Excel文件的所有Sheet添加相同列名。 在实际应用中，可以根据具体需求修改代码，例如修改文件名分割方式、添加不同的列值等。 掌握这种批量处理技巧，可以大大提高数据处理的效率。
