CODEWITHSUNDEEP
pythonexcelxlrd
mandroid
mandroid

Reputation: 2328

Accessing the value of the intersection of named ranges of an Excel spreadsheet in Python using xlrd?

In Excel VBA, you can use the Intersect function which returns a range object that is the intersection of the arguments. You can then get a value from that like so.

Intersection("Name1", "Name2").Value

In a standard cell formula you can do the same thing:

=Name1 Name2

How do I accomplish this using xlrd? I've tried reading through the Name class and area2d looks like what I'll be using, but I don't know how to tie this all together.

Upvotes: 1

Views: 2227

Answers (1)

Reinier Torenbeek
Reinier Torenbeek

Reputation: 17383

As far as I know, xlrd does not natively support this kind of intersection functionality. For the kinds of rectangular ranges that you describe, you can fairly easily calculate the intersection though, using the Name class, like you already suggested yourself.

The xlrd-0.9.0 distribution that I use with Python 2.7.2 has a directory examples that shows how to work with named ranges. It comes with the file namesdemo.xls which is a workbook from an older version of Excel (it says version 97-2004).

An example of using that file to determine the cells in a range called Sales is as follows:

>>> import xlrd
>>> book=xlrd.open_workbook('namesdemo.xls')
>>> nameObj = book.name_and_scope_map.get(('sales', -1))
>>> nameObj.area2d()[1:5]
(1, 2, 1, 14)
>>> nameObj.result.text
u'Sheet3!$B$2:$N$2'

You can parse that last result to obtain the rectangle of cells in your range, or use the elements [1:5] from the tuple returned by the area2d() function -- they represent rowxlo, rowxhi, colxlo and colxhi of the rectangle. Do the same thing for another range and then you can intersect them "manually".

Note the lower-case name 'sales' in the call to name_and_scope_map.get(). That is required because Excel does not have case-sensitive range names. The -1 parameter indicates that it should search for the name globally, on all sheets.

If saved in the newer Excel .xlsx format, the Name object seems to behave slightly differently:

>>> book=xlrd.open_workbook('namesdemo.xlsx')
>>> nameObj = book.name_and_scope_map.get(('sales', -1))
>>> nameObj.formula_text
u'Sheet3!$B$2:$N$2'

In this case, I have not found any way to get access to any actual coordinates via area2d():

>>> nameObj.area2d()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Library/Python/2.7/site-packages/xlrd/book.py", line 291, in area2d
    self.dump(self.book.logfile,
AttributeError: 'NoneType' object has no attribute 'logfile'

so it looks like you would need to parse the formula_text string or check out the source code of xlrd to see if you can fix it :-)

The example script xlrdnameAPIdemo.py gives some more explanations, for example about how to look for named ranges in a particular scope/worksheet.

Upvotes: 1

Related Questions