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'