您可以使用with：

with pd.ExcelWriter('test.xlsx', engine='openpyxl', mode='a') as writer:
    d1.to_excel(writer,sheet_name='d1')
    d2.to_excel(writer,sheet_name='d2')
    writer.save()

writer.close()

更新

这应该可以工作，只是需要注意一个空白文件需要事先创建。如果你想的话，你可以用python创建一个空白文件。我创建了一个简单的循环，在某些方面模仿了你要完成的事情的本质：

import pandas as pd
from openpyxl import load_workbook

d1 = pd.DataFrame({"A":['Bob','Joe', 'Mark'], 
               "B":['5', '10', '20']})
d2 = pd.DataFrame({"A":['Jeffrey','Ann', 'Sue'], 
                "B":['1', '2', '3']})

dfs = [d1,d2]

for i in range(len(dfs)):
    sheet = 'd'+str(i+1)
    data = dfs[i]
    writer = pd.ExcelWriter('atest.xlsx',engine='openpyxl', mode='a')
    writer.book = load_workbook('atest.xlsx') # here is the difference
    data.to_excel(writer,sheet_name=sheet)
    writer.save()
    writer.close()

或者这里是修改的第一示例：

d1 = pd.DataFrame({"A":['Bob','Joe', 'Mark'], 
               "B":['5', '10', '20']})
d2 = pd.DataFrame({"A":['Jeffrey','Ann', 'Sue'], 
                "B":['1', '2', '3']})

writer = pd.ExcelWriter('atest.xlsx', engine='openpyxl', mode='w')
d1.to_excel(writer,sheet_name='d1')
writer.save()
writer.close()

writer = pd.ExcelWriter('atest.xlsx', engine='openpyxl', mode='a')
writer.book = load_workbook('atest.xlsx')
d2.to_excel(writer,sheet_name='d2')
writer.save()
writer.close()

我在GitHub上提交了一个帖子，并收到了来自贡献者的回复（见下面突出显示的部分）。原来这个功能要到0.24才能发布，所以它在0.23.1中不可用。仅供参考-我下载了RC并成功地试用了mode='a'选项。但是，可能有一个bug与不存在的工作簿有关;我收到FileNotFoundError: [Errno 2] No such file or directory: 'test.xlsx'。
“此功能是作为0.24的一部分发布的，我们在过去几天里刚刚发布了一个候选版本。您可以在RC或Master上试用，如果两者都不起作用，请按照贡献指南打开一个问题，但这在比它更旧的版本上不起作用”

进口Pandas当PD

writer = pd.ExcelWriter(wk_path + save_file)
# ....
# build sc_files DataFrame and save. sc_files includes
# a column called OS.

sc_file.to_excel(writer, sheet_name='test')

# build data frame of OS counts out of sc_file
counts_os = sc_file.OS.value_counts() 

# To append to 'test' sheet, use startcol=x1, startrow=y
# To append counts_OS to the end of the current 'test' sheet
y = len(sc_file)
y += 1
counts_os.to_excel(writer, sheet_name='test', 
    startcol=1, startrow=y)

# write counts_os to sheet test2 
counts_os.to_excel(writer, sheet_name='test2')
writer.save()
writer.close()

这对我很有效，如果文件不存在，它会创建一个文件，如果文件已经存在，它会追加到文件的末尾
你可能需要安装openpyxl

import pandas as pd
from openpyxl import load_workbook

def append_xlsx(df, file = 'results.xlsx'):
    ext = '.xlsx'
    if ext not in file:
        file+=ext
    
    if os.path.exists(file):
        mode="a"
        if_sheet_exists="overlay"
        header = False

        wb = load_workbook(file)
        sheet = wb.worksheets[0]
        startrow = sheet.max_row
    else:
        mode='w'
        if_sheet_exists = None
        header = True
        startrow = 0

    with pd.ExcelWriter(
        file,
        mode=mode,
        engine="openpyxl",
        if_sheet_exists=if_sheet_exists,
    ) as writer:

        df.to_excel(
            writer, 
            sheet_name="Sheet1",
            startrow=startrow,
            header=header,
            index=False,
            encoding='utf8'
        )