CODEWITHSUNDEEP
pythonopenpyxl
John E
John E

Reputation: 11

is there a better way to use OpenPyXL's defined_names feature to return values from an Excel Named Range?

I have an Excel workbook that has a worksheet called 'Professional Staff'. On that sheet, there is a range of cells named 'ProStaff'. I retrieve a list of the values in those cells with this code:

import openpyxl

wb = openpyxl.load_workbook(filename='SOexample.xlsx', read_only=True)


#Get the ProStaff range values
ProStaffRange = wb.defined_names['ProStaff']

#returns a generator of (worksheet title, cell range) tuples
dests = ProStaffRange.destinations

#use generator to create a list of (sheet, cell) tuples
cells = []
for title, coord in dests:
    ws = wb[title]
    cells.append(ws[coord])

#Above was from the OpenPyXL website

#Below is my best attempt to retrieve the values from those cells
cellsStr = []
startChar = '.'
stopChar = '>'

for item in cells[0]:
    itemStr = str(item)
    cellsStr.append( (itemStr.split("'")[1].strip(),  itemStr[itemStr.find(startChar)+1:itemStr.find(stopChar)]) )

for item in cellsStr:
    print(wb[item[0]][item[1]].value)

The string manipulation I do takes something like:

(<ReadOnlyCell 'Professional Staff'.A1>,)

and turns it into:

('Professional Staff', 'A1')

It seems to me that there should be a way to work with the ReadOnlyCell items directly in order to retrieve their values, but I haven't been able to figure out how.

Upvotes: 1

Views: 2317

Answers (1)

Henry Milewski
Henry Milewski

Reputation: 41

Try this, modified from something I saw elsewhere, it works for single-cell named ranges:

wb = load_workbook('filename.xlsx', data_only=True)
ws = wb['sheet_name']

val=ws[list(wb.defined_names['single_cell_named_range'].destinations)[0][1]].value

print(val)

I'm using Openpyxl 2.5.12.

Upvotes: 4

Related Questions