恭请批评
# Encoding: utf-8
import xlrd
import xlwt
import datetime as DateTime
style_date = xlwt.easyxf(
'font: name Times New Roman',
num_format_str='YYYY-MM-DD' )
style_datetime = xlwt.easyxf(
'font: name Times New Roman',
num_format_str='YYYY-MM-DD HH:MM:SS' )
style_money = xlwt.easyxf(
'font: name Times New Roman',
num_format_str='####.00')
style_int = xlwt.easyxf(
'font: name Times New Roman',
num_format_str='####')
dttryformats = ('%Y-%m-%d %H:%M:%S',
'%Y.%m.%d %H:%M:%S',
'%Y/%m/%d %H:%M:%S',
'%Y%m%d%H%M%S',
'%Y%m%d%H:%M:%S',
'%Y-%m-%d',
'%Y.%m.%d',
'%Y/%m/%d',
'%Y%m%d',
)
def trys2dt(s):
for format in dttryformats:
try:
return DateTime.datetime.strptime(s,format)
except:
continue
else:
return s
def _tp(thetype, value):
return value if not thetype or type(value) == unicode else thetype(value)
def dt(xldate):
return xlrd.xldate_as_tuple(xldate, datemode=0)
class ExcelFile:
def __init__(self):
self.handle, self.book = None, None
self.sheets = {}
def read(self, filename):
self.handle = xlrd.open_workbook(filename)
self.sheets = dict([(sheetname,self.handle.sheet_by_name(sheetname))
for sheetname in self.handle.sheet_names()])
def sheet_row(self, sheetname, rownumber = 0):
if sheetname not in self.sheets: return None
return self.sheets[sheetname].row_values(rownumber)
def sheet_datas(self, sheetname, start_row, types = []):
for r in xrange(start_row, self.sheets[sheetname].nrows, 1):
try:
yield map(_tp, types, self.sheets[sheetname].row_values(r))
except Exception:
pass
def addsheet(self, name, title, data, styles=[]):
''' 添加Sheet页
name: 指定sheet页标签
title: 指定各列标题
data: 数组数据
styles: 指定各列格式
'''
if not self.book: self.book = xlwt.Workbook()
if name not in self.sheets:
self.sheets[name] = self.book.add_sheet(name)
sheet = self.sheets[name]
styles = ['Normal' if not item else item for item in styles]
r = 0
def write(d):
i, c = d
c = u'' if not c else (c if type(c)!=str else unicode(c,'gbk'))
style = 'Normal' if i<0 or i>=len(styles) else styles[i]
if style == 'DATE':
sheet.write(r, i, c, style_date)
elif style == 'DATETIME':
if type(c)==DateTime.datetime:
sheet.write(r, i, c, style_datetime)
else:
sheet.write(r, i, trys2dt(c), style_datetime)
elif style == 'MONEY':
sheet.write(r, i, c, style_money)
elif style == 'INT':
sheet.write(r, i, c, style_int)
elif style:
try:
sheet.write(r, i, c, style)
except:
sheet.write(r, i, c)
else:
sheet.write(r, i, c)
map( write, enumerate(title) )
for rid, row in enumerate(data):
r = rid+1
map( write, enumerate(row) )
def saveto(self, filename):
self.book.save(filename)
