Python xlrd Named Range Value

Question

Using XLRD in Python to read from Excel.

Simple scenario. I have a cell with a value and this is associated with a named range.

NamedRange "Foo" = Sheet1!$A$1 The value in A1 is "Bar"

book =xlrd.open_workbook("")

rng =  book.name_map['foo'][0]  # lower case for some reason.

print rng.???   # how to print the cell value bar??

I just want to reference the Named range "Foo" in python code and print out the value "Bar" of the cell.

EDIT: Here is another more complete example:

import xlrd

workbook = xlrd.open_workbook('/path/to/metester.xls')
cell_obj = workbook.name_and_scope_map.get(('sales', -1))
# this does print Sheet1!$A$1
print cell_obj.formula_text
# this raises the NoneTypeError
print cell_obj.cell()

formula_text is there to ensure excel can read the file. In my case the named cell is "sales" in Sheet1, cell A1.

Returns:

Sheet1!$A$1
Traceback (most recent call last):
  File "tester.py", line 7, in <module>
    print cell_obj.cell()
  File "/usr/local/lib/python2.7/dist-packages/xlrd/book.py", line 253, in cell
    self.dump(self.book.logfile,
AttributeError: 'NoneType' object has no attribute 'logfile'

Answer 1

Firstly, it is lower case as explained in the xlrd module information (https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html?p=4966):

name_map [#]

A mapping from lower_case_name to a list of Name objects. The list is sorted in scope order. Typically there will be one item (of global scope) in the list.

You have two options. If you a truely only setting a name for a single cell, then you use the 'cell' method of the Name class (see the docs):

import xlrd
book = xlrd.open_workbook("")
Name = book.name_map['foo'][0]
print(Name.cell())

Console:

text:'Bar'

If you have, however, named an entire range of values, then you need to use the area2d method of the Name class:

import xlrd
book = xlrd.open_workbook("q1.xls")
Name = book.name_map['foo'][0]
Sheet, rowxlo, rowxhi, colxlo, colxhi = Name.area2d()
for i in range(rowxhi):
    print(Sheet.cell(i,0))

Console:

text:'Bar'

Answer 2

The initial question and example is answered exactly by @jonnybazookatone answer below. The expanded example is a different error. It seems to be an xlrd deficiency to me, but I've hacked a solution for what you want. First section is explanation, last section the hack.

Explanation

Looking at the more expanded example, the Error message is produced from the lines of code that handle the error condition on a Name object where the result of evaluating the formulae in a Name range has failed for some reason.

The actual reported error

AttributeError: 'NoneType' object has no attribute 'logfile'

is a secondary error - indicating that self.book evaluates to None for this Name object.

Secondly - you are missing an important detail, I think, that your Excel file is in .xlsx format. Note the trailing x. In the normal xls parser, the Name object doesn't have an attribute formula_text, so your code fails with the following error.

Traceback (most recent call last):
  File "D:\q1.py", line 16, in <module>
    print cell_obj.formula_text
AttributeError: 'Name' object has no attribute 'formula_text'

Please be careful with your code examples - it took me a while to track that difference - xlsx files are parsed by entirely different code within xlrd. With the xlsx fixed, I can reproduce your error.

The formula_text attribute in this case is just giving the R-C notation reference to the cell that is your named range. It should be noted that the function that sets formula_text is caveated "#### UNDER CONSTRUCTION ####" and has been since the initial commit of the xlsx module in 2012.

The problem is - as far as I can see - formulae are never evaluated in the opening of an xlsx - so you hit the error that Name.res is None and therefore you see your error. It seems to be an xlrd bug / feature.

Hack

I have come up with a nasty hack that will do what you want in the case where your range is a single cell by expanding the cell reference. Note it will fail for named ranges referring to more than one cell as it is now, but could be easily modified to cope with that scenario:

hack = cell_obj.formula_text
(sheetName,ref) = hack.split('!')
(discard,colStr,rowStr) = ref.split('$')
col = 0
for i in range(len(colStr)):
    colAdd = string.ascii_uppercase.index(colStr)
    col += colAdd * 10**i
row = int(rowStr)-1
print("Trying to evaluate cell",row,col,"in sheet",sheetName)
print workbook.sheet_by_name(sheetName).cell(row,col)