I am trying to append data on existing excel sheet using Pandas-ExcelWriter functionality. As per

Output I am expecting:

Version details:

Python : 3.9.2
Pandas : pandas==1.4.3
openpyxl : openpyxl==3.0.10
xlsx : XlsxWriter==3.0.3

Trials:

1. Tried with engine='xlsxwriter' for append mode. but got ValueError: Append mode is not supported with xlsxwriter!

CodePudding user response：

I would suggest to ignore xlswriter as it does not support .xlsx extention.

my approach would be as below:

import pandas as pd
import openpyxl

df = pd.DataFrame({'Data': [10, 20, 30]})
df.to_excel('pandas_simple.xlsx', sheet_name='Sheet1', index=False) #saving initial dataframe to file


df1 = pd.DataFrame({'Data': [100, 200, 300]}) # new data

wb = openpyxl.load_workbook('pandas_simple.xlsx') # open old file
ws = wb["Sheet1"] # assign sheet to work with or as below
# ws = wb.active
for index, row in df1.iterrows():
    ws.append(row.values.tolist())

wb.save("pandas_simple.xlsx")