AttributeError: can't set attribute for workbook

Question

So I have the followiing code where I am writing to an already existing excel file:

book = load_workbook(file_path)
writer = pd.ExcelWriter(file_path, engine = 'openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

I am getting an error at line

writer.book = book as

writer.book = book

AttributeError: can't set attribute

And the worse thing is this error is happening on my colleagues mac machine but not on my windows machine. Any ideas ?

Answer 1

@P-TATE's answer worked for me. I encountered the exact same issue as @bella when running 1.5.0 but not when running 1.4.4. I found the following in the release notes for 1.5.0

All attributes of ExcelWriter were previously documented as not public. However some third party Excel engines documented accessing ExcelWriter.book or ExcelWriter.sheets, and users were utilizing these and possibly other attributes. Previously these attributes were not safe to use; e.g. modifications to ExcelWriter.book would not update ExcelWriter.sheets and conversely. In order to support this, pandas has made some attributes public and improved their implementations so that they may now be safely used. (GH45572)

The following attributes are now public and considered safe to access.

• book
• check_extension
• close
• date_format
• engine
• if_sheet_exists
• sheets
• supported_extensions

The following attributes have been deprecated. They now raise a FutureWarning when accessed and will be removed in a future version. Users should be aware that their usage is considered unsafe, and can lead to unexpected results.

• cur_sheet
• handles
• path
• save
• write_cells

EDIT:

So after a lot (lot) of struggling with this, I've found a solution that works for me and might work for others.

As referenced here, the philosophy of the Pandas team is to make the book (and sheets) setters private, and push those responsibilities onto the Excel engine to implement.

The solution that worked for me is to copy the template excel file to a new file using python, eg:

import shutil

output_path = shutil.copy(template_path, output_path)

• Then, load output_path as the workbook when you instantiate ExcelWriter. Therefore, there is no need to call openpyxl.load_workbook() at all, and also no need to copy the .book or .sheets attributes.
• Also, ensure that you set the mode and if_sheet_exists flags as follows (see the docs for more info):

writer = pd.ExcelWriter(
    output_path,
    engine="openpyxl",
    mode="a",
    if_sheet_exists="overlay",
)

• Finally be sure to remove any references to book (if you had any previously), which was a particularly subtle bug I encountered. All my code to modify Tables, defined names, refs, etc. within the workbook had been applied to the book variable which was a workbook instance returned by openpyxl.load_workbook(). This variable was then assigned to the ExcelWriter instance's book attribute with: writer.book = book just as you did. However, this is just a reference to the same object in memory, so modifying book and ExcelWriter.book have the same effect, and my code happened to act on book. However, if you make the changes described above, you will no longer have the book variable to modify, so you simply use writer.book to interact with the workbook, as in writer.book.defined_names.get(defined_name).attr_text = ref, instead of book.defined_names.get(defined_name).attr_text = ref.

Hope this helps

Answer 2

I have found that the key is to use writer.workbook instead of writer.book in code here:
writer.workbook = openpyxl.load_workbook('test.xlsx')
and to add options in:
pd.ExcelWriter( ... , mode='a', if_sheet_exists='overlay')

import pandas as pd
import openpyxl
import warnings

warnings.simplefilter(action='ignore', category=FutureWarning)

# create new excel file
df_empty = pd.DataFrame()
df_empty.to_excel('test.xlsx')

# ... with a sheet name
workbook = openpyxl.load_workbook('test.xlsx')
ex_sheet = workbook['Sheet1']
ex_sheet.title = 'Tmp'
workbook.save('test.xlsx')

# prepare a dataframe
df = pd.DataFrame({'Column1': ['aa', 'bb', 'cc', 'dd'],
                   'Column2': [100, 170, 140, 160]})

# insert a dataframe into an excel sheet
writer = pd.ExcelWriter('test.xlsx', engine='openpyxl', mode='a', if_sheet_exists='overlay')
writer.workbook = openpyxl.load_workbook('test.xlsx')
df.to_excel(writer, sheet_name='Tmp', index=False, header=True, startrow=3, startcol=3)

writer.save()

I use python 3.8; openpyxl: 3.0.10; pandas: 1.5.0