Named ranges pointing to the same address are not recognized

[lukelamar]

Pylightxl Version:1.61 Python Version: 3.7.4

Summary of Bug/Feature: Named ranges pointing to the same address are not recognized. db.nr_names() will not return the name and attempting to use one of the named ranges (as in db.nr('my_second_named_range') will return an empty list even if data exists within the range.

Traceback: No error is thrown. The behavior is unexpected.

To reproduce:

• Create a new Excel document.
• Enter a value in a cell and name it.
• Enter a value in another cell and name it.
• Add a second named range using the Name Manager (found under Formulas menu item) that has the same reference as one of the other named ranges.
• List the named ranges - one should be missing.
• Access the data using the range names - one of the double referenced ranges will return an empty list.

I created my test Excel sheet using: Microsoft Office Professional Plus 2013 and Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20858) 64-bit

Suggestion for fix: I am not familiar enough with the xlsx format to suggest a fix to the code.

[PydPiper]

Hi @lukelamar thanks for posting this. I am held up by school work at the moment, but after this weekend I should be able to take a look and will get back to you with a fix!

[PydPiper]

@lukelamar after further review i am not follow the error here. To my understanding a spreadsheet that points to the same address can only have 1 named range (ex: name1 at A1 cannot be named something else without renaming name1). In the case where there is an overlap (ex: name1 at A1 and name2 at A1:A3) will work just fine as well.

from pylightxl import pylightxl as xl

db = xl.readxl('test.xlsx')
db.ws('Sheet1').update_address('A1',1)
db.ws('Sheet1').update_address('A2',2)
db.ws('Sheet1').update_address('A3',3)
db.add_nr('name1', 'Sheet1', address='A1')
db.add_nr('name2', 'Sheet1', address='A1:A3')
print(db.nr('name1'))
print(db.nr('name2'))

produces

[[1]]
[[1], [2], [3]]

[lukelamar]

@PydPiper I have verified that a given address (or range) can be assigned multiple names (in various versions on MS Excel at least). Using the Name Manager feature will allow me to create any number of named ranges that point to exactly to the same address. Within Excel, using any of the names that point to that range will work. Using the pylightxl library, only one of the named ranges is listed with db.nr_names() and only the one listed will work. (I have not determined which one is recognized - the first added, last added or random, but I don't think that matters much.)

I've been pretty busy as well. When I have more time, I will review the Pylightxl code and suggest a fix.

Thanks!