Pandas append sheet to workbook if sheet doesn't exist, else overwrite sheet

Question

I am updating an existing Excel workbook using pandas. When using an ExcelWriter object, can I overwrite a sheet if it exists and otherwise create a new sheet? The code I have appends new sheets, but when I try to overwrite an existing sheet it appends a new sheet with a slightly varied name (ex: If sheet 'data1' exists, running the code appends a new sheet named 'data1 1').

import pandas as pd
import openpyxl
path = 'test-out.xlsx'
book = openpyxl.load_workbook(path)
df1 = pd.DataFrame({'a': range(10), 'b': range(10)})
writer = pd.ExcelWriter(path, mode='a')
writer.book = book
df1.to_excel(writer, sheet_name='data1')
writer.save()

Answer 1

Pass the sheets to the writer with writer.sheets = dict((ws.title, ws) for ws in book.worksheets):

import pandas as pd
import openpyxl
path = 'test-out.xlsx'
book = openpyxl.load_workbook(path)
df1 = pd.DataFrame({'a': range(10), 'b': range(10)})
writer = pd.ExcelWriter(path, mode='a')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df1.to_excel(writer, sheet_name='data1')
writer.save()

Edit:

Seems like you don't even need mode='w', writer = pd.ExcelWriter(path, mode='a') is still working...