引言
在日常数据分析和报告工作中，我们经常需要处理大量分散在多个 excel 文件中的数据。这些文件可能包含多个工作表，并且我们需要从中提取特定工作表的数据进行整合。手动操作不仅效率低下，还容易出错。python 的 pandas 库提供了强大的数据处理能力，能够自动化这一复杂过程。本文将深入探讨如何利用 pandas 优雅地解决多 excel 文件、多工作表的数据合并问题。
环境准备
在开始之前，请确保您的 Python 环境中已安装 Pandas 和用于读取 Excel 文件的引擎库（如 openpyxl 或 xlrd）。如果尚未安装，可以通过以下命令进行安装：
pip install pandas openpyxl xlrd
理解常见错误：AttributeError: 'str' object has no attribute 'sheet_names'
在处理 Excel 文件时，一个常见的错误是 AttributeError: 'str' object has no attribute 'sheet_names'。这个错误通常发生在尝试对一个文件路径字符串（str 类型）直接调用 sheet_names 方法时。sheet_names 是 pandas.ExcelFile 对象的属性，而不是文件路径字符串的属性。
错误原因示例：
path = "your_excel_file.xlsx"
# 错误：path 是字符串，没有 sheet_names 属性
for sheet_name in path.sheet_names:
pass正确做法：
立即学习“Python免费学习笔记（深入）”；
杰易OA办公自动化系统6.0
下载
基于Intranet/Internet 的Web下的办公自动化系统，采用了当今最先进的PHP技术，是综合大量用户的需求,经过充分的用户论证的基础上开发出来的，独特的即时信息、短信、电子邮件系统、完善的工作流、数据库安全备份等功能使得信息在企业内部传递效率极大提高，信息传递过程中耗费降到最低。办公人员得以从繁杂的日常办公事务处理中解放出来，参与更多的富于思考性和创造性的工作。系统力求突出体系结构简明
在使用 sheet_names 之前，必须先将文件路径传递给 pd.ExcelFile() 构造函数，创建一个 ExcelFile 对象。
file_path = "your_excel_file.xlsx"
xls = pd.ExcelFile(file_path) # 创建 ExcelFile 对象
for sheet_name in xls.sheet_names: # 现在可以访问 sheet_names 属性
pass理解这一点是避免此类错误的关键，也是本文核心解决方案的基础。
核心解决方案：使用 Pandas 合并多文件多工作表数据
我们的目标是遍历指定目录下的所有 Excel 文件，识别并合并其中符合特定条件（例如，名称匹配）的工作表数据。最终，我们将把来自不同文件的同名工作表数据合并成一个独立的 DataFrame，并存储在一个字典中。
解决方案概述
- 指定根目录：确定存放 Excel 文件的最上层目录。
- 遍历文件系统：使用 os.walk 遍历根目录及其所有子目录，查找 Excel 文件。
- 加载 Excel 文件：对每个找到的 Excel 文件，使用 pd.ExcelFile() 加载。
- 获取工作表名称：通过 xls.sheet_names 获取当前 Excel 文件中所有工作表的名称。
- 条件筛选与解析：根据预设条件（如工作表名称）筛选工作表，并使用 xls.parse() 将其解析为 Pandas DataFrame。
- 数据整合：将来自不同文件的同名工作表数据收集起来，并使用 pd.concat() 进行纵向合并。
- 存储结果：将合并后的 DataFrame 存储在一个字典中，以工作表名称作为键。
示例代码
以下是一个完整的 Python 函数，实现了上述数据合并逻辑：
import os
import pandas as pd
def merge_excel_sheets(base_path, target_sheet_names=None):
"""
合并指定路径下多个Excel文件中符合条件的工作表。
Args:
base_path (str): 包含Excel文件的根目录路径。
target_sheet_names (list, optional): 一个列表，包含需要合并的工作表名称。
如果为None，则合并所有非排除工作表。
Returns:
dict: 键为工作表名称，值为合并后的DataFrame的字典。
每个DataFrame包含来自所有Excel文件中同名工作表的数据。
"""
# 临时存储每个工作表名称下的所有DataFrame列表
all_sheet_data_lists = {}
print(f"开始遍历目录: {base_path}")
# 遍历指定目录及其子目录
for root, _, files in os.walk(base_path):
for fname in files:
file_path = os.path.join(root, fname)
# 确保只处理Excel文件（.xlsx 或 .xls 扩展名）
if fname.endswith(('.xlsx', '.xls')):
try:
# 使用 pd.ExcelFile 加载 Excel 文件，而不是直接操作字符串路径
xls = pd.ExcelFile(file_path)
print(f"\n正在处理文件: {fname}")
# 遍历当前Excel文件中的所有工作表
for sheet_name in xls.sheet_names:
# 根据 target_sheet_names 筛选工作表
if target_sheet_names and sheet_name not in target_sheet_names:
continue # 跳过不符合条件的工作表
print(f" - 发现并处理工作表: '{sheet_name}'")
try:
# 解析指定工作表到 DataFrame
df = xls.parse(sheet_name)
# 将当前 DataFrame 添加到对应工作表名称的列表中
if sheet_name not in all_sheet_data_lists:
all_sheet_data_lists[sheet_name] = []
all_sheet_data_lists[sheet_name].append(df)
except Exception as e:
print(f" - 警告: 无法解析工作表 '{sheet_name}' 在文件 '{fname}' 中: {e}")
continue
except Exception as e:
print(f" - 错误: 无法加载Excel文件 '{fname}': {e}")
continue
else:
print(f" - 跳过非Excel文件: {fname}")
# 将每个工作表名称下的所有DataFrame列表合并成一个DataFrame
final_merged_dict = {}
for sheet_name, df_list in all_sheet_data_lists.items():
if df_list:
# 使用 pd.concat 纵向合并所有 DataFrame
final_merged_dict[sheet_name] = pd.concat(df_list, ignore_index=True)
print(f"\n成功合并工作表 '{sheet_name}' 的数据。总行数: {len(final_merged_dict[sheet_name])}")
else:
print(f"警告: 工作表 '{sheet_name}' 未找到任何数据进行合并。")
return final_merged_dict
# --- 使用示例 ---
# 请将 'your/excel/files/path' 替换为你的Excel文件所在的实际路径
# 确保该路径下包含多个Excel文件，且这些文件内有同名的工作表。
excel_directory_path = 'your/excel/files/path'
# 示例：合并名为 'Portfolios' 和 'SP Search Term Req' 的工作表
# 如果希望合并所有工作表，可以将 target_sheet_names 设置为 None
target_sheets_to_merge = ['Portfolios', 'SP Search Term Req']
# 调用函数执行合并操作
merged_dataframes = merge_excel_sheets(excel_directory_path, target_sheet_names=target_sheets_to_merge)
# 打印合并结果的概览
if merged_dataframes:
print("\n--- 合并结果概览 ---")
for sheet_name, df in merged_dataframes.items():
print(f"\n工作表 '{sheet_name}' 合并后的数据 (前5行):")
print(df.head())
print(f"总行数: {len(df)}")
else:
print("\n未找到符合条件的工作表数据进行合并。")
# 如果需要将所有合并后的DataFrame进一步整合成一个大的DataFrame
# all_combined_dfs = list(merged_dataframes.values())
# if all_combined_dfs:
# final_single_df = pd.concat(all_combined_dfs, ignore_index=True)
# print("\n所有符合条件的工作表合并成一个大DataFrame的概览 (前5行):")
# print(final_single_df.head())
# print(f"总行数: {len(final_single_df)}")代码详解
- import os 和 import pandas as pd: 导入所需的 os 模块用于文件系统操作，以及 pandas 模块用于数据处理。
-
merge_excel_sheets(base_path, target_sheet_names=None) 函数:
- base_path: Excel 文件所在的根目录路径。
- target_sheet_names: 一个可选列表，包含您希望合并的工作表名称。如果为 None，则会尝试合并所有发现的工作表（请注意，这可能会导致大量数据）。
- all_sheet_data_lists = {}: 这是一个字典，用于临时存储。它的键是工作表名称，值是一个列表，该列表包含了来自不同 Excel 文件的同名工作表的 DataFrame。
- os.walk(base_path): 这是一个生成器，它会递归地遍历 base_path 下的所有目录和文件。每次迭代返回一个三元组 (root, dirs, files)，其中 root 是当前目录的路径，dirs 是 root 下的子目录列表，files 是 root 下的文件列表。
- os.path.join(root, fname): 用于构建文件的完整路径，确保跨平台兼容性。
- fname.endswith(('.xlsx', '.xls')): 检查文件扩展名，确保只处理 Excel 文件。
- pd.ExcelFile(file_path): 关键步骤。它将 Excel 文件加载为一个 ExcelFile 对象。只有通过这个对象，我们才能访问文件的元数据（如 sheet_names）和内容。
- xls.sheet_names: 返回当前 ExcelFile 对象中所有工作表的名称列表。
- 条件判断 if target_sheet_names and sheet_name not in target_sheet_names:: 根据 target_sheet_names 列表筛选需要处理的工作表。
- xls.parse(sheet_name): 从 ExcelFile 对象中解析指定名称的工作表，并将其转换为一个 Pandas DataFrame。
- 数据收集 all_sheet_data_lists[sheet_name].append(df): 将解析出的 DataFrame 添加到 all_sheet_data_lists 字典中对应工作表名称的列表中。
- pd.concat(df_list, ignore_index=True): 在遍历完所有文件并收集到所有同名工作表的 DataFrame 列表后，使用 pd.concat 将这些 DataFrame 纵向堆叠（即行追加），ignore_index=True 会重置合并后的 DataFrame 的索引。
- 错误处理 try...except: 捕获在加载 Excel 文件或解析工作表时可能发生的错误，提高代码的健壮性。
注意事项
- 文件路径准确性：请务必将示例代码中的 'your/excel/files/path' 替换为您的 Excel 文件所在的实际路径。路径错误是导致程序无法运行的常见原因。
- 内存消耗：如果您的 Excel 文件数量庞大或单个工作表数据量巨大，pd.concat 操作可能会消耗大量内存。在这种情况下，可以考虑：
- 数据结构一致性：当合并多个 Excel 文件中的同名工作表时，最好确保这些工作表的列结构（列名、列顺序）大致相同。如果列名不一致，pd.concat 默认会保留所有列，并在缺失值处填充 NaN。
- 错误处理与日志记录：示例代码中包含了基本的 try-except 块来处理文件加载和工作表解析错误。在生产环境中，建议加入更详细的日志记录，以便追踪问题。
- 空文件或空工作表：代码会尝试处理所有 Excel 文件。如果存在空文件或空工作表，xls.parse() 可能会返回空的 DataFrame，这在 pd.concat 中通常
