Add worksheet to existing Excel file with pandas

Question

# 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?

Maybe this can help: https://stackoverflow.com/a/34452659 – ian0411 Oct 10 '17 at 17:08 — ian0411, Oct 10 '17 at 17:08

score 13 · Accepted Answer · edited Oct 11 '17 at 08:26

13

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()

edited Oct 11 '17 at 08:26

jmcnamara

33,735
6
80
93

answered Oct 10 '17 at 17:48

Brad Campbell

2,750
2
20
20

I have tried it and it does not work. I got an error on line " book = openpyxl.load_workbook(file_name)" saying: KeyError: "There is no item named '[Content_Types].xml' in the archive" – zwornik Jan 17 '20 at 19:52
@zwornik that is usually a sign of a corrupt Excel file. – Brad Campbell Apr 01 '21 at 17:51

score 4 · Answer 2 · answered Oct 10 '17 at 21:27

As the OP mentioned, xlsxwriter will overwrite your existing workbook. Xlsxwriter is for writing original .xlsx files. Openpyxl, on the other hand, can modify existing .xlsx files.

@Brad Campbell answer using openpyxl is the best way to do this. Since the OP was using the xlsxwriter engine, I wanted to demonstrate that it is possible to read in your existing .xlsx file and then create a new workbook (of the same name) containing that data from the original sheets and the new sheet that you'd like to add on.

import pandas as pd
import os

xl = pd.ExcelFile('NHL_STATS_JSB_final.xlsx')
sheet_names = xl.sheet_names  # a list of existing sheet names

#the next three lines are OPs original code 
os.chdir(os.path.dirname(os.path.abspath(__file__)))

test = send_request().content
df = pd.read_csv(io.StringIO(test.decode('utf-8')))

#beginning the process of creating new workbook with the same name
writer = pd.ExcelWriter('NHL_STATS_JSB_final.xlsx', engine = 'xlsxwriter')

d = {} #creating an empty dictionary 
for i in range (0, len(sheet_names)):
    current_sheet_name = sheet_names[i]
    d[current_sheet_name] = pd.read_excel('NHL_STATS_JSB_final.xlsx', sheetname = i)
    d[current_sheet_name].to_excel(writer, '%s' % (current_sheet_name), index=False)

# adding in the new worksheet
df.to_excel(writer, 'Player statistics', index=False)
writer.save()

Add worksheet to existing Excel file with pandas

2 Answers2

Linked