Error in ExcelModel().loads(filename) - 'DefinedNameDict' object has no attribute 'definedName' (Python 3.9)

[horkah]

Describe the bug
The test_excel.py script provided here on Git fails to complete without error.

To Reproduce
I downloaded (6feb2023) the files test_excel.py and test_files from here:
https://github.com/vinci1it2000/formulas/tree/master/test

I use python 3.9.16 and installed the missing required packages (dill, schedula, formulas[all])

I appended the following lines at the end of test_excel.py:

[...]
if __name__ == '__main__':
    print("hello world")
    t = TestExcelModel()
    t.setUp()
    t.test_excel_model()
    exit()

and receive the following error message:

hello world

[info] test_excel_model: Loading excel-model.
Traceback (most recent call last):
  File "[...]\PycharmProjects\[...]\test_excel.py", line 285, in <module> t.test_excel_model()
  File "[...]\PycharmProjects\[...]\test_excel.py", line 112, in test_excel_model xl_mdl.loads(self.filename)
  File "[...]\PycharmProjects\[...]\venv\lib\site-packages\formulas\excel\__init__.py", line 108, in loads self.load(filename)
  File "[...]\PycharmProjects\[...]\venv\lib\site-packages\formulas\excel\__init__.py", line 112, in load book, context = self.add_book(filename)
  File "[...]\PycharmProjects\[...]\venv\lib\site-packages\formulas\excel\__init__.py", line 188, in add_book data['references'] = self.add_references(book, context=context)
  File "[...]\PycharmProjects\[...]\venv\lib\site-packages\formulas\excel\__init__.py", line 93, in add_references for n in book.defined_names.definedName:
AttributeError: 'DefinedNameDict' object has no attribute 'definedName'

Process finished with exit code 1

Expected behavior
The test_excel.py provided here with the repository is expected to work under Python 3.9 with all required packages installed.

Desktop:

• OS: Windows 11
• Python 3.9.16

[michaelkryukov]

I've had same issue – looks like it's because of new version of openpyxl. Pinning version to openpyxl>=3.0.7,<3.1.0 seems to help. I think formulas should be updated with recent recommendations from openpyxl on how to interact with defined names.

[maffblaster]

Anyone know how difficult it would be to fix this bug? I am also in the same boat on python 3.11.x:

'DefinedNameDict' object has no attribute 'definedName'

I am using openpyxl>=3.1.0 since I need the newly added Custom Data Properties functionality, so I can't downgrade to an older version.

@vinci1it2000, thanks for the awesome work!

[maffblaster]

I've had same issue – looks like it's because of new version of openpyxl. Pinning version to openpyxl>=3.0.7,<3.1.0 seems to help. I think formulas should be updated with recent recommendations from openpyxl on how to interact with defined names.

What are these recent recommendations, @michaelkryukov?

[michaelkryukov]

I'm not sure, but docs should contain needed information (something like that).

[MizsakPeterEcon]

I'm facing the same issue and did some digging in the OpenPyXl repository:

In the release note of openpyxl 3.1.0 I found that defined names were restructured: Issue link
(commit of the change)

DefinedNameList is replaced by DefinedNameDict when accessing from the worksheet object.

In openpyxl 3.0.10:

print(type(workbook.defined_names))
>>> <class 'openpyxl.workbook.defined_name.DefinedNameList'>

In openpyxl 3.1.0:

print(type(workbook.defined_names))
>>> <class 'openpyxl.workbook.defined_name.DefinedNameDict'>

---

An other change that might be relevant in the removal of formula_attributes attribute. (see release notes)
Here the array_formulae property can be used instead. (commit of the change)

@vinci1it2000 I've never contributed to any open source project, but with some assistance I can try to make a PR to fix these issues.

[maffblaster]

Looks like it is getting fixed in the next release! Thank you, @vinci1it2000!