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

writer = pd.ExcelWriter(filename, engine='openpyxl', mode='a')
writer.sheets= ExcelWorkbook cant set attribute 'sheets'

Issue Description

I get an error AttributeError :can't set attribute 'sheets'

This issue is from version 1.5, I don't get this issue in version 1.4.4

Expected Behavior

na

Installed Versions

2.0

Comment From: phofl

cc @rhshadrach

IIRC this was intentional?

Comment From: Dr-Irv

This may be an issue with openpyxl. With openpyxl 3.1.1, I get errors in trying to read an Excel file. With openpyxl 3.1.2, the error goes away. Issue seems to be that the conda version on conda-forge is still at 3.1.1.

@sabirjana can you verify the version of openpyxl that you have installed in your environment. You didn't provide the details in the issue.

Comment From: rhshadrach

@Dr-Irv - this issue is about setting the sheets attribute, that sounds unrelated, no?

@phofl - Yes, IIRC you can't safely set the sheets attribute, this is deprecated, and then removed in 2.0. I'd like to leave this issue open until it's verified that users can't safely set the sheet attribute (I'd like to just double check).

Comment From: sabirjana

Thanks for the response. My openpyxl version is '3.0.10'. I see pip has updated version openpyxl 3.1.2

Comment From: DeaMariaLeon

It works if mode='a' is removed (at least with the dev version).

And if it was deprecated, it's still on the documentation.

Comment From: Dr-Irv

@Dr-Irv - this issue is about setting the sheets attribute, that sounds unrelated, no?

It might be. I found this issue first, but the problem I was having is described in #51392

Comment From: rhshadrach

It works if mode='a' is removed (at least with the dev version).

And if it was deprecated, it's still on the documentation.

@DeaMariaLeon - I can't reproduce, can you provide an example? Also, where is setting sheets in the documentation - I look a quick look but didn't see anything. Could have easily missed it.

I took a look, if we were to allow a user to set the sheets attribute, we'd also need to update book accordingly, otherwise unexpected behavior can occur. While this may be doable, without a compelling use case, I don't see a reason to add.

@sabirjana - try modifying book directly rather than sheets. If this doesn't seem to suite your use case, please share any details here.

Comment From: DeaMariaLeon

@rhshadrach :

The documentation:

https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.sheets.html

df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"])  
df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  
with pd.ExcelWriter("nameagain.xlsx") as writer:
    df1.to_excel(writer, sheet_name="Sheet1", engine='openpyxl')  
    df2.to_excel(writer, sheet_name="Sheet2", engine='openpyxl') 

writer = pd.ExcelWriter("nameagain.xlsx", engine='openpyxl', mode='a')
writer.sheets

{'Sheet1': <Worksheet "Sheet1">, 'Sheet2': <Worksheet "Sheet2">}

The resulting file: nameagain.xlsx

I even added mode='a'

Comment From: rhshadrach

Ah, I see. This issue is about setting the sheets property, not just accessing it. Something like writer.sheets = []. The sheets property itself was not deprecated, only setting it was.

Comment From: DeaMariaLeon

Apologies then..

Comment From: sabirjana

@DeaMariaLeon Thanks for sharing the code snippet. Yes it works in this case however if I need to open the same excel and now add sheet3 then what's the way. I used following code but writer.book throws error for pandas 2.0

`path = "data1\PhD_data.xlsx"

book = load_workbook('nameagain.xlsx')
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

df3.to_excel(writer, sheet_name = 'x6')
df4.to_excel(writer, sheet_name = 'x7')
writer.close()`

Comment From: DeaMariaLeon

This added sheets x6 and 7 for me:

with pd.ExcelWriter("nameagain.xlsx", engine='openpyxl', mode='a') as writer:
    df3.to_excel(writer, sheet_name = "x6")
    df4.to_excel(writer, sheet_name = "x7")

Comment From: sabirjana

@DeaMariaLeon It works with pandas 1.4.4. now let me upgrade to 2.0 and check it. Thanks!

Comment From: sabirjana

It works, we can close the issue. Thanks for your help!!