如何使用openpyxl通过DefinedName获取单元格内容? [英] How to use openpyxl to get the cell content via the DefinedName?

问题描述

例如，单元格的坐标为A1，用coordinate='A1'设置DefinedName name="cat".然后，我想通过DefinedName "cat"从cell中读取内容.但是似乎不支持.还有其他方法可以帮助吗? 在此处查看示例图片

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

推荐答案

我如何找到答案

我不知道excel的功能.因此，我将单元格A1命名为cat并保存了文件.我通过rar文件提取了文件.在xl/workbook.xml中找到cat，原始内容为<definedName name="cat">工作表1!$A$1</definedName>.这是一个名为definedName的元素，具有属性name，其内容由工作表的title和单元格coordinate组成.因此该函数称为defined name或相关名称.我在官方文档

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

答案

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')

这篇关于如何使用openpyxl通过DefinedName获取单元格内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！