Pandas: if_sheet_exists "replace" not working

Question

Here is my code:

def save_excel_sheet(df, filepath, sheetname, index=False):
    # Create file if it does not exist
    if not os.path.exists(filepath):
        df.to_excel(filepath, sheet_name=sheetname, index=index)

    # Otherwise, add a sheet. Overwrite if there exists one with the same name.
    else:
        with pd.ExcelWriter(filepath, engine='openpyxl', if_sheet_exists='replace', mode='a') as writer:
            df.to_excel(writer, sheet_name=sheetname, index=index)

The expected behavior would be that when call the function on an existing file, with an existing sheet of name "sheetname", it should replace/overwrite the sheet. But it doesn't work. It simply creates sheetname, sheetname1, sheetname2, ... I tried setting the mode to "w" but it overwrites any other existing sheet, and I want to keep those.

What should I do? Thanks

Answer 1

In my case, pd.ExcelWriter(path_mapas + xlsx_mapa, mode = 'a', engine = 'openpyxl', if_sheet_exists = 'overlay') doesnt seem to be working.