pandas.DateFrame.to_excel() method overwrite the EXCEL

romlym picture romlym · Jun 11, 2018 · Viewed 7k times · Source

Struggling for this for hours so I decided to ask for help from experts here:

I want to modify existing excel sheet without overwriting content. I have other sheets in this excel file and I don't want to impact other sheets.

I've created sample code, not sure how to add the second sheet that I want to keep though.

t=pd.date_range('2004-01-31', freq='M', periods=4)
first=pd.DataFrame({'A':[1,1,1,1],
             'B':[2,2,2,2]}, index=t)
first.index=first.index.strftime('%Y-%m-%d')
writer=pd.ExcelWriter('test.xlsx')
first.to_excel(writer, sheet_name='Here')
first.to_excel(writer, sheet_name='Keep')

#how to update the sheet'Here', cell A5:C6 with following without overwriting the rest?
#I want to keep the sheet "Keep"
update=pd.DataFrame({'A':[3,4],
                     'B':[4,5]}, index=pd.date_range('2004-04-30', 
                                                     periods=2,
                                                     freq='M'))

I've researched SO. But not sure how to write a dataframe into the sheet.

Example I've tried:

import openpyxl
xfile = openpyxl.load_workbook('test.xlsx')
sheet = xfile.get_sheet_by_name('test')
sheet['B5']='wrote!!'
xfile.save('test2.xlsx')

Answer