|
| 1 | +""" |
| 2 | +Some io tools for excel -- requires pypyExcelerator |
| 3 | +
|
| 4 | +Example usage: |
| 5 | +
|
| 6 | + import matplotlib.mlab as mlab |
| 7 | + import matplotlib.toolkits.exceltools as exceltools |
| 8 | + |
| 9 | + r = mlab.csv2rec('somefile.csv', checkrows=0) |
| 10 | +
|
| 11 | + formatd = dict( |
| 12 | + weight = mlab.FormatFloat(2), |
| 13 | + change = mlab.FormatPercent(2), |
| 14 | + cost = mlab.FormatThousands(2), |
| 15 | + ) |
| 16 | +
|
| 17 | +
|
| 18 | + exceltools.rec2excel(r, 'test.xls', formatd=formatd) |
| 19 | + mlab.rec2csv(r, 'test.csv', formatd=formatd) |
| 20 | +
|
| 21 | +""" |
| 22 | +import copy |
| 23 | +import numpy as npy |
| 24 | +import pyExcelerator as excel |
| 25 | +import matplotlib.cbook as cbook |
| 26 | +import matplotlib.mlab as mlab |
| 27 | + |
| 28 | + |
| 29 | +def xlformat_factory(format): |
| 30 | + """ |
| 31 | + copy the format, perform any overrides, and attach an xlstyle instance |
| 32 | + copied format is returned |
| 33 | + """ |
| 34 | + format = copy.deepcopy(format) |
| 35 | + |
| 36 | + |
| 37 | + |
| 38 | + xlstyle = excel.XFStyle() |
| 39 | + if isinstance(format, mlab.FormatPercent): |
| 40 | + zeros = ''.join(['0']*format.precision) |
| 41 | + xlstyle.num_format_str = '0.%s%%;[RED]-0.%s%%'%(zeros, zeros) |
| 42 | + format.scale = 1. |
| 43 | + elif isinstance(format, mlab.FormatFloat): |
| 44 | + zeros = ''.join(['0']*format.precision) |
| 45 | + xlstyle.num_format_str = '#,##0.%s;[RED]-#,##0.%s'%(zeros, zeros) |
| 46 | + elif isinstance(format, mlab.FormatInt): |
| 47 | + xlstyle.num_format_str = '#,##;[RED]-#,##' |
| 48 | + else: |
| 49 | + xlstyle = None |
| 50 | + |
| 51 | + format.xlstyle = xlstyle |
| 52 | + |
| 53 | + return format |
| 54 | + |
| 55 | +def rec2excel(r, ws, formatd=None, rownum=0, colnum=0): |
| 56 | + """ |
| 57 | + save record array r to excel pyExcelerator worksheet ws |
| 58 | + starting at rownum. if ws is string like, assume it is a |
| 59 | + filename and save to it |
| 60 | +
|
| 61 | + start writing at rownum, colnum |
| 62 | +
|
| 63 | + formatd is a dictionary mapping dtype name -> mlab.Format instances |
| 64 | +
|
| 65 | + The next rownum after writing is returned |
| 66 | + """ |
| 67 | + |
| 68 | + autosave = False |
| 69 | + if cbook.is_string_like(ws): |
| 70 | + filename = ws |
| 71 | + wb = excel.Workbook() |
| 72 | + ws = wb.add_sheet('worksheet') |
| 73 | + autosave = True |
| 74 | + |
| 75 | + |
| 76 | + if formatd is None: |
| 77 | + formatd = dict() |
| 78 | + |
| 79 | + formats = [] |
| 80 | + font = excel.Font() |
| 81 | + font.bold = True |
| 82 | + |
| 83 | + stylehdr = excel.XFStyle() |
| 84 | + stylehdr.font = font |
| 85 | + |
| 86 | + for i, name in enumerate(r.dtype.names): |
| 87 | + dt = r.dtype[name] |
| 88 | + format = formatd.get(name) |
| 89 | + if format is None: |
| 90 | + format = mlab.defaultformatd.get(dt.type, mlab.FormatObj()) |
| 91 | + |
| 92 | + format = xlformat_factory(format) |
| 93 | + ws.write(rownum, colnum+i, name, stylehdr) |
| 94 | + formats.append(format) |
| 95 | + |
| 96 | + rownum+=1 |
| 97 | + |
| 98 | + |
| 99 | + ind = npy.arange(len(r.dtype.names)) |
| 100 | + for row in r: |
| 101 | + for i in ind: |
| 102 | + val = row[i] |
| 103 | + format = formats[i] |
| 104 | + val = format.toval(val) |
| 105 | + if format.xlstyle is None: |
| 106 | + ws.write(rownum, colnum+i, val) |
| 107 | + else: |
| 108 | + if mlab.safe_isnan(val): |
| 109 | + ws.write(rownum, colnum+i, 'NaN') |
| 110 | + else: |
| 111 | + ws.write(rownum, colnum+i, val, format.xlstyle) |
| 112 | + rownum += 1 |
| 113 | + |
| 114 | + if autosave: |
| 115 | + wb.save(filename) |
| 116 | + return rownum |
| 117 | + |
| 118 | + |
| 119 | + |
| 120 | + |
0 commit comments