How to append dataframe to an existing excel file with some rows of data in it?

I have an excel sheet which has 150 rows of data. Now, I want to append a dataframe to that sheet without deleting or replacing the data using python.

I have tried code like this, it is deleting the existing content from the excel and writing the dataframe into it.

import pandas as pd
import openpyxl

workbook = openpyxl.load_workbook("test.xlsx")
writer = pd.ExcelWriter('test.xlsx', engine='openpyxl')
writer.book = workbook
writer.sheets = dict((ws.title, ws) for ws in workbook.worksheets)
data_df.to_excel(writer, 'Existing_sheetname')
writer.save()
writer.close()

And other solutions provided here but with no outcome.

Any suggestion is appreciated.

Answer

Firstly I created excel file with some data in range C5:I18.

# Required imports
import pandas as pd
from pathlib import Path
import numpy as np

# Path to excel file
xl_path = Path("C://Path//to//your//Excel_file.xlsx")

# sheet name
sht_name = 'test'

# columns names
cols = list("ABCDEFG")

# random values
values = np.random.randint(1000, size=(20,7))
# create dataframe
df = pd.DataFrame(data=values, columns=cols)

# since I am going to create writer object with 'openpyxl' engine all methods from 
# openpyxl could be used 
with pd.ExcelWriter(xl_path, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
    # create new variable with sheet into which we are going to save the data
    ws = writer.sheets[sht_name]
    # check max row for columns of interest / in my case "C"
    max_row_for_c = max((c.row for c in ws['C'] if c.value is not None))
    # save data to excel starting in col C so startcol=2 since pandas counts from 0 
    # from this same reason there is no need to add 1 to max_row_from_c
    df.to_excel(writer, sheet_name=sht_name, startcol=2, startrow= max_row_for_c, header=None, index=False)