1

I am trying to take a workbook, loop through specific worksheets retrieve a dataframe, manipulate it and essentially paste the dataframe back in the same place without changing any of the other data / sheets in the document, this is what I am trying:

path= '<folder location>.xlsx'
wb = pd.ExcelFile(path)

for sht in ['sheet1','sheet2','sheet3']:
    df= pd.read_excel(wb,sheet_name = sht, skiprows = 607,nrows = 11, usecols = range(2,15))
    # here I manipulate the df, to then save it down in the same place
    df.to_excel(wb,sheet_name = sht, startcol=3, startrow=607)

# Save down file
wb.save(path))
wb.close()

My solution so far will just save the first sheet down with ONLY the data that I manipulated, I lose all other sheets and data that was on the sheet that I want to stay, so I end up with just sheet1 with only the data I manipulated.

Would really appreciate any help, thank you

Shiva Govindaswamy
  • 609
  • 5
  • 10
11l
  • 73
  • 1
  • 9

1 Answers1

1

Try using an ExcelWriter instead of an ExcelFile:

path= 'folder location.xlsx'

with pd.ExcelWriter(path) as writer:   
    for sht in ['sheet1','sheet2','sheet3']:
        df= pd.read_excel(wb,sheet_name = sht, skiprows = 607,nrows = 11, usecols = range(2,15))
        ####here I manipulate the df, to then save it down in the same place###
        df.to_excel(writer,sheet_name = sht, startcol=3, startrow=607)

Although I am not sure how it will behave when the file already exists and you overwrite some of them. It might be easier to read everything in first, manipulate the required sheets and save to a new file.

ewz93
  • 2,444
  • 1
  • 4
  • 12
  • Thanks for your help! I tried this but it seems to produce a unreadable file, I think the issue might be that the stage 'to_excel' tries to overwrite a new document with just the amended dataframe (not what I want) – 11l Jul 10 '22 at 11:43
  • 1
    This is always tricky when handling Excel files, you could try something like combining an approach for appending sheets to an existing file without changing it (https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas) with then just deleting the old sheet (and renaming the new one to the proper name after). – ewz93 Jul 10 '22 at 12:14