1

I have an existing excel file which I have to update every week with new data, appending it to the last line of an existing sheet. I was accomplishing this in this manner, following the solution provided in this post How to write to an existing excel file without overwriting data (using 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()

This code was running ok until today, when it returned the following error:

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

which apparently results from the latest update of the openpyxl package, which added the "if_sheet_exists" argument to the ExcelWriter function. How can I correct this code, in order to append my data to the last line of the sheet?

francescobfc
  • 107
  • 1
  • 11

1 Answers1

1

adding if_sheet_exists=replace to the end of your df.to_excel should work, like below:

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

More information on it's use can be found here: https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html

StuCode
  • 86
  • 6
  • This doesn't work, it returns "TypeError: to_excel() got an unexpected keyword argument 'if_sheet_exists'". Adding this argument to ExcelWriter, in its turn, overwrites the existing sheet (I lose the data on the sheet). – francescobfc Sep 20 '21 at 14:20
  • Have you tried setting if_sheet_exists to None instead? also the argument should be put in the original declaration of writer instead of df.to_excel() – StuCode Sep 20 '21 at 15:45
  • This also returns "ValueError: Sheet 'Preço_por_quilo' already exists and if_sheet_exists is set to 'error'." – francescobfc Sep 21 '21 at 12:21
  • the ```if_sheet_exists``` argument does not go with ```to_excel()``` but with ```pandas.ExcelWriter```. That is why you got that error. I am also having this issue. It looks like the choices for what do if the sheet exists are 'replace', 'new', 'error' which respectively create replace the existing sheet, create a separate sheet with a slightly different name (probably appending a '_1' or something), or to raise an error. It doesn't look like there is an option to append to an existing sheet, which is somewhat frustrating considering that is the whole point of going into append mode, right? – Nesha25 Nov 17 '21 at 21:34