下面是我的一个项目的代码片段。这应该完全符合你的要求。您需要使用openpyxl而不是xlsxwriter来允许您更新现有文件。

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

[更新-九月。[2023]现在实现这一目标的一个更新、更好的方法是：

with pd.ExcelWriter(
        "path_to_file.xlsx",
        mode="a",
        engine="openpyxl",
        if_sheet_exists="replace",
    ) as writer:
    df.to_excel(writer, sheet_name="Sheet1")

以下是ExcelWriter文档的链接：https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html

正如OP所提到的，xlsxwriter将覆盖您现有的工作簿。Xlsxwriter用于编写原始的.xlsx文件。另一方面，OpenPyxl可以修改现有的.xlsx文件。
@布拉德坎贝尔回答使用openpyxl是最好的方法。由于OP使用的是xlsxwriter引擎，我想演示一下，可以读取现有的.xlsx文件，然后创建一个新的工作簿（同名），其中包含原始工作表和您想要添加的新工作表中的数据。

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

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