How to overwrite data on an existing excel sheet while preserving all other sheets?

Question

I have a pandas dataframe df which I want to overwrite to a sheet Data of an excel file while preserving all the other sheets since other sheets have formulas linked to sheet Data

I used the following code but it does not overwrite an existing sheet, it just creates a new sheet with the name Data 1

with pd.ExcelWriter(filename, engine="openpyxl", mode="a") as writer:
     df.to_excel(writer, sheet_name="Data")

Is there a way to overwrite on an existing sheet?

Answer 1

You can do it using openpyxl:

import pandas as pd
from openpyxl import load_workbook

book = load_workbook(filename)
writer = pd.ExcelWriter(filename, engine='openpyxl') 
writer.book = book

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, "Data")

writer.save()

You need to initialize writer.sheets in order to let ExcelWriter know about the sheets. Otherwise, it will create a new sheet with the name that you pass.