Python为Excel每个表添加列名方法

本文旨在提供一个Python解决方案，用于批量处理Excel文件，并将文件名作为新列添加到每个工作表中。通过使用pandas库，我们可以高效地读取、修改和保存Excel文件，从而简化数据处理流程。本文将详细介绍实现步骤，并提供可直接使用的代码示例，帮助读者快速掌握该技巧。

在数据处理过程中，经常需要批量处理Excel文件，并对每个工作表进行相同的操作。一个常见的需求是将文件名添加到每个工作表中作为一个新的列。以下是如何使用Python的pandas库来实现这一目标的详细步骤和示例代码。

步骤详解

1. 导入必要的库: 首先，我们需要导入os库用于文件路径操作，以及pandas库用于Excel文件的读写。

   import os
   import pandas as pd

2. 指定文件路径: 定义包含Excel文件的根目录路径。请注意替换'XXXX'为实际的路径。

   path = 'XXXX'

3. 遍历文件: 使用os.walk()函数遍历指定路径下的所有文件。os.walk()会返回一个三元组 (roots, dirs, files)，分别表示当前目录路径、子目录列表和文件列表。

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

   这里使用列表推导式筛选出所有以.xlsx结尾的文件。

4. 处理每个Excel文件: 遍历筛选出的Excel文件列表。

   for xlsf in xlsfile:
       print(xlsf)
       excel_path = os.path.join(roots, xlsf)
       xls = pd.ExcelFile(excel_path)

   • excel_path = os.path.join(roots, xlsf): 构建Excel文件的完整路径。
   • xls = pd.ExcelFile(excel_path): 使用pd.ExcelFile对象读取Excel文件，这可以更高效地处理包含多个工作表的Excel文件。

5. 读取并修改每个工作表: 使用pd.ExcelWriter和循环遍历工作表来处理每个工作表。

   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)

   • with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:: 创建一个ExcelWriter对象，用于将修改后的数据写回原始Excel文件。使用with语句可以确保文件在使用完毕后被正确关闭。 engine='openpyxl' 指定了使用 openpyxl 引擎，该引擎支持 .xlsx 格式。
   • for sheet_name in xls.sheet_names:: 遍历Excel文件中的每个工作表。
   • df = pd.read_excel(xls, sheet_name): 读取当前工作表的数据到DataFrame对象df。
   • df['filename'] = xlsf.split("-")[-2]: 创建一个名为filename的新列，并将文件名（从文件名中提取的部分）赋值给该列的每一行。这里假设文件名格式为 ...-部分内容-....xlsx，需要提取中间的部分内容。
   • df.to_excel(writer, sheet_name=sheet_name, index=False): 将修改后的DataFrame写回到Excel文件的当前工作表。index=False表示不写入索引列。

完整代码示例

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:
        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)

注意事项

• 文件名格式: 代码中的 xlsf.split("-")[-2] 假设文件名包含 - 分隔符，并且需要提取倒数第二个部分。请根据实际文件名格式进行调整。
• 依赖库: 确保安装了 pandas 和 openpyxl 库。可以使用 pip install pandas openpyxl 命令进行安装。
• 文件路径: 确保提供的文件路径是正确的，并且程序有权限访问该路径下的文件。
• 引擎选择: engine='openpyxl' 确保程序可以处理 .xlsx 格式的文件。如果处理 .xls 格式的文件，可能需要使用 xlrd 和 xlwt 库，并指定相应的引擎。
• 数据安全: 在处理大量数据时，建议先在一个小的测试数据集上进行测试，确保代码的正确性，避免意外修改或损坏原始数据。

总结

本文提供了一个清晰、可操作的Python脚本，用于批量处理Excel文件，并将文件名作为新列添加到每个工作表中。通过使用pandas库和openpyxl引擎，可以高效地完成这项任务。在实际应用中，可以根据具体需求修改代码，例如调整文件名提取规则、添加错误处理机制等，以满足不同的数据处理场景。