如何在openpyxl更新后将数据框附加到现有的excel工作表(不覆盖它)?

Question

我有一个现有的excel文件，我必须每周更新新数据，将其附加到现有表的最后一行。我是以这种方式完成的，按照这篇文章中提供的解决方案，如何在不覆盖数据的情况下写入现有的excel文件(使用pandas)?

import pandas as pd
import openpyxl
from openpyxl import load_workbook
book = load_workbook(excel_path)
writer = pd.ExcelWriter(excel_path, engine = 'openpyxl',  mode = 'a')
writer.book = book
## ExcelWriter for some reason uses writer.sheets to access the sheet.
## If you leave it empty it will not know that sheet Main is already there
## and will create a new sheet.
ws = book.worksheets[1]
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, 'Preço_por_quilo', startrow = len(ws["C"]), header = False, index = False)
writer.save()
writer.close()

这段代码一直运行正常，直到今天，它返回了以下错误:

ValueError: Sheet 'Preço_por_quilo' already exists and if_sheet_exists is set to 'error'.

显然是由于openpyxl包的最新更新，它添加了"if_sheet_exists"ExcelWriter函数的参数。我怎样才能纠正这个代码，以便将我的数据附加到工作表的最后一行?

Answer 1

将if_sheet_exists=replace添加到df.to_excel的末尾应该可以工作，如下所示:

df.to_excel(writer, 'Preço_por_quilo', startrow = len(ws["C"]), header = False, index = False, if_sheet_exists='replace')

更多关于它的使用信息可以在这里找到:https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html