如何将文件夹中200+个Excel文件合并为一个Excel文件？

你的代码存在多处逻辑和语法错误，导致出现“无效路径文件为”的报错，以下是具体问题和修正方案：

核心错误点 #

• 路径收集阶段：变量名拼写错误，将fullname误写为filename，导致收集的路径完全无效。
• 文件读取逻辑：pd.read_excel()传入了整个路径列表path_names，而非单个文件路径；同时嵌套了重复的循环，逻辑冗余且错误。
• 工作表命名：sheet_number未定义，无法生成唯一的工作表名称。
• 多工作表处理缺失：原代码仅读取每个Excel的默认第一个工作表，未遍历文件内的所有工作表。

修正后的完整代码 #

import pandas as pd
import os

excelfolder = 'path to the folder'  # 替换为你的目标文件夹路径
path_names = []

# 收集所有有效Excel文件的完整路径
for root, _, files in os.walk(excelfolder):
    for file in files:
        # 排除Excel临时文件（以~$开头）和非xlsx文件
        if file.endswith('.xlsx') and not file.startswith('~$'):
            fullname = os.path.join(root, file)
            path_names.append(fullname)

# 创建目标Excel写入器，使用with语句自动管理资源
with pd.ExcelWriter('goal.xlsx', engine='xlsxwriter') as writer:
    sheet_counter = 1
    # 遍历每个收集到的Excel文件
    for file_path in path_names:
        # 获取当前文件的所有工作表名称
        xls_file = pd.ExcelFile(file_path)
        all_sheets = xls_file.sheet_names
        
        # 遍历当前文件的每个工作表
        for sheet_name in all_sheets:
            df = pd.read_excel(file_path, sheet_name=sheet_name)
            # 生成唯一的工作表名称（保留原文件和工作表信息，避免重名）
            new_sheet_name = f"{os.path.basename(file_path)[:15]}_{sheet_name[:10]}_{sheet_counter}"
            # 限制工作表名称长度（Excel最多允许31个字符）
            new_sheet_name = new_sheet_name[:31]
            # 将数据写入目标文件，不保留索引列
            df.to_excel(writer, sheet_name=new_sheet_name, index=False)
            sheet_counter += 1

关键优化说明 #

• 新增排除Excel临时文件的逻辑，避免读取无效的缓存文件。
• 遍历每个Excel文件的所有工作表，确保所有数据都被合并。
• 生成包含原文件和工作表信息的唯一名称，避免工作表重名；同时限制长度符合Excel的31字符限制。
• 使用with语句管理ExcelWriter，无需手动调用save()，资源管理更安全。

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