[FIXED] Replacing data in xlsx sheet with pandas dataframe

Issue

I have an xlsx file with multiple tabs, one of them being Town_names that already has some data in it.

I'd like to overwrite that data with a dataframe - Town_namesDF - while keeping the rest of the xlsx tabs intact.

I've tried the following:

with pd.ExcelWriter(r'path/to/file.xlsx', engine='openpyxl', mode='a') as writer:
    Town_namesDF.to_excel(writer,sheet_name='Town_names')
    writer.save()

writer.close()

But it ends up creating a new tab Town_names1 instead of overwriting the Town_names tab. Am I missing something? Thanks.

Solution

Well, I've managed to do this. This is not a clean solution and not fast at all, but I've made use of openpyxl documentation for working with pandas found here: https://openpyxl.readthedocs.io/en/latest/pandas.html

I'm effectively selecting the Town_names sheet, clearing it with ws.delete_rows() and then appending each row of my dataframe to the sheet.

wb = openpyxl.load_workbook(r'path/to/file.xlsx')
ws = wb.get_sheet_by_name('Town_names')
ws.delete_rows(0, 1000)

wb.save(r'path/to/file.xlsx')

wb = openpyxl.load_workbook(r'path/to/file.xlsx')
activeSheet = wb.get_sheet_by_name('Town_names')

for r in dataframe_to_rows(Town_namesDF, index=False, header=True):
    activeSheet.append(r)

for cell in activeSheet['A'] + activeSheet[1]:
    cell.style = 'Pandas'

wb.save(r'path/to/file.xlsx')

A bit messy and I hope there's a better solution than mine, but this worked for me.

Answered By - Robert C