BUG : pandas.DataFrame.to_excel function not working in pandas 1.3.1

[KuhakuPixel]

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

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

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

# Your code here
writer = pd.ExcelWriter('existingFile.xlsx', mode='a')
df.to_excel(writer, sheet_name="existingSheet", startrow=writer.sheets["existingSheet"].max_row, index=False, header=False)

Problem description

It seems this function is broken in pandas 1.3.0, when trying to append to an existing sheet it will either raise an error, create a new one or replace it so it is impossible to append a data frame to an existing sheet. according to this document

if_sheet_exists{‘error’, ‘new’, ‘replace’}, default ‘error’
How to behave when trying to write to a sheet that already exists (append mode only).

error: raise a ValueError.

new: Create a new sheet, with a name determined by the engine.

replace: Delete the contents of the sheet before writing to it.

New in version 1.3.0

Expected Output

Able to append to an existing sheet without error

Output of pd.show_versions()

[paste the output of pd.show_versions() here leaving a blank line after the details tag]

[rhshadrach]

The documentation you quoted does not have an option to append to an existing sheet; is there a part of the documentation that indicates to you that you should be able to? Related: #42221

[KuhakuPixel]

The documentation you quoted does not have an option to append to an existing sheet; is there a part of the documentation that indicates to you that you should be able to? Related: #42221

Yes there is

You can also append to an existing Excel file:

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

I am able to append to existing sheet on older pandas like 1.2.3

[rhshadrach]

I see - there is a difference between an excel file and an excel sheet. With the option, you can append to the existing file, meaning to add or entirely replace a sheet in it, but there is currently no option to append to an already existing sheet. Perhaps the wording could be made more clear?

I am able to append to existing sheet on older pandas like 1.2.3

Can you post a reproducible example?

[KuhakuPixel]

I see - there is a difference between an excel file and an excel sheet. With the option, you can append to the existing file, meaning to add or entirely replace a sheet in it, but there is currently no option to append to an already existing sheet. Perhaps the wording could be made more clear?

I am able to append to existing sheet on older pandas like 1.2.3

Can you post a reproducible example?

import pandas as pd
import numpy as np
from openpyxl import load_workbook
file_name="excelTest.xlsx"
sheet_name="example_sheet0"
df=pd.DataFrame(data={"column 0":np.array([0,1,2,3,4,5,6])})

with pd.ExcelWriter(file_name,mode='w') as writer:
    df.to_excel(writer,sheet_name=sheet_name,index=False,header=True)
#appending to existing sheet
with pd.ExcelWriter(file_name,mode='a') as writer:
    book=load_workbook(file_name)
    writer.book=book
    writer.sheets={ws.title: ws for ws in book.worksheets}
    start_row = writer.sheets[sheet_name].max_row
    df.to_excel(excel_writer=writer,sheet_name=sheet_name,index=False,header=False,startrow=start_row)
    

when using an older version of pandas this will be able to append properly to an existing sheet like this

but when using pandas 1.3.0 or newer it will throw

ValueError: Sheet 'example_sheet0' already exists and if_sheet_exists is set to 'error'.

shouldn't if_sheet_exists have another mode like "append" ?

[twoertwein]

@KuhakuPixel The documentation of ExcelWriter says: "None of the methods and properties are considered public.". You are overwriting book and sheet in your example. Overwriting book will cause issues as described here #39576 (comment)

[rhshadrach]

Thanks for supplying an example @KuhakuPixel - as I had suspected it is very similar in nature to #42221. As such, I would request that any further discussion on whether or not pandas should support appending to an excel sheet be in #42221.

Pending the outcome of that discussion, if it is not to be supported, then it seems to me we could perhaps make the documentation here a bit more clear. In particular, adding the a sentence to the effect of "pandas allows users to append to Excel files, but does not support the option to append to an already existing Excel sheet".

[phofl]

@rhshadrach I think we can close, since this is implemented now?

[rhshadrach]

Yes, thanks @phofl