How can we write new data to existing Excel spreadsheet?

Question

I have a process that creates a dataframe of almost 1,000 rows that runs each week. I would like to be able to append to an existing sheet without having to re-read the spreadsheet because that will take a long time as the file grows. I saw this answer here: Append existing excel sheet with new dataframe using python pandas. Unfortunately, it doesn't seem to be working correctly for me. Here is some dummy code that I am trying to append to that existing file. It causes two issues at present - first, it does not append, but rather overwrites the data. Secondly, when I go to open the file, even after the program runs, it only allows me to open it in read-only mode. I have confirmed I am using pandas 1.4 as well.

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 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.