[FIXED] Add worksheet to existing Excel file with pandas

Issue

# Set the working folder to the same folder as the script
os.chdir(os.path.dirname(os.path.abspath(__file__)))

test = send_request().content
df = pd.read_csv(io.StringIO(test.decode('utf-8')))
writer = pd.ExcelWriter('NHL_STATS_JSB_final.xlsx', \
                        engine = 'xlsxwriter')
df.to_excel(writer, 'Player statistics', index=False)
writer.save()

I don't understand why, but I am trying to add the worksheet Player statistics to my current NHL_STATS_JSB_final.xlsx file, but it is not working. Instead of adding the worksheet to the file, my code use the current file and erase all previous worksheet to add the new one.

How could I add Player statistics to my current Excel file with erasing all other worksheets?

Solution

Here is a snippet of code from one of my projects. This should do exactly what you want. You need to use openpyxl rather than xlsxwriter to allow you to update an existing file.

writer = pd.ExcelWriter(file_name, engine='openpyxl')

if os.path.exists(file_name):
    book = openpyxl.load_workbook(file_name)
    writer.book = book

df.to_excel(writer, sheet_name=key)
writer.save()
writer.close()

Answered By - Brad Campbell