如何使用Python将多个Excel工作簿合并为包含所有工作表的单个工作簿

报错原因说明 #

• 环境依赖问题：你当前使用的Python 2.7 + xlwt组件组合存在兼容性限制，xlwt仅支持生成后缀为.xls的旧版Excel文件，且你在调用writer.save()前没有成功将任何工作表写入到Writer对象中，导致内部工作表列表为空，访问激活工作表索引时抛出越界错误
• 逻辑遗漏问题：你参考的代码默认仅合并每个工作簿的第一个工作表，不会自动读取全部4个工作表，这是无法得到32个完整工作表的核心原因

正确实现步骤 #

• 第一步：升级环境到Python3.6+，安装依赖：pip install pandas openpyxl，openpyxl支持新版xlsx格式，无工作表数量限制，兼容性更好
• 第二步：遍历所有待合并的Excel工作簿，逐个读取每个工作簿下的全部工作表
• 第三步：设置工作表命名规则（如「原工作簿文件名_原工作表名」），避免同一工作簿内出现重名工作表
• 第四步：全部工作表写入完毕后再保存输出文件

可直接运行的代码示例 #

import pandas as pd
import os

# 配置参数
input_dir = "./excel_files/"  # 存放8个待合并Excel文件的文件夹路径
output_path = "./merged_result.xlsx"  # 合并后文件的输出路径

# 初始化Excel写入器
with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
    # 遍历文件夹下所有Excel文件
    for filename in os.listdir(input_dir):
        if filename.endswith((".xlsx", ".xls")) and not filename.startswith("~$"):
            file_path = os.path.join(input_dir, filename)
            # 读取当前工作簿的全部工作表
            excel_file = pd.ExcelFile(file_path)
            for sheet_name in excel_file.sheet_names:
                # 读取单个工作表数据
                df = excel_file.parse(sheet_name)
                # 生成新的工作表名，避免重名：原文件名（去掉后缀）+ 原工作表名
                new_sheet_name = f"{os.path.splitext(filename)[0]}_{sheet_name}"
                # Excel要求工作表名最大长度为31字符，超出部分截断
                new_sheet_name = new_sheet_name[:31]
                # 将工作表写入合并文件
                df.to_excel(writer, sheet_name=new_sheet_name, index=False)

之前遗漏的核心步骤 #

• 没有配置读取全部工作表的逻辑，仅读取了每个工作簿的默认第一个工作表
• 使用了兼容性差的xlwt组件，且代码逻辑存在缺陷，导致没有任何工作表成功写入就调用了保存方法
• 没有处理重名工作表的问题，Excel要求同一工作簿内工作表名不能重复，且长度不能超过31字符

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