Pandas version checks

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

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

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

Reproducible Example

import pandas as pd

def main():
    header1 = ['name', 'gender', 'age']
    df1 = pd.DataFrame([['sally', 'f', 69], ['kevin', 'm', 42]],columns=header1)
    header2 = ['sales', 'worked hours']
    df2 = pd.DataFrame([[4, 40]],columns=header2)
    # the excel file must exist for the error to come!!
    file2 = 'test.xlsx'

    try:
        with pd.ExcelWriter(file2, engine='openpyxl', mode='a', if_sheet_exists="replace") as writer:
            df1.to_excel(writer, sheet_name='test', index=False, startrow=0)
            df2.to_excel(writer, sheet_name='test', index=False, startrow=4)
    except FileNotFoundError:
        with pd.ExcelWriter(file2, engine='openpyxl') as writer:
            df1.to_excel(writer, sheet_name='test', index=False, startrow=0)
            df2.to_excel(writer, sheet_name='test', index=False, startrow=4)


if __name__ == '__main__':
    main()

Issue Description

Hello,

i am writing a programm that writes some results into an excel. I have two df that i need to print into one excel sheet. This shouldn't be a problem because there are a lot of tutorials showing how this is done. But in my case i want to rewrite the whole sheet if it already exists. The problem is that the first df that i write into the excel with to_excel will not be writen. I also changed the order of the df and always the first one is not written into the excel sheet.

If the file is not existing i am creating one and in the except part everythin works fine. Both df are printed in the excel. As soon as the try part is executed with the append mode the first excel here '´df1' is not printed.

Expected Behavior

I except that both df are printed into the same excel sheet if the mode is 'a' and the content of the sheet is replaced.

Installed Versions

INSTALLED VERSIONS ------------------ commit : 8dab54d6573f7186ff0c3b6364d5e4dd635ff3e7 python : 3.10.8.final.0 python-bits : 64 OS : Windows OS-release : 10 Version : 10.0.19044 machine : AMD64 processor : Intel64 Family 6 Model 141 Stepping 1, GenuineIntel byteorder : little LC_ALL : None LANG : de_DE.UTF-8 LOCALE : de_DE.cp1252 pandas : 1.5.2 numpy : 1.23.4 pytz : 2022.1 dateutil : 2.8.2 setuptools : 65.5.0 pip : 22.2.2 Cython : None pytest : 7.2.0 hypothesis : None sphinx : None blosc : None feather : None xlsxwriter : None lxml.etree : 4.9.1 html5lib : None pymysql : None psycopg2 : None jinja2 : 3.1.2 IPython : None pandas_datareader: None bs4 : None bottleneck : 1.3.5 brotli : 1.0.9 fastparquet : None fsspec : None gcsfs : None matplotlib : 3.6.2 numba : None numexpr : 2.8.4 odfpy : None openpyxl : 3.0.10 pandas_gbq : None pyarrow : None pyreadstat : None pyxlsb : None s3fs : None scipy : 1.9.3 snappy : None sqlalchemy : None tables : None tabulate : None xarray : None xlrd : None xlwt : None zstandard : None tzdata : None None

Comment From: phofl

Hi, thanks for your report.

I guess we can do one of bother: Either replace the sheet when append is set or append, but what we can not do: Replace in the first call and append afterwards.

Comment From: rhshadrach

I see two options with our current API. (A) Use two separate contexts:

with pd.ExcelWriter(file2, engine='openpyxl', mode='a', if_sheet_exists="replace") as writer:
            df1.to_excel(writer, sheet_name='test', index=False, startrow=0)
with pd.ExcelWriter(file2, engine='openpyxl', mode='a', if_sheet_exists="overlay") as writer:
            df2.to_excel(writer, sheet_name='test', index=False, startrow=4)

or (B) use writer.book.sheetnames and writer.book.remove_sheet to remove the sheet if it exists along with if_sheet_exists="overlay".

Comment From: rhshadrach

Also - I don't think you need the try..except here; append should create the file if it doesn't exist. Is this wrong?

Comment From: Salman-F

Also - I don't think you need the try..except here; append should create the file if it doesn't exist. Is this wrong?

I always get the FileNotFoundError without the try except... It makes sense because why would you append to something that is not created but it would be convenient in my situation, if a file would be created :)

Comment From: Salman-F

I see two options with our current API. (A) Use two separate contexts:

with pd.ExcelWriter(file2, engine='openpyxl', mode='a', if_sheet_exists="replace") as writer: df1.to_excel(writer, sheet_name='test', index=False, startrow=0) with pd.ExcelWriter(file2, engine='openpyxl', mode='a', if_sheet_exists="overlay") as writer: df2.to_excel(writer, sheet_name='test', index=False, startrow=4)

or (B) use writer.book.sheetnames and writer.book.remove_sheet to remove the sheet if it exists along with if_sheet_exists="overlay".

Thank you very much for your help @rhshadrach and @phofl :) I was wrong i was replacing the first df with the second df i wrote into the excel. My fault :) Now everything is working as i imagined 👍🏻