0

How do I clear an Excel sheet if the dataframe i am intending to write to is empty or has rows less than the original data?

Basically,

Sheet in excel:

Header1   Header2   Header3
aaa       bbb       ccc
ddd       eee       fff

DataFrame:

Header1   Header2   Header3
111       222       333

Expected output on the excel sheet:

Header1   Header2   Header3
111       222       333

What i got instead:

Header1   Header2   Header3
111       222       333
ddd       eee       fff

The code i use:

book = load_workbook(filepath)
writer=pd.ExcelWriter(filepath, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
Df.to_excel(writer,"Outstanding", index = False, header=True)

Is there a way to clear the sheet or overwrite the entire sheet instead of only those cells that has data on it? The Dataframe is dynamic and could be empty, meaning the sheet should be empty if that is the case.The excel has other sheets on it where other parts of the code has to access.

Soh Zheng Wei
  • 69
  • 11

1 Answers1

0

You can see how to overwrite excel sheets here. From what I can tell you may just need to add writer.save(), or use the default writer = pd.ExcelWriter('g.xlsx') instead of the one you're using.

If all else fails you can delete the sheet before you write to it:

std=book.get_sheet_by_name(<sheet_name>)
book.remove_sheet(std)

Check the link, though.

JC Ryan
  • 1
  • 3