ExcelWriter not overwriting sheet and instead creating a new one

Question

This might be going right over my head.

I am using this block:

writer = pd.ExcelWriter(
    filepath, engine="openpyxl", mode="a", if_sheet_exists="replace"
)
df.to_excel(writer, "MySheetName", index=False)
writer.save()

to overwrite an existing Excel sheet with a pandas dataframe.

This is not overwriting the sheet 'MySheetName' and is instead creating a new sheet called 'MySheetName1'.

What am I missing here?

Theres some good literature on this problem if you're at all interested: https://github.com/pandas-dev/pandas/issues/40230 Also, this might be the answer you're looking for. https://stackoverflow.com/questions/62618680/overwrite-an-excel-sheet-with-pandas-dataframe-without-affecting-other-sheets — YoungTim, Oct 15 '21 at 15:53

score 0 · Accepted Answer · answered Oct 19 '21 at 15:52

Not a real answer but I did a workaround by just deleting the original sheet and renaming the duplicate afterwards.

        from openpyxl import load_workbook
        wb = load_workbook(path)
        if 'Provider Detail' in wb.sheetnames:
            wb.remove(wb['Name'])
        wb.save(path)

        ss_sheet = wb['Name1']
        ss_sheet.title = 'Name'
        wb.save(path)

ExcelWriter not overwriting sheet and instead creating a new one

1 Answers1