Pandas - Saving Excel File back into/overwriting an existing sheet

2
votes

with my code below I am trying to open existing excel file (wos.xlsx) and then overwrite the "Detail - All" sheet with another dataframe I have saved called 'results'. But what saves in my path is the excel file "wos.xlsx" , just with 1 new tab "Detail-All", and none of the other tabs (there were 7). My goal was just to replace the "Detail-All" tab with my 'results' dataframe. Not sure where I went wrong. 

ws_dict = pd.read_excel('wos.xlsx',
                        sheetname=None)

ws_dict['Detail - All'] = results
print(ws_dict)

with pd.ExcelWriter('wos.xlsx',
                    engine='xlsxwriter') as writer:

    for ws_name, df_sheet in ws_dict.items():
        results.to_excel(writer, sheet_name='Detail - All')
1
pythonexcelpandas

1 Answers

2
votes

This is because you are overwriting the excel file 'wos.xlsx' with xlsxwriter, deleting any other sheet that was there before. If you want to edit instead of overwriting, you should use xlwings instead.

Example (not tested):

import xlwings as x
wb = x.Book('wos.xlsx')
wb.sheets['Detail - All'].range('A1').options(index=False, header=True).value = results