使用Pandas追加Excel文件时遇AttributeError及BadZipFile问题求助

核心问题 #

• 执行writer.book = book时触发AttributeError: can't set attribute 'book'
• 操作过程中出现BadZipFile错误

为规避BadZipFile错误，先通过dataOutput=pd.DataFrame(dictDataOutput,index=[0])生成并写入Excel文件，但AttributeError问题仍未解决。不考虑降级openpyxl或改用CSV的方案，希望找到更优解决办法。

尝试过的代码方案 #

初始实现代码 #

dataOutput=pd.DataFrame(dictDataOutput,index=[0])
dataOutput.to_excel('output.xlsx') # 也曾尝试写入output.xlsm
book = load_workbook('output.xlsx') # 也曾加载output.xlsm
writer = pd.ExcelWriter('output.xlsx') # 也曾尝试output.xlsm，试过添加engine='openpyxl',mode='a',if_sheet_exists='overlay'参数
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}

for sheetname in writer.sheets:
    dataOutput.to_excel(writer,sheet_name=sheetname, startrow=writer.sheets[sheetname].max_row, index = False,header= False)

writer.save()

简化with语句写法 #

改用上下文管理器写法后，出现API废弃警告：

with pd.ExcelWriter('output.xlsx', mode='a',if_sheet_exists='overlay') as writer:
    dataOutput.to_excel(writer, sheet_name='Sheet1')
    writer.save()

警告提示：FutureWarning: save is not part of the public API, usage can give in unexpected results and will be removed in a future version

参考建议后的修改版本 #

调整后仍存在writer.book = book的AttributeError问题：

with pd.ExcelWriter('outputData.xlsm', engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
    book = load_workbook('outputData.xlsm', keep_vba=True)

    writer.book = book
    writer.sheets = {ws.title: ws for ws in book.worksheets}
    current_sheet = book['Sheet1']
    Column_A = current_sheet['A']
    maxrow = max(c.row for c in Column_A if c.value is not None)

    for sheetname in writer.sheets:
        AllDataOutput.to_excel(writer, sheet_name=sheetname, startrow=maxrow, index=False, header=False)

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