Why does pandas read_excel fail on an openpyxl error saying 'ReadOnlyWorksheet' object has no attribute 'defined_names'?

This bug suddenly came up literally today after read_excel previously was working fine. Fails no matter which version of python3 I use - either 10 or 11.

Do folks know the fix?

  File "/Users/aizenman/My Drive/code/daily_new_clients/code/run_daily_housekeeping.py", line 38, in <module>
    main()
  File "/Users/aizenman/My Drive/code/daily_new_clients/code/run_daily_housekeeping.py", line 25, in main
    sb = diana.superbills.load_superbills_births(args.site, ath)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/aizenman/My Drive/code/daily_new_clients/code/diana/superbills.py", line 148, in load_superbills_births
    sb = pd.read_excel(SUPERBILLS_EXCEL, sheet_name="Births", parse_dates=["DOS", "DOB"])
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/util/_decorators.py", line 211, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/util/_decorators.py", line 331, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/io/excel/_base.py", line 482, in read_excel
    io = ExcelFile(io, storage_options=storage_options, engine=engine)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/io/excel/_base.py", line 1695, in __init__
    self._reader = self._engines[engine](self._io, storage_options=storage_options)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/io/excel/_openpyxl.py", line 557, in __init__
    super().__init__(filepath_or_buffer, storage_options=storage_options)
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/io/excel/_base.py", line 545, in __init__
    self.book = self.load_workbook(self.handles.handle)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/io/excel/_openpyxl.py", line 568, in load_workbook
    return load_workbook(
           ^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/openpyxl/reader/excel.py", line 346, in load_workbook
    reader.read()
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/openpyxl/reader/excel.py", line 303, in read
    self.parser.assign_names()
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/openpyxl/reader/workbook.py", line 109, in assign_names
    sheet.defined_names[name] = defn
    ^^^^^^^^^^^^^^^^^^^
AttributeError: 'ReadOnlyWorksheet' object has no attribute 'defined_names'

I am using Azure Databricks and I ran into this same issue today. I checked and openpyxl was on version 3.1.1.

At the start of my notebook I added a line to pin to the previous version:

%pip install --force-reinstall -v "openpyxl==3.1.0"

After that my job was working again. It seems like some change in the most recent update is causing this to break.

You can first try to uninstall the openpyxl
pip uninstall openpyxl -y
and then use
pip install openpyxl==3.1.0 -y

Note: Use ! infront of code if case of using notebooks.
!pip uninstall openpyxl -y
!pip install openpyxl==3.1.0 -y

If the above code does not work. You can try to upgrade the pandas. i.e
!pip uninstall pandas -y && !pip install pandas

By installing the 'xlxswriter', the trouble was solved. Thanks to the above solutions, but they do not work in my case. So, this maybe another issuse you may consider.

Currently, we face the issue on python3.8 environment (Rocky 8.8 OS). We downgrade the openpyxl module to 3.0.9 to fix this issue.

> sudo pip3 install openpyxl==3.0.9

Enjoy it!