如果遇到以下任何一个错误,您需要解决: writer.book=book AttributeError: can't set attribute 'book'
或者 BadZipFile
为了避免出现badzipfile错误,我先加入写入Excel文件的代码行:dataOutput=pd.DataFrame(dictDataOutput,index=[0])
但是,即使这样做,我仍然无法摆脱 writer.book = book AttributeError: can't set attribute 'book'
的问题。正如Stack Overflow中的一篇答案建议的那样,我需要将openpyxl还原到先前的版本,或者使用CSV文件而不是Excel文件。但我认为这不是解决办法。应该有其他的解决办法,但我找不到。
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')OR'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()
我在此处查找答案,并在此处中详细解决了AttributeError的问题。
——我尝试了另一种方法。
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.save()
根据@Andrew的评论,我修改了代码如下;
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)
ExcelWriter
文档中,你可以通过engine_kwargs
参数向引擎传递参数。当使用openpyxl
作为引擎时,ExcelWriter
使用openpyxl.reader.excel.load_workbook
方法。从这些文档
中得知,keep_vba
是一个参数,所以在你的pd.ExcelWriter()
参数中添加engine_kwargs = {keep_vba: True}
(同时确保你有engine = 'openpyxl'
),它应该就可以工作了。 - Andrew