Can not access Worksheet Defined Names in openpyxl 3.1.0

82 views
Skip to first unread message

Ilnur Khairullin (IkeKazan)

unread,
Feb 2, 2023, 7:59:27 AM2/2/23
to openpyxl-users
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.
test.py
definedNames_openpyxl_3_1_0.png
TestWB.xlsx
definedNames_openpyxl_3_0_10.png
Name_manager.png

Charlie Clark

unread,
Feb 2, 2023, 11:59:41 AM2/2/23
to openpyxl-users
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)

unread,
Feb 2, 2023, 11:57:08 PM2/2/23
to openpyxl-users
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:
Reply all
Reply to author
Forward
0 new messages