BUG: Writer.sheets= ExcelWorkbook cant set attribute 'sheets'

[sabirjana]

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

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

[phofl]

cc @rhshadrach

IIRC this was intentional?

[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.

[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).

[sabirjana]

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

[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.

[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

[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.

[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'

[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.

[DeaMariaLeon]

Apologies then..

[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()`

[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")

[sabirjana]

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

[sabirjana]

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