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
|
# -*- coding: utf-8 -*-
from __future__ import division, print_function, absolute_import
import locale
from petl.compat import izip_longest, next, xrange, BytesIO
from petl.util.base import Table
from petl.io.sources import read_source_from_arg, write_source_from_arg
def fromxls(filename, sheet=None, use_view=True, **kwargs):
"""
Extract a table from a sheet in an Excel .xls file.
Sheet is identified by its name or index number.
N.B., the sheet name is case sensitive.
"""
return XLSView(filename, sheet=sheet, use_view=use_view, **kwargs)
class XLSView(Table):
def __init__(self, filename, sheet=None, use_view=True, **kwargs):
self.filename = filename
self.sheet = sheet
self.use_view = use_view
self.kwargs = kwargs
def __iter__(self):
# prefer implementation using xlutils.view as dates are automatically
# converted
if self.use_view:
from petl.io import xlutils_view
source = read_source_from_arg(self.filename)
with source.open('rb') as source2:
source3 = source2.read()
wb = xlutils_view.View(source3, **self.kwargs)
if self.sheet is None:
ws = wb[0]
else:
ws = wb[self.sheet]
for row in ws:
yield tuple(row)
else:
import xlrd
source = read_source_from_arg(self.filename)
with source.open('rb') as source2:
source3 = source2.read()
with xlrd.open_workbook(file_contents=source3,
on_demand=True, **self.kwargs) as wb:
if self.sheet is None:
ws = wb.sheet_by_index(0)
elif isinstance(self.sheet, int):
ws = wb.sheet_by_index(self.sheet)
else:
ws = wb.sheet_by_name(str(self.sheet))
for rownum in xrange(ws.nrows):
yield tuple(ws.row_values(rownum))
def toxls(tbl, filename, sheet, encoding=None, style_compression=0,
styles=None):
"""
Write a table to a new Excel .xls file.
"""
import xlwt
if encoding is None:
encoding = locale.getpreferredencoding()
wb = xlwt.Workbook(encoding=encoding, style_compression=style_compression)
ws = wb.add_sheet(sheet)
if styles is None:
# simple version, don't worry about styles
for r, row in enumerate(tbl):
for c, v in enumerate(row):
ws.write(r, c, label=v)
else:
# handle styles
it = iter(tbl)
try:
hdr = next(it)
flds = list(map(str, hdr))
for c, f in enumerate(flds):
ws.write(0, c, label=f)
if f not in styles or styles[f] is None:
styles[f] = xlwt.Style.default_style
except StopIteration:
pass # no header written
# convert to list for easy zipping
styles = [styles[f] for f in flds]
for r, row in enumerate(it):
for c, (v, style) in enumerate(izip_longest(row, styles,
fillvalue=None)):
ws.write(r+1, c, label=v, style=style)
target = write_source_from_arg(filename)
with target.open('wb') as target2:
wb.save(target2)
Table.toxls = toxls
|
