1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
# -*- coding: utf-8 -*-
# Copyright (c) 2013 Simplistix Ltd
#
# This Software is released under the MIT License:
# http://www.opensource.org/licenses/mit-license.html
# See license.txt for more details.
from datetime import datetime, time
from petl.compat import xrange
class Index(object):
def __init__(self, name):
self.name = name
class Row(Index):
"""
A one-based, end-inclusive row index for use in slices,
eg:: ``[Row(1):Row(2), :]``
"""
def __index__(self):
return int(self.name) - 1
class Col(Index):
"""
An end-inclusive column label index for use in slices,
eg: ``[:, Col('A'), Col('B')]``
"""
def __index__(self):
from xlwt.Utils import col_by_name
return col_by_name(self.name)
class SheetView(object):
"""
A view on a sheet in a workbook. Should be created by indexing a
:class:`View`.
These can be sliced to create smaller views.
Views can be iterated over to return a set of iterables, one for each row
in the view. Data is returned as in the cell values with the exception of
dates and times which are converted into :class:`~datetime.datetime`
instances.
"""
def __init__(self, book, sheet, row_slice=None, col_slice=None):
#: The workbook used by this view.
self.book = book
#: The sheet in the workbook used by this view.
self.sheet = sheet
for name, source in (('rows', row_slice), ('cols', col_slice)):
start = 0
stop = max_n = getattr(self.sheet, 'n'+name)
if isinstance(source, slice):
if source.start is not None:
start_val = source.start
if isinstance(start_val, Index):
start_val = start_val.__index__()
if start_val < 0:
start = max(0, max_n + start_val)
elif start_val > 0:
start = min(max_n, start_val)
if source.stop is not None:
stop_val = source.stop
if isinstance(stop_val, Index):
stop_val = stop_val.__index__() + 1
if stop_val < 0:
stop = max(0, max_n + stop_val)
elif stop_val > 0:
stop = min(max_n, stop_val)
setattr(self, name, xrange(start, stop))
def __row(self, rowx):
from xlrd import XL_CELL_DATE, xldate_as_tuple
for colx in self.cols:
value = self.sheet.cell_value(rowx, colx)
if self.sheet.cell_type(rowx, colx) == XL_CELL_DATE:
date_parts = xldate_as_tuple(value, self.book.datemode)
# Times come out with a year of 0.
if date_parts[0]:
value = datetime(*date_parts)
else:
value = time(*date_parts[3:])
yield value
def __iter__(self):
for rowx in self.rows:
yield self.__row(rowx)
def __getitem__(self, slices):
assert isinstance(slices, tuple)
assert len(slices) == 2
return self.__class__(self.book, self.sheet, *slices)
class View(object):
"""
A view wrapper around a :class:`~xlrd.Book` that allows for easy
iteration over the data in a group of cells.
:param path: The path of the .xls from which to create views.
:param class_: An class to use instead of :class:`SheetView` for views of
sheets.
"""
#: This can be replaced in a sub-class to use something other than
#: :class:`SheetView` for the views of sheets returned.
class_ = SheetView
def __init__(self, file_contents, class_=None, **kwargs):
self.class_ = class_ or self.class_
from xlrd import open_workbook
self.book = open_workbook(file_contents=file_contents,
on_demand=True, **kwargs)
def __getitem__(self, item):
"""
Returns of a view of a sheet in the workbook this view is created for.
:param item: either zero-based integer index or a sheet name.
"""
if isinstance(item, int):
sheet = self.book.sheet_by_index(item)
else:
sheet = self.book.sheet_by_name(item)
return self.class_(self.book, sheet)
|
