Expression parsing error when referring to a Named Range

[raybsmith]

What actually happened

I'm getting errors parsing expressions that refer to a named range that is a single cell. Microsoft Excel parses this fine and evaluates the single-cell named range as a value, but pycel throws an error for me. I've attached a small working example spreadsheet file plus the script that attempts to parse it (the script assumes the spreadsheet file is in the same directory as the script).

Traceback: File "Y:/tmp/20240521_pycel_named_range_reference_test/read01.py", line 11, in main namedB = excel.evaluate('Sheet1!B2') File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelcompiler.py", line 892, in _evaluate_iterative results = self._evaluate_non_iterative(address) File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelcompiler.py", line 859, in _evaluate_non_iterative self._gen_graph(address) File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelcompiler.py", line 928, in _gen_graph self._process_gen_graph() File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelcompiler.py", line 938, in _process_gen_graph for precedent_address in dependant.needed_addresses: File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelcompiler.py", line 1127, in needed_addresses return self.formula and self.formula.needed_addresses or () File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelformula.py", line 593, in needed_addresses if self.python_code: File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelformula.py", line 615, in python_code self._python_code = self.ast.emit File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelformula.py", line 305, in emit ss = f'{args[0].emit}{op} {args[1].emit}' File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelformula.py", line 341, in emit return self._emit() File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelformula.py", line 351, in _emit address = AddressRange.create(addr_str, sheet=sheet, cell=self.cell) File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelutil.py", line 343, in create addr_tuple, sheetname = range_boundaries( File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelutil.py", line 722, in range_boundaries name_addr = cell and cell.excel and cell.excel.defined_names.get(address) File "C:\Users\raysmith\pysysproj4\pysysenv4\lib\site-packages\pycel\excelwrapper.py", line 172, in defined_names for d_name in self.workbook.defined_names.definedName: AttributeError: 'DefinedNameDict' object has no attribute 'definedName'

What was expected to happen

Expected output: Named_A = 2 Named_B = 4

Code Sample

from pathlib import Path

from pycel import ExcelCompiler

def main():
    selfdir = Path(__file__).resolve().parent
    excel = ExcelCompiler(selfdir/'test01.xlsx')
    namedA = excel.evaluate('Sheet1!B1')
    print(f'Named_A = {namedA}')
    namedB = excel.evaluate('Sheet1!B2')
    print(f'Named_B = {namedB}')

if __name__ == '__main__':
    main()

test01.xlsx: test01.xlsx

Environment

pycel 1.0b30 Python 3.8.10 Windows 10

[raybsmith]

I'm not sure what else I might have broken by doing this, but adding this monkey patch seems to be working for me so far:

# pycel monkey patch to enable evaluating a Named Range as part of formula
def defined_names_alt(self):
    if self.workbook is not None and self._defined_names is None:
        self._defined_names = {}

        # Original
        # for d_name in self.workbook.defined_names.definedName:
        for d_name in self.workbook.defined_names.values():
            destinations = [
                (alias, wksht) for wksht, alias in d_name.destinations
                if wksht in self.workbook]
            if len(destinations):
                self._defined_names[str(d_name.name)] = destinations
    return self._defined_names

pycel.excelwrapper.ExcelOpxWrapper.defined_names = property(defined_names_alt)
# End pycel monkey patch