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