Compatibility issue with openpyxl 3.1.0 - read_sheet - TypeError: 'set' object is not subscriptable

[chel-ou]

Recent update of openpyxl to 3.1.0 breaks read_sheet.

Below is the traceback

Traceback (most recent call last):
  File "/code/base/tests/tests.py", line 3318, in test_sheet
    sheet = pe.get_sheet(
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel/core.py", line 37, in get_sheet
    named_content = sources.get_sheet_stream(**keywords)
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel/internal/core.py", line 21, in get_sheet_stream
    sheets = a_source.get_data()
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel/plugins/sources/memory_input.py", line 40, in get_data
    sheets = self.__parser.parse_file_content(
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel/plugins/parsers/excel.py", line 27, in parse_file_content
    return self._parse_any(
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel/plugins/parsers/excel.py", line 40, in _parse_any
    sheets = get_data(anything, file_type=file_type, **keywords)
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel_io/io.py", line 86, in get_data
    data, _ = _get_data(
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel_io/io.py", line 105, in _get_data
    return load_data(**keywords)
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel_io/io.py", line 205, in load_data
    result = reader.read_all()
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel_io/reader.py", line 95, in read_all
    content_dict = self.read_sheet_by_index(sheet_index)
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel_io/reader.py", line 84, in read_sheet_by_index
    sheet_reader = self.reader.read_sheet(sheet_index)
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel_xlsx/xlsxr.py", line 148, in read_sheet
    sheet = SlowSheet(native_sheet, **self.keywords)
  File "/opt/project/.venv/lib/python3.10/site-packages/pyexcel_xlsx/xlsxr.py", line 72, in __init__
    for ranges in sheet.merged_cells.ranges[:]:

TypeError: 'set' object is not subscriptable

Pinning version of openpyxl to 3.0.10 is a temporary workaround.

[konstista]

@chfw sorry for ping and thanks for your amazing work!

This line here:
for ranges in sheet.merged_cells.ranges[:]:

Should probably be replaced with:
for ranges in list(sheet.merged_cells.ranges)[:]:

For everyone who is struggling, here are your requirements.txt:

pyexcel==0.6.7
pyexcel-xlsx==0.6.0
openpyxl==3.0.10

[peter279k]

@chfw sorry for ping and thanks for your amazing work!

This line here: for ranges in sheet.merged_cells.ranges[:]:

Should probably be replaced with: for ranges in list(sheet.merged_cells.ranges)[:]:

For everyone who is struggling, here are your requirements.txt:

pyexcel==0.6.7
pyexcel-xlsx==0.6.0
openpyxl==3.0.10

Thanks! Your comment is very helpful for me!

[kodurusivakumar34]

May i know ,any update on this issue?

[kristianperkins]

Looks like this is causing problems with downstream applications and maintainers are warning against using pyexcel because breaking changes aren't being fixed (see above linked issues).

[stuchalk]

Also not working with macOS Ventura 13.6.4 Excel v16.83. Python 3.11.8.

[TheDuckGoesQuark]

@chfw sorry for ping and thanks for your amazing work!

This line here: for ranges in sheet.merged_cells.ranges[:]:

Should probably be replaced with: for ranges in list(sheet.merged_cells.ranges)[:]:

For everyone who is struggling, here are your requirements.txt:

pyexcel==0.6.7
pyexcel-xlsx==0.6.0
openpyxl==3.0.10

This failed for me saying that version pyexcel 0.6.7 doesn't exist, but just pinning the openpyxl version to 3.0.10 worked 😄

Thank you!

[OrchidSoleil]

I'm still having this issue, the versions are as follows:
openpyxl==3.1.5
pyexcel==0.7.0
pyexcel-io==0.6.6
pyexcel-xls==0.7.0
pyexcel-xlsx==0.6.0

Only thing that works for me is chfw's solution as well, thank you very much!