def set_style(self, isblod): style = XFStyle() fnt = Font() # 创建一个文本格式,包括字体、字号和颜色样式特性 fnt.name = u'微软雅黑' # 设置其字体为微软雅黑 fnt.bold = isblod style.font = fnt return style
def get_styles(): style = easyxf( 'font: name Arial;' ) style_s = easyxf( 'font: name Arial;' 'borders: left thin, right thin, top medium, bottom thin;' ) style_m = easyxf( 'font: name Arial, bold True;' 'borders: left thin, right thin, top thin, bottom thin;' ) font = Font() font.bold = 1 font.height = 320 style.font = font style.alignment.horz = Alignment.HORZ_LEFT style.alignment.vert = Alignment.VERT_CENTER style_s.font.height = 200 style_s.font.bold = 1 style_s.alignment.horz = Alignment.HORZ_CENTER style_s.alignment.vert = Alignment.VERT_CENTER style_s.alignment.wrap = True return style, style_s, style_m
def export_to_excel(self, **kwargs): work_book = Workbook(encoding="ascii") center_alignment = Alignment() center_alignment.horz = Alignment.HORZ_RIGHT center_alignment.vert = Alignment.VERT_CENTER border = Borders() border.top = Borders.THIN border.left = Borders.THIN border.bottom = Borders.THIN border.right = Borders.THIN head_style = XFStyle() head_pattern = Pattern() head_pattern.pattern = Pattern.SOLID_PATTERN head_pattern.pattern_fore_colour = Style.colour_map['gray25'] head_style.pattern = head_pattern head_font = Font() head_font.bold = True head_style.font = head_font head_style.alignment = center_alignment head_style.borders = border data_style = XFStyle() data_style.alignment = center_alignment data_style.borders = border work_sheet = work_book.add_sheet("Sheet") limit = 10000 count = self.get_data_scale(**kwargs)['count'] times = (count + limit - 1) // limit add_header = False for i in range(1): kwargs["startIndex"] = i * limit + 1 kwargs['endIndex'] = (i + 1) * limit + 1 records = self.get_data(**kwargs) column_names = [] y = 0 for data in records: if not add_header: x = 0 for column_name in data.keys(): work_sheet.write(y, x, column_name, head_style) column_names.append(column_name) x += 1 add_header = True y += 1 x = 0 for column_name in column_names: work_sheet.write(y, x, data[column_name], data_style) x += 1 y += 1 x = 0 work_book.save(BASE_WIN_OUTPUT_PATH + "result.xls")
def RUNN(): # ghi file excel fnt = Font() fnt.name = "Times New Roman" fnt.height = 280 borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style = XFStyle() style.font = fnt style.borders = borders wb = Workbook() sheet1 = wb.add_sheet("Điểm") col = 0 row = 0 sheet1.write(row, col, "STT", style) sheet1.write(row, col + 1, "TÊN BÀI LÀM", style) sheet1.write(row, col + 2, "ĐIỂM", style) row += 1 diem = [] st = 1 for i in t: if i == name_form: continue else: try: t1 = docx.Document(i) sheet1.write(row, col, st, style) sheet1.write(row, col + 1, i[:-5], style) sheet1.write(row, col + 2, round(10 * so_sanh(t1), 2), style) row += 1 st += 1 except Exception: pass wb.save("KetQua.xls")
def getDefualtStyle(): fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN #pattern = Pattern() #pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF #pattern.pattern_fore_colour = 0x37 alignment = Alignment() #alignment.horizontal = Alignment.HORZ_LEFT alignment.horizontal = Alignment.HORZ_RIGHT style = XFStyle() #~ style.num_format_str='0.000%' #~ style.num_format_str='0+' #~ style.font = fnt style.align = alignment #style.borders = borders #~ style.pattern = pattern return style
def defineFontStyle(argFontName,argIsBold): style = XFStyle() font = Font() font.name = argFontName font.bold = argIsBold style.font = font return style
def getDefualtStyle(): fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN #pattern = Pattern() #pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF #pattern.pattern_fore_colour = 0x37 alignment = Alignment() #alignment.horizontal = Alignment.HORZ_LEFT alignment.horizontal = Alignment.HORZ_RIGHT style = XFStyle() #~ style.num_format_str='0.000%' #~ style.num_format_str='0+' #~ style.font = fnt style.align = alignment style.borders = borders #~ style.pattern = pattern return style
def get_report_info_perid( self, cmd, diff_time, thre_time, max_number, ): # 发送要执行的命令 pre_time_stamp = [0] * 4 self._channel.send(cmd + '\r') # 回显很长的命令可能执行较久,通过循环分批次取回回显 index = [0] * 4 line_counter = 0 line_feed_byte = '\n'.encode(self.encoding) while True: buffer = self._channel.recv(65535) if len(buffer) == 0: logger.info('end______________') break lines = buffer.split(line_feed_byte) for current_line in lines: style = None line = current_line.decode(self.encoding) # logger.debug('shell显示:%s' % line) col = self.check_type(line) if not line.startswith(self.rq) or col == 10: line_counter += 1 continue time_stamp = int( time.mktime( time.strptime(' '.join([line[:8], line[9:17]]), "%Y%m%d %H:%M:%S"))) time_stamp_dec = line[18:21] # 精确到毫秒 time_stamp = time_stamp * 1000 + int(time_stamp_dec) logger.info('%s:%s' % (senior_name[col], time_stamp)) if pre_time_stamp[col] == 0: pre_time_stamp[col] = time_stamp else: if abs((time_stamp - pre_time_stamp[col]) - diff_time[col]) > thre_time[col]: logger.error('两帧数据间隔为{}ms,时间戳分别为:({},{}),行号:{}'.format( time_stamp - pre_time_stamp[col], time_stamp, pre_time_stamp[col], index[col])) style = XFStyle() fnt = Font() fnt.name = u'微软雅黑' # 设置其字体为微软雅黑 fnt.colour_index = 2 # 设置其字体颜色 fnt.bold = True style.font = fnt self.write_xl(index[col] + 1, col * 2, time_stamp) self.write_xl(index[col] + 1, col * 2 + 1, time_stamp - pre_time_stamp[col], style=style) index[col] += 1 pre_time_stamp[col] = time_stamp line_counter += 1
class Column(Cell): title_fnt = Font() title_fnt.name = 'Calibri' title_fnt.bold = True title_fnt.height = 12 * 20 # font size 12 obj_fnt = Font() obj_fnt.name = 'Calibri' obj_fnt.height = 12 * 20 # font size 12 title_aln = Alignment() title_aln.horz = Alignment.HORZ_CENTER title_aln.vert = Alignment.VERT_CENTER title_aln.wrap = Alignment.WRAP_AT_RIGHT title_ptn = Pattern() title_ptn.pattern = Pattern.SOLID_PATTERN title_ptn.pattern_fore_colour = 0x2C def __init__(self, text, left_border=False, right_border=False, use_pattern=False, width=0x0E00, func=lambda obj: ""): super(Column, self).__init__(text, self.title_fnt, self.title_aln, Borders(), self.title_ptn, width=width) self.obj_style = XFStyle() self.obj_style.alignment = self.title_aln self.obj_style.borders = Borders() self.obj_style.borders.left = Borders.HAIR self.obj_style.borders.right = Borders.HAIR self.obj_style.borders.top = Borders.HAIR self.obj_style.borders.bottom = Borders.HAIR self.border.left = Borders.HAIR self.border.right = Borders.HAIR self.border.top = Borders.THICK self.border.bottom = Borders.THICK if left_border: self.obj_style.borders.left = Borders.THICK self.border.left = Borders.THICK if right_border: self.obj_style.borders.right = Borders.THICK self.border.right = Borders.THICK self.obj_style.font = self.obj_fnt if use_pattern: self.obj_style.pattern = self.pattern self.func = func
def xls_headers(self, text, title): u""" """ # headers style header1 = easyxf() header2 = easyxf() # font font1 = Font() font1.name = 'Times New Roman' font1.bold = True # alignment alig1 = Alignment() alig1.horz = 2 # assign header1.font = font1 header1.alignment = alig1 header2.font = font1 # headers 1st sheet self.feuy1.write_merge(0, 0, 0, 1, text.get('titre') + title, header1) self.feuy1.write(1, 0, text.get('nomfic'), header2) self.feuy1.write(2, 0, text.get('mtcthem'), header2) self.feuy1.write(3, 0, text.get('mtcgeo'), header2) self.feuy1.write(4, 0, text.get('description'), header2) self.feuy1.write(5, 0, text.get('cadre'), header2) self.feuy1.write(6, 0, text.get('num_objets'), header2) self.feuy1.write(7, 0, text.get('num_attrib'), header2) self.feuy1.write(8, 0, text.get('date_crea'), header2) self.feuy1.write(9, 0, text.get('date_actu'), header2) self.feuy1.write(10, 0, text.get('source'), header2) self.feuy1.write(11, 0, text.get('diffusion'), header2) self.feuy1.write(12, 0, text.get('responsable'), header2) self.feuy1.write(13, 0, text.get('ptcontact'), header2) self.feuy1.write(14, 0, text.get('siteweb'), header2) self.feuy1.write(15, 0, text.get('geometrie'), header2) self.feuy1.write(16, 0, text.get('echelle'), header2) self.feuy1.write(17, 0, text.get('precision'), header2) self.feuy1.write(18, 0, text.get('srs'), header2) self.feuy1.write(19, 0, text.get('emprise'), header2) # headers 2nd sheet self.feuy2.write(0, 0, text.get('numero'), header2) self.feuy2.write(0, 1, text.get('nom'), header2) self.feuy2.write(0, 2, text.get('type'), header2) self.feuy2.write(0, 3, text.get('longueur'), header2) self.feuy2.write(0, 4, text.get('precision'), header2) self.feuy2.write(0, 5, text.get('description'), header2) self.feuy2.write(0, 6, text.get('somme'), header2) self.feuy2.write(0, 7, text.get('moyenne'), header2) self.feuy2.write(0, 8, text.get('mediane'), header2) self.feuy2.write(0, 9, text.get('min'), header2) self.feuy2.write(0, 10, text.get('max'), header2) self.feuy2.write(0, 11, text.get('ecartype'), header2) # End of function return self.feuy1, self.feuy2
def excelWriter(self, filename): workbook1 = xlwt.Workbook() sheet1 = workbook1.add_sheet('表01') sheet1.write(0, 0, 'Unformated value') sheet1.write(0, 1, '大家好') # 设置字体样式 font1 = Font() font1.name = '华文琥珀' style = XFStyle() style.font = font1 sheet1.write(1, 0, 'Formated value', style) workbook1.save(filename)
def Style1(): style = XFStyle() fnt = Font() fnt.name = u'微软雅黑' fnt.bold = True style.font = fnt alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_CENTER alignment.vert = xlwt.Alignment.VERT_CENTER style.alignment = alignment # 给样式添加文字居中属性 style.font.height = 430 # 设置字体大小 return style
def write_file(headers, data): wb = Workbook() font = Font() style = XFStyle() page = wb.add_sheet('All Data') for i in range(len(data)): font.bold = True style.font = font page.write(0, i, headers[i], style=style) for j in range(len(data[0])): page.write(j + 1, i, data[i][j]) wb.save(search_excel_file)
def writLabel(self): """向excel写入内容,内容自定义""" style = XFStyle() # 初始化样式 font = Font() # 为样式创建字体 font.name = 'Times New Roman' # font.bold = True # 黑体 # font.underline = True # 下划线 # font.italic = True # 斜体字 style.font = font # 设定样式 for i in range(10): for j in range(5): self.excel_w_sheet.write(i,j, label = 'admin%d%d'%(i,j))# 参数对应 行, 列, 值 self.excel_w.save('Excelw.xls')
def write_file(headers, data): wb = Workbook() font = Font() style = XFStyle() page = wb.add_sheet('All Data') for i in range(len(data)): font.bold = True style.font = font page.write(0, i, headers[i], style=style) for j in range(len(data[i])): page.write(j + 1, i, data[i][j]) wb.save('info/misc/rating_plot.xls')
def def_style(): style = XFStyle() font = Font() # 这部分设置字体 font.name = 'Times New Roman' # 或者换成外面传进来的参数,这样可以使一个函数定义所有style # font.bold = 'True' # font.height = 24 # font.size = 2000 # font.colour_index = 3 style.font = font alignment = Alignment() # 这部分设置居中格式 alignment.horz = Alignment.HORZ_CENTER # 水平居中 alignment.vert = Alignment.VERT_CENTER # 垂直居中 style.alignment = alignment return style
def get_style( font_specs=dict(), border_specs=dict(), pattern=None, num_format_str=None, ): style_key = (freeze(font_specs), freeze(border_specs), pattern, num_format_str) try: return styles[style_key] except KeyError: style = XFStyle() style.font = Font() for key, value in font_specs.items(): setattr(style.font, key, value) style.borders = Borders() for key, value in border_specs.items(): setattr(style.borders, key, value) if pattern: style.pattern = pattern if num_format_str: style.num_format_str = num_format_str styles[style_key] = style return style
class ReportStyles(): alignment = Alignment() font = Font() borders = Borders() # col = Column() def borders_light(self): self.borders.left = Borders.THIN self.borders.right = Borders.THIN self.borders.top = Borders.THIN self.borders.bottom = Borders.THIN return self.borders def align_hor_right(self): self.alignment.horz = Alignment.HORZ_RIGHT return self.alignment def align_hor_left(self): self.alignment.horz = Alignment.HORZ_LEFT return self.alignment def align_hor_center(self): self.alignment.horz = Alignment.HORZ_CENTER return self.alignment def text_bold(self): self.font.bold = True return self.font
def css2excel(css): #custom_css = 'font: name "%s", %s on'%(current_value['font-family'].split(",")[0], current_value['font-weight'].split(",")[0]) #export_sheet.write(row_idx, col_idx, current_value['data'], xlwt.easyxf('font: italic on; pattern: pattern solid, fore-colour grey25')) xf_list = [] fnt = Font() borders = Borders() pattern = Pattern() align = Alignment() process_css = { 'font-family' : [fnt, "name" , lambda x : x.split(",")[0]], 'color' : [fnt, "colour_index", lambda x : excel_color_map.get(x,0)+8], 'font-weight' : [fnt, "bold", lambda x : x.upper() == 'BOLD'], #'font-weight' : [fnt, "bold", lambda x : x == '700'], 'text-align' : [align, "horz", lambda x : {'left':align.HORZ_LEFT, 'right':align.HORZ_RIGHT, 'center':align.HORZ_CENTER, 'justified': align.HORZ_JUSTIFIED}[x]], 'background-color' : [pattern,"pattern_fore_colour", lambda x: excel_color_map.get(x,16)+8], } #TODO process_css -> css for i in process_css.keys(): #print process_css[i][0] ,".",process_css[i][1], " => " , css[i] ," | ", process_css[i][2](css[i]) setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i])) style = XFStyle() style.font = fnt borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style.borders = borders style.pattern = pattern style.pattern.pattern = 1 style.alignment = align return style
def setupFormat(self): headFont = Font() headFont.bold = True alignmentSetup = Alignment() alignmentSetup.wrap = True borders = Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 self.header_style = XFStyle() self.header_style.font = headFont self.table_style = XFStyle() self.table_style.borders = borders self.table_style.alignment = alignmentSetup
def estiloCeldasSeparadas(): fnt = Font() fnt.bold=True #fnt.colour_index=2 borders = Borders() borders.left = Borders.MEDIUM borders.right = Borders.MEDIUM borders.top = Borders.MEDIUM borders.bottom = Borders.MEDIUM pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x18#0x99 style = XFStyle() style.num_format_str='YYYY-MM-DD' style.font = fnt style.borders = borders style.pattern = pattern return style
def __init__(self): self.style = XFStyle() base_font = Font() # 设置基本字体 base_font.height = FONT_SIZE_UNIT * 12 self.style.font = base_font alignment = Alignment() # 设置对齐 alignment.horz = Alignment.HORZ_CENTER alignment.vert = Alignment.VERT_CENTER self.style.alignment = alignment borders = Borders() # 设置边框 borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN borders.left_colour = COLOUR_BLACK borders.right_colour = COLOUR_BLACK borders.top_colour = COLOUR_BLACK borders.bottom_colour = COLOUR_BLACK self.style.borders = borders self.style.num_format_str = '#,##0' # 设置数字格式
def create_act(self): template = open_workbook(self.template, formatting_info=1) new_file = copy(template) sheet = new_file.get_sheet(0) for cell in cells_conf: print('Coздаем ячейку {}'.format(cell['name'])) style = XFStyle() font = Font() style.alignment.horz = cell['align_horz'] font.name = cell['fontname'] font.bold = cell['fontbold'] font.height = cell['fontsize'] style.font = font name = cell['name'] sheet.write(cell['row'], cell['col'], self._get_str(name), style) new_file.save(self.path)
def downloadTrue(request): response = HttpResponse(mimetype=u'application/ms-excel') filename = u'错误实名制列表.xls' response['Content-Disposition'] = (u'attachment;filename=%s' % filename).encode('utf-8') import xlwt from xlwt import Font, Alignment style1 = xlwt.XFStyle() font1 = Font() font1.height = 250 font1.name = u'仿宋' style1.font = font1 algn = Alignment() algn.horz = Alignment.HORZ_LEFT style1.alignment = algn style1.font = font1 wb = xlwt.Workbook() ws = wb.add_sheet(u"错误实名制列表", cell_overwrite_ok=True) rownum = 0 ws.write_merge(rownum, rownum, 0, 0, u'手机号', style1) ws.write_merge(rownum, rownum, 1, 1, u'姓名', style1) ws.write_merge(rownum, rownum, 2, 2, u'身份证号', style1) ws.write_merge(rownum, rownum, 3, 3, u'地址', style1) ws.write_merge(rownum, rownum, 4, 4, u'错误原因', style1) rownum += 1 for o in Truename.objects.filter(status=2).order_by('datetime'): ws.write_merge(rownum, rownum, 0, 0, o.tel, style1) ws.write_merge(rownum, rownum, 1, 1, o.name, style1) ws.write_merge(rownum, rownum, 2, 2, o.number, style1) ws.write_merge(rownum, rownum, 3, 3, o.address, style1) ws.write_merge(rownum, rownum, 4, 4, o.help, style1) rownum += 1 for i in range(5): ws.col(i).width = 256 * 20 wb.save(response) return response
def queryExcel(filename, dategroup, response): filename += u'.xls' response['Content-Disposition'] = (u'attachment;filename=%s' % filename).encode('utf-8') import xlwt from xlwt import Font, Alignment style1 = xlwt.XFStyle() font1 = Font() font1.height = 360 font1.name = u'仿宋' style1.font = font1 algn = Alignment() algn.horz = Alignment.HORZ_LEFT style1.alignment = algn style1.font = font1 style0 = xlwt.XFStyle() algn0 = Alignment() algn0.horz = Alignment.HORZ_CENTER font = Font() font.height = 320 font.bold = False font.name = u'仿宋' style0.alignment = algn0 style0.font = font wb = xlwt.Workbook() ws = wb.add_sheet(u"销售报表", cell_overwrite_ok=True) rownum = 0 ws.write_merge(rownum, rownum, 0, 0, u'序号', style0) ws.write_merge(rownum, rownum, 1, 1, u'品牌', style0) ws.write_merge(rownum, rownum, 2, 2, u'型号', style0) ws.write_merge(rownum, rownum, 3, 3, u'类型', style0) ws.write_merge(rownum, rownum, 4, 4, u'数量', style0) ws.write_merge(rownum, rownum, 5, 5, u'账户', style0) ws.write_merge(rownum, rownum, 6, 6, u'姓名', style0) ws.write_merge(rownum, rownum, 7, 7, u'主管', style0) rownum += 1 datanum = 1 for data in dategroup: ws.write_merge(rownum, rownum, 0, 7, u'日期:%s 厅台:%s 总计:%s 台' % (data['date'], data['officename'],data['totalnum']), style1) rownum += 1 for i, row in enumerate(data['query']): ws.write_merge(rownum, rownum, 0, 0, datanum, style0) ws.write_merge(rownum, rownum, 1, 1, row['productbrandsname'], style0) ws.write_merge(rownum, rownum, 2, 2, row['productname'], style0) ws.write_merge(rownum, rownum, 3, 3, row['ordertypename'], style0) ws.write_merge(rownum, rownum, 4, 4, row['ordernum'], style0) ws.write_merge(rownum, rownum, 5, 5, row['username'], style0) ws.write_merge(rownum, rownum, 6, 6, row['get_full_name'], style0) ws.write_merge(rownum, rownum, 7, 7, row['managername'], style0) datanum += 1 rownum += 1 for i in range(8): ws.col(i).width = 256 * 20 wb.save(response)
def creaHojaDeTraduccion(excel, nombreHoja="traduccion"): hoja=excel.creaHoja(nombreHoja) hoja.col(0).width=90*256 hoja.col(1).width=20*256 fnt = Font() fnt.bold=True #fnt.colour_index=2 borders = Borders() borders.left = Borders.MEDIUM borders.right = Borders.MEDIUM borders.top = Borders.MEDIUM borders.bottom = Borders.MEDIUM pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x18#0x99 style = XFStyle() style.num_format_str='YYYY-MM-DD' style.font = fnt style.borders = borders style.pattern = pattern hoja.write(0,0,"Original Text",style) hoja.write(0,1,"Translation",style) return hoja
def queryExcel(filename, dategroup, response): ''' 导出excel ''' filename += u'.xls' response['Content-Disposition'] = (u'attachment;filename=%s' % filename).encode('utf-8') import xlwt from xlwt import Font, Alignment style1 = xlwt.XFStyle() font1 = Font() font1.height = 260 font1.name = u'仿宋' style1.font = font1 algn = Alignment() algn.horz = Alignment.HORZ_LEFT style1.alignment = algn style1.font = font1 style0 = xlwt.XFStyle() algn0 = Alignment() algn0.horz = Alignment.HORZ_CENTER font = Font() font.height = 220 font.bold = False font.name = u'仿宋' style0.alignment = algn0 style0.font = font wb = xlwt.Workbook() ws = wb.add_sheet(u"考试报表", cell_overwrite_ok=True) rownum = 0 ws.write_merge(rownum, rownum, 0, 0, u'序号', style0) ws.write_merge(rownum, rownum, 1, 1, u'员工ID', style0) ws.write_merge(rownum, rownum, 2, 2, u'姓名', style0) ws.write_merge(rownum, rownum, 3, 3, u'得分', style0) ws.write_merge(rownum, rownum, 4, 4, u'主管', style0) rownum += 1 datanum = 1 for data in dategroup: ws.write_merge(rownum, rownum, 0, 4, u'考试:%s 参考人数:%s 旷考人数:%s' % (data['name'],data['totalnum'],data['untotalnum']), style1) rownum += 1 for i, row in enumerate(data['query']): ws.write_merge(rownum, rownum, 0, 0, datanum, style0) ws.write_merge(rownum, rownum, 1, 1, row['username'], style0) ws.write_merge(rownum, rownum, 2, 2, row['get_full_name'], style0) ws.write_merge(rownum, rownum, 3, 3, row['score'], style0) ws.write_merge(rownum, rownum, 4, 4, row['managername'], style0) datanum += 1 rownum += 1 for i in range(5): ws.col(i).width = 256 * 10 wb.save(response)
def export(match_list, file_path): wb = xlwt.Workbook() ws = wb.add_sheet('Ranking') # writing header header = ['Ranking', 'Name', 'Rating', 'Reviews', 'Price', 'Cuisines', 'Edenred Name', 'Match', 'Edenred Address'] font = Font() font.name = 'Arial' font.bold = True style = XFStyle() style.font = font for index in range(len(header)): ws.write(0, index, header[index], style) # writing restaurants for index in range(len(match_list)): match = match_list[index] ws.write(index+1, 0, int(match.restaurant1.ranking)) ws.write(index+1, 1, xlwt.Formula('HYPERLINK("%s"; "%s")' % (match.restaurant1.url, match.restaurant1.name))) ws.write(index+1, 2, float(match.restaurant1.rating.replace(',', '.'))) ws.write(index+1, 3, int(match.restaurant1.review_count)) ws.write(index+1, 4, match.restaurant1.price) ws.write(index+1, 5, ', '.join(match.restaurant1.cuisines)) ws.write(index+1, 6, match.restaurant2.name) ws.write(index+1, 7, int(100.0 * match.score)) address = match.restaurant2.address + ', ' + match.restaurant2.postcode map_link = 'https://www.google.com/maps?f=q&source=s_q&hl=es&q=%s' % address ws.write(index+1, 8, xlwt.Formula('HYPERLINK("%s"; "%s")' % (map_link, address))) wb.save(file_path)
def main(): lst = [] path = r'./Homework/03/' with open(path + 'test.txt', 'r') as fpin: title = fpin.readline().strip().replace('\n', '').split(',') while True: s = fpin.readline().strip().replace('\n', '').split(',') if s == ['']: break lst.append(s) fpin.close() book = Workbook() sheet1 = book.add_sheet('data') style = XFStyle() font = Font() font.name = '宋体' font.height = 280 style.font = font alignment = Alignment() alignment.horz = Alignment.HORZ_CENTER alignment.vert = Alignment.VERT_CENTER style.alignment = alignment row = sheet1.row(0) for i, j in zip(range(len(title)), title): row.write(i, j, style=style) for i, j in zip(range(1, len(lst) + 1), lst): row = sheet1.row(i) for x, y in zip(range(len(j)), j): row.write(x, y, style=style) book.save(path + 'data.xls')
def in_ket_qua(): # tao file ketqua.xls de ghi ket qua fnt = Font() fnt.name = "Times New Roman" fnt.height = 280 borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style = XFStyle() style.font = fnt style.borders = borders wb = Workbook() sheet1 = wb.add_sheet("Điểm") col = 0 row = 0 sheet1.write(row, col, "STT", style) sheet1.write(row, col + 1, "TÊN BÀI LÀM", style) sheet1.write(row, col + 2, "ĐIỂM", style) row += 1 st = 1 # Tinh ket qua cua tat ca cac file bai lam va in ket qua ra file KetQua.xls for name in list_file_word: sheet1.write(row, col, st, style) sheet1.write(row, col + 1, name[:-5], style) sheet1.write(row, col + 2, tinhKetQua(name), style) row += 1 st += 1 print(name, "___ĐÃ CHẤM XONG___") # Xoa bo cac file anh sau khi da xu li xong for name in glob.glob("*.png"):os.remove(name) # Luu ket qua ra file os.chdir(local) # shutil.rmtree('Temp') wb.save("KetQua.xls")
def output(file_or_stream, sheetname, headers, values, encoding = 'utf8', footer_text = None, footer_link = None): import xlwt book = xlwt.Workbook(encoding) sh = book.add_sheet(sheetname) datestyle = XFStyle() datestyle.num_format_str = 'DD/MM/YYYY' timestyle = XFStyle() timestyle.num_format_str = 'HH:MM:SS' header_font = Font() header_font.bold = True al = Alignment() al.horz = Alignment.HORZ_CENTER header_style = XFStyle() header_style.font = header_font header_style.alignment = al for i,header in enumerate(headers): sh.write(0, i, header, header_style) sh.set_panes_frozen(True) # frozen headings instead of split panes sh.set_horz_split_pos(1) # in general, freeze after last heading row sh.set_remove_splits(True) # if user does unfreeze, don't leave a split there for j, row in enumerate(values): for i, value in enumerate(row): if value.__class__ == date: sh.write(j+1, i, value, datestyle) elif value.__class__ == time: sh.write(j+1, i, value, timestyle) else: sh.write(j+1, i, value) if footer_link and footer_text: link_font = Font() link_font.name = 'Verdana' link_font.colour_index = 4 link_font.height = 20*8 al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_BOTTOM link_style = XFStyle() link_style.font = link_font link_style.alignment = al row = len(values) + 1 sh.write_merge(row, row, 0, len(headers)-1, Formula('HYPERLINK("' + footer_link + '";"' + footer_text + '")'), link_style) book.save(file_or_stream)
def css2excel(css): fnt = Font() borders = Borders() pattern = Pattern() align = Alignment() process_css = { 'font-family': [fnt, "name", lambda x: x.split(",")[0]], 'font-size': [fnt, "height", lambda x: get_font_height(x.upper())], 'color': [fnt, "colour_index", lambda x: match_color_index(x.upper())], 'font-weight': [fnt, "bold", lambda x: x == 'bold'], 'font-style': [fnt, "italic", lambda x: x == 'italic'], 'text-align': [align, "horz", lambda x: get_horizontal_align(x, align)], 'vertical-align': [align, "vert", lambda x: get_vertical_align(x, align)], 'background-color': [ pattern, "pattern_fore_colour", lambda x: match_color_index(x.upper()) ], } for i in css.keys(): if i in process_css.keys(): setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i].strip())) style = XFStyle() style.font = fnt borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style.borders = borders style.pattern = pattern style.pattern.pattern = 1 style.alignment = align style.num_format_str = '#,##0.00' return style
def __init__(self, timesheets, year=datetime.date.today().year): self.timesheets = timesheets.order_by('activity__person', 'project', 'year', 'month') self.book = Workbook() self.year = int(year) font_header = Font() font_header.bold = True font_title = Font() font_title.bold = True grey_pattern = Pattern() grey_pattern.pattern = Pattern.SOLID_PATTERN grey_pattern.pattern_fore_colour = Style.colour_map['gray25'] self.header_style = XFStyle() self.header_style.pattern = grey_pattern self.header_style.font = font_header self.header_title = XFStyle() self.header_title.font = font_title self.date_style = XFStyle() self.date_style.num_format_str = 'M/D/YY' self.title_style = XFStyle() self.title_style.pattern = grey_pattern self.title_style.font = font_title self.title_style.alignment.horz = self.title_style.alignment.HORZ_CENTER self.txt_centered_al = Alignment() self.txt_centered_al.horz = Alignment.HORZ_CENTER self.txt_centered = XFStyle() self.txt_centered.alignment = self.txt_centered_al self.header_date_style = XFStyle() self.header_date_style.pattern = grey_pattern self.header_date_style.font = font_header self.header_date_style.alignment = self.txt_centered_al
def get_sheet_title_style(bg_color=0x39, font_color=0x0, font_size=300, has_pattern=True, horz_center=True): fnt = Font() fnt.name = 'Arial' fnt.colour_index = font_color fnt.bold = True fnt.height = font_size borders = Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 al = Alignment() if horz_center: al.horz = Alignment.HORZ_CENTER else: al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER pattern = None if has_pattern: pattern = Pattern() pattern.pattern = 1 pattern.pattern_fore_colour = bg_color pattern.pattern_back_colour = bg_color style = XFStyle() style.font = fnt style.borders = borders style.alignment = al if pattern: style.pattern = pattern return style
def write_sheet(self, data, sheet_name, print_to_screen=False): '''Write a very simple table to a new sheet in a spreadsheet, Optionally, print the table to the screen''' # most cells al = Alignment() al.horz = Alignment.HORZ_RIGHT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.height = 9 * 20 # 9 pt style = XFStyle() style.font = font style.alignment = al # tops cells al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.height = 9 * 20 # 9 pt style_top = XFStyle() style_top.font = font style_top.alignment = al # left cells al = Alignment() al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.italic = True font.height = 9 * 20 # 9 pt style_left = XFStyle() style_left.font = font style_left.alignment = al ws = self.add_sheet(sheet_name) for i, row in enumerate(data): for j, cell in enumerate(row): borders = Borders() if i == 0: borders.top = 1 borders.bottom = 2 if i == len(row) - 1: borders.bottom = 1 if j == 0: borders.left = 1 borders.right = 1 if j == len(row) - 1: borders.right = 1 if j == 0: _style = style_left elif i == 0: _style = style_top else: _style = style _style.borders = borders ws.write(i + 1, j + 1, cell, _style) if print_to_screen: print print_table(data, sheet_name, bold=True)
from datetime import date from xlwt import Workbook, XFStyle, Borders, Pattern, Font fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THICK borders.right = Borders.THICK borders.top = Borders.THICK borders.bottom = Borders.THICK pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x0A style = XFStyle() style.num_format_str='YYYY-MM-DD' style.font = fnt style.borders = borders style.pattern = pattern book = Workbook() sheet = book.add_sheet('A Date') sheet.write(1,1,date(2009,3,18),style) book.save('date.xls')
def create_template(self): try: open_workbook(self.template_path) except FileNotFoundError: # 文件夹下无模板文件,直接创建然后导入数据 wb = Workbook(encoding='ascii') ws = wb.add_sheet("1") style1 = XFStyle() font1 = Font() font1.bold = True style1.font = font1 ws.write(0, 0, OLD_FILE_NAME, style1) ws.write(0, 1, NEW_FILE_NAME, style1) style2 = XFStyle() font2 = Font() font2.bold = True font2.colour_index = 2 style2.font = font2 ws.write(1, 3, TEMPLATE_HINT, style2) new_row_id = 1 for i in range(len(self.table_name_list0)): if i not in self.disable_pos_list: ws.write(new_row_id, 0, self.table_name_list0[i]) new_row_id += 1 wb.save(self.template_path) return # 文件夹下已经存在模板文件 # 暂时不检查完整性 rb = open_workbook(self.template_path, formatting_info=True) r_sheet = rb.sheet_by_index(0) wb = copy(rb) sheet = wb.get_sheet(0) def update_old_name(old_name_list, disable_pos_list): needed_add_name_list = [] old_name_index = 0 # 旧文件名列序号 name0_list = r_sheet.col_values(old_name_index, start_rowx=1, end_rowx=None) for i in range(len(old_name_list)): if i not in disable_pos_list: if old_name_list[i] not in name0_list: needed_add_name_list.append(old_name_list[i]) if len(needed_add_name_list) == 0: print("没有文件名需要添加到模板文件中,或者所有文件名已经添加") return n_row = r_sheet.nrows print("原模板文件中已经有" + str(n_row) + "行") for name in needed_add_name_list: print("将文件名" + name + "添加到模板文件中") sheet.write(n_row, old_name_index, name) n_row += 1 update_old_name(self.table_name_list0, self.disable_pos_list) try: wb.save(self.template_path) except PermissionError: messagebox.showwarning("模板文件异常", "请关闭文件夹下的模板文件后再重新导入") return messagebox.showinfo("一切正常", "文件导入成功,模板文件已生成。\n请打开对应文件夹的模板文件编辑新文件名")
def _getStyle(self, org_style, org_font): from xlwt import XFStyle, Font, Borders, Pattern # font font = Font() font.name = org_font.name font.height = org_font.height font.italic = org_font.italic font.struck_out = org_font.struck_out font.outline = org_font.outline font.shadow = org_font.shadow font.colour_index = org_font.colour_index font.bold = org_font.bold font._weight = org_font.weight font.escapement = org_font.escapement_type font.underline = org_font.underline_type font.family = org_font.family font.charset = org_font.character_set # border borders = Borders() borders.left = Borders.THIN #org_style.xf.border.left_line_style borders.right = Borders.THIN #org_style.xf.border.right_line_style borders.top = Borders.THIN #org_style.xf.border.top_line_style borders.bottom = Borders.THIN #org_style.xf.border.bottom_line_style #borders.diag = self.NO_LINE borders.left_colour = org_style.xf.border.left_colour_index borders.right_colour = org_style.xf.border.right_colour_index borders.top_colour = org_style.xf.border.top_colour_index borders.bottom_colour = org_style.xf.border.bottom_colour_index #borders.diag_colour = org_style.xf.border.left_colour_index #borders.need_diag1 = self.NO_NEED_DIAG1 #borders.need_diag2 = self.NO_NEED_DIAG2 # Pattern pattern = Pattern() pattern.pattern = org_style.xf.background.fill_pattern pattern.pattern_fore_colour = org_style.xf.background.pattern_colour_index pattern.pattern_back_colour = org_style.xf.background.background_colour_index # style = XFStyle() style.borders = borders style.font = font style.pattern = pattern return style
def makeXLS(self, id_relatorio, id_promocao=None): """ cria o arquivo xls a var index é utilizada para controlar em qual linha entrara o conteudo """ timename = str(util.dtnow('%d/%m/%Y %H:%M%S')).replace("/", "") timename = timename.replace(":", "") timename = "_" + timename.replace(" ", "") site = getDadosSite(id_site=self.id_site, request=self.request) base = site["base_app"] if not base.endswith("/"): base = base + "/" if int(id_relatorio) == 1: titulo = 'Relacao_usuario_participacao' elif int(id_relatorio) == 2: titulo = 'Relacao_participantes_contemplados' elif int(id_relatorio) == 4: titulo = 'Dados_usuarios_por_promocao' else: titulo = 'Relacao_participantes_promocao' saveplace = ("{0}/ns{1}/arquivos/tmp/" "{2}{3}.xls").format(str(settings.PATH_FILES), str(self.id_site), titulo, timename) saveurl = "{0}tmp/{1}{2}.xls".format( base, titulo, timename) book = Workbook() font = Font() font.bold = True style = XFStyle() style.font = font style0 = XFStyle() style0.font = font alignment = Alignment() alignment.horz = Alignment.HORZ_CENTER style0.alignment = alignment pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 22 style0.pattern = pattern index = 0 sheet = book.add_sheet('promocoes') # sheet if int(id_relatorio) == 1 or int(id_relatorio) == 3: promocoes = self._getConteudo() if int(id_relatorio) == 1: campos_s1 = [{"nome": "Nome"}, {"email": "email"}, {"cpf": "CPF"}, {"quantidade de participacoes": u"Quantidade de participações"}, {"status":"status"}, {"bloqueio":"bloqueado"}] sheet.write_merge(0, 0, 0, 5, u"Relação usuário participação por promoção", style0) else: campos_s1 = [{"nome":"Nome"}, {"email":"email"}, {"cpf":"CPF"}, {"telefone":"Telefone"}, {"endereco":u"Endereço"}, {"data_hora":u"Hora participação"}] sheet.write_merge(0, 0, 0, 5, u"Dados de usuários por promoção", style0) total_geral = 0 count = 0 for i in promocoes: count_participacoes = self._getCountParticipacoes(i['id_conteudo']) count += count_participacoes index += 1 titulo = i['titulo'] sheet.write_merge(index, index, 0, 5, titulo.decode("latin1"), style0) index += 1 for j in range(len(campos_s1)): sheet.write(index, j, campos_s1[j].values()[0], style) sheet.col(j).width = 30 * 256 cont = 0 if int(id_relatorio) == 1: for y in self.execSql("select_usuarios_promocoes_all", id_conteudo=int(i['id_conteudo'])): sheet.write(index + 1, 0, self.dec(y['nome'])) sheet.write(index + 1, 1, self.dec(y['email'])) sheet.write(index + 1, 2, self.dec(y['cpf'])) sheet.write(index + 1, 3, self.dec(y['total'])) sheet.write(index + 1, 4, self.dec(y['status'])) if y['bloqueio']: bloqueio = 'Sim' else: bloqueio = 'Não' sheet.write(index + 1, 5, self.dec(bloqueio)) index += 1 cont += 1 if int(id_relatorio) == 3: for y in self.execSql("select_usuarios_promocoes_all2", id_conteudo=int(i['id_conteudo'])): endereco = "{0}, {1}, {2}, {3}, {4}". format(y['endereco'], y['numero'], y['bairro'], y['estado'], y['cep']) sheet.write(index + 1, 0, self.dec(y['nome'])) sheet.write(index + 1, 1, self.dec(y['email'])) sheet.write(index + 1, 2, self.dec(y['cpf'])) sheet.write(index + 1, 3, self.dec(y['telefone'])) sheet.write(index + 1, 4, self.dec(endereco)) sheet.write(index + 1, 5, y['dhora_participacao']) index += 1 cont += 1 if cont: index += 2 sheet.write(index, 0, 'Total', style) sheet.write(index, 1, cont) total_geral += cont index += 1 sheet.write(index, 0, u'Total Participações', style) sheet.write(index, 1, count_participacoes) else: index += 2 sheet.write(index, 0, 'Total', style) sheet.write(index, 1, cont) index += 2 index += 6 sheet.write_merge(index, index, 0, 1, "Total Geral", style0) index += 1 sheet.write(index, 0, "Total de Participantes", style) sheet.write(index, 1, self.dec(total_geral), style) index += 1 sheet.write(index, 0, u"Total de Participações Geral", style) sheet.write(index, 1, self.dec(count), style) else: if int(id_relatorio) == 4: cont = 0 titulo_promo = self._getConteudo(id_promocao)['titulo'] sheet.write_merge(0, 0, 0, 5, u"Relação de partcipantes da promoção " + titulo_promo.decode('latin1'), style0) campos_s1 = [{"nome":"Nome"}, {"email":"email"}, {"cpf":"CPF"}, {"telefone":"Telefone"}, {"endereco":u"Endereço"}, {"status":"Status"} ] saveplace = ("{0}/ns{1}/arquivos/tmp/{2}{3}.xls").format(str(settings.PATH_FILES), str(self.id_site), unicode(titulo + '_' + titulo_promo, errors='ignore'), timename) saveurl = "{0}tmp/{1}{2}.xls".format(base, unicode(titulo + '_' + titulo_promo, errors='ignore'), timename) index += 1 for j in range(len(campos_s1)): sheet.write(index, j, campos_s1[j].values()[0], style) sheet.col(j).width = 30 * 256 for i in self.execSql("select_usuarios_promocoes_all2", id_conteudo=int(id_promocao)): endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'], i['numero'], i['bairro'], i['estado'], i['cep']) sheet.write(index + 1, 0, self.dec(i['nome'])) sheet.write(index + 1, 1, self.dec(i['email'])) sheet.write(index + 1, 2, self.dec(i['cpf'])) sheet.write(index + 1, 3, self.dec(i['telefone'])) sheet.write(index + 1, 4, self.dec(endereco)) sheet.write(index + 1, 5, self.dec(i['status'])) index += 1 cont += 1 else: cont = 0 sheet.write_merge(0, 0, 0, 5, u"Relação de usuários contemplados", style0) campos_s1 = [{"nome":"Nome"}, {"email":"email"}, {"cpf":"CPF"}, {"telefone":"Telefone"}, {"endereco":u"Endereço"}, {"Contemplações":u"Contemplações"}] index += 1 for j in range(len(campos_s1)): sheet.write(index, j, campos_s1[j].values()[0], style) sheet.col(j).width = 30 * 256 for i in self.execSql("select_contemplados"): endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'], i['numero'], i['bairro'], i['estado'], i['cep']) sheet.write(index + 1, 0, self.dec(i['nome'])) sheet.write(index + 1, 1, self.dec(i['email'])) sheet.write(index + 1, 2, self.dec(i['cpf'])) sheet.write(index + 1, 3, self.dec(i['telefone'])) sheet.write(index + 1, 4, self.dec(endereco)) sheet.write(index + 1, 5, self.dec(i['total'])) index += 1 cont += 1 index += 6 sheet.write(index, 0, u"Total de usuários", style) sheet.write(index, 1, self.dec(cont), style) book.save(saveplace) return saveurl
def exportReport5ToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1'); sheet1.col(1).width = 256*80; sheet1.col(2).width = 256*10; sheet1.col(3).width = 256*20; borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 wrap = Alignment(); wrap.wrap = 1; wrap.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True styleWrap = XFStyle(); styleWrap.alignment = wrap; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; styleDate.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = wrap; if( objectProject): i=0; row1 = sheet1.row(i) ; row1.write(0, ('risk id').decode('UTF8'),styleHead ); #sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ); row1.write(1, ('รายละเอียด').decode('UTF8'),styleHead); row1.write(2, ('วันที่รายงาน').decode('UTF8'),styleHead ); row1.write(3, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead); i=i+1; for value in objectProject: row1 = sheet1.row(i) ; row1.write(0, value.get('risk_management_id') ,styleRowDetail ); row1.write(1, value.get('risk_detail').decode('UTF8'),StyleRowDetailWrap ); #sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap ); row1.write(2, value.get('report_date') ,styleDate ); row1.write(3, value.get('report').decode('UTF8') ,styleRowDetail ); i=i+1; for sub in value.get('response') : row1 = sheet1.row(i) ; row1.write(0," " ); text = "(" + sub.get('risk_team').decode('UTF8') + " ) " + sub.get('result').decode('UTF8'); row1.write(1, text ,StyleRowDetailWrap ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simpleReport5.xls'); book.save(dirTempFile); return dirTempFile;
def qiandaoXls(filename,qiandao,dategroup,mi,response): import uuid filename+=u'.xls' response['Content-Disposition'] = (u'attachment;filename=%s'%filename).encode('utf-8') import xlwt from xlwt import Font,Alignment style1=xlwt.XFStyle() font1=Font() font1.height=320 font1.name=u'仿宋' style1.font=font1 algn=Alignment() algn.horz=Alignment.HORZ_RIGHT style1.alignment=algn style0=xlwt.XFStyle() algn0=Alignment() algn0.horz=Alignment.HORZ_CENTER algn0.vert=Alignment.VERT_CENTER font=Font() font.height=320 font.bold=False font.name=u'仿宋' style0.alignment=algn0 style0.font=font style3=xlwt.XFStyle() algn3=Alignment() algn3.horz=Alignment.HORZ_CENTER font3=Font() font3.height=320 font3.bold=False font1.name=u'仿宋' style3.alignment=algn3 style3.font=font3 pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour =2 style3.pattern=pattern wb=xlwt.Workbook() style2=xlwt.XFStyle() algn2=Alignment() algn2.horz=Alignment.HORZ_LEFT font2=Font() font2.height=350 font2.bold=True font1.name=u'仿宋' style2.alignment=algn2 style2.font=font2 wb=xlwt.Workbook() ws=wb.add_sheet(u"签到报表",cell_overwrite_ok=True) rownum=2 usernum=1 ws.write_merge(0,1,0,0,u'序号',style2) ws.write_merge(0,1,1,1,u'员工ID',style2) ws.col(1).width=0x0d00 +3000 ws.write_merge(0,1,2,2,u'姓名',style2) ws.col(2).width=0x0d00 +3000 for i,q in enumerate(qiandao): ws.write_merge(0,0,5*i+3,5*i+6,q.name,style2) ws.write_merge(1,1,5*i+3,5*i+3,u'厅台',style2) ws.col(5*i+3).width=0x0d00 + 7000 ws.write_merge(1,1,5*i+4,5*i+4,u'地址',style2) ws.col(5*i+4).width=0x0d00 + 9000 ws.write_merge(1,1,5*i+5,5*i+5,u'时间',style2) ws.write_merge(1,1,5*i+6,5*i+6,u'位置判断',style2) ws.col(6).width=0x0d00 +3000 ws.write_merge(1,1,5*i+7,5*i+7,u'时间判读',style2) ws.col(7).width=0x0d00 +3000 for data in dategroup: ws.write_merge(rownum,rownum,0,data['rowspan2'],u'日期:%s'%(data['date'],),style2) rownum+=1 tempnum=0 for query in data['query']: for i,rows in enumerate(query['qiandaolist']): for j,row in enumerate(rows): ws.write_merge(rownum+j,rownum+j,3+i*5+0,3+i*5+0,row['officename'],style0) ws.write_merge(rownum+j,rownum+j,3+i*5+1,3+i*5+1,row['address'],style0) ws.write_merge(rownum+j,rownum+j,3+i*5+2,3+i*5+2,row['dateTime'],style0) if row['officegps']: if row['gpsdistance']<mi: ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'合格',style0) else: ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'不合格',style3) else: ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'0',style0) if row['time']: ws.write_merge(rownum+j,rownum+j,3+i*5+4,3+i*5+4,u'合格',style0) else: ws.write_merge(rownum+j,rownum+j,3+i*5+4,3+i*5+4,u'不合格',style3) if tempnum<j: tempnum=j ws.write_merge(rownum,rownum+tempnum,0,0,usernum,style0) ws.write_merge(rownum,rownum+tempnum,1,1,query['user']['username'],style0) ws.write_merge(rownum,rownum+tempnum,2,2,query['user']['get_full_name'],style0) rownum+=tempnum+1 usernum+=1 wb.save(response)
def exportToXls(self): # opening file dialog fileName = QFileDialog.getSaveFileName(self, 'Save as', RES, 'Microsoft Excel Spreadsheet (*.xls)') if fileName.count() > 0: try: COLUMN_WIDTH = 3000 alignment = Alignment() alignment.horizontal = Alignment.HORZ_CENTER alignment.vertical = Alignment.VERT_CENTER borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style = XFStyle() style.alignment = alignment style.borders = borders font = Font() font.bold = True headerStyle = XFStyle() headerStyle.font = font separate = Borders() separate.left = Borders.THIN separate.right = Borders.DOUBLE separate.top = Borders.THIN separate.bottom = Borders.THIN separateStyle = XFStyle() separateStyle.borders = separate book = Workbook(encoding='utf-8') # modelling data dec_sheet = book.add_sheet('Data decomposition') # decomposition data # initial data column = 0 row = 0 dec_sheet.write(row, column, 'Time series', headerStyle) dec_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in self.data[0]: dec_sheet.write(row, column, item, separateStyle) row += 1 # decomposition for lvl in self.wCoefficients: row = 0 column += 1 dec_sheet.write(row, column, 'Level' + str(column - 1), headerStyle) dec_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in lvl: dec_sheet.write(row, column, item, style) row += 1 # decomposition graphs pass levels_sheet = book.add_sheet('Multiscale forecast') # levels data column = 0 for lvl in self.forecast: row = 0 levels_sheet.write(row, column, 'Level' + str(column), headerStyle) levels_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in lvl[1]: levels_sheet.write(row, column, float(item), style) row += 1 column += 1 result_sheet = book.add_sheet('Results') # initial column = 0 row = 0 result_sheet.write(row, column, 'Initial data', headerStyle) result_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in self.data[0]: result_sheet.write(row, column, item, separateStyle) row += 1 # forecast row = 0 column += 1 result_sheet.write(row, column, 'Forecast', headerStyle) result_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in self.inverseWT(): result_sheet.write(row, column, item, style) row += 1 row = 0 column = 2 self.updateGraph() self.plotResult.saveFigure('forecast', format='bmp') result_sheet.insert_bitmap(RES + TEMP + 'forecast.bmp', row, column) # saving xls try: book.save(unicode(fileName)) except Exception: pass except Exception, e: pass
def CreateExcelSpreadsheet(table, output_excel, use_alias=True): """Exports table to excel Required: table -- input table output_excel -- output excel table (.xlsx, .xls) Optional: use_alias -- use field alias name for column headers. Default is True """ # build field dict fieldNames = [(f.name, f.aliasName) for f in arcpy.ListFields(table) if f.type != 'Geometry'] fields = [f[1] if use_alias in ('true', True) else f[0] for f in fieldNames] widths = {i: arial10.fitwidth(f) + 1024 for i,f in enumerate(fields)} # get field values *Changed from type dict to list with arcpy.da.SearchCursor(table, [f[0] for f in fieldNames]) as rows: values = [r for r in rows] # Create spreadsheet wb = Workbook() ws = wb.add_sheet('Sheet 1') cols = len(fields) rows = len(values) + 1 # set styles #*************************************************************************** borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style = XFStyle() style.borders = borders # headers fntHeaders = Font() fntHeaders.bold = True fntHeaders.height = 220 styleHeaders = XFStyle() styleHeaders.font = fntHeaders styleHeaders.borders = borders # for date fields styleDate = XFStyle() styleDate.borders = borders styleDate.num_format_str = 'MM/DD/YYYY' #*************************************************************************** # write headers and freeze panes for ci,field in enumerate(fields): ws.write(0, ci, field, styleHeaders) # freeze headers ws.set_panes_frozen(True) ws.set_horz_split_pos(1) # fill in values start = 1 for vals in values: for i, value in enumerate(vals): ws.write(start, i, value, styleDate if isinstance(value, datetime.datetime) else style) v_width = arial10.fitwidth(str(value).strip()) if v_width > widths[i]: widths[i] = v_width start += 1 if not start % 1000: ws.flush_row_data() # autofit column widths for ci,width in widths.iteritems(): ws.col(ci).width = int(width + 256) # just a little more padding # save workbook wb.save(output_excel) del wb out_url = '/'.join([PATH_URL, os.path.basename(out_file)]) arcpy.SetParameter(2, out_url) arcpy.AddMessage(out_url) return out_url
unicode(localtime()[1]) + u"-" +\ unicode(localtime()[2]) # date du jour ################################### ######### Fichier Excel ########### ################################### # configuration du fichier excel de sortie book = Workbook(encoding = 'utf8') feuy1 = book.add_sheet(u'Dossiers', cell_overwrite_ok=True) feuy2 = book.add_sheet(u'Shapes', cell_overwrite_ok=True) feuy3 = book.add_sheet(u'Champs', cell_overwrite_ok=True) feuy4 = book.add_sheet(u'MXD', cell_overwrite_ok=True) feuy5 = book.add_sheet(u'Metadatos', cell_overwrite_ok=True) # personnalisation du fichier excel font1 = Font() # cr?ation police 1 font1.name = 'Times New Roman' font1.bold = True entete = XFStyle() # cr?ation style pour les en-t?tes entete.font = font1 # application de la police 1 au style entete hyperlien = easyxf(u'font: underline single') erreur = easyxf(u'font: name Arial, bold 1, colour red') # colonnes feuille dossiers feuy1.write(0, 0, u'Nom actuel', entete) feuy1.write(0, 1, u'Nom chang?', entete) feuy1.write(0, 2, u'Type g?om?trie', entete) feuy1.write(0, 3, u'Emprise', entete) feuy1.write(0, 4, u'Projection', entete) feuy1.write(0, 5, u'EPSG', entete)
def toupiaoExcel(request): subjectid = request.REQUEST.get('subjectid') optionid = request.REQUEST.get('optionid') subject = Subject.objects.get(pk=subjectid) if subject.isNoName: return HttpResponse(u'匿名投票不可导出') filename=u'%s'%subject.title if optionid: option = Option.objects.get(pk=optionid) if option.subject.pk!=subject.pk: raise Http404 filename+=u'——%s'%option.content response = HttpResponse(mimetype=u'application/ms-excel') filenames = u'%s.xls'%filename response['Content-Disposition'] = (u'attachment;filename=%s' % filenames).encode('utf-8') import xlwt from xlwt import Font, Alignment style1 = xlwt.XFStyle() font1 = Font() font1.height = 260 font1.name = u'仿宋' style1.font = font1 algn = Alignment() algn.horz = Alignment.HORZ_LEFT style1.alignment = algn style1.font = font1 style0 = xlwt.XFStyle() algn0 = Alignment() algn0.horz = Alignment.HORZ_CENTER font = Font() font.height = 220 font.bold = False font.name = u'仿宋' style0.alignment = algn0 style0.font = font wb = xlwt.Workbook() ws = wb.add_sheet(u"人员名单", cell_overwrite_ok=True) ws.header_str = filename ws.footer_str ='' rownum = 0 num=1 query=Toupiao.objects.filter(subject=subject) if optionid: query=query.filter(options=option) ws.write_merge(rownum,rownum,0,0,u'序号',style0) ws.write_merge(rownum,rownum,1,1,u'部门',style0) ws.write_merge(rownum,rownum,2,2,u'人员',style0) rownum+=1 for toupiao in query: ws.write_merge(rownum,rownum,0,0,num,style0) ws.write_merge(rownum,rownum,1,1,unicode(getattr(toupiao.user.person,'depate',u'无')),style0) ws.write_merge(rownum,rownum,2,2,toupiao.user.person.truename,style0) rownum+=1 num+=1 else: for opt in Option.objects.filter(subject=subject): ws.write_merge(rownum,rownum,0,2,u'选项:%s'%opt.content,style1) rownum+=1 ws.write_merge(rownum,rownum,0,0,u'序号',style0) ws.write_merge(rownum,rownum,1,1,u'部门',style0) ws.write_merge(rownum,rownum,2,2,u'人员',style0) rownum+=1 for toupiao in query.filter(options=opt): ws.write_merge(rownum,rownum,0,0,num,style0) ws.write_merge(rownum,rownum,1,1,unicode(getattr(toupiao.user.person,'depate',u'无')),style0) ws.write_merge(rownum,rownum,2,2,toupiao.user.person.truename,style0) rownum+=1 num+=1 width=256*10 ws.col(0).width = width ws.col(1).width = width*3 ws.col(2).width = width*2 wb.save(response) return response
def report_get(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'Employee Name',style_header) ws.col(2).width = 8000 ws.write(0,3,'Salary Amount',style_header) ws.col(3).width = 5000 ws.write(0,4,month_name,style_header) ws.col(4).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource\ as res on (emp.resource_id=res.id) where emp.department_id = \ '"+str(this.dept_id.id)+"' and emp.department_id is not null and \ res.active=True order by emp.department_id") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource \ as res on (emp.resource_id=res.id) where res.active=True order by emp.department_id") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=0 dept_dict = {} grand = total = pay_total = pay_grand = budget = 0.0 pay_data = False flag = True for each in emp_obj.browse(cr, uid, emp_ids): pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id)]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) i+=1 if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: salary = 0.0 if flag: ws.write(i,0, 'Allocated Budget',style_header) ws.write(i,1, 0.0,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, total,style_header) if pay_total: ws.write(i,4,pay_total,style_header) else: ws.write(i,4, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,5, diff,style_header1) flag = False i += 2 total = pay_total = budget = 0.0 name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,0, 'X Department') ws.write(i,1,'X Reporting Officer') ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 else: dept_dict[str(each.department_id.id)] = '' if i != 1: ws.write(i,0, 'Allocated Budget',style_header) if budget: ws.write(i,1, budget,style_header) else: budget = 0.0 ws.write(i,1,budget,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, total,style_header) if pay_total: ws.write(i,4,pay_total,style_header) else: ws.write(i,4, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,5, diff,style_header1) i += 2 total = pay_total = budget = 0.0 if each.department_id: salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+1,2, 'Total',style_header) ws.write(i+1,3, total,style_header) ws.write(i+3,2, 'Grand Total',style_header) ws.write(i+3,3, grand,style_header) ws.write(i+1,4, pay_total,style_header) ws.write(i+3,4, pay_grand,style_header) diff_pay = pay_total - total diff_grand = pay_grand - grand ws.write(i+1,5, diff_pay,style_header1) ws.write(i+3,5, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def exportToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1') sheet1.col(1).width = 256*20; sheet1.col(2).width = 256*80; sheet1.col(3).width = 256*10; sheet1.col(4).width = 256*20; default_book_style = book.default_style default_book_style.font.height = 20 * 36 # 36pt fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 algn1 = Alignment(); algn1.wrap = 1; #algn1.horz = Alignment.HORZ_CENTER #algn1.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP print "export"; if( objectProject): i=0; print "start" ; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; row1 = sheet1.row(i) ; row1.write(0, ('risk id').decode('UTF8'),styleHead ); sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ,styleHead ); # row1.write(1, ('รายละเอียด').decode('UTF8')); row1.write(3, ('วันที่รายงาน').decode('UTF8'), styleHead ); row1.write(4, ('หน่วยที่รายงาน').decode('UTF8'), styleHead ); i=i+1; style1 = XFStyle(); style1.alignment = algn1; #style0 = xlwt.easyxf('font: name Times New Roman size 20, color-index black, bold on') for value in objectProject: row1 = sheet1.row(i) ; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = algn1; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; row1.write(0, value.get('risk_management_id'),styleRowDetail ); #row1.write(1, value.get('risk_detail').decode('UTF8') , style1); sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap ); row1.write(3, value.get('report_date') ,styleDate); row1.write(4, value.get('report').decode('UTF8') ,styleRowDetail ); i=i+1; row1 = sheet1.row(i) ; row1.write(0," " ); row1.write(1,('หน่วยที่เกี่ยวข้อง').decode('UTF8') ,styleHead ); sheet1.write_merge(i, i, 2, 3,('รายละเอียดการตอบ').decode('UTF8') , styleHead ); i=i+1; for sub in value.get('response') : row1 = sheet1.row(i) ; row1.write(0," " ); row1.write(1,sub.get('risk_team').decode('UTF8') , styleRowDetail ); sheet1.write_merge(i, i, 2, 3,sub.get('result').decode('UTF8') , StyleRowDetailWrap ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simple.xls'); print dirTempFile; book.save(dirTempFile);
def write_cursor_to_excel(curs, filename, sheetTitle): """write_cursor_to_excel curs: a cursor for an open connection to an oracle database filename: name of the XLS file to create sheetTitle: name of the sheet to create """ # create style for header row - bold font, thin border below fnt = Font() fnt.bold = True borders = Borders() borders.bottom = Borders.THIN hdrstyle = XFStyle() hdrstyle.font = fnt hdrstyle.borders = borders # create a date format style for any date columns, if any datestyle = XFStyle() datestyle.num_format_str = 'DD/MM/YYYY' # create the workbook. (compression: try to reduce the number of repeated styles) wb = Workbook(style_compression=2) # the workbook will have just one sheet sh = wb.add_sheet(sheetTitle) # write the header line, based on the cursor description c = 0 colWidth = [] for col in curs.description: #col[0] is the column name #col[1] is the column data type sh.write(0, c, col[0], hdrstyle) colWidth.append(1) # arbitrary min cell width if col[1] == cx_Oracle.DATETIME: colWidth[-1] = len(datestyle.num_format_str) if colWidth[-1] < len(col[0]): colWidth[-1] = len(col[0]) c += 1 # write the songs, one to each row r = 1 for song in curs: row = sh.row(r) for c in range(len(song)): if song[c]: if curs.description[c][1] == cx_Oracle.DATETIME: row.write(c, song[c], datestyle) else: if colWidth[c] < len(str(song[c])): colWidth[c] = len(str(song[c])) row.write(c, song[c]) r += 1 for c in range(len(colWidth)): sh.col(c).width = colWidth[c] * 350 # freeze the header row sh.panes_frozen = True sh.vert_split_pos = 0 sh.horz_split_pos = 1 wb.save(filename)
def detail_budget(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) if int(month) in [1,3,5,7,8,10,12]: join_date=year +'-'+month+'-'+'31' if int(month) in [4,6,9,11]: join_date=year +'-'+month+'-'+'30' if int(month) in [2]: if int(year) % 4 == 0: join_date=year +'-'+month+'-'+'29' else: join_date=year +'-'+month+'-'+'28' #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'RO of HOD',style_header) ws.col(2).width = 8000 ws.write(0,3,'Employee Name',style_header) ws.col(3).width = 9000 ws.write(0,4,'Designation',style_header) ws.col(4).width = 8000 ws.write(0,5,'Working Days',style_header) ws.col(5).width = 5000 ws.write(0,6,'Working Hours',style_header) ws.col(6).width = 5000 ws.write(0,7,'Working Month',style_header) ws.col(7).width = 5000 ws.write(0,8,'Salary Amount',style_header) ws.col(8).width = 5000 ws.write(0,9,month_name,style_header) ws.col(9).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource "\ "as res on (emp.resource_id=res.id) where emp.department_id = "\ "'"+str(this.dept_id.id)+"' and emp.department_id is not null and "\ "res.active=True and emp.joining_date <'"+str(join_date)+"' order by emp.department_id, (substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource "\ "as res on (emp.resource_id=res.id) where res.active=True and emp.joining_date < '"+str(join_date)+"' order by emp.department_id, "\ "(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=0 dept_dict = {} grand = total = pay_total = pay_grand = budget = 0.0 pay_data = False flag = True for each in emp_obj.browse(cr, uid, emp_ids): tolal_months = 0 if not each.joining_date: continue dt1 = datetime.strptime(each.joining_date,'%Y-%m-%d') dt2 = datetime.strptime(time.strftime(DEFAULT_SERVER_DATE_FORMAT),'%Y-%m-%d') start_month=dt1.month end_months=(dt2.year-dt1.year)*12 + dt2.month+1 dates=[datetime(year=yr, month=mn, day=1) for (yr, mn) in ( ((m - 1) / 12 + dt1.year, (m - 1) % 12 + 1) for m in range(start_month, end_months) )] for val in dates: tolal_months += 1 pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) i+=1 if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days or '0.0') ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: salary = 0.0 if flag: ws.write(i,0, 'Allocated Budget',style_header) ws.write(i,1, 0.0,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, '',style_header) ws.write(i,4, '',style_header) ws.write(i,5, '',style_header) ws.write(i,6, '',style_header) ws.write(i,7, '',style_header) ws.write(i,8, total,style_header) if pay_total: ws.write(i,9,pay_total,style_header) else: ws.write(i,9, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,10, diff,style_header1) flag = False i += 2 total = pay_total = budget = 0.0 name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,0, 'X Department') ws.write(i,1,'X Reporting Officer') ws.write(i,2,'X Reporting Officer') ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days or '0.0') ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 else: dept_dict[str(each.department_id.id)] = '' if i != 1: ws.write(i,0, 'Allocated Budget',style_header) if budget: ws.write(i,1, budget,style_header) else: budget = 0.0 ws.write(i,1,budget,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, '',style_header) ws.write(i,4, '',style_header) ws.write(i,5, '',style_header) ws.write(i,6, '',style_header) ws.write(i,7, '',style_header) ws.write(i,8, total,style_header) if pay_total: ws.write(i,9,pay_total,style_header) else: ws.write(i,9, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,10, diff,style_header1) i += 2 total = pay_total = budget = 0.0 if each.department_id: salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days) ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+1,7, 'Total',style_header) ws.write(i+1,8, total,style_header) ws.write(i+3,7, 'Grand Total',style_header) ws.write(i+3,8, grand,style_header) ws.write(i+1,9, pay_total,style_header) ws.write(i+3,9, pay_grand,style_header) diff_pay = pay_total - total diff_grand = pay_grand - grand ws.write(i+1,10, diff_pay,style_header1) ws.write(i+3,10, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def account_budget(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if int(month) in [1,3,5,7,8,10,12]: join_date=year +'-'+month+'-'+'31' if int(month) in [4,6,9,11]: join_date=year +'-'+month+'-'+'30' if int(month) in [2]: if int(year) % 4 == 0: join_date=year +'-'+month+'-'+'29' else: join_date=year +'-'+month+'-'+'28' if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 #Define the font attributes for header fnt2 = Font() fnt2.name = 'Arial' fnt2.height= 275 #Define the font attributes for header content_fnt2 = Font() content_fnt2.name ='Arial' content_fnt2.height =220 align_content2 = Alignment() align_content2.horz= Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 #The text should be centrally aligned align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0x0A #apply the above settings to the row(0) header style_header2= XFStyle() style_header2.font= fnt2 style_header2.pattern= pattern2 style_header2.borders = borders2 style_header2.alignment=align2 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'RO of HOD',style_header) ws.col(2).width = 8000 ws.write(0,3,'Allocated Budget',style_header) ws.col(3).width = 8000 ws.write(0,4,'Salary Amount',style_header) ws.col(4).width = 5000 ws.write(0,5,month_name,style_header) ws.col(5).width = 8000 ws.write(0,6,'Difference',style_header) ws.col(6).width = 8000 ws.write(0,7,'No Of Emp',style_header) ws.col(7).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource " \ "as res on (emp.resource_id=res.id) left join hr_department as dept on " \ "(emp.department_id=dept.id) where emp.department_id = '"+str(this.dept_id.id)+"' and " \ "emp.department_id is not null and res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code, " \ "(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource " \ "as res on (emp.resource_id=res.id) left join hr_department as dept on (emp.department_id=dept.id) " \ "where res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code,(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=1 dept_dict = {} grand = total = pay_total = pay_grand = budget = total_budget = 0.0 pay_data = False flag = True count1=0.0 for each in emp_obj.browse(cr, uid, emp_ids): pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic total += salary grand += salary # if pay_data.employee_id.id == each.id: # print"ssssssssssssssssssss minssssssssss",count1 # count1=count1+1 if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 count1=count1+1 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: if flag: i -= 1 ws.write(i,3, total) ws.write(i,4, pay_total) if budget: diff = float(budget) - pay_total else: diff = pay_total if budget and diff > 0: ws.write(i,5, diff,style_header1) elif budget and diff < 0: ws.write(i,5, diff,style_header2) else: ws.write(i,5, diff,style_header2) ws.write(i,6, count1) count1=0.0 flag = False i += 1 # salary = 0.0 # name = '[' + str(each.sinid) +'] '+ str(each.name) # # ws.write(i,0, 'X Department') # ws.write(i,1,'X Reporting Officer') # ws.write(i,2, name) # # if each.daily: # salary = each.salary * working_day # else: # salary = each.salary # ws.write(i,3, salary) # if pay_data and pay_data.employee_id.id == each.id: # ws.write(i,4,pay_data.total_amount or 0.0) # else: # ws.write(i,4, 0.0) else: dept_dict[str(each.department_id.id)] = '' if i != 1: i -= 1 ws.write(i,4, total) ws.write(i,5, pay_total) if budget: diff = float(budget) - pay_total else: diff = pay_total if budget and diff > 0: ws.write(i,6, diff,style_header1) elif budget and diff < 0: ws.write(i,6, diff,style_header2) else: ws.write(i,6, diff,style_header2) ws.write(i,7, count1) count1=0.0 i += 1 if each.department_id: total = pay_total = budget = 0.0 salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') ws.write(i,3, budget) total_budget += budget if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic total += salary grand += salary # if pay_data.employee_id.id == each.id: # print"saaaaaaaaaaaaaallllllllllllllllllllllllllll",count1 # count1=count1+1 if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 count1=count1+1 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+3,1, 'Grand Total',style_header) ws.write(i+3,3, total_budget,style_header) ws.write(i+3,4, grand,style_header) ws.write(i+3,5, pay_grand,style_header) diff_grand = total_budget - pay_grand ws.write(i+3,6, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def produce_summary(self, cr, uid, travel, context=None): number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]') total_fnt = Font() total_fnt.name = 'Calibri' total_fnt.bold = True total_fnt.height = 16 * 20 # font size 12 total_cell_l_style = XFStyle() total_cell_l_style.alignment = Column.title_aln total_cell_l_style.borders = Borders() total_cell_l_style.borders.left = Borders.THICK total_cell_l_style.borders.right = Borders.HAIR total_cell_l_style.borders.top = Borders.THICK total_cell_l_style.borders.bottom = Borders.THICK total_cell_l_style.pattern = Column.title_ptn total_cell_l_style.num_format_str = number_format total_cell_l_style.font = Column.obj_fnt total_cell_r_style = XFStyle() total_cell_r_style.alignment = Column.title_aln total_cell_r_style.borders = Borders() total_cell_r_style.borders.left = Borders.HAIR total_cell_r_style.borders.right = Borders.THICK total_cell_r_style.borders.top = Borders.THICK total_cell_r_style.borders.bottom = Borders.THICK total_cell_r_style.pattern = Column.title_ptn total_cell_r_style.num_format_str = number_format total_cell_r_style.font = Column.obj_fnt sub_total_cell_label = Cell(_(u'SOUS-TOTAL'), Column.title_fnt, Column.title_aln, total_cell_l_style.borders, Column.title_ptn) total_cell_label = Cell(_(u'TOTAL'), total_fnt, Column.title_aln, total_cell_r_style.borders, Column.title_ptn, number_format) journeys = [i for i in travel.journey_ids] w = Workbook() ws = w.add_sheet(_('Travel Summary')) ws.row(2).height = 0x0280 ws.row(3 + len(journeys)).height = 0x0140 ws.row(4 + len(journeys)).height = 0x0180 row = 0 row += 2 # Write headers for i, col in enumerate(self._excel_columns): ws.col(i).width = col.width ws.write(row, i, col.text, col.style) row += 1 for i, obj in enumerate(journeys): ws.write(row + i, 0, i + 1, self._excel_columns[0].obj_style) for j in xrange(1, len(self._excel_columns)): ws.write(row + i, j, self._excel_columns[j].func(obj), self._excel_columns[j].obj_style) row += len(journeys) rate_index = next(i for i, x in enumerate(self._excel_columns) if x.text == _('TICKET RATE')) - 1 cost_index = next(i for i, x in enumerate(self._excel_columns) if x.text == _('COSTS')) - 1 total_index = next(i for i, x in enumerate(self._excel_columns) if x.text == _('TOTAL')) - 1 # Sub total label ws.write_merge(row, row, 0, rate_index, sub_total_cell_label.text, sub_total_cell_label.style) # Sub totals ws.write( row, rate_index + 1, Formula("SUM(%s%d:%s%d)" % (chr(66 + rate_index), 4, chr(66 + rate_index), row)), total_cell_l_style) ws.write( row, rate_index + 2, Formula("SUM(%s%d:%s%d)" % (chr(66 + cost_index), 4, chr(66 + cost_index), row)), total_cell_r_style) total_top_underline_style = XFStyle() total_top_underline_style.borders = Borders() total_top_underline_style.borders.top = Borders.THICK total_top_underline_style.font = Column.obj_fnt # Draw a line above total to close box ws.write(row, total_index + 1, "", total_top_underline_style) row += 1 # Total label ws.write_merge(row, row, 0, rate_index, total_cell_label.text, total_cell_label.style) # Total ws.write_merge( row, row, rate_index + 1, total_index, Formula("%s%d+%s%d" % (chr(66 + rate_index), row, chr(66 + cost_index), row)), total_cell_label.style) return w
def salary_payment_report(self,cr,uid,ids,context=None): #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.size=16 fnt.style= 'Regular' #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.size=16 content_fnt.style= 'Regular' align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x01 borders.right = 0x01 borders.top = 0x01 borders.bottom = 0x01 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt3 = Font() fnt3.name = 'Arial' fnt3.size=16 fnt3.style= 'Regular' #Define the font attributes for header content_fnt3 = Font() content_fnt3.name ='Arial' content_fnt3.size=16 content_fnt3.style= 'Regular' align_content3 = Alignment() align_content3.horz= Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x01 borders3.right = 0x01 borders3.top = 0x01 borders3.bottom = 0x01 #The text should be centrally aligned align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern3 = Pattern() #apply the above settings to the row(0) header style_header3= XFStyle() style_header3.font= fnt3 style_header3.pattern= pattern3 style_header3.borders = borders3 style_header3.alignment=align3 #Define the font attributes for header fnt4 = Font() fnt4.name = 'Arial' #Define the font attributes for header content_fnt4 = Font() content_fnt4.name ='Arial' align_content4 = Alignment() align_content4.horz= Alignment.HORZ_LEFT borders4 = Borders() borders4.left = 0x01 borders4.right = 0x01 borders4.top = 0x01 borders4.bottom = 0x01 #The text should be centrally aligned align4 = Alignment() align4.horz = Alignment.HORZ_LEFT align4.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern4 = Pattern() #apply the above settings to the row(0) header style_header4= XFStyle() style_header4.font= fnt4 style_header4.pattern= pattern4 style_header4.borders = borders4 style_header4.alignment=align4 #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.size=10 fnt1.Style= 'Regular' #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.size=10 content_fnt1.Style= 'Regular' align_content1 = Alignment() align_content1.horz= Alignment.HORZ_RIGHT borders1 = Borders() borders1.left = 0x01 borders1.right = 0x01 borders1.top = 0x01 borders1.bottom = 0x01 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_RIGHT align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 #Define the font attributes for header fnt2 = Font() fnt2.name = 'Arial' fnt2.size=16 fnt2.Style= 'Regular' #Define the font attributes for header content_fnt2 = Font() content_fnt2.name ='Arial' content_fnt2.size=16 content_fnt2.Style= 'Regular' align_content2 = Alignment() align_content2.horz= Alignment.HORZ_RIGHT borders2 = Borders() borders2.left = 0x01 borders2.right = 0x01 borders2.top = 0x01 borders2.bottom = 0x01 #The text should be centrally aligned align2 = Alignment() align2.horz = Alignment.HORZ_RIGHT align2.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern2 = Pattern() #apply the above settings to the row(0) header style_header2= XFStyle() style_header2.font= fnt2 style_header2.pattern= pattern style_header2.borders = borders2 style_header2.alignment=align2 wb = Workbook() ws = wb.add_sheet('Salary Report') this=self.browse(cr,uid,ids[0],context=context) ws.row(0).height=300 ws.row(1).height=300 ws.row(2).height=300 # ws.col(0).width = 5000 # ws.col(0).width = 5000 # ws.col(0).width = 5000 # ws.col(0).width = 5000 ws.write_merge(0,0,0,5, 'SALARY REPORT',style_header) ws.write_merge(1,1,0,5, ('DEPARTMENT : ',this.department_id.name),style_header) ws.write_merge(2,2,0,1, 'Month',style_header) ws.write_merge(2,2,2,3, 'Salary Amount',style_header) ws.write_merge(2,2,4,5, 'Remark',style_header) l=[] lst=[] for val in this.month_ids: i = 3 query="""select hy.name,spl.month, sum(spl.total_amount) from salary_payment_line as spl left join holiday_year as hy on spl.year_id = hy.id where month = '"""+str(val.month)+"""' and year_id = '"""+str(val.year_id.id)+"""' and curr_department = '"""+str(this.department_id.id)+"""' and salary_type='Salary' group by spl.month, hy.name""" cr.execute(query) temp = cr.fetchall() for year,month, total_amount in temp: t=() mon = int(month) t = (year,mon, total_amount) lst.append(t) total = 0.0 lst.sort() for year,month, total_amount in lst: if month == 1: month1 = 'January' elif month == 2: month1 = 'February' elif month == 3: month1 = 'March' elif month == 4: month1 = 'April' elif month == 5: month1 = 'May' elif month == 6: month1 = 'June' elif month == 7: month1 = 'July' elif month == 8: month1 = 'August' elif month == 9: month1 = 'September' elif month == 10: month1 = 'October' elif month == 11: month1 = 'November' else: month1 = 'December' total += total_amount ws.write_merge(i,i,0,1, (month1,' ',year),style_header4) ws.write_merge(i,i,2,3, round(total_amount,2),style_header1) ws.write_merge(i,i,4,5, ' ',style_header3) i += 1 ws.write_merge(i,i,0,1, 'Total Amount',style_header2) ws.write_merge(i,i,2,3, round(total,2),style_header2) ws.write_merge(i,i,4,5, ' ',style_header) if len(lst) < 1: raise osv.except_osv(_('Warning!'),_('No Record found!')) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) sal_report = self.write(cr, uid, ids, {'export_data':out, 'filename':'Salary Report.xls'}, context=context) return sal_report
def create_excel(work_excel, withdraw_info=[]): title_fnt = Font() title_fnt.height = 0x0140 title_fnt.name = u'宋体' title_fnt.bold = True brd = Borders() brd.bottom = 1 brd.top = 1 brd.left = 1 brd.right = 1 title_location = Alignment() title_location.horz = Alignment.HORZ_CENTER title_location.vert = Alignment.VERT_CENTER title_style = XFStyle() title_style.font = title_fnt title_style.alignment = title_location style = XFStyle() style.font.height = 0x00E0 style.font.name = u'宋体' style.font.bold = False style.alignment.horz = Alignment.HORZ_CENTER style.alignment.vert = Alignment.VERT_CENTER content_title_style = deepcopy(style) content_title_style.alignment.horz = Alignment.HORZ_LEFT content_title_style.font.height = 0x00E0 content_style = deepcopy(style) content_style.alignment.horz = Alignment.HORZ_LEFT center_content_style = deepcopy(style) center_content_style.alignment.horz = Alignment.HORZ_CENTER center_content_style.borders = brd style.borders = brd content_style.borders = brd merge_up_style = deepcopy(style) merge_up_style.borders.bottom = 0 content_up_style = deepcopy(merge_up_style) content_up_style.alignment.horz = Alignment.HORZ_CENTER merge_down_style = deepcopy(style) merge_down_style.borders.top = 0 content_down_style = deepcopy(merge_down_style) content_down_style.alignment.horz = Alignment.HORZ_RIGHT content_down_style.font.height = 0x00CA for i, info in enumerate(withdraw_info): width = i * 25 work_excel.write_merge(2 + width, 2 + width, 0, 16, u'商户提现付款申请单', title_style) work_excel.write_merge(3 + width, 3 + width, 1, 5, info['supplier_type'], content_title_style) work_excel.write_merge(3 + width, 3 + width, 6, 16, info['supplier'], content_title_style) work_excel.write(4 + width, 0, u'收款单位名称', style) work_excel.write_merge(4 + width, 4 + width, 1, 5, info['company'], content_style) work_excel.write(4 + width, 6, u'申请提现帐号', style) work_excel.write_merge(4 + width, 4 + width, 7, 16, info['applier'], center_content_style) work_excel.write(5 + width, 0, u'开户银行', style) work_excel.write_merge(5 + width, 5 + width, 1, 5, info['bank'], content_style) work_excel.write(5 + width, 6, u'申请提现日期', style) work_excel.write_merge(5 + width, 5 + width, 7, 16, info['apply_time'], center_content_style) work_excel.write(6 + width, 0, u'银行帐号', style) work_excel.write_merge(6 + width, 6 + width, 1, 5, info['account'], content_style) work_excel.write(6 + width, 6, u'合同账期', style) work_excel.write_merge(6 + width, 6 + width, 7, 16, info['payment_day_type'], center_content_style) work_excel.write(7 + width, 0, u'付款用途', style) work_excel.write_merge(7 + width, 7 + width, 1, 5, u'商户提现', content_style) work_excel.write(7 + width, 6, u'付款日期', style) work_excel.write_merge(7 + width, 7 + width, 7, 16, info['date'], center_content_style) units = [u'千', u'百', u'十', u'万', u'千', u'百', u'十', u'元', u'角', u'分'] digit = list(str(info['money'])) digit.remove('.') digit.reverse() for j, unit in enumerate(units): work_excel.write(8 + width, 7 + j, unit, content_style) for index, value in enumerate(digit): work_excel.write(9 + width, 16 - index, value, content_style) work_excel.write(9 + width, 16 - index - 1, '¥', content_style) work_excel.write_merge(10 + width, 11 + width, 0, 0, u'备注事项', style) work_excel.write_merge(10 + width, 10 + width, 1, 6, '', content_style) work_excel.write_merge(10 + width, 10 + width, 7, 16, '', style) work_excel.write_merge(11 + width, 11 + width, 1, 16, info['print_info'], content_down_style) work_excel.write(8 + width, 0, u'付款金额', merge_up_style) work_excel.write(9 + width, 0, u'人民币(大写)', merge_down_style) work_excel.write_merge(8 + width, 9 + width, 1, 6, info['china_money'], content_style) work_excel.write_merge(13 + width, 13 + width, 0, 2, u'部门主管:', content_title_style) work_excel.write_merge(13 + width, 13 + width, 3, 5, u'财务经理:', content_title_style) work_excel.write_merge(13 + width, 13 + width, 6, 16, u'公司总经理:', content_title_style) for row in range(2, 12): work_excel.row(row + width).height_mismatch = 1 work_excel.row(row + width).height = 478 work_excel.row(2 + width).height_mismatch = 1 work_excel.row(2 + width).height = 1000 work_excel.col(0).width = 3871 work_excel.col(1).width = 1771 work_excel.col(2).width = 1509 work_excel.col(3).width = 2348 work_excel.col(4).width = 840 work_excel.col(5).width = 3241 work_excel.col(6).width = 3441 for col in range(7, 17): work_excel.col(col).width = 709
def produce_summary(self, cr, uid, travel, context=None): number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]') total_fnt = Font() total_fnt.name = 'Calibri' total_fnt.bold = True total_fnt.height = 16 * 20 # font size 12 total_cell_l_style = XFStyle() total_cell_l_style.alignment = Column.title_aln total_cell_l_style.borders = Borders() total_cell_l_style.borders.left = Borders.THICK total_cell_l_style.borders.right = Borders.HAIR total_cell_l_style.borders.top = Borders.THICK total_cell_l_style.borders.bottom = Borders.THICK total_cell_l_style.pattern = Column.title_ptn total_cell_l_style.num_format_str = number_format total_cell_l_style.font = Column.obj_fnt total_cell_r_style = XFStyle() total_cell_r_style.alignment = Column.title_aln total_cell_r_style.borders = Borders() total_cell_r_style.borders.left = Borders.HAIR total_cell_r_style.borders.right = Borders.THICK total_cell_r_style.borders.top = Borders.THICK total_cell_r_style.borders.bottom = Borders.THICK total_cell_r_style.pattern = Column.title_ptn total_cell_r_style.num_format_str = number_format total_cell_r_style.font = Column.obj_fnt sub_total_cell_label = Cell( _(u'SOUS-TOTAL'), Column.title_fnt, Column.title_aln, total_cell_l_style.borders, Column.title_ptn) total_cell_label = Cell( _(u'TOTAL'), total_fnt, Column.title_aln, total_cell_r_style.borders, Column.title_ptn, number_format) journeys = [j for i in travel.passenger_ids for j in i.journey_ids] w = Workbook() ws = w.add_sheet(_('Travel Summary')) ws.row(2).height = 0x0280 ws.row(3 + len(journeys)).height = 0x0140 ws.row(4 + len(journeys)).height = 0x0180 row = 0 row += 2 _excel_columns = self.get_excel_columns(context) # Write headers for i, col in enumerate(_excel_columns): ws.col(i).width = col.width ws.write(row, i, col.text, col.style) row += 1 for i, obj in enumerate(journeys): ws.write(row + i, 0, i + 1, _excel_columns[0].obj_style) for j in xrange(1, len(_excel_columns)): ws.write(row + i, j, _excel_columns[j].func(obj), _excel_columns[j].obj_style) row += len(journeys) rate_index = [i for i, x in enumerate(_excel_columns) if x.text == _('TICKET RATE')][0] - 1 cost_index = [i for i, x in enumerate(_excel_columns) if x.text == _('COSTS')][0] - 1 total_index = [i for i, x in enumerate(_excel_columns) if x.text == _('TOTAL')][0] - 1 # Sub total label ws.write_merge(row, row, 0, rate_index, sub_total_cell_label.text, sub_total_cell_label.style) # Sub totals ws.write(row, rate_index + 1, Formula("SUM(%s%d:%s%d)" % (chr(66 + rate_index), 4, chr(66 + rate_index), row)), total_cell_l_style) ws.write(row, rate_index + 2, Formula("SUM(%s%d:%s%d)" % (chr(66 + cost_index), 4, chr(66 + cost_index), row)), total_cell_r_style) total_top_underline_style = XFStyle() total_top_underline_style.borders = Borders() total_top_underline_style.borders.top = Borders.THICK total_top_underline_style.font = Column.obj_fnt # Draw a line above total to close box ws.write(row, total_index + 1, "", total_top_underline_style) row += 1 # Total label ws.write_merge(row, row, 0, rate_index, total_cell_label.text, total_cell_label.style) # Total ws.write_merge(row, row, rate_index + 1, total_index, Formula("%s%d+%s%d" % (chr(66 + rate_index), row, chr(66 + cost_index), row)), total_cell_label.style) return w
import logging as log log.root.level = log.DEBUG from xlwt import Workbook, XFStyle, Borders, Pattern, Font from sqlalchemy import and_, or_, distinct, asc from sqlalchemy.orm import eagerload, eagerload_all try: import erp.model as m import erp.model.testing as tst except ImportError: import portal.model as m import portal.model.testing as tst fnt = Font() fnt.name = 'Arial' fnt.bold = True style_bold = XFStyle() style_bold.font = fnt border_bottom = Borders() border_bottom.bottom = Borders.THIN style_bold_border_bottom = XFStyle() style_bold_border_bottom.font = fnt style_bold_border_bottom.borders = border_bottom border_right = Borders() border_right.right = Borders.THIN
def exportReport1ToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1'); sheet1.col(1).width = 256*20; sheet1.col(2).width = 256*80; sheet1.col(3).width = 256*10; sheet1.col(4).width = 256*10; sheet1.col(5).width = 256*20; sheet1.col(6).width = 256*20; borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 wrap = Alignment(); wrap.wrap = 1; wrap.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True styleWrap = XFStyle(); styleWrap.alignment = wrap; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; styleDate.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = wrap; if( objectProject): i=0; row1 = sheet1.row(i) ; row1.write(0, ('ลำดับที่').decode('UTF8') ,styleHead); #sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ); row1.write(1, ('เลขความเสี่ยง').decode('UTF8'),styleHead ); row1.write(2, ('อุบัติการณ์/ภาวะไม่พึงประสงค์').decode('UTF8'),styleHead); row1.write(3, ('วันที่รายงาน').decode('UTF8'),styleHead ); row1.write(4, ('ความรุนแรง').decode('UTF8'),styleHead ); row1.write(5, ('ด้าน/โปรแกรม').decode('UTF8') ,styleHead); row1.write(6, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead); i=i+1; for value in objectProject: row1 = sheet1.row(i) ; row1.write(0, value.get('row') ,styleRowDetail ); row1.write(1, str(value.get('risk_id')).decode('UTF8'),styleRowDetail ); row1.write(2, value.get('detail').decode('UTF8'),StyleRowDetailWrap ); row1.write(3, value.get('report_date') ,styleDate ); row1.write(4, value.get('level').decode('UTF8') ,styleRowDetail ); row1.write(5, value.get('pro').decode('UTF8') ,styleRowDetail ); row1.write(6, value.get('reporter').decode('UTF8') ,styleRowDetail ); i=i+1; row2 = sheet1.row(i) ; row2.write(2, ('รายละเอียด').decode('UTF8'),styleHead); row2.write(3, ('หน่วยที่ตอบ').decode('UTF8'),styleHead ); row2.write(4, ('ระยะเวลาตอบ').decode('UTF8'),styleHead ); i=i+1; for resp in value.get('responsible'): row2 = sheet1.row(i) ; row2.write(2, str(resp.get('detail')).decode('UTF8'),StyleRowDetailWrap); row2.write(3, str(resp.get('service_name')).decode('UTF8'),styleRowDetail ); row2.write(4, str(resp.get('report_date')).decode('UTF8'),styleRowDetail ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simpleReport1.xls'); book.save(dirTempFile); return dirTempFile;