python之在带有 openpyxl 的 Python 中使用 Excel 命名范围

如何使用带有 Python 2.7 的 openpyxl 遍历 Excel 命名范围/定义名称中的单元格并在命名范围内设置每个单元格值？

我发现了以下内容，但未能让它用于打印和设置指定范围内各个单元格的值。

Read values from named ranges with openpyxl

到目前为止，这是我的代码，我已经在我希望进行更改的地方添加了注释。感谢期待。

    #accessing a named range called 'metrics'  
    namedRange = loadedIndividualFile.defined_names['metrics'] 
 
    #obtaining a generator of (worksheet title, cell range) tuples 
    generator = namedRange.destinations 
 
    #looping through the generator and getting worksheet title, cell range 
 
    cells = [] 
    for worksheetTitle, cellRange in generator: 
        individualWorksheet = loadedIndividualFile[worksheetTitle] 
 
        #============================== 
        #How do I set cell values here? 
        # I am looking to print and change each cell value within the defined name range 
        #============================== 
 
        print cellRange 
        print worksheetTitle 
        #theWorksheet = workbook[worksheetTitle] 
        #cell = theWorksheet[cellRange] 

请您参考如下方法:

我设法解决了它。对于希望使用 openpyxl 访问定义名称或命名范围中每个单元格的值的其他人，以下内容可能会有用。

import openpyxl 
 
wb = openpyxl.load_workbook('filename.xlsx')  
#getting the address  
address = list(wb.defined_names['metrics'].destinations) 
 
#removing the $ from the address 
for sheetname, cellAddress in address: 
    cellAddress = cellAddress.replace('$','') 
 
#looping through each cell address, extracting it from the tuple and printing it out      
worksheet = wb[sheetname] 
for i in range(0,len(worksheet[cellAddress])): 
    for item in worksheet[cellAddress][i]: 
        print item.value`