Python操作Excel遇IndexError报错且文件损坏，求解决方法

问题根源 #

1. 报错原因：Excel强制要求工作簿至少保留一个可见工作表，你的代码在写入新表时，因openpyxl与pandas的交互逻辑异常，或文件句柄占用导致的写入冲突，意外将所有工作表标记为隐藏，触发该错误。
2. 文件损坏原因：无用的wb1 = pd.read_excel(filename)未释放文件句柄，导致后续写入操作无法正常完成文件流的完整写入，直接破坏了Excel文件的格式结构。

修复后的代码 #

import pandas as pd
from openpyxl import load_workbook

filename = r"C:\Users\Desktop\Docs\Excel To Automatize\file.xlsx"
sheets = ["Efdfg", "khefjkh", "jdshfjhd"]

# 读取指定工作表并捕获异常
dfs = []
for sheet in sheets:
    try:
        df_sheet = pd.read_excel(filename, sheet_name=sheet)
        dfs.append(df_sheet)
    except Exception as e:
        print(f"读取工作表 {sheet} 失败: {str(e)}")

if dfs:
    df_aggregate = pd.concat(dfs, ignore_index=True)
    
    # 加载原工作簿，确保至少一个工作表可见
    wb = load_workbook(filename)
    if all(sheet.sheet_state == 'hidden' for sheet in wb.worksheets):
        wb.worksheets[0].sheet_state = 'visible'
    
    # 写入合并后的工作表，替换已存在的同名表
    with pd.ExcelWriter(filename, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
        df_aggregate.to_excel(writer, sheet_name="Aggregate Table", index=False)
else:
    print("未读取到有效工作表，无法生成合并表")

核心修复点 #

• 移除未使用的wb1变量，释放文件资源，避免写入冲突。
• 增加工作表读取的异常处理，防止单个表读取失败导致整个流程中断。
• 显式检查并设置至少一个可见工作表，从根源解决IndexError问题。
• 使用if_sheet_exists='replace'参数，避免重复创建同名工作表的错误。
• 提前加载原工作簿，确保原有工作表的状态被正确保留。

注意事项 #

• 确认sheets列表中的表名与Excel文件中的完全一致（区分大小写）。
• 操作前务必备份原文件，防止意外损坏。
• 更新依赖库到最新版本：pip install --upgrade pandas openpyxl

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