I'm running some basic python code which generates a pandas DataFrame called df and then writes it to a pre-formatted Excel file using pandas ExcelWriter and openpyxl as its engine.
workbook = load_workbook('example.xlsx')
sheet = workbook['example_sheet']
writer = pd.ExcelWriter('example.xlsx', engine='openpyxl', mode='a')
writer.book = workbook
writer.sheets = {ws.title: ws for ws in writer.book.worksheets}
sheet.cell(row=8, column=5).value = some.value
df.to_excel(writer, sheet_name='example_sheet', index=False, header=False, startrow=10, startcol=5)
The strange thing is that when running on my machine it works perfectly, however on my colleague's machine it throws the mentioned error:
File "C:\Users\user\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\excel\_openpyxl.py", line 437, in write_cells f"Sheet '{sheet_name}' already exists and " ValueError: Sheet 'example_sheet' already exists and if_sheet_exists is set to 'error'.
I've tried working around it by explicitly setting
if_sheet_exists = 'replace'
however now (as expected) it replaces the entire sheet and destroys the formatting that was applied before. When running on my machine however it would not replace the sheet, even though the writer was set to do so.
I'm not entirely sure where to look for differences in the machines so if anyone could throw me an idea I would be very thankful.