How to add a dataframe to an existing Excel sheet with Pandas, on a .xlsm file

Question

I want to import the values from a Pandas dataframe into an existing Excel sheet. I want to insert the data inside the sheet without deleting what is already there in the other cells (like formulas using those datas etc).

I tried using data.to_excel like:

writer = pd.ExcelWriter(r'path\TestBook.xlsm')    
data.to_excel(writer, 'Sheet1', startrow=1, startcol=11, index = False)    
writer.save()

The problem is that this way i overwrite the entire sheet. Is there a way to only add the dataframe? It would be perfect if I could also keep the format of the destination cells. Thanks

user_10331864 · Answer

I found a good solution for it. Xlwings natuarally supports pandas dataframe: https://docs.xlwings.org/en/stable/datastructures.html#pandas-dataframes

user_10889701 · Answer

The to_excel function provides a mode parameter to insert (w) of append (a) a data frame into an excel sheet, see below example:

with pd.ExcelWriter(p_file_name, mode='a') as writer:
    df.to_excel(writer, sheet_name='Data', startrow=2, startcol=2)

How to add a dataframe to an existing Excel sheet with Pandas, on a .xlsm file

2 Answers