Can not access Worksheet Defined Names in openpyxl 3.1.0

[Ilnur Khairullin (IkeKazan)]

Hi All.

I have difficulties accessing the Worksheet defined names in loaded Workbook since openpyxl updated from 3.0.10 to 3.1.0.

In 3.0.10 all defined names (Global and Local for all worksheets) were stored in wb.defined_names.definedName list and were accessible (were loading correctly)

In 3.1.0 according to the documentation Defined Names — openpyxl 3.1.0 documentation, defined names were separated to:
1 Global ones that can be accessed through wb.defined_names dictionary 

2 Local (Worksheet) ones for each worksheet that can be accessed through ws.defined_names dictionary.

The problem I'm facing is that  when loading workboot using openpyxl 3.1.0 - 
I can not see any definedName object for Worksheets. At the same time, Global defined names are loaded normally.

Here is a test a used to demonstrate that behaviour.

See the TestWB.xlsx and Name Manager screenshot in attachements

Test script using openpyxl (also attached)

import openpyxl

wb: openpyxl.Workbook = openpyxl.load_workbook('TestWB.xlsx')

print(f"openpyxl {openpyxl.__version__}")

if openpyxl.__version__ == "3.1.0":

    ws = wb.worksheets[0]

    print(wb.defined_names)

    print(ws.defined_names)

else:

    print(wb.defined_names.definedName)

Output for script when using openpyx 3.0.10 (screenshot in attachements)

openpyxl 3.0.10

[<openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='Range1', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='Sheet1!$A$1', <openpyxl.workbook.defined_name.DefinedName
object>
Parameters:
name='Range2', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='Sheet1!$A$2:$B$2', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='Range3', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=0,
hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='Sheet1!$A$3', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='Range4', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=0,
hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='Sheet1!$A$4:$B$4']

Output for script when using openpyx 3.1.0 (screenshot in attachements)

openpyxl 3.1.0
{'Range1': <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='Range1', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='Sheet1!$A$1', 'Range2': <openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='Range2', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='Sheet1!$A$2:$B$2'}
{}

I am using
Python 3.10.5 on Windows 10 + VS Code

I am wondering if anyone faced with such behavior and has an idea what I am doing wrong.

Best regards Ilnur.

[Charlie Clark]

On 2 Feb 2023, at 13:59, Ilnur Khairullin (IkeKazan) wrote:

> Hi All.
>
> I have difficulties accessing the Worksheet defined names in loaded
>
> Workbook since openpyxl updated from 3.0.10 to 3.1.0.
>
> In 3.0.10 all defined names (Global and Local for all worksheets) were
>

> stored in *wb.defined_names.definedName* list and were accessible (were

>
> loading correctly)
>
> In 3.1.0 according to the documentation Defined Names — openpyxl 3.1.0
>

> documentation <https://openpyxl.readthedocs.io/en/stable/defined_names.html> ,

>
> defined names were separated to:
>

> 1 Global ones that can be accessed through *wb.defined_names* dictionary
>
> 2 Local (Worksheet) ones for each worksheet that can be accessed through *ws.defined_names
>
> *dictionary.

>
> The problem I'm facing is that when loading workboot using openpyxl 3.1.0

My apologies, it looked like this did indeed slip through testing. I've made the necessary changes to the 3.1 branch, including tests. You can work with this using a checkout.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Ilnur Khairullin (IkeKazan)]

No worries. It is good that you have found the reason.
I'm glad I was able to help find the bug and improve the library.

Again thank you for maintainig the tool.

Best regards Ilnur.

четверг, 2 февраля 2023 г. в 19:59:41 UTC+3, charli...@clark-consulting.eu: