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