CODEWITHSUNDEEP
pythonopenpyxl
Ella
Ella

Reputation: 43

How to use openpyxl to get the cell content via the DefinedName?

For example, the cell's coordinate is A1, setup a DefinedName name="cat" with coordinate='A1' . Then I want to read the content from cell via DefinedName "cat". But it seems not supported. Any other methods to help? See here for example picture

from openpyxl import load_workbook
wb = load_workbook(filename="test.xlsx")
ws = wb['Sheet1']
cat = ws['cat'].value

Upvotes: 3

Views: 3627

Answers (1)

aishenghuomeidaoli
aishenghuomeidaoli

Reputation: 302

How I found the answer

I don't know the function of excel. So I 'named' the cell A1 as cat and save the file. I extracted the file through as rar file. cat was found in xl/workbook.xml, the origin content is <definedName name="cat">工作表1!$A$1</definedName>. It's a element called definedName with attribute name, and its content consists of worksheet's title and cell coordinate. So the function is called defined name or something related. I found it in official doc

Answer

from openpyxl import load_workbook
wb = load_workbook(filename="test.xlsx")

# get DefinedNameList instance
defined_names_cat = wb.defined_names['cat']

# get destinations which is a generator
destinations_cat = defined_names_cat.destinations  
values = {}
for sheet_title, coordinate in destinations_cat:
    values.update({sheet_title: wb[sheet_title][coordinate].value})
my_value = values.get('Sheet1')

Upvotes: 3

Related Questions