Pandas ExcelWriter覆盖Excel工作簿而非追加至指定工作表问题

先帮你拆解下核心问题：你遇到的mode='a'+if_sheet_exists='overlay'失效、工作表被覆盖，以及后续的KeyError和IndexError，本质是Openpyxl对原Excel文件的兼容性问题，加上代码参数的小失误共同导致的。

先解释那些警告的影响 #

你看到的这两条警告：

UserWarning: File contains an invalid specification for Closed POA&M Items. This will be removed
UserWarning: File contains an invalid specification for Open POA&M Items. This will be removed

这是关键！Openpyxl在加载你的Excel文件时，识别到目标工作表（以及另一个工作表）存在它不支持的格式/结构（比如复杂条件格式、宏残留、损坏的数据透视表缓存，甚至是某些自定义单元格样式），于是自动移除了这些有问题的工作表。这就是为什么你后续操作会出现KeyError: 'Open POA&M Items'——因为加载后的工作簿里已经没有这个工作表了，mode='a'自然会变成创建新工作表，甚至误删其他正常工作表。

分步解决方案 #

1. 先修复Excel文件的兼容性问题 #

这是前提，否则任何代码调整都没用：

• 打开原Excel文件September.xlsx，另存为普通的.xlsx格式（不要带宏的.xlsm），保存时选择「文件类型」为Excel 工作簿(*.xlsx)。
• 检查目标工作表Open POA&M Items：
  • 移除所有可能的复杂元素：比如多余的条件格式、数据透视表、宏代码（如果有的话）。
  • 确保工作表是可见状态（右键工作表标签，不要选择「隐藏」），避免后续出现IndexError: At least one sheet must be visible。
• 可以尝试把原工作表的表头和格式复制到一个全新的空白.xlsx文件中，只保留必要的结构，再用这个新文件测试。

2. 修正代码逻辑与参数 #

你的代码里有两个小问题：

• header=4是指定用DataFrame的第4行作为表头，但你要保留现有Excel的表头，应该设为header=False。
• 直接用startrow=5是正确的（对应Excel第6行），但要先确保工作表确实存在。

修正后的完整代码：

import pandas as pd
from openpyxl import load_workbook

curr_poam = "Reports/September.xlsx"
curr_target_sheet = "Open POA&M Items"

def main():
    # 先提前验证目标工作表是否存在
    wb = load_workbook(curr_poam)
    if curr_target_sheet not in wb.sheetnames:
        raise ValueError(f"错误：目标工作表 {curr_target_sheet} 不存在于文件中")
    wb.close()
    
    # 执行写入
    writeToExcel(closed_items, curr_poam, curr_target_sheet)

def writeToExcel(dataframe, path_to_excel, sheet_name):
    # 使用overlay模式追加数据
    with pd.ExcelWriter(
        path_to_excel,
        engine="openpyxl",
        mode='a',
        if_sheet_exists='overlay'
    ) as writer:
        # 获取现有工作表，确认起始行
        worksheet = writer.sheets[sheet_name]
        start_row = 5  # Excel第6行，对应索引5（从0开始计数）
        
        # 写入数据：不生成新表头，从指定行开始
        dataframe.to_excel(
            writer,
            sheet_name=sheet_name,
            startrow=start_row,
            index=False,
            header=False
        )

3. 验证步骤 #

1. 运行main()前，先单独执行这段代码检查工作表状态：

   from openpyxl import load_workbook
   wb = load_workbook(curr_poam)
   print("当前工作簿的工作表列表：", wb.sheetnames)
   print("目标工作表是否可见：", wb[curr_target_sheet].sheet_state == 'visible')
   wb.close()
   

   如果输出里有目标工作表，且状态为visible，再执行写入代码。

为什么之前的尝试无效？ #

• 更新Pandas/Openpyxl：只是修复版本bug，但解决不了原Excel文件的格式兼容性问题。
• 用writer.sheets[sheet_name].max_row：因为Openpyxl已经移除了目标工作表，所以触发KeyError。
• IndexError：是因为所有工作表都被Openpyxl标记为隐藏/删除，保存时不符合Excel的要求（至少要有一个可见工作表）。

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