Overwrite an excel sheet with pandas dataframe without affecting other sheets

Question

I want to overwrite an existing sheet in an excel file with Pandas dataframe but don't want any changes in other sheets of the same file. How this can be achieved. I tried below code but instead of overwriting, it is appending the data in 'Sheet2'.

import pandas as pd
from openpyxl import load_workbook

book = load_workbook('sample.xlsx')
writer = pd.ExcelWriter('sample.xlsx', engine = 'openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, 'sheet2', index = False)
writer.save()

Answer 1

I didn't find any other option other than this, this would be a quick solution for you.

I believe still there's no direct way to do this, correct me if I'm wrong. That's the reason we need to play with these logical ways.

import pandas as pd

def write_excel(filename,sheetname,dataframe):
    with pd.ExcelWriter(filename, engine='openpyxl', mode='a') as writer: 
        workBook = writer.book
        try:
            workBook.remove(workBook[sheetname])
        except:
            print("Worksheet does not exist")
        finally:
            dataframe.to_excel(writer, sheet_name=sheetname,index=False)
            writer.save()

df = pd.DataFrame({'Col1':[1,2,3,4,5,6], 'col2':['foo','bar','foobar','barfoo','foofoo','barbar']})

write_excel('PRODUCT.xlsx','PRODUCTS',df)

Let me know if you found this helpful, or ignore it if you need any other better solution.