-1

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.

Nandy
  • 3
  • 2
  • what "other solutions" have you tried so far ? the question needs sufficient code for a minimal reproducible example: https://stackoverflow.com/help/minimal-reproducible-example – D.L Nov 01 '22 at 14:43
  • have you tried `pandas.read_excel()` and if so, what have you done there and where are you stuck ? – D.L Nov 01 '22 at 14:44
  • "I have tried other solutions." Please outline what solutions you've tried and what didn't work from those. Show your work along with where you're stuck to help understand what the problem is. – Abirbhav G. Nov 02 '22 at 05:43
  • Check following question https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas – 1001001 Nov 02 '22 at 07:19
  • @D.L please take a look at the updated the question. Thank you! – Nandy Nov 02 '22 at 07:36
  • @1001001 Thank you for the comment but I have already checked that and none of the solutions worked – Nandy Nov 02 '22 at 07:37
  • Help us to help you - Please improve your question by adding example of your data(existing excel data, python dataframe) and expected output, so that we can reproduce your issue easily. Take a minute and fix your indentation. Would be great. Thanks – Devam Sanghvi Nov 02 '22 at 07:43

2 Answers2

0

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)
1001001
  • 236
  • 1
  • 6
-1
file_path = "/path/to/file.xlsx"

df_excel = pd.read_excel(file_path)
result = pd.concat([df_excel, df], ignore_index=True)
result.to_excel(file_path, index=False)
Jason Baker
  • 3,170
  • 2
  • 12
  • 15