Reading a named range from excel - Python - xlrd

Question

Following is the piece of code that I wrote, and I'm unable to proceed beyond reading the range. I want to be able to read the actual content of the range. Any help is appreciated.

import xlrd
xlBook = xlrd.open_workbook('data.xlsx')
# Open the sheet
sht = xlBook.sheet_by_name('calc')
# Look at the named range
named_obj = xlBook.name_map['load'][0]

# Now, able to retrieve the range
rangeData = (named_obj.formula_text)
(sheetName,ref) = rangeData.split('!')
# Gives me the range as $A$2:$B$20
print(ref)
# How do I print the contents of the cells knowing the range.

Answer 1

My method is to find out his column coordinates,

but I still recommend using openpyxl to be more intuitive.

def col2int(s: str):
    weight = 1
    n = 0
    list_s = list(s)
    while list_s:
        n += (ord(list_s.pop()) - ord('A')+1) * weight
        weight *= 26
    return n

# ...
# How do I print the contents of the cells knowing the range. ↓
temp, col_start, row_start, col_end, row_end = ref.replace(':', '').split('$')
for row in range(int(row_start)-1, int(row_end)):
    for col in range(col2int(col_start)-1, col2int(col_end)):
        print(sht.cell(row, col).value)