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.