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?

Answer 1

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

Answer 2

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

Answer 3

# I needed to append tabs to a workbook only if data existed
# OP wants to append sheets to a workbook.  
# using mode 'a' appends if the file exists
# mode 'w' creates a new file if failed to append.
# ended up with this:

def create_POC_file_tab(df, sheetname):
    # within function before the 'if' code below, prep data. 
    # Like extracting df_SA values from df, 
    # building POC_file name using df_SA+date, etc.
    # 
    #  might not have data after filtering so check length.
    if len(df_SA) > 0:  # extracted dataframe contains data 
        # Have data so finalize workbook path/name
        POC_file = PATH + POC_file # build file path

        try:
            # mode='a' tries to append a new tab if the 
            # workbook exists already
            writer_SA = pd.ExcelWriter(POC_file + ' ' +
                process_date + '.xlsx', engine='openpyxl', mode='a')
            print(POC, 'File exists. Appending to POC',POC,sheetname)
        except:
            # mode='w' creates a new workbook if one does not exist
            writer_SA = pd.ExcelWriter(POC_file + ' '  + 
                process_date + '.xlsx', engine='openpyxl', mode='w')
            print(POC, ' !!! Creating !!! ', sheetname)

        try:
            df_SA.to_excel(writer_SA, sheet_name=sheetname, 
                index=False)  
            writer_SA.save()
        except:
            print ("error on writing sheetname: ", sheetname,
                "for: ",POC)

    return
# when I exit the file seems to be closed properly.

# In brief, to append a new tab to a workbook use:
writer=pd.ExcelWriter('filename.xlsx',engine='openpyxl', mode='a')
df.to_excel(writer, sheet_name='my_sheet_name', index=False)  
writer_SA.save()