pythonpandaspandas.excelwriter

Python-pandas excelwriter is not working as expected

I am trying to append data on existing excel sheet using Pandas-ExcelWriter functionality. As per python official document, if_sheet_exists=overlay : Write contents to the existing sheet without removing the old contents.

Code I tried:

import pandas as pd

df = pd.DataFrame({'Data': [10, 20, 30]})
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter', mode='w')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()

df = pd.DataFrame({'Data': [100, 200, 300]})
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='openpyxl', mode='a', if_sheet_exists='overlay')

df.to_excel(writer, sheet_name='Sheet1')
writer.save()

Output I am getting: (overwriting new data instead of appending)

enter image description here

Output I am expecting:

enter image description here

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!
Solution

  • I would suggest to ignore `xlswriter.

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