CODEWITHSUNDEEP
pythonexcelpandas
AgFre
AgFre

Reputation: 15

Python and Excel take cell value by its name instead that by its coordinate

How can I take a cell value with Python 2.7 by accessing cell's name, created in Excel, rather than giving coordinates?

In this case by accessing "Surname" instead of D8:

IMG: http://www.hosting.universalsite.org/image-excel-EBA2_58B5BC06.png

Upvotes: -1

Views: 2331

Answers (1)

Steven Rumbalski
Steven Rumbalski

Reputation: 45552

openpyxl supports defined names:

Example from docs:

my_range = wb.defined_names['my_range']
# if this contains a range of cells then the destinations attribute is not None
dests = my_range.destinations # returns a generator of (worksheet title, cell range) tuples

cells = []
for title, coord in dests:
    ws = wb[title]
    cells.append(ws[coord])

Assuming 'Surname' applies to only one cell you could extract it as follows:

from openpyxl import load_workbook
wb = load_workbook(filename='your_workbook.xlsx')
title, coord = next(wb.defined_names['Surname'].destinations)
result = wb[title][coord].value

Upvotes: 1

Related Questions