BUG: pd.ExcelWriter(.., mode="a", if_sheet_exists="overlay") gives unexpected result

[abokey1]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

df1 = pd.DataFrame({"col1": [1, 2, 3]})
df1.to_excel("wbook.xlsx", sheet_name = "Sheet1")

df2 = pd.DataFrame({"col2": [4, 5, 6]})

with pd.ExcelWriter("wbook.xlsx",
                    mode = "a",
                    engine = "openpyxl",
                    if_sheet_exists = "overlay") as writer:  
    df2.to_excel(writer, sheet_name = "Sheet1")

Issue Description

pd.ExcelWriter with mode = "a" and if_sheet_exists = "overlay" shows unexpected behaviour (at least for me).

From the documentation, we read :

if_sheet_exists{‘error’, ‘new’, ‘replace’, ‘overlay’}, default ‘error’

• overlay: Write contents to the existing sheet without removing the old contents.

But when I run the code above, I got this :

I feel like this is not a bug tough but counterintuitive to be honest.

Expected Behavior

I would expected the second dataframe to be written right after the first one (like below) :

I know that I can pass startrow=len(df1)+1 to df2.to_excel() to get this kind of output but what if I had only one dataframe that needs to be appended to an existing spreadsheet (starting from the first non empty row) by using only pd.ExcelWriter ?

Installed Versions

INSTALLED VERSIONS

python : 3.11.0.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.22000
machine : AMD64
pandas : 1.5.3

[rhshadrach]

Thanks for the report. This is the intended behavior. Perhaps we could change the description to something like:

Write contents to the existing sheet without first removing, but possibly over top of, the existing contents.

PRs to clarify the description are welcome!

but what if I had only one dataframe that needs to be appended to an existing spreadsheet (starting from the first non empty row) by using only pd.ExcelWriter ?

You can read the sheet with pd.read_excel with header=None and use the length of the result to determine what line the contents stop on.

[abokey1]

@rhshadrach, thank you so much for the answer!

I created a PR, btw my first every PR, so go easy on me ;)
The commit to consider is 81de560.