How can we write new data to existing Excel spreadsheet

Question

I have a weekly procedure that generates a data frame with about 1,000 entries. In order to avoid having to read the spreadsheet again as the file gets larger, I would like to be able to add it to an existing sheet. I saw this answer here: Append existing excel sheet with new dataframe using python pandas. Sadly, it doesn't seem to be functioning well for me. I'm attempting to add the following dummy code to that already-existing file. Currently, it has two drawbacks: first, it overwrites the data rather than appending it. Second, even after the software has started, when I try to open the file, it will only let me do so in read-only mode. I can confirm that I'm also utilizing Pandas 1.4.

import pandas as pd


data = {'Name': ['Tom', 'Joseph', 'Krish', 'John'], 'Age': [20, 21, 19, 18]}
df = pd.DataFrame(data)


filename = "Testing Append Process.xlsx"
writer = pd.ExcelWriter(filename, engine="openpyxl", mode="a", if_sheet_exists="overlay")

df.to_excel(writer, index=False)
writer.save()

Answer 1

Please notes that the Testing Append Process.xlsx file has to be created before running this code.

from openpyxl import load_workbook
import pandas as pd

data = {'Name': ['Tom', 'Joseph', 'Krish', 'John'], 'Age': [20, 21, 19, 18]}
df = pd.DataFrame(data)

filename = "Testing Append Process.xlsx"
workbook = load_workbook(filename)
writer = pd.ExcelWriter(filename, engine='openpyxl')
writer.book = workbook
writer.sheets = {ws.title: ws for ws in workbook.worksheets}

df.to_excel(writer, startrow=writer.sheets['Sheet1'].max_row, index = False, header= False)

writer.close()

Returns the following if you will run the code twice.