xlutils.viewΒΆ

Iterating over the cells in a Sheet can be tricky, especially if you want to exclude headers and the like. This module is designed to make things easier.

For example, to iterate over the cells in the first sheet of a workbook:

>>> def print_data(rows):
...     for row in rows:
...         for value in row:
...             print(value, end=' ')
...         print()
>>> from os.path import join
>>> from xlutils.view import View
>>> view = View(join(test_files,'testall.xls'))
>>> print_data(view[0])
R0C0 R0C1
R1C0 R1C1
A merged cell


More merged cells

You can also get a sheet by name:

>>> print_data(view['Sheet2'])
R0C0 R0C1
R1C0 R1C1

One helpful feature is that dates are converted to datetime objects rather than being left as numbers:

>>> for row in View(join(test_files,'datetime.xls'))[0]:
...     for value in row:
...         print(repr(value))
datetime.datetime(2012, 4, 13, 0, 0)
datetime.time(12, 54, 37)
datetime.datetime(2014, 2, 14, 4, 56, 23)

Now, things get really interesting when you start slicing the view of a sheet:

>>> print_data(view['Sheet1'][:2, :1])
R0C0
R1C0

As you can see, these behave exactly as slices into lists would, with the first slice being on rows and the second slice being on columns.

Since looking at a sheet and working with the row and column labels shown is much easier, Row and Col helpers are provided. When these are used for the stop part of a slice, they are inclusive. For example:

>>> from xlutils.view import Row, Col
>>> print_data(view['Sheet1'][Row(1):Row(2), Col('A'):Col('B')])
R0C0 R0C1
R1C0 R1C1

Finally, to aid with automated tests, there is a CheckerView subclass of View that provides CheckSheet views onto sheets in a workbook. These have a compare() method that produces informative AssertionError exceptions when the data in the view of the sheet is not as expected:

>>> from xlutils.view import CheckerView
>>> sheet_view = CheckerView(join(test_files,'testall.xls'))[0]
>>> sheet_view[:, Col('A'):Col('A')].compare(
...     (u'R0C0', ),
...     (u'R0C1', ),
... )
Traceback (most recent call last):
...
AssertionError: sequence not as expected:

same:
((u'R0C0',),)

expected:
((u'R0C1',),)

actual:
((u'R1C0',), (u'A merged cell',), (u'',), (u'',), (u'More merged cells',))

While comparing [1]: sequence not as expected:

same:
()

expected:
(u'R0C1',)

actual:
(u'R1C0',)

While comparing [1][0]: u'R0C1' (expected) != u'R1C0' (actual)

Use of the compare() method requires testfixtures to be installed.

Looking at the implementation of CheckerView will also show you how you can wire in SheetView subclasses to provide any extra methods you may require.