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
|
"""
Some io tools for excel -- requires pypyExcelerator
Example usage:
import matplotlib.mlab as mlab
import mpl_toolkits.exceltools as exceltools
r = mlab.csv2rec('somefile.csv', checkrows=0)
formatd = dict(
weight = mlab.FormatFloat(2),
change = mlab.FormatPercent(2),
cost = mlab.FormatThousands(2),
)
exceltools.rec2excel(r, 'test.xls', formatd=formatd)
mlab.rec2csv(r, 'test.csv', formatd=formatd)
"""
import copy
import numpy as npy
import pyExcelerator as excel
import matplotlib.cbook as cbook
import matplotlib.mlab as mlab
def xlformat_factory(format):
"""
copy the format, perform any overrides, and attach an xlstyle instance
copied format is returned
"""
format = copy.deepcopy(format)
xlstyle = excel.XFStyle()
if isinstance(format, mlab.FormatPercent):
zeros = ''.join(['0']*format.precision)
xlstyle.num_format_str = '0.%s%%;[RED]-0.%s%%'%(zeros, zeros)
format.scale = 1.
elif isinstance(format, mlab.FormatFloat):
zeros = ''.join(['0']*format.precision)
xlstyle.num_format_str = '#,##0.%s;[RED]-#,##0.%s'%(zeros, zeros)
elif isinstance(format, mlab.FormatInt):
xlstyle.num_format_str = '#,##;[RED]-#,##'
else:
xlstyle = None
format.xlstyle = xlstyle
return format
def rec2excel(r, ws, formatd=None, rownum=0, colnum=0):
"""
save record array r to excel pyExcelerator worksheet ws
starting at rownum. if ws is string like, assume it is a
filename and save to it
start writing at rownum, colnum
formatd is a dictionary mapping dtype name -> mlab.Format instances
The next rownum after writing is returned
"""
autosave = False
if cbook.is_string_like(ws):
filename = ws
wb = excel.Workbook()
ws = wb.add_sheet('worksheet')
autosave = True
if formatd is None:
formatd = dict()
formats = []
font = excel.Font()
font.bold = True
stylehdr = excel.XFStyle()
stylehdr.font = font
for i, name in enumerate(r.dtype.names):
dt = r.dtype[name]
format = formatd.get(name)
if format is None:
format = mlab.defaultformatd.get(dt.type, mlab.FormatObj())
format = xlformat_factory(format)
ws.write(rownum, colnum+i, name, stylehdr)
formats.append(format)
rownum+=1
ind = npy.arange(len(r.dtype.names))
for row in r:
for i in ind:
val = row[i]
format = formats[i]
val = format.toval(val)
if format.xlstyle is None:
ws.write(rownum, colnum+i, val)
else:
if mlab.safe_isnan(val):
ws.write(rownum, colnum+i, 'NaN')
else:
ws.write(rownum, colnum+i, val, format.xlstyle)
rownum += 1
if autosave:
wb.save(filename)
return rownum
|
