Using Excel named ranges in Python with openpyxl

Question

How do I loop through the cells in an Excel named range/defined name and set each cell value within the named range using openpyxl with Python 2.7?

I found the following, but have not managed to get it to work for printing and setting the values of individual cells within the named range.

Read values from named ranges with openpyxl

Here's my code so far, I have put in comments where I am looking to make the changes. Thanks in anticipation.

    #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]

Extracting individual cell values, but I have resolved it now and will post the solution in case anyone else ever has the same issue. Thanks for looking into it. — Py1001

Py1001 Py1001 · Accepted Answer · 2017-05-16T17:11:09

I managed to resolve it. Perhaps the following will be useful to someone else who is looking to access the values of each cell in a defined name or named range using 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`

Using Excel named ranges in Python with openpyxl

1 Answers