Using Excelwriter to write a DataFrame to a new sheet deletes all other sheets even when using append mode

Question

I have an .xlsx file that I want to append to. I am using:

excel_writer = pd.ExcelWriter('asdf.xlsx', engine='openpyxl', mode='a')
df.to_excel(excel_writer, sheet_name='dummy', index=False)
excel_writer.close()

which does successfully create a the sheet 'dummy', but deletes all other existing sheets in 'asdf.xlsx'. I am using append mode so I'm not too sure where else to check. Running Pandas version 0.20.1

I tried the code in the docs for 0.20.1 but it just overwrites everything as well.

If passing an existing ExcelWriter object, then the sheet will be added to the existing workbook. This can be used to save different DataFrames to one workbook:

writer = pd.ExcelWriter('output.xlsx')
df1.to_excel(writer,'Sheet1')
df2.to_excel(writer,'Sheet2')
writer.save()

Answer 1

The problem is, that you misunderstand the append functionality. It means, that you append to the current excelwriter, but you create a new empty excel file, if you set no mode. You have to set the mode of the ExcelWriter to a (append). This will append new sheets every time you run it:

import pandas as pd
from openpyxl import load_workbook

data = [
    {"a": 1, "b": 2}
]

df = pd.DataFrame(data)
df2 = pd.DataFrame(data)

book = load_workbook("output.xlsx")
writer = pd.ExcelWriter('output.xlsx', mode="a")

if "Sheet11" not in book.sheetnames:
    df.to_excel(writer, 'Sheet11')

if "Sheet21" not in book.sheetnames:
    df2.to_excel(writer, 'Sheet21')
    writer.save()

print(book.sheetnames)

EDIT:
Added load_workbook
A part of this answer is based on this answer