def genAttendanceSheet(lis):
""" lis is UserRecord list """
sheet2 = wb.add_sheet(ATTENDANCE_SHEET_NAME, True)
percentStyle = XFStyle()
percentStyle.num_format_str = '0%'
integerStyle = XFStyle()
integerStyle.num_format_str = '0'
titles = ('姓名', '总工作日（天）', '总工时（小时）', '个人总出勤（小时）', '个人日均（小时）', '个人出勤率')
row = 0
for i, item in enumerate(titles):
sheet2.write(row, i, item)
row += 1
workdays = workdayIntVar.get()
workhours = workdays * 8
lis.sort(key=attrgetter('totalTime'), reverse=True)
for u in lis:
hours = round(u.totalTime / 3600.0)
sheet2.write(row, 0, u.userName)
sheet2.write(row, 1, workdays)
sheet2.write(row, 2, workhours, integerStyle)
sheet2.write(row, 3, hours, integerStyle)
sheet2.write(row, 4, hours / workdays, integerStyle)
sheet2.write(row, 5, round(hours / workhours, 2), percentStyle)
row += 1
def style_rd2wt(self, rdbook, rdxf):
wtxf = XFStyle()
#
# number format
#
wtxf.num_format_str = rdbook.format_map[rdxf.format_key].format_str
#
# font
#
wtf = wtxf.font
rdf = rdbook.font_list[rdxf.font_index]
wtf.height = rdf.height
wtf.italic = rdf.italic
wtf.struck_out = rdf.struck_out
wtf.outline = rdf.outline
wtf.shadow = rdf.outline
wtf.colour_index = rdf.colour_index
wtf.bold = rdf.bold #### This attribute is redundant, should be driven by weight
wtf._weight = rdf.weight #### Why "private"?
wtf.escapement = rdf.escapement
wtf.underline = rdf.underline_type ####
# wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing
wtf.family = rdf.family
wtf.charset = rdf.character_set
wtf.name = rdf.name
#
# protection
#
wtp = wtxf.protection
rdp = rdxf.protection
wtp.cell_locked = rdp.cell_locked
wtp.formula_hidden = rdp.formula_hidden
#
# border(s) (rename ????)
#
wtb = wtxf.borders
rdb = rdxf.border
# wtb.left = rdb.left_line_style
# wtb.right = rdb.right_line_style
# wtb.top = rdb.top_line_style
# wtb.bottom = rdb.bottom_line_style
wtb.left = 1
wtb.right = 1
wtb.top = 1
wtb.bottom = 1
wtb.diag = rdb.diag_line_style
wtb.left_colour = rdb.left_colour_index
wtb.right_colour = rdb.right_colour_index
wtb.top_colour = rdb.top_colour_index
wtb.bottom_colour = rdb.bottom_colour_index
wtb.diag_colour = rdb.diag_colour_index
wtb.need_diag1 = rdb.diag_down
wtb.need_diag2 = rdb.diag_up
#
# background / pattern (rename???)
#
wtpat = wtxf.pattern
rdbg = rdxf.background
wtpat.pattern = rdbg.fill_pattern
wtpat.pattern_fore_colour = rdbg.pattern_colour_index
wtpat.pattern_back_colour = rdbg.background_colour_index
#
# alignment
#
wta = wtxf.alignment
rda = rdxf.alignment
wta.horz = rda.hor_align
wta.vert = rda.vert_align
wta.dire = rda.text_direction
# wta.orie # orientation doesn't occur in BIFF8! Superceded by rotation ("rota").
wta.rota = rda.rotation
wta.wrap = rda.text_wrapped
wta.shri = rda.shrink_to_fit
wta.inde = rda.indent_level
# wta.merg = ????
#
return wtxf
def create(self, params, filename=None):
"""
IN
params <dict>
filename <str> (opcional)
OUT
El XML del informe
<str>
"""
util = self.Util()
self.tmpl.globals['util'] = util
self.tmpl.globals['xlrd'] = xlrd
informe_xml = self.tmpl.render(**params).encode('utf-8')
# f = file('/home/leon/informe.xml', 'wb')
# f.write(informe_xml)
# f.close()
root = etree.fromstring(informe_xml)
wb = Workbook()
estilos = {}
xfs0 = XFStyle()
n_sheet = 0
for sheet in root.iter('sheet'):
n_sheet += 1
# title
title = 'Sheet-%d' % n_sheet
if sheet.attrib.has_key('title') and sheet.attrib['title']:
title = sheet.attrib['title']
ws = wb.add_sheet(title, True)
util.init_count()
for item in sheet:
# style
if item.tag == 'style':
estilos[item.attrib['name']] = easyxf(';'.join(self.calcular_estilo(item)))
# image
if item.tag == 'image':
bm_path = item.find('path').text
col = int(item.attrib.get('col', 0))
scale_x = float(item.attrib.get('scale_x', '1'))
scale_y = float(item.attrib.get('scale_y', '1'))
ws.insert_bitmap(bm_path, util.current_line(), col, scale_x=scale_x, scale_y=scale_y)
# cell
if item.tag == 'cell':
col = int(item.attrib['col'])
num_format = None
value = item.find('value')
dato = value.text
# type: aplicar una conversión (str, int, float, ...)
if item.attrib.has_key('type') and item.attrib['type']:
tipo = item.attrib['type'].split(';')
# date
if tipo[0] == 'date':
try:
dato = strtodate(dato, fmt='%Y-%m-%d')
except:
dato = None
# time
elif tipo[0] == 'time':
try:
dato = strtotime(dato)
except:
dato = None
# formula
elif tipo[0] == 'formula':
dato = Formula(dato)
# resto de tipos
else:
if dato:
try:
f = lambda t,v: t(v)
dato = f(eval(tipo[0]), dato)
except:
dato = 'ERROR! (%s)' % dato
# date;dd/mm/yyyy
# date;dd/mmm/yyyy
# date;NN D MMMM YYYY
# time;hh:mm
# float;#,##0.00
# float;+#,##0.00
# float;#,##0.00;[RED]-#,##0.00
if len(tipo) > 1:
num_format = ';'.join(tipo[1:])
#print dato, tipo, num_format
# style: aplicar un estilo
estilo = item.find('style')
xfs = xfs0
if estilo != None:
if not estilos.has_key(estilo.attrib['name']):
xfs = easyxf(';'.join(self.calcular_estilo(estilo)))
estilos[estilo.attrib['name']] = xfs
else:
xfs = estilos[estilo.attrib['name']]
xfs.num_format_str = 'General'
if num_format:
if not xfs:
xfs = XFStyle()
xfs.num_format_str = num_format
# merge
if item.attrib.has_key('merge') and item.attrib['merge']:
try:
_merge = re.search(r'(\d+)\s+(\d+)', item.attrib['merge'], re.U)
if _merge:
hg = int(_merge.group(1))
wd = int(_merge.group(2))
_current_line = util.current_line()
ws.write_merge(_current_line, _current_line + hg - 1,
col, col + wd -1,
dato, xfs)
else:
raise Exception(u'merge: Invalid format')
except Exception:
ws.write(util.current_line(), col, dato, xfs)
else:
ws.write(util.current_line(), col, dato, xfs)
elif item.tag == 'line_feed':
n = int(item.attrib.get('n', 1))
util.line_feed(n)
elif item.tag == 'bookmark':
util.add_bookmark(item.attrib['name'])
if filename:
wb.save(filename)
return informe_xml
def style_rd2wt(self, rdbook, rdxf):
wtxf = XFStyle()
#
# number format
#
wtxf.num_format_str = rdbook.format_map[rdxf.format_key].format_str
#
# font
#
wtf = wtxf.font
rdf = rdbook.font_list[rdxf.font_index]
wtf.height = rdf.height
wtf.italic = rdf.italic
wtf.struck_out = rdf.struck_out
wtf.outline = rdf.outline
wtf.shadow = rdf.outline
wtf.colour_index = rdf.colour_index
wtf.bold = rdf.bold #### This attribute is redundant, should be driven by weight
wtf._weight = rdf.weight #### Why "private"?
wtf.escapement = rdf.escapement
wtf.underline = rdf.underline_type ####
# wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing
wtf.family = rdf.family
wtf.charset = rdf.character_set
wtf.name = rdf.name
#
# protection
#
wtp = wtxf.protection
rdp = rdxf.protection
wtp.cell_locked = rdp.cell_locked
wtp.formula_hidden = rdp.formula_hidden
#
# border(s) (rename ????)
#
wtb = wtxf.borders
rdb = rdxf.border
# wtb.left = rdb.left_line_style
# wtb.right = rdb.right_line_style
# wtb.top = rdb.top_line_style
# wtb.bottom = rdb.bottom_line_style
wtb.left = 1
wtb.right = 1
wtb.top = 1
wtb.bottom = 1
wtb.diag = rdb.diag_line_style
wtb.left_colour = rdb.left_colour_index
wtb.right_colour = rdb.right_colour_index
wtb.top_colour = rdb.top_colour_index
wtb.bottom_colour = rdb.bottom_colour_index
wtb.diag_colour = rdb.diag_colour_index
wtb.need_diag1 = rdb.diag_down
wtb.need_diag2 = rdb.diag_up
#
# background / pattern (rename???)
#
wtpat = wtxf.pattern
rdbg = rdxf.background
wtpat.pattern = rdbg.fill_pattern
wtpat.pattern_fore_colour = rdbg.pattern_colour_index
wtpat.pattern_back_colour = rdbg.background_colour_index
#
# alignment
#
wta = wtxf.alignment
rda = rdxf.alignment
wta.horz = rda.hor_align
wta.vert = rda.vert_align
wta.dire = rda.text_direction
# wta.orie # orientation doesn't occur in BIFF8! Superceded by rotation ("rota").
wta.rota = rda.rotation
wta.wrap = rda.text_wrapped
wta.shri = rda.shrink_to_fit
wta.inde = rda.indent_level
# wta.merg = ????
#
return wtxf
def add_ideogram_table(self, analyses,
widths=None,
# configure_table=True,
add_title=False, add_header=False, tablenum=1, **kw):
sh = self._wb.add_sheet(analyses[0].labnumber)
# 'sheet{:02n}'.format(self._sheet_count))
self._sheet_count += 1
cols = ['Status', 'N', 'Power', 'Moles_40Ar',
'Ar40', 'Ar40Er',
'Ar39', 'Ar39Er',
'Ar38', 'Ar38Er',
'Ar37', 'Ar37Er',
'Ar36', 'Ar36Er',
'Blank',
'Ar40', 'Ar40Er',
'Ar39', 'Ar39Er',
'Ar38', 'Ar38Er',
'Ar37', 'Ar37Er',
'Ar36', 'Ar36Er'
]
# write header row
hrow = 0
borders = Borders()
borders.bottom = 2
style = XFStyle()
style.borders = borders
for i, ci in enumerate(cols):
sh.write(hrow, i, ci, style=style)
numstyle = XFStyle()
numstyle.num_format_str = '0.00'
from operator import attrgetter
def getnominal_value(attr):
def get(x):
v = getattr(x, attr)
return float(v.nominal_value)
return get
def getstd_dev(attr):
def get(x):
v = getattr(x, attr)
return float(v.std_dev)
return get
def getfloat(attr):
def get(x):
v = getattr(x, attr)
return float(v)
return get
def null_value():
return lambda x: ''
attrs = [(attrgetter('step'),),
(getfloat('extract_value'),),
(null_value(),),
(getnominal_value('Ar40'),),
(getstd_dev('Ar40'),),
(getnominal_value('Ar39'),),
(getstd_dev('Ar39'),),
(getnominal_value('Ar38'),),
(getstd_dev('Ar38'),),
(getnominal_value('Ar37'),),
(getstd_dev('Ar37'),),
(getnominal_value('Ar36'),),
(getstd_dev('Ar36'),),
(null_value(),),
(getnominal_value('Ar40_blank'),),
(getstd_dev('Ar40_blank'),),
(getnominal_value('Ar39_blank'),),
(getstd_dev('Ar39_blank'),),
(getnominal_value('Ar38_blank'),),
(getstd_dev('Ar38_blank'),),
(getnominal_value('Ar37_blank'),),
(getstd_dev('Ar37_blank'),),
(getnominal_value('Ar36_blank'),),
(getstd_dev('Ar36_blank'),),
]
for i, ai in enumerate(analyses):
for j, arg in enumerate(attrs):
if len(arg) == 2:
func, style = arg
else:
func, style = arg[0], Style.default_style
sh.write(i + hrow + 1, 1 + j, func(ai),
style
)
def genTotalSheet(lis):
""" lis is UserRecord list """
global wb
wb = Workbook(encoding='gbk')
timeStyle = XFStyle()
timeStyle.num_format_str = 'h:mm'
lateStyle = XFStyle()
lateStyle.num_format_str = 'h:mm'
lateStyle.font = Font()
### when isLate = false, set colour_index = 0 will still get red font in Excel.
lateStyle.font.colour_index = 2
earlyStyle = XFStyle()
earlyStyle.num_format_str = 'h:mm'
earlyStyle.font = Font()
earlyStyle.font.colour_index = 7
sheet1 = wb.add_sheet(TOTAL_SHEET_NAME)
rowNum = 0
for i, s in enumerate(totalSheetTitles): # write header
sheet1.write(0, i, str(s))
rowNum += 1
for a in lis:
for colNum, colName in enumerate(totalSheetTitles):
if(colNum == 0):
sheet1.write_merge(rowNum, rowNum + 1, colNum, colNum, a.userId)
elif(colNum == 1):
sheet1.write_merge(rowNum, rowNum + 1, colNum, colNum, a.userName)
else:
onTime = ''
offTime = ''
if(colName in a.recDict):
times = a.recDict[colName]
if(len(times) >= 2):
if(len(times) > 2):
print '! ' + a.userName + ' ' + monthStr + str(colName) + ' 两条以上打卡记录.'
onTime = times[0]
offTime = times[len(times) - 1]
elif(len(times) == 1):
onlyTime = strToTime(times[0])
if(onlyTime.hour < divider):
onTime = times[0]
else:
offTime = times[0]
onTime = strToTime(onTime)
offTime = strToTime(offTime)
# if(onTime and offTime):
# a.totalTime += (offTime - onTime).seconds / 3600
isLate, isEarly = checkTime(onTime, offTime, colName)
if(isLate): # late
# a.late += 1
sheet1.write(rowNum, colNum, onTime, lateStyle)
else:
sheet1.write(rowNum, colNum, onTime, timeStyle)
if(isEarly): # early
# a.early += 1
sheet1.write(rowNum + 1, colNum, offTime, earlyStyle)
else:
sheet1.write(rowNum + 1, colNum, offTime, timeStyle)
rowNum += 2
getTargetFileName()
try:
wb.save(targetFileName)
addLog('"' + targetFileName + '" is in the same directory of the .txt file.')
except IOError: #IOError: [Errno 13] Permission denied: '***.xls'
addLog('please close ' + targetFileName + ' and retry')
def create(self, params, filename=None):
"""
IN
params <dict>
filename <str> (opcional)
OUT
El XML del informe
<str>
"""
util = self.Util()
self.tmpl.globals['util'] = util
self.tmpl.globals['xlrd'] = xlrd
informe_xml = self.tmpl.render(**params).encode('utf-8')
root = etree.fromstring(informe_xml)
wb = Workbook()
estilos = {}
xfs0 = XFStyle()
n_sheet = 0
for sheet in root.iter('sheet'):
n_sheet += 1
cols_width = None
# title
title = sheet.attrib.get('title', 'Sheet-%d' % n_sheet)
# auto-width
sheet_width = sheet.attrib.get('width')
cols_width = {}
ws = wb.add_sheet(title, True)
util.init_count()
for item in sheet:
# style
if item.tag == 'style':
estilos[item.attrib['name']] = easyxf(';'.join(self.calcular_estilo(item)))
# image
if item.tag == 'image':
bm_path = item.find('path').text
col = int(item.attrib.get('col', 0))
scale_x = float(item.attrib.get('scale_x', '1'))
scale_y = float(item.attrib.get('scale_y', '1'))
ws.insert_bitmap(bm_path, util.current_line(), col, scale_x=scale_x, scale_y=scale_y)
# cell
if item.tag == 'cell':
col = int(item.attrib['col'])
num_format = None
# width
width = item.attrib.get('width', sheet_width)
if width is not None:
if not cols_width.get(col):
cols_width[col] = dict(auto=width == 'auto',
width=int(width) if width != 'auto' else 0)
# value
value = item.find('value')
dato = value.text
# type: aplicar una conversión (str, int, float, ...)
type_ = item.attrib.get('type')
if type_:
tipo = type_.split(';')
# date
if tipo[0] == 'date':
try:
dato = strtodate(dato, fmt='%Y-%m-%d')
except:
dato = None
# time
elif tipo[0] == 'time':
try:
dato = strtotime(dato)
except:
dato = None
# formula
elif tipo[0] == 'formula':
dato = Formula(dato)
# resto de tipos
else:
if dato != '':
try:
f = lambda t,v: t(v)
dato = f(eval(tipo[0]), dato)
except:
dato = None
# date;dd/mm/yyyy
# date;dd/mmm/yyyy
# date;NN D MMMM YYYY
# time;hh:mm
# float;#,##0.00
# float;+#,##0.00
# float;#,##0.00;[RED]-#,##0.00
if len(tipo) > 1:
num_format = ';'.join(tipo[1:])
#print dato, tipo, num_format
# style: aplicar un estilo
estilo = item.find('style')
xfs = xfs0
if estilo != None:
if not estilos.has_key(estilo.attrib['name']):
xfs = easyxf(';'.join(self.calcular_estilo(estilo)))
estilos[estilo.attrib['name']] = xfs
else:
xfs = estilos[estilo.attrib['name']]
xfs.num_format_str = 'General'
if num_format:
if not xfs:
xfs = XFStyle()
xfs.num_format_str = num_format
# merge
if item.attrib.has_key('merge') and item.attrib['merge']:
try:
_merge = re.search(r'(\d+)\s+(\d+)', item.attrib['merge'], re.U)
if _merge:
hg = int(_merge.group(1))
wd = int(_merge.group(2))
_current_line = util.current_line()
ws.write_merge(_current_line, _current_line + hg - 1,
col, col + wd -1,
dato, xfs)
else:
raise Exception(u'merge: Invalid format')
except Exception:
ws.write(util.current_line(), col, dato, xfs)
else:
ws.write(util.current_line(), col, dato, xfs)
# width
colw = cols_width.get(col, None)
if colw is not None:
if colw['auto']:
try:
width_ = len(str(dato))
except:
width_ = len(unicode(dato))
if width_ > colw['width']:
cols_width[col]['width'] = width_
elif item.tag == 'line_feed':
n = int(item.attrib.get('n', 1))
util.line_feed(n)
elif item.tag == 'bookmark':
util.add_bookmark(item.attrib['name'])
# width
if cols_width is not None:
for col, colw in cols_width.iteritems():
if colw['width'] < 10:
colw['width'] = colw['width'] + 2
ws.col(col).width = colw['width']*256
if filename:
wb.save(filename)
return informe_xml
