使用Pandas 1.3.5替换Excel工作表触发ValueError报错求助

问题根源 #

你触发的ValueError: <Worksheet "Positions_auto"> is not in list错误，核心原因是手动加载工作簿并赋值给writer.book的操作，破坏了Pandas ExcelWriter的内部状态同步：

• 当使用mode='a'和if_sheet_exists='replace'初始化pd.ExcelWriter时，ExcelWriter会自动负责加载目标工作簿，并维护内部的工作表列表。
• 额外调用load_workbook获取工作簿后手动覆盖writer.book，会导致ExcelWriter内部记录的工作表集合与实际工作簿的工作表集合不一致。此时执行df.to_excel时，它找不到要替换的工作表，从而抛出错误。

修复后的代码 #

删除手动加载工作簿和赋值writer.book的代码，让ExcelWriter自动处理工作簿加载：

writer = pd.ExcelWriter(f"{file_path}\\{file_nm}.xlsx", mode="a", engine='openpyxl', if_sheet_exists='replace')
df.to_excel(writer, sheet_name='Positions_auto', index=False)
writer.close()  # 务必关闭writer确保写入生效

补充说明 #

• Pandas 1.3.5版本中，if_sheet_exists='replace'的逻辑依赖ExcelWriter自身管理工作簿状态，手动干预工作簿实例会导致逻辑失效。
• 路径拼接建议用os.path.join或f-string，避免手动拼接\\出现路径错误：

  import os
  full_path = os.path.join(file_path, f"{file_nm}.xlsx")
  writer = pd.ExcelWriter(full_path, mode="a", engine='openpyxl', if_sheet_exists='replace')
  

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