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 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 defineFontStyle(argFontName,argIsBold): style = XFStyle() font = Font() font.name = argFontName font.bold = argIsBold style.font = font return style
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 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
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 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 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 __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 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 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 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 _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 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 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 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 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 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 createSpreadsheet(recordset, dst_dir_path, dst_file_name): dst_dir_path = os.path.realpath(dst_dir_path) logger.debug("opening new workbook at %s" % (os.path.join(dst_dir_path, dst_file_name))) if not os.path.exists(dst_dir_path): logger.error("Destination path %s does not exist. Error: %s" % (dst_dir_path)) raise Exception("Destination path %s does not exist." % (dst_dir_path)) # Start some Excel magic wb = Workbook() ws0 = wb.add_sheet('Forecast') ws0.set_panes_frozen(True) ws0.set_horz_split_pos(1) logger.debug("Workbook created with name 'Forecast'") #0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray #Create the default font defaultFont = Font() defaultFont.name = 'Arial' defaultFont.bold = False defaultFont.size = 10 #create the default Pattern defaultPattern = Pattern() #create the default style defaultStyle = XFStyle() defaultStyle.font = defaultFont #Create the failure font failureFont = Font() failureFont.name = 'Arial' failureFont.bold = True failureFont.size = 10 failureFont.colour = 'red' #create the failure Pattern failurePattern = Pattern() failurePattern.pattern = Pattern.SOLID_PATTERN failurePattern.pattern_fore_colour = 2 #Create the failure style #failureStyle = XFStyle() #easyxf( 'font: colour red, bold True, size 10, name Arial;') #failureStyle.font = failureFont #failureStyle.pattern = failurePattern failureStyle = easyxf('font: bold 1, name Arial , height 200, color red;') #create the failure Pattern runningPattern = Pattern() runningPattern.pattern = Pattern.SOLID_PATTERN runningPattern.pattern_fore_colour = 3 #Create a running style runningStyle = XFStyle() runningStyle.font = defaultFont runningStyle.pattern = runningPattern # Grey background for the header row headerPattern = Pattern() headerPattern.pattern = Pattern.SOLID_PATTERN headerPattern.pattern_fore_colour = 22 # Bold Fonts for the header row headerFont = Font() headerFont.name = 'Arial' headerFont.bold = True headerFont.size = 10 # style and write field labels headerStyle = XFStyle() headerStyle.font = headerFont headerStyle.pattern = headerPattern logger.debug("Writing data to worksheet.") row_number = 1 col_width_dict = dict() for i in range(8): col_width_dict[i] = 0 for record in recordset: currentStyle = defaultStyle # if str(record[4]).upper() == "FAILURE": # logger.debug( "Status %s is in FAILURE" % ( str(record[5] ) ) ) # currentStyle = failureStyle # elif str(record[4] ).upper() == "RUNNING": # logger.debug( "Status %s is in RUNNING" % ( str(record[5] ) ) ) # currentStyle = runningStyle column = 0 for field in record: #i.e. for each field in the record logger.debug("Writing data field %s to worksheet" % (str(field))) if field: ws0.write( row_number, column, str(field), currentStyle) #write excel cell from the cursor at row 1 else: ws0.write( row_number, column, "", currentStyle) #write excel cell from the cursor at row 1 if len(str(field)) > col_width_dict[column]: #only redefine the column width if we need it to be bigger col_width_dict[column] = len(str(field)) ws0.col(column).width = len(str(field)) * 256 ws0.col(column).width = col_width_dict[column] * 256 column = column + 1 #increment the column to get the next field row_number += 1 logger.debug("Writing header row to worksheet.") ws0.write(0, 0, 'DAY', headerStyle) ws0.col(0).width = 10 * 256 ws0.write(0, 1, 'JOB NAME', headerStyle) ws0.write(0, 2, 'UC', headerStyle) ws0.col(2).width = 17 * 256 ws0.write(0, 3, 'START TIME', headerStyle) ws0.write(0, 4, 'PERIODICALLY', headerStyle) ws0.col(4).width = 16 * 256 ws0.write(0, 5, 'STATUS', headerStyle) ws0.col(5).width = 11 * 256 logger.debug("Writing excel file %s" % (os.path.join(dst_dir_path, dst_file_name))) wb.save(os.path.join(dst_dir_path, dst_file_name)) return 0
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 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;
def performance_register_report(self, cr, uid, ids, data, context=None): obj = self.browse(cr, uid, ids) emp_obj = self.pool.get('hr.employee') f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('Contractor Payment Bonus') total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x00 borders1.right = 0x00 borders1.top = 0x00 borders1.bottom = 0x00 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x1F style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 300 fnt2.bold = True align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x00 borders2.right = 0x00 borders2.top = 0x00 borders2.bottom = 0x00 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x1F style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 300 fnt3.bold = True align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x00 borders3.right = 0x00 borders3.top = 0x00 borders3.bottom = 0x00 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x1F style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 200 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() align_content5.horz = Alignment.HORZ_CENTER borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.horz = Alignment.HORZ_CENTER align5.vert = Alignment.VERT_CENTER pattern5 = Pattern() # pattern5.pattern = Pattern.SOLID_PATTERN # pattern5.pattern_fore_colour = 0x1F style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 if obj.partner_id: get_name = obj.partner_id.name elif obj.company_id: get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip else: get_name = obj.employee_id.partner_id.name ws.row(0).height = 500 ws.write_merge(0, 0, 0, 19, get_name, style_header1) date1 = datetime.strptime(obj.from_date, "%Y-%m-%d").timetuple().tm_year date2 = datetime.strptime(obj.till_date, "%Y-%m-%d").timetuple().tm_year if date1 == date2: d_name = 'BONUS' + ' - ' + str(date1) else: d_name = 'BONUS' + ' ' + str(date1) + ' ' + '-' + ' ' + str(date2) ws.row(1).height = 500 ws.write_merge(1, 1, 0, 19, d_name, style_header2) ws.col(0).width = 5000 ws.col(1).width = 7500 ws.col(2).width = 5000 ws.col(3).width = 3000 ws.col(4).width = 3000 ws.col(5).width = 3000 ws.col(6).width = 3000 ws.col(7).width = 3000 ws.col(8).width = 3000 ws.col(9).width = 3000 ws.col(10).width = 3000 ws.col(11).width = 3000 ws.col(12).width = 3000 ws.col(13).width = 3000 ws.col(14).width = 3000 ws.col(15).width = 3000 ws.col(16).width = 3000 ws.col(17).width = 3000 ws.col(18).width = 3000 ws.col(19).width = 3000 ws.col(20).width = 3000 ws.col(21).width = 3000 ws.col(22).width = 3000 ws.col(23).width = 3000 ws.col(24).width = 3000 ws.col(25).width = 3000 ws.col(26).width = 3000 ws.col(27).width = 3000 ws.col(28).width = 3000 ws.col(29).width = 4000 ws.row(2).height = 400 ws.write(2, 0, 'EMP. CODE', style_header) ws.write(2, 1, 'NAME', style_header) ws.write(2, 2, 'JOINING DATE', style_header) ws.write_merge(2, 2, 3, 4, 'APRIL', style_header) ws.write_merge(2, 2, 5, 6, 'MAY', style_header) ws.write_merge(2, 2, 7, 8, 'JUNE', style_header) ws.write_merge(2, 2, 9, 10, 'JULY', style_header) ws.write_merge(2, 2, 11, 12, 'AUGUST', style_header) ws.write_merge(2, 2, 13, 14, 'SEPTEMBER', style_header) ws.write_merge(2, 2, 15, 16, 'OCTOBER', style_header) ws.write_merge(2, 2, 17, 18, 'NOVEMBER', style_header) ws.write_merge(2, 2, 19, 20, 'DECEMBER', style_header) ws.write_merge(2, 2, 21, 22, 'JANUARY', style_header) ws.write_merge(2, 2, 23, 24, 'FEBRUARY', style_header) ws.write_merge(2, 2, 25, 26, 'MARCH', style_header) ws.write_merge(2, 2, 27, 28, 'TOTAL', style_header) ws.write(2, 29, 'BONUS', style_header) ws.row(3).height = 400 ws.write(3, 0, '', style_header) ws.write(3, 1, '', style_header) ws.write(3, 2, '', style_header) ws.write(3, 3, 'DAYS', style_header) ws.write(3, 4, 'SALARY', style_header) ws.write(3, 5, 'DAYS', style_header) ws.write(3, 6, 'SALARY', style_header) ws.write(3, 7, 'DAYS', style_header) ws.write(3, 8, 'SALARY', style_header) ws.write(3, 9, 'DAYS', style_header) ws.write(3, 10, 'SALARY', style_header) ws.write(3, 11, 'DAYS', style_header) ws.write(3, 12, 'SALARY', style_header) ws.write(3, 13, 'DAYS', style_header) ws.write(3, 14, 'SALARY', style_header) ws.write(3, 15, 'DAYS', style_header) ws.write(3, 16, 'SALARY', style_header) ws.write(3, 17, 'DAYS', style_header) ws.write(3, 18, 'SALARY', style_header) ws.write(3, 19, 'DAYS', style_header) ws.write(3, 20, 'SALARY', style_header) ws.write(3, 21, 'DAYS', style_header) ws.write(3, 22, 'SALARY', style_header) ws.write(3, 23, 'DAYS', style_header) ws.write(3, 24, 'SALARY', style_header) ws.write(3, 25, 'DAYS', style_header) ws.write(3, 26, 'SALARY', style_header) ws.write(3, 27, 'DAYS', style_header) ws.write(3, 28, 'SALARY', style_header) ws.write(3, 29, '', style_header) if obj.partner_id: list_ids = emp_obj.search(cr, uid, [('partner_id', '=', obj.partner_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) list_ids1 = emp_obj.search(cr, uid, [('partner_id', '=', obj.partner_id.id), ('active', '=', False), ('type', '=', 'Contractor')]) list_ids = list_ids + list_ids1 elif obj.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', obj.employee_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) elif obj.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', False), ('type', '=', 'Contractor')]) list_ids = list_ids + list_ids1 else: raise osv.except_osv( _('Warning !'), _("Please Select Atleast Company Or Employee.")) if len(list_ids) == 1: query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\ "where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\ "where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) columnno = 4 for val in temp: doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y") ws.write(columnno, 0, val[0], style_header5) ws.write(columnno, 1, val[1], style_header5) ws.write(columnno, 2, doj, style_header5) ws.write(columnno, 3, val[3], style_header5) ws.write(columnno, 4, val[17], style_header5) ws.write(columnno, 5, val[4], style_header5) ws.write(columnno, 6, val[18], style_header5) ws.write(columnno, 7, val[5], style_header5) ws.write(columnno, 8, val[19], style_header5) ws.write(columnno, 9, val[6], style_header5) ws.write(columnno, 10, val[20], style_header5) ws.write(columnno, 11, val[7], style_header5) ws.write(columnno, 12, val[21], style_header5) ws.write(columnno, 13, val[8], style_header5) ws.write(columnno, 14, val[22], style_header5) ws.write(columnno, 15, val[9], style_header5) ws.write(columnno, 16, val[23], style_header5) ws.write(columnno, 17, val[10], style_header5) ws.write(columnno, 18, val[24], style_header5) ws.write(columnno, 19, val[11], style_header5) ws.write(columnno, 20, val[25], style_header5) ws.write(columnno, 21, val[12], style_header5) ws.write(columnno, 22, val[26], style_header5) ws.write(columnno, 23, val[13], style_header5) ws.write(columnno, 24, val[27], style_header5) ws.write(columnno, 25, val[14], style_header5) ws.write(columnno, 26, val[28], style_header5) ws.write(columnno, 27, val[15], style_header5) ws.write(columnno, 28, val[29], style_header5) ws.write(columnno, 29, val[16], style_header5) columnno += 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Contractor Payment Bonus.xls' }, context=context)
def get_attendance_report(self): try: import xlwt except: raise Warning(_('User Error'), _('Please Install xlwt Library.!')) filename = 'Attendance Register.xls' fp = BytesIO() wb = xlwt.Workbook(encoding='utf-8') worksheet = wb.add_sheet('PC_EXAM_ATTENDANCE_REGISTER') current_obj = self # ----------------------- # Excel Font Style & Sizes # ---------------------- fnt1 = Font() fnt1.name = 'TimesNewRoman' fnt1.bold = True fnt1.height = 16 * 0x14 fnt2 = Font() fnt2.name = 'verdana' fnt2.bold = True fnt2.height = 12 * 0x14 fnt3 = Font() fnt3.name = 'TimesNewRoman' fnt3.bold = True fnt3.height = 13 * 0x12 fnt4 = Font() fnt4.name = 'TimesNewRoman' fnt4.height = 16 * 0x14 # ----------------- # Excel Alignment # ---------------- al3 = Alignment() al3.horz = Alignment.HORZ_CENTER al3.vert = Alignment.VERT_CENTER al4 = Alignment() al4.horz = Alignment.HORZ_LEFT al4.vert = Alignment.VERT_CENTER al5 = Alignment() al5.horz = Alignment.HORZ_LEFT al5.vert = Alignment.VERT_CENTER # ----------------------- # Excel Style # ---------------------- style1 = XFStyle() style1.alignment = al3 style1.font = fnt1 style2 = XFStyle() style2.font = fnt2 style2.alignment = al4 style3 = XFStyle() style3.alignment = al3 style3.font = fnt3 style4 = XFStyle() style4.alignment = al5 style4.font = fnt4 # ------------------------------------------------------------------------- # HEADER In Excel # ------------------------------------------------------------------------- lst = [] fields = ['CIMA STUDENT NUMBER', 'SURNAME', 'NAME', 'SIGNATURE'] for i in [1, 2, 3, 4]: first_col = worksheet.col(i) first_col.width = 500 * 20 date_begin = str( datetime.strptime(current_obj.date_begin, "%Y-%m-%d %H:%M:%S") + timedelta(hours=2)) date_end = str( datetime.strptime(current_obj.date_end, "%Y-%m-%d %H:%M:%S") + timedelta(hours=2)) worksheet.merge(0, 16, 1, 4) try: worksheet.insert_bitmap( '/opt/custom_modules/event_price_kt/images/charterquest.bmp', 0, 1, 9, 3) except: pass worksheet.write_merge(17, 18, 1, 1, 'SUBJECT:', style1) worksheet.write_merge(17, 18, 2, 4, current_obj.name, style1) k = 19 pc_exam = True if current_obj.pc_exam == False: pc_exam = False worksheet.write_merge(k, k + 1, 1, 1, 'LECTURER:', style1) # if current_obj.main_speaker_id: # worksheet.write_merge(k,k+1,2, 4,current_obj.main_speaker_id.name , style1) # else: # worksheet.write_merge(k,k+1,2, 4,"", style1) k = k + 2 worksheet.write_merge(k, k + 1, 1, 1, 'DATE:', style1) worksheet.write_merge(k, k + 1, 2, 4, (date_begin).split(' ')[0], style1) worksheet.write_merge(k + 2, k + 3, 1, 1, 'TIME:', style1) worksheet.write_merge(k + 2, k + 3, 2, 4, (date_begin)[11:16] + '-' + (date_end)[11:16], style1) if not pc_exam: worksheet.write_merge(k + 4, k + 5, 1, 1, 'COURSE STUDY OPTION:', style1) # worksheet.write_merge(k+4,k+5,2, 4,current_obj.study.name, style1) k = k + 4 else: k = k + 2 worksheet.write_merge(k + 2, k + 3, 1, 1, 'CAMPUS:', style1) worksheet.write_merge(k + 2, k + 3, 2, 4, current_obj.address_id.name, style1) worksheet.write_merge( k + 4, k + 5, 1, 4, "PLEASE SIGN ATTENDANCE REGISTER & ENTER CIMA STUDENT NUMBER", style1) i = 1 for field in fields: worksheet.write_merge(k + 6, k + 7, i, i, field, style1) i += 1 i = k + 8 for obj in current_obj.registration_ids: partner_name = obj.partner_id.name name = partner_name.split(' ') if len(name) == 1: name = partner_name.split(' ') if obj.partner_id.prof_body_id: worksheet.write_merge(i, i + 1, 1, 1, obj.partner_id.prof_body_id, style4) else: worksheet.write_merge(i, i + 1, 1, 1, "", style4) try: worksheet.write_merge(i, i + 1, 2, 2, name[1], style4) except: worksheet.write_merge(i, i + 1, 2, 2, '', style4) worksheet.write_merge(i, i + 1, 3, 3, name[0], style4) worksheet.write_merge(i, i + 1, 4, 4, '', style4) i += 2 wb.save(fp) out = base64.encodestring(fp.getvalue()) final_arr_data = {} final_arr_data['file_stream'] = out final_arr_data['name'] = filename pl_report_id = self.env['attendance.sheet.report'].create( final_arr_data) vals = { 'name': 'Attendance Sheet Report', 'datas': out, 'datas_fname': filename } return { 'res_id': pl_report_id.id, 'name': filename, 'view_type': 'form', 'view_mode': 'form', 'res_model': 'attendance.sheet.report', 'type': 'ir.actions.act_window', }
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 style_bold_border_right = XFStyle()
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
def pf_upload_report(self, cr, uid, ids, context=None): f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('CONTRACTOR PF UPLOAD') fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x17 style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 250 fnt2.bold = False align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x09 style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 275 fnt3.bold = False align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x02 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x09 style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x16 style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 275 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() # align_content5.horz= Alignment.HORZ_JUSTIFIED borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() # align5.horz = Alignment.HORZ_JUSTIFIED align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 ws.row(0).height = 1500 ws.col(0).width = 6500 ws.col(1).width = 5000 ws.col(2).width = 10000 ws.col(3).width = 4000 ws.col(4).width = 4000 ws.col(5).width = 4500 ws.col(6).width = 4000 ws.col(7).width = 5500 ws.col(8).width = 4000 ws.col(9).width = 4500 ws.col(10).width = 4000 ws.col(11).width = 4000 ws.write(0, 0, 'Employee Pay Code', style_header) ws.write(0, 1, 'UAN Number', style_header) ws.write(0, 2, 'Member Name', style_header) ws.write(0, 3, 'Gross Wages', style_header) ws.write(0, 4, 'EPF Wages', style_header) ws.write(0, 5, 'EPS Wages', style_header) ws.write(0, 6, 'EDLI Wages', style_header) ws.write(0, 7, 'EE Share', style_header) ws.write(0, 8, 'EPS Contribution', style_header) ws.write(0, 9, 'ER Share', style_header) ws.write(0, 10, 'NCP Days', style_header) ws.write(0, 11, 'Refund', style_header) this = self.browse(cr, uid, ids[0], context=context) month = this.month.month company_id = this.company_id.id year = this.month.year_id.id year_name = this.month.year_id.name partner_id = this.partner_id.id emp_obj = self.pool.get('hr.employee') # pf_no=' ' emp_name = ' ' epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 diff_eps_epf = 0 full_date_month = [] total_epf_wages = 0 total_epf_cont = 0 total_eps_cont = 0 total_diff_calc = 0 total_diff_diff = 0 total_gross = 0 holiday_list = [] inact_list = [] inact_list1 = [] hr_list = [] i = 1 val_zero = 0 # epf_date_start='' if len(str(month)) == 1: start_date = str(year_name) + '-' + '0' + str(month) + '-' + '01' month_val = '0' + str(month) else: start_date = str(year_name) + '-' + str(month) + '-' + '01' month_val = str(month) if int(this.month.month) in [1, 3, 5, 7, 8, 10, 12]: month_tup = 31 if int(this.month.month) in [4, 6, 9, 11]: month_tup = 30 if int(this.month.month) in [2]: if int(this.month.year_id.name) % 4 == 0: month_tup = 29 else: month_tup = 28 month_val = int(month_val) # month_tup = calendar.monthrange(year,month_val) end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup) # month_val=int(month_val) # month_tup = calendar.monthrange(year,month_val) # end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup[1]) for month_date in rrule.rrule( rrule.DAILY, dtstart=datetime.strptime(start_date, '%Y-%m-%d'), until=datetime.strptime(end_date, '%Y-%m-%d')): month_date = datetime.strftime(month_date, "%Y-%m-%d") full_date_month.append(month_date) if this.company_id: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('type', '=', 'Contractor')]) inact_ids = emp_obj.search(cr, uid, [('active', '=', False), ('company_id', '=', company_id), ('epf_tick', '=', True), ('epf_end_date', '>=', start_date), ('epf_end_date', '<=', end_date), ('type', '=', 'Contractor')]) elif this.partner_id: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('partner_id', '=', partner_id), ('epf_tick', '=', True), ('type', '=', 'Contractor')]) inact_ids = emp_obj.search(cr, uid, [('active', '=', False), ('partner_id', '=', partner_id), ('epf_tick', '=', True), ('epf_end_date', '>=', start_date), ('epf_end_date', '<=', end_date), ('type', '=', 'Contractor')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if inact_ids: for val in inact_ids: inact_emp_browse = emp_obj.browse(cr, uid, val) if inact_emp_browse: inact_id = val inact_emp_name = inact_emp_browse.name inact_emp_sinid = inact_emp_browse.sinid inact_days_amount = 0 tup = (inact_id, inact_emp_name, inact_days_amount, inact_emp_sinid) inact_list.append(tup) inact_list1.append(val) if len(list_ids) == 1: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() else: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) temp_add_value1 = temp1 + inact_list1 emp_list = emp_obj.search(cr, uid, [('id', 'not in', temp_add_value1), ('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('doj', '<=', end_date), ('type', '=', 'Contractor')]) if emp_list: for valll in emp_list: emp_list_ids = emp_obj.browse(cr, uid, valll) hr_id = valll hr_name = emp_list_ids.name hr_sinid = emp_list_ids.sinid hr_days_amount = 0 tup = (hr_id, hr_name, hr_days_amount, hr_sinid) hr_list.append(tup) if temp: temp_add_value = temp + inact_list + hr_list temp_add_value = sorted(temp_add_value, key=lambda x: x[3]) for val in temp_add_value: epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 epf_gross_wages = 0 ws.row(i).height = 500 emp_browse = emp_obj.browse(cr, uid, val[0]) uan_number = emp_browse.uan if val[2]: gross = val[7] + val[8] else: gross = 0 if len(val) > 5: epf_cont = val[4] eps_cont = val[5] calc_eps_epf = val[6] epf_gross_wages = round(val[2], 0) diff_eps_epf = (epf_cont - eps_cont) date_of_join_epf = emp_browse.epf_start_date date_of_exit_epf = emp_browse.epf_end_date total_epf_wages += val[2] total_epf_cont += epf_cont total_eps_cont += eps_cont total_diff_calc += calc_eps_epf total_diff_diff += diff_eps_epf total_gross += gross ws.write(i, 0, (val[3]), style_header2) ws.write(i, 1, uan_number, style_header2) ws.write(i, 2, (val[1]), style_header2) ws.write(i, 3, gross, style_header2) ws.write(i, 4, epf_gross_wages, style_header2) ws.write(i, 5, epf_gross_wages, style_header2) ws.write(i, 6, epf_gross_wages, style_header2) ws.write(i, 7, epf_cont, style_header2) ws.write(i, 8, eps_cont, style_header2) ws.write(i, 9, calc_eps_epf, style_header2) ws.write(i, 10, val_zero, style_header2) ws.write(i, 11, val_zero, style_header2) i = i + 1 ws.row(i + 1).height = 500 ws.write(i + 1, 2, 'TOTAL', style_header1) ws.write(i + 1, 3, total_gross, style_header1) ws.write(i + 1, 4, total_epf_wages, style_header1) ws.write(i + 1, 5, total_epf_wages, style_header1) ws.write(i + 1, 6, total_epf_wages, style_header1) ws.write(i + 1, 7, total_epf_cont, style_header1) ws.write(i + 1, 8, total_eps_cont, style_header1) ws.write(i + 1, 9, total_diff_calc, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) pf_upload_report = self.write( cr, uid, ids, { 'export_data': out, 'filename': 'contractor_pf_upload.xls' }, context=context) return pf_upload_report
def pf_upload_report(self, cr, uid, ids, context=None): # req = urllib2.Request('http://www.voidspace.org.uk') # response = urllib2.urlopen(req) # the_page = response.read() # print " html===========================",the_page f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('PF UPLOAD') fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x17 style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 250 fnt2.bold = False align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x09 style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 275 fnt3.bold = False align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x02 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x09 style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x16 style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 275 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() # align_content5.horz= Alignment.HORZ_JUSTIFIED borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() # align5.horz = Alignment.HORZ_JUSTIFIED align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 ws.row(0).height = 1500 ws.col(0).width = 6500 ws.col(1).width = 5000 ws.col(2).width = 10000 ws.col(3).width = 4000 ws.col(4).width = 4000 ws.col(5).width = 4500 ws.col(6).width = 4000 ws.col(7).width = 5500 ws.col(8).width = 4000 ws.col(9).width = 4500 ws.col(10).width = 4000 ws.col(11).width = 4000 # ws.col(12).width = 6500 # ws.col(13).width = 6500 # ws.col(14).width = 6500 # ws.col(15).width = 6500 # ws.col(16).width = 4500 # ws.col(17).width = 7500 # ws.col(18).width = 3000 # ws.col(19).width = 4000 # ws.col(20).width = 3000 # ws.col(21).width = 6500 # ws.col(22).width = 6500 # ws.col(23).width = 7500 # ws.col(24).width = 7500 # ws.col(25).width = 2500 # ws.col(26).width = 5000 # ws.col(27).width = 4000 ws.write(0, 0, 'Employee Pay Code', style_header) ws.write(0, 1, 'UAN Number', style_header) ws.write(0, 2, 'Member Name', style_header) ws.write(0, 3, 'Gross Wages', style_header) ws.write(0, 4, 'EPF Wages', style_header) ws.write(0, 5, 'EPS Wages', style_header) ws.write(0, 6, 'EDLI Wages', style_header) ws.write(0, 7, 'EE Share', style_header) ws.write(0, 8, 'EPS Contribution', style_header) ws.write(0, 9, 'ER Share', style_header) ws.write(0, 10, 'NCP Days', style_header) ws.write(0, 11, 'Refund', style_header) # ws.write(0,1,'Member ID',style_header) # ws.write(0,6,'EPF Contribution (EE Share) being remitted',style_header5) # ws.write(0,8,'EPS Contribution being remitted',style_header5) # ws.write(0,10,'Diff EPF and EPS Contribution (ER Share) being remitted',style_header5) # ws.write(0,15,'Arrear EPF Wages',style_header) # ws.write(0,16,'Arrear EPF EE Share',style_header) # ws.write(0,17,'Arrear EPF ER Share',style_header) # ws.write(0,18,'Arrear EPS',style_header) # ws.write(0,19,'Father / Husband Name',style_header) # ws.write(0,20,'Relationship with the Member',style_header5) # ws.write(0,21,'Date of Birth',style_header) # ws.write(0,22,'Gender',style_header) # ws.write(0,23,'Date of Joining EPF',style_header) # ws.write(0,24,'Date of Joining EPS',style_header) # ws.write(0,25,'Date of Exit From EPF',style_header) # ws.write(0,26,'Date of Exit From EPS',style_header) # ws.write(0,27,'Reason for Leaving',style_header5) this = self.browse(cr, uid, ids[0], context=context) month = this.month.month company_id = this.company_id.id year = this.month.year_id.id year_name = this.month.year_id.name emp_obj = self.pool.get('hr.employee') # pf_no=' ' emp_name = ' ' epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 diff_eps_epf = 0 full_date_month = [] total_epf_wages = 0 total_epf_cont = 0 total_eps_cont = 0 total_diff_calc = 0 total_diff_diff = 0 total_gross = 0 holiday_list = [] inact_list = [] inact_list1 = [] hr_list = [] i = 1 val_zero = 0 # epf_date_start='' if len(str(month)) == 1: start_date = str(year_name) + '-' + '0' + str(month) + '-' + '01' month_val = '0' + str(month) else: start_date = str(year_name) + '-' + str(month) + '-' + '01' month_val = str(month) if int(this.month.month) in [1, 3, 5, 7, 8, 10, 12]: month_tup = 31 if int(this.month.month) in [4, 6, 9, 11]: month_tup = 30 if int(this.month.month) in [2]: if int(this.month.year_id.name) % 4 == 0: month_tup = 29 else: month_tup = 28 month_val = int(month_val) # month_tup = calendar.monthrange(year,month_val) end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup) for month_date in rrule.rrule( rrule.DAILY, dtstart=datetime.strptime(start_date, '%Y-%m-%d'), until=datetime.strptime(end_date, '%Y-%m-%d')): month_date = datetime.strftime(month_date, "%Y-%m-%d") full_date_month.append(month_date) if this.company_id: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('type', '=', 'Employee')]) inact_ids = emp_obj.search(cr, uid, [('active', '=', False), ('company_id', '=', company_id), ('epf_tick', '=', True), ('epf_end_date', '>=', start_date), ('epf_end_date', '<=', end_date), ('type', '=', 'Employee')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if inact_ids: for val in inact_ids: inact_emp_browse = emp_obj.browse(cr, uid, val) if inact_emp_browse: inact_id = val inact_emp_name = inact_emp_browse.name inact_emp_sinid = inact_emp_browse.sinid inact_days_amount = 0 tup = (inact_id, inact_emp_name, inact_days_amount, inact_emp_sinid) inact_list.append(tup) inact_list1.append(val) if len(list_ids) == 1: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() else: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) temp_add_value1 = temp1 + inact_list1 emp_list = emp_obj.search(cr, uid, [('id', 'not in', temp_add_value1), ('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('doj', '<=', end_date), ('type', '=', 'Employee')]) if emp_list: for valll in emp_list: emp_list_ids = emp_obj.browse(cr, uid, valll) hr_id = valll hr_name = emp_list_ids.name hr_sinid = emp_list_ids.sinid hr_days_amount = 0 tup = (hr_id, hr_name, hr_days_amount, hr_sinid) hr_list.append(tup) if temp: temp_add_value = temp + inact_list + hr_list temp_add_value = sorted(temp_add_value, key=lambda x: x[3]) for val in temp_add_value: epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 epf_gross_wages = 0 ws.row(i).height = 500 emp_browse = emp_obj.browse(cr, uid, val[0]) # emp_pf=emp_browse.pf_number uan_number = emp_browse.uan if val[2]: gross = val[7] + val[8] else: gross = 0 # if '/' in emp_pf : # emp_pf=emp_pf.upper().split('/') # emp_pf_len=len(emp_pf)-1 # if emp_pf : # pf_no=emp_pf[emp_pf_len] if len(val) > 5: epf_cont = val[4] eps_cont = val[5] calc_eps_epf = val[6] epf_gross_wages = round(val[2], 0) diff_eps_epf = (epf_cont - eps_cont) date_of_join_epf = emp_browse.epf_start_date date_of_exit_epf = emp_browse.epf_end_date # f_name='' # relation_member = '' # date_of_birth='' # gender= '' # epf_date_start='' # leave_reason=' ' # epf_end_date=' ' # if date_of_join_epf : # if date_of_join_epf in full_date_month : # epf_date_start=datetime.strptime(date_of_join_epf,'%Y-%m-%d') # epf_date_start=epf_date_start.strftime('%d-%m-%Y') # query1 = cr.execute("select name from family where employee_id='"+str(val[0])+"' and relation='Father' ") # temp1 = cr.fetchall() # if temp1 : # f_name = temp1[0][0] # relation_member='F' # date_of_birth=emp_browse.birthday # date_of_birth=datetime.strptime(date_of_birth,'%Y-%m-%d') # date_of_birth=date_of_birth.strftime('%d-%m-%Y') # gender=emp_browse.gender # gender=gender.upper()[0] # if date_of_exit_epf : # leave_reason=emp_browse.leaving_reason[0] # epf_end_date=datetime.strptime(date_of_exit_epf,'%Y-%m-%d') # epf_end_date=epf_end_date.strftime('%d-%m-%Y') # if date_of_join_epf and not date_of_exit_epf : # leave_reason='&' # epf_end_date='&' # if not date_of_join_epf and date_of_exit_epf : # epf_date_start='&' total_epf_wages += val[2] total_epf_cont += epf_cont total_eps_cont += eps_cont total_diff_calc += calc_eps_epf total_diff_diff += diff_eps_epf total_gross += gross ws.write(i, 0, (val[3]), style_header2) ws.write(i, 1, uan_number, style_header2) # ws.write(i,1,pf_no,style_header2) ws.write(i, 2, (val[1]), style_header2) ws.write(i, 3, gross, style_header2) ws.write(i, 4, epf_gross_wages, style_header2) ws.write(i, 5, epf_gross_wages, style_header2) ws.write(i, 6, epf_gross_wages, style_header2) ws.write(i, 7, epf_cont, style_header2) # ws.write(i,6,epf_cont,style_header2) ws.write(i, 8, eps_cont, style_header2) # ws.write(i,8,eps_cont,style_header2) ws.write(i, 9, calc_eps_epf, style_header2) # ws.write(i,10,diff_eps_epf,style_header2) ws.write(i, 10, val_zero, style_header2) ws.write(i, 11, val_zero, style_header2) # ws.write(i,15,val_zero,style_header2) # ws.write(i,16,val_zero,style_header2) # ws.write(i,17,val_zero,style_header2) # ws.write(i,18,val_zero,style_header2) # ws.write(i,19,f_name and f_name or '&',style_header2) # ws.write(i,20,relation_member and relation_member or '&',style_header2) # ws.write(i,21,date_of_birth and date_of_birth or '&',style_header2) # ws.write(i,22,gender and gender or '&',style_header2) # ws.write(i,23,epf_date_start and epf_date_start or '&',style_header2) # ws.write(i,24,epf_date_start and epf_date_start or '&',style_header2) # ws.write(i,25,epf_end_date,style_header2) # ws.write(i,26,epf_end_date,style_header2) # ws.write(i,27,leave_reason,style_header2) i = i + 1 ws.row(i + 1).height = 500 ws.write(i + 1, 2, 'TOTAL', style_header1) ws.write(i + 1, 3, total_gross, style_header1) ws.write(i + 1, 4, total_epf_wages, style_header1) ws.write(i + 1, 5, total_epf_wages, style_header1) ws.write(i + 1, 6, total_epf_wages, style_header1) ws.write(i + 1, 7, total_epf_cont, style_header1) ws.write(i + 1, 8, total_eps_cont, style_header1) ws.write(i + 1, 9, total_diff_calc, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) pf_upload_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'employee_pf_upload.xls' }, context=context) return pf_upload_report
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 # one of the libraries can get confused with plain strings if isinstance(cell, str): cell = unicode(cell, 'utf-8') ws.write(i + 1, j + 1, cell, _style) if print_to_screen: print print_table(data, sheet_name, bold=True)
def print_report(self, cr, uid, ids, context=None): wb = Workbook() ws = wb.add_sheet('Earn Leave Report') fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x17 style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 250 fnt2.bold = False align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x09 style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 275 fnt3.bold = False align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x02 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x09 style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x16 style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 275 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 ws.row(0).height = 1000 ws.col(0).width = 4000 ws.col(1).width = 5000 ws.col(2).width = 4000 ws.col(3).width = 5000 ws.col(4).width = 5000 ws.col(5).width = 6500 ws.col(6).width = 4000 ws.col(7).width = 4000 ws.col(8).width = 4000 ws.write(0, 0, 'Employee ID', style_header) ws.write(0, 1, 'Employee Code', style_header) ws.write(0, 2, 'Earn Date', style_header) ws.write(0, 3, 'Total Work Day', style_header) ws.write(0, 4, 'Allocation EL', style_header) ws.write(0, 5, 'Opening Earn Leave', style_header) ws.write(0, 6, 'Total EL Paid', style_header) ws.write(0, 7, 'Balance EL', style_header) ws.write(0, 8, 'Import EL', style_header) this = self.browse(cr, uid, ids[0], context=context) company_id = this.company_id.id employee_id = this.employee_id.id emp_obj = self.pool.get('hr.employee') year = this.month.year_id.id balance1 = 0 balance = 0 import_el = 0 i = 1 if this.company_id and this.employee_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('id', '=', employee_id), ('active', '=', True), ('type', '=', 'Employee')]) elif this.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('active', '=', True), ('type', '=', 'Employee')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if len(list_ids) == 1: query = "select spl.sinid,sum(spl.work_day),hr.earn_date,sum(spl.earned_leave),hr.earn_open,hr.id from salary_payment_line as spl left join hr_employee as hr on spl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id where spl.employee_id = '" + str( list_ids[0] ) + "' and spl.year_id='" + str( year ) + "' and rr.active = 'True' group by spl.sinid,hr.earn_date,hr.earn_open,hr.id order by spl.sinid" cr.execute(query) temp = cr.fetchall() else: query = "select spl.sinid,sum(spl.work_day),hr.earn_date,sum(spl.earned_leave),hr.earn_open,hr.id from salary_payment_line as spl left join hr_employee as hr on spl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id where spl.employee_id in " + str( tuple(list_ids) ) + " and spl.year_id='" + str( year ) + "' and rr.active = 'True' group by spl.sinid,hr.earn_date,hr.earn_open,hr.id order by spl.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) for val in temp: day = format((val[1] / 20), '.2f') b = str(day) c = b.split('.') d = c[0] e = c[1][0:2] if int(e) < 50: s = d + '.' + str(0) work_day = float(s) else: s = d + '.' + e t = float(s) work_day = math.ceil(t) if val[4] != None: balance1 = (work_day + val[4] - val[3]) import_el = work_day + val[4] else: balance1 = (work_day - val[3]) import_el = work_day if balance1 >= 30: balance = 30 else: balance = balance1 ws.row(i).height = 500 ws.write(i, 0, val[5], style_header2) ws.write(i, 1, val[0], style_header2) ws.write(i, 2, val[2], style_header2) ws.write(i, 3, val[1], style_header2) ws.write(i, 4, work_day, style_header2) ws.write(i, 5, val[4], style_header2) ws.write(i, 6, val[3], style_header2) ws.write(i, 7, balance, style_header2) ws.write(i, 8, import_el, style_header2) i = i + 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) pf_upload_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Earn Leave Report.xls' }, context=context) return pf_upload_report
def output(self): # from xlrd import open_workbook from xlwt import Workbook, XFStyle, Borders, Alignment, Font, Pattern, Style, easyxf # from xlutils.copy import copy """ rb = open_workbook(r"Templates\template.xls") wb = copy(rb) s = wb.get_sheet(0) s.write(0, 0, 'A1') wb.save(r"C:\Temp\nephro-planner\new.xls") """ book = Workbook(encoding="utf-8") sheet = book.add_sheet(r"Feuille1") style_title = XFStyle() font_title = Font() font_title.name = "Comic Sans MS" font_title.height = 280 style_title.font = font_title style_title.alignment.horz = Alignment.HORZ_CENTER style_title.alignment.vert = Alignment.VERT_CENTER style_cell_bottom = XFStyle() borders_cell_bottom = Borders() borders_cell_bottom.bottom = Borders.MEDIUM style_cell_bottom.borders = borders_cell_bottom style_header = XFStyle() font_header = Font() font_header.bold = 1 font_header.name = "Arial Narrow" font_header.height = 240 style_header.font = font_header style_header.alignment.horz = Alignment.HORZ_CENTER style_header.alignment.vert = Alignment.VERT_CENTER borders_header = Borders() borders_header.top = Borders.MEDIUM borders_header.left = Borders.MEDIUM borders_header.bottom = Borders.MEDIUM borders_header.right = Borders.MEDIUM style_header.borders = borders_header style_sub_header = XFStyle() font_sub_header = Font() font_sub_header.name = "Arial Narrow" font_sub_header.height = 240 style_sub_header.font = font_sub_header style_sub_header.alignment.horz = Alignment.HORZ_CENTER style_sub_header.alignment.vert = Alignment.VERT_CENTER borders_sub_header = Borders() borders_sub_header.top = Borders.MEDIUM borders_sub_header.left = Borders.MEDIUM borders_sub_header.bottom = Borders.MEDIUM borders_sub_header.right = Borders.MEDIUM style_sub_header.borders = borders_sub_header style_date = XFStyle() font_date = Font() font_date.name = "Arial Narrow" font_date.height = 240 style_date.font = font_date style_date.alignment.horz = Alignment.HORZ_RIGHT style_date.alignment.vert = Alignment.VERT_CENTER borders_date = Borders() borders_date.left = Borders.MEDIUM style_date.borders = borders_date style_date_status = XFStyle() font_date_status = Font() font_date_status.name = "Arial Narrow" font_date_status.height = 240 style_date_status.font = font_date_status style_date_status.alignment.horz = Alignment.HORZ_LEFT style_date_status.alignment.vert = Alignment.VERT_CENTER borders_date_status = Borders() borders_date_status.right = Borders.MEDIUM style_date_status.borders = borders_date_status style_cell_normal = XFStyle() font_cell_normal = Font() font_cell_normal.name = "Arial Narrow" font_cell_normal.height = 220 style_cell_normal.font = font_cell_normal style_cell_normal.alignment.horz = Alignment.HORZ_CENTER style_cell_normal.alignment.vert = Alignment.VERT_CENTER style_cell_right = XFStyle() font_cell_right = Font() font_cell_right.name = "Arial Narrow" font_cell_right.height = 220 style_cell_right.font = font_cell_right style_cell_right.alignment.horz = Alignment.HORZ_CENTER style_cell_right.alignment.vert = Alignment.VERT_CENTER borders_cell_right = Borders() borders_cell_right.right = Borders.MEDIUM style_cell_right.borders = borders_cell_right style_cell_top = XFStyle() borders_cell_top = Borders() borders_cell_top.top = Borders.MEDIUM style_cell_top.borders = borders_cell_top column_offset = 1 date_column_offset = 2 row_offset = 1 table_width = 1 + 3 * len(Database.team()) # build titles sheet.write_merge(row_offset, row_offset, column_offset, table_width + 1, "POLE MEDECINE INTERNE", style_title) sheet.write_merge( row_offset + 1, row_offset + 1, column_offset, table_width + 1, "Service NEPHROLOGIE – HEMODIALYSE", style_title, ) sheet.write_merge( row_offset + 2, row_offset + 2, column_offset, table_width + 1, "Planning de {0} {1}".format(self.human_readable_months[self.month - 1], self.year), style_title, ) # patch date columns top borders sheet.write(row_offset + 5, column_offset, "", style_cell_bottom) sheet.write(row_offset + 5, column_offset + 1, "", style_cell_bottom) # build header and sub header for x in Database.team(): sheet.write_merge( row_offset + 4, row_offset + 4, column_offset + date_column_offset + 3 * (x.id - 1), column_offset + date_column_offset + 3 * x.id - 1, x.name, style_header, ) sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1), "M", style_sub_header) sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 1, "AM", style_sub_header) sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 2, "N", style_sub_header) """ i = 40 for x in sorted(Style.colour_map): style = XFStyle() pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = Style.colour_map[x] style.pattern = pattern sheet.write(i, 1, x, style) i += 1 """ pattern_pale_blue = Pattern() pattern_pale_blue.pattern = Pattern.SOLID_PATTERN pattern_pale_blue.pattern_fore_colour = Style.colour_map["pale_blue"] pattern_light_yellow = Pattern() pattern_light_yellow.pattern = Pattern.SOLID_PATTERN pattern_light_yellow.pattern_fore_colour = Style.colour_map["light_yellow"] pattern_ice_blue = Pattern() pattern_ice_blue.pattern = Pattern.SOLID_PATTERN pattern_ice_blue.pattern_fore_colour = Style.colour_map["ice_blue"] pattern_light_green = Pattern() pattern_light_green.pattern = Pattern.SOLID_PATTERN pattern_light_green.pattern_fore_colour = Style.colour_map["light_green"] pattern_ivory = Pattern() pattern_ivory.pattern = Pattern.SOLID_PATTERN pattern_ivory.pattern_fore_colour = Style.colour_map["ivory"] pattern_tan = Pattern() pattern_tan.pattern = Pattern.SOLID_PATTERN pattern_tan.pattern_fore_colour = Style.colour_map["tan"] pattern_gold = Pattern() pattern_gold.pattern = Pattern.SOLID_PATTERN pattern_gold.pattern_fore_colour = Style.colour_map["gold"] def __cell_colouration__(style, current_activity): if current_activity is Activity.CONSULTATION: style.pattern = pattern_pale_blue elif current_activity is Activity.DIALYSIS: style.pattern = pattern_ice_blue elif current_activity is Activity.NEPHROLOGY: style.pattern = pattern_light_green elif current_activity is Activity.OTHERS: style.pattern = pattern_tan elif current_activity is Activity.OBLIGATION: style.pattern = pattern_ivory elif current_activity is Activity.OBLIGATION_WEEKEND: style.pattern = pattern_light_yellow elif current_activity is Activity.OBLIGATION_HOLIDAY: style.pattern = pattern_gold else: style.pattern = Pattern() return style last_day = calendar.monthrange(self.year, self.month)[1] for x in range(1, last_day + 1): current_date = date(self.year, self.month, x) current_daily_planning = self.daily_plannings[current_date] # build date and date status columns sheet.write( row_offset + 5 + x, column_offset, "{0}. {1}".format(self.human_readable_days[current_daily_planning.weekday][0:3].lower(), x), style_date, ) if current_daily_planning.weekday in [5, 6]: sheet.write(row_offset + 5 + x, column_offset + 1, "WK", style_date_status) elif not current_daily_planning.is_working_day: sheet.write(row_offset + 5 + x, column_offset + 1, "Férié", style_date_status) else: sheet.write(row_offset + 5 + x, column_offset + 1, "", style_date_status) """ easyxf( 'font: bold 1, name Tahoma, height 160;' 'align: vertical center, horizontal center, wrap on;' 'borders: left thin, right thin, top thin, bottom thin;' 'pattern: pattern solid, pattern_fore_colour green, pattern_back_colour green' ) """ # fill in month planning for y in Database.team(): current_activity = current_daily_planning.__get_activity__(TimeSlot.FIRST_SHIFT, y) sheet.write( row_offset + 5 + x, column_offset + date_column_offset + 3 * (y.id - 1), self.human_readable_activities[current_activity] if current_activity else "", __cell_colouration__(style_cell_normal, current_activity), ) current_activity = current_daily_planning.__get_activity__(TimeSlot.SECOND_SHIFT, y) sheet.write( row_offset + 5 + x, column_offset + date_column_offset + 1 + 3 * (y.id - 1), self.human_readable_activities[current_activity] if current_activity else "", __cell_colouration__(style_cell_normal, current_activity), ) current_activity = current_daily_planning.__get_activity__(TimeSlot.THIRD_SHIFT, y) sheet.write( row_offset + 5 + x, column_offset + date_column_offset + 2 + 3 * (y.id - 1), self.human_readable_activities[current_activity] if current_activity else "", __cell_colouration__(style_cell_right, current_activity), ) # patch table bottom border for x in range(0, table_width + 1): sheet.write(row_offset + 5 + (last_day + 1), column_offset + x, "", style_cell_top) book.save(r"C:\Temp\nephro-planner\new.xls")
if hasattr(settings, 'EXPORT_EXCEL_FONT'): fontname = settings.EXPORT_EXCEL_FONT titleFont.name = fontname # Setting Fonts Name headerFont.name = fontname cellFont.name = fontname title_bold = True header_bold = True cell_bold = False if hasattr(settings, 'EXPORT_EXCEL_TITLE_BOLD'): title_bold = settings.EXPORT_EXCEL_TITLE_BOLD if hasattr(settings, 'EXPORT_EXCEL_HEADER_BOLD'): header_bold = settings.EXPORT_EXCEL_HEADER_BOLD if hasattr(settings, 'EXPORT_EXCEL_CELL_BOLD'): cell_bold = settings.EXPORT_EXCEL_CELL_BOLD titleFont.bold = title_bold # Setting title font to bold headerFont.bold = header_bold # Setting column header font to bold cellFont.bold = cell_bold # Setting cell font to bold titlesize = 240 # 12*20 = 240 Font Size fontsize = 200# 10*20 = 200 Font Size if hasattr(settings, 'EXPORT_EXCEL_TITLE_SIZE'): titlesize = settings.EXPORT_EXCEL_TITLE_SIZE if hasattr(settings, 'EXPORT_EXCEL_FONT_SIZE'): fontsize = settings.EXPORT_EXCEL_FONT_SIZE titleFont.height = titlesize headerFont.height = fontsize cellFont.height = fontsize brdLeft = Borders() # Defining border which is around header brdLeft.left = 0x01
def archivo_excel(titulo, archivo_logo = 'unprg.bmp', label_resumen = [], datos_resumen = [], heads = [], color = 0x9ACD32, registros = [], nombre_archivo = 'descargar.xls'): book = Workbook() sheet1 = book.add_sheet('Hoja 01') #estilos de celda titulo fnt_titulo = Font() fnt_titulo.name = 'Arial' fnt_titulo.bold = True style_titulo = XFStyle() style_titulo.font = fnt_titulo #estilos de celda etiqueta resumen fnt_etiqueta_resumen = Font() fnt_etiqueta_resumen.name = 'Arial' fnt_etiqueta_resumen.bold = True style_etiqueta_resumen = XFStyle() style_etiqueta_resumen.font = fnt_etiqueta_resumen #estilos de celda datos resumen fnt_dato_resumen = Font() fnt_dato_resumen.name = 'Arial' style_dato_resumen = XFStyle() style_dato_resumen.font = fnt_dato_resumen #estilos de celda heads fnt_heads = Font() fnt_heads.name = 'Arial' fnt_heads.bold = True borders_heads = Borders() borders_heads.left = Borders.THIN borders_heads.right = Borders.THIN borders_heads.top = Borders.THIN borders_heads.bottom = Borders.THIN pattern_heads = Pattern() pattern_heads.pattern = Pattern.SOLID_PATTERN pattern_heads.pattern_fore_colour = color style_heads = XFStyle() style_heads.font = fnt_heads style_heads.borders = borders_heads style_heads.pattern = pattern_heads #estilos de celda registros fnt_registros = Font() fnt_registros.name = 'Arial' borders_registros = Borders() borders_registros.left = Borders.THIN borders_registros.right = Borders.THIN borders_registros.top = Borders.THIN borders_registros.bottom = Borders.THIN style_registros = XFStyle() style_registros.font = fnt_registros style_registros.borders = borders_registros sheet1.insert_bitmap(settings.MEDIA_ROOT + 'archivos_excel/%s' % archivo_logo, 1, 0) #escribir el titulo sheet1.write(10,0,titulo, style_titulo) row = 12 col = 0 #escribir las etiquetas del resumen for etiqueta in label_resumen: sheet1.write(row, col, etiqueta, style_etiqueta_resumen) row+=1 row = 12 col = 1 #escribir los datos del resumen for dato in datos_resumen: sheet1.write(row, col, dato, style_dato_resumen) row+=1 row+=1 col = 0 #escribimos los encabezados for head in heads: sheet1.write(row,col,head,style_heads) col+=1 row+=1 col=1 n = 1 #recorremos la lista y escribimos los datos for fila in registros: sheet1.write(row,0,n,style_registros) for dato in fila: sheet1.write(row,col,dato,style_registros) col+=1 col=1 row+=1 n+=1 #book.save(settings.MEDIA_ROOT + 'archivos_excel/%s' % nombre_archivo) book.save(TemporaryFile())
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 archivo_excel1(titulo, archivo_logo = 'unprg.bmp', label_resumen = [], datos_resumen = [], heads = [], color = 0x9ACD32, registros = [], nombre_archivo = 'descargar.xls'): import StringIO output = StringIO.StringIO() from django.http import HttpResponse book = Workbook() sheet1 = book.add_sheet('Hoja 01') #estilos de celda titulo fnt_titulo = Font() fnt_titulo.name = 'Arial' fnt_titulo.bold = True style_titulo = XFStyle() style_titulo.font = fnt_titulo #estilos de celda etiqueta resumen fnt_etiqueta_resumen = Font() fnt_etiqueta_resumen.name = 'Arial' fnt_etiqueta_resumen.bold = True style_etiqueta_resumen = XFStyle() style_etiqueta_resumen.font = fnt_etiqueta_resumen #estilos de celda datos resumen fnt_dato_resumen = Font() fnt_dato_resumen.name = 'Arial' style_dato_resumen = XFStyle() style_dato_resumen.font = fnt_dato_resumen #estilos de celda heads fnt_heads = Font() fnt_heads.name = 'Arial' fnt_heads.bold = True borders_heads = Borders() borders_heads.left = Borders.THIN borders_heads.right = Borders.THIN borders_heads.top = Borders.THIN borders_heads.bottom = Borders.THIN pattern_heads = Pattern() pattern_heads.pattern = Pattern.SOLID_PATTERN pattern_heads.pattern_fore_colour = color style_heads = XFStyle() style_heads.font = fnt_heads style_heads.borders = borders_heads style_heads.pattern = pattern_heads #estilos de celda registros fnt_registros = Font() fnt_registros.name = 'Arial' borders_registros = Borders() borders_registros.left = Borders.THIN borders_registros.right = Borders.THIN borders_registros.top = Borders.THIN borders_registros.bottom = Borders.THIN style_registros = XFStyle() style_registros.font = fnt_registros style_registros.borders = borders_registros sheet1.insert_bitmap(settings.MEDIA_ROOT + 'archivos_excel/%s' % archivo_logo, 1, 0) #escribir el titulo sheet1.write(10,0,titulo, style_titulo) row = 12 col = 0 #escribir las etiquetas del resumen for etiqueta in label_resumen: sheet1.write(row, col, etiqueta, style_etiqueta_resumen) row+=1 row = 12 col = 1 #escribir los datos del resumen for dato in datos_resumen: sheet1.write(row, col, dato, style_dato_resumen) row+=1 row+=1 col = 0 #escribimos los encabezados for head in heads: sheet1.write(row,col,head,style_heads) col+=1 row+=1 col=1 n = 1 #recorremos la lista y escribimos los datos for fila in registros: sheet1.write(row,0,n,style_registros) for dato in fila: sheet1.write(row,col,dato,style_registros) col+=1 col=1 row+=1 n+=1 book.save(settings.MEDIA_ROOT + 'archivos_excel/%s' % nombre_archivo) book.save(output) output.seek(0) response = HttpResponse(content=output.getvalue(),mimetype='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=libros_excel.xls' return response
def performance_register_report(self, cr, uid, ids, data, context=None): obj = self.browse(cr, uid, ids) emp_obj = self.pool.get('hr.employee') f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('Payment Bonus') total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x00 borders1.right = 0x00 borders1.top = 0x00 borders1.bottom = 0x00 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x1F style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x00 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt7 = Font() fnt7.name = 'Arial' fnt7.height = 275 content_fnt7 = Font() content_fnt7.name = 'Arial' content_fnt7.height = 150 align_content7 = Alignment() align_content7.horz = Alignment.HORZ_CENTER borders7 = Borders() borders7.left = 0x02 borders7.right = 0x02 borders7.top = 0x02 borders7.bottom = 0x02 align7 = Alignment() align7.horz = Alignment.HORZ_CENTER align7.vert = Alignment.VERT_CENTER pattern7 = Pattern() pattern7.pattern7 = Pattern.SOLID_PATTERN pattern7.pattern7_fore_colour = 0x1F style_header7 = XFStyle() style_header7.font = fnt style_header7.pattern = pattern style_header7.borders = borders style_header7.alignment = align fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 275 content_fnt2 = Font() content_fnt2.name = 'Arial' content_fnt2.height = 150 fnt2.bold = True align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x00 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x1F style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 300 fnt3.bold = True align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x00 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x1F style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt6 = Font() fnt6.name = 'Arial' fnt6.height = 275 content_fnt6 = Font() content_fnt6.name = 'Arial' content_fnt6.height = 150 align_content6 = Alignment() align_content6.horz = Alignment.HORZ_CENTER borders6 = Borders() borders6.left = 0x02 borders6.right = 0x02 # borders6.top = 0x00 # borders6.bottom = 0x00 align6 = Alignment() align6.horz = Alignment.HORZ_CENTER align6.vert = Alignment.VERT_CENTER pattern6 = Pattern() pattern6.pattern = Pattern.SOLID_PATTERN pattern6.pattern_fore_colour = 0x1F style_header6 = XFStyle() style_header6.font = fnt style_header6.pattern = pattern style_header6.borders = borders6 style_header6.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 200 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() align_content5.horz = Alignment.HORZ_CENTER borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.horz = Alignment.HORZ_CENTER align5.vert = Alignment.VERT_CENTER pattern5 = Pattern() # pattern5.pattern = Pattern.SOLID_PATTERN # pattern5.pattern_fore_colour = 0x1F style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 if obj.company_id: get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip else: get_name = obj.employee_id.resource_id.company_id.name + ' ' + obj.employee_id.resource_id.company_id.street + ' ' + ',' + obj.employee_id.resource_id.company_id.city + ' ' + '-' + obj.employee_id.resource_id.company_id.zip date1 = datetime.strptime(obj.from_date, "%Y-%m-%d").timetuple().tm_year date2 = datetime.strptime(obj.till_date, "%Y-%m-%d").timetuple().tm_year if date1 == date2: d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' - ' + str( date1) + ' ' + '[See Rule 4(b)]' else: d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' ' + str( date1) + ' ' + '-' + ' ' + str(date2) + ' ' + '[See Rule 4(b)]' ws.row(0).height = 500 ws.row(1).height = 500 ws.write_merge(0, 0, 0, 2, 'FORM C', style_header1) ws.write_merge(0, 0, 3, 10, d_name, style_header1) ws.write_merge(1, 1, 0, 2, 'Name of the Establishment', style_header1) ws.write_merge(1, 1, 3, 10, get_name, style_header1) ws.write_merge(1, 1, 11, 19, 'No. of Working days in the Year', style_header1) ws.col(0).width = 5000 ws.col(1).width = 7500 ws.col(2).width = 5000 ws.col(3).width = 7000 ws.col(4).width = 5000 ws.col(5).width = 8000 ws.col(6).width = 6000 ws.col(7).width = 8000 ws.col(8).width = 8000 ws.col(9).width = 9000 ws.col(10).width = 8000 ws.col(11).width = 9000 ws.col(12).width = 7000 ws.col(13).width = 7000 ws.col(14).width = 5000 ws.col(15).width = 5000 ws.col(16).width = 7000 ws.col(17).width = 3000 ws.col(18).width = 3000 ws.col(19).width = 3000 ws.col(20).width = 3000 ws.col(21).width = 3000 ws.col(22).width = 3000 ws.col(23).width = 3000 ws.col(24).width = 3000 ws.col(25).width = 3000 ws.col(26).width = 3000 ws.col(27).width = 3000 ws.col(28).width = 3000 ws.col(29).width = 4000 ws.row(2).height = 400 ws.write(2, 0, 'EMP. CODE', style_header) ws.write(2, 1, 'NAME', style_header) ws.write(2, 2, 'JOINING DATE', style_header) ws.write(2, 3, 'Father Name', style_header) ws.write(2, 4, 'Designation', style_header) ws.write(2, 5, 'Whether he has ', style_header) ws.write(2, 6, 'No.of days', style_header) ws.write(2, 7, 'Total Salary or wages', style_header) ws.write(2, 8, 'Account of bonus payable', style_header) ws.write_merge(2, 2, 9, 12, 'Deduction', style_header7) ws.write(2, 13, 'Net amount payable', style_header) ws.write(2, 14, 'Amount actualy', style_header) ws.write(2, 15, 'Date on which ', style_header) ws.write(2, 16, 'Signature/ Thumb ', style_header) ws.write(2, 17, 'Remarks', style_header) ws.write_merge(2, 2, 18, 19, 'APRIL', style_header7) ws.write_merge(2, 2, 20, 21, 'MAY', style_header7) ws.write_merge(2, 2, 22, 23, 'JUNE', style_header7) ws.write_merge(2, 2, 24, 25, 'JULY', style_header7) ws.write_merge(2, 2, 26, 27, 'AUGUST', style_header7) ws.write_merge(2, 2, 28, 29, 'SEPTEMBER', style_header7) ws.write_merge(2, 2, 30, 31, 'OCTOBER', style_header7) ws.write_merge(2, 2, 32, 33, 'NOVEMBER', style_header7) ws.write_merge(2, 2, 34, 35, 'DECEMBER', style_header7) ws.write_merge(2, 2, 36, 37, 'JANUARY', style_header7) ws.write_merge(2, 2, 38, 39, 'FEBRUARY', style_header7) ws.write_merge(2, 2, 40, 41, 'MARCH', style_header7) ws.write_merge(2, 2, 42, 43, 'TOTAL', style_header7) ws.write(2, 44, 'BONUS', style_header) ws.row(3).height = 400 ws.write(3, 0, '', style_header6) ws.write(3, 1, '', style_header6) ws.write(3, 2, '', style_header6) ws.write(3, 3, '', style_header6) ws.write(3, 4, '', style_header6) ws.write(3, 5, 'completed 15 year of', style_header6) ws.write(3, 6, 'worked in the', style_header6) ws.write(3, 7, 'in respect of', style_header6) ws.write(3, 8, 'under section 10', style_header6) ws.write(3, 9, 'Puja bonus or other customary', style_header7) ws.write(3, 10, 'Interim bonus', style_header7) ws.write(3, 11, 'Deduction on account of financial', style_header7) ws.write(3, 12, 'Total sum deducted', style_header7) ws.write(3, 13, '(Col.8 minus Col.12)', style_header6) ws.write(3, 14, 'paid', style_header6) ws.write(3, 15, 'paid', style_header6) ws.write(3, 16, 'impression', style_header6) ws.write(3, 17, '', style_header6) ws.write(3, 18, '', style_header7) ws.write(3, 19, '', style_header7) ws.write(3, 20, '', style_header7) ws.write(3, 21, '', style_header7) ws.write(3, 22, '', style_header7) ws.write(3, 23, '', style_header7) ws.write(3, 24, '', style_header7) ws.write(3, 25, '', style_header7) ws.write(3, 26, '', style_header7) ws.write(3, 27, '', style_header7) ws.write(3, 28, '', style_header7) ws.write(3, 29, '', style_header7) ws.write(3, 30, '', style_header7) ws.write(3, 31, '', style_header7) ws.write(3, 32, '', style_header7) ws.write(3, 33, '', style_header7) ws.write(3, 34, '', style_header7) ws.write(3, 35, '', style_header7) ws.write(3, 36, '', style_header7) ws.write(3, 37, '', style_header7) ws.write(3, 38, '', style_header7) ws.write(3, 39, '', style_header7) ws.write(3, 40, '', style_header7) ws.write(3, 41, '', style_header7) ws.write(3, 42, '', style_header7) ws.write(3, 43, '', style_header7) ws.write(3, 44, '', style_header7) ws.row(4).height = 400 ws.write(4, 0, '', style_header6) ws.write(4, 1, '', style_header6) ws.write(4, 2, '', style_header6) ws.write(4, 3, '', style_header6) ws.write(4, 4, '', style_header6) ws.write(4, 5, 'age at the beginning', style_header6) ws.write(4, 6, 'Establishment', style_header6) ws.write(4, 7, 'the accounting year', style_header6) ws.write(4, 8, 'or section 11', style_header6) ws.write(4, 9, 'bonus paid during', style_header6) ws.write(4, 10, 'or bonus paid in ', style_header6) ws.write(4, 11, 'loss if any caused by', style_header6) ws.write(4, 12, '(Col.9,10 and 11)', style_header6) ws.write(4, 13, '', style_header6) ws.write(4, 14, '', style_header6) ws.write(4, 15, '', style_header6) ws.write(4, 16, 'of the employee', style_header6) ws.write(4, 17, '', style_header6) ws.write(4, 18, '', style_header6) ws.write(4, 19, '', style_header6) ws.write(4, 20, '', style_header6) ws.write(4, 21, '', style_header6) ws.write(4, 22, '', style_header6) ws.write(4, 23, '', style_header6) ws.write(4, 24, '', style_header6) ws.write(4, 25, '', style_header6) ws.write(4, 26, '', style_header6) ws.write(4, 27, '', style_header6) ws.write(4, 28, '', style_header6) ws.write(4, 29, '', style_header6) ws.write(4, 30, '', style_header6) ws.write(4, 31, '', style_header6) ws.write(4, 32, '', style_header6) ws.write(4, 33, '', style_header6) ws.write(4, 34, '', style_header6) ws.write(4, 35, '', style_header6) ws.write(4, 36, '', style_header6) ws.write(4, 37, '', style_header6) ws.write(4, 38, '', style_header6) ws.write(4, 39, '', style_header6) ws.write(4, 40, '', style_header6) ws.write(4, 41, '', style_header6) ws.write(4, 42, '', style_header6) ws.write(4, 43, '', style_header6) ws.write(4, 44, '', style_header6) ws.row(5).height = 400 ws.write(5, 0, '', style_header6) ws.write(5, 1, '', style_header6) ws.write(5, 2, '', style_header6) ws.write(5, 3, '', style_header6) ws.write(5, 4, '', style_header6) ws.write(5, 5, 'of the accounting year', style_header6) ws.write(5, 6, '', style_header6) ws.write(5, 7, '', style_header6) ws.write(5, 8, 'as the case may be', style_header6) ws.write(5, 9, 'the accounting year', style_header6) ws.write(5, 10, 'advance', style_header6) ws.write(5, 11, 'misconduct of the employee', style_header6) ws.write(5, 12, '', style_header6) ws.write(5, 13, '', style_header6) ws.write(5, 14, '', style_header6) ws.write(5, 15, '', style_header6) ws.write(5, 16, '', style_header6) ws.write(5, 17, '', style_header6) ws.write(5, 18, 'DAYS', style_header6) ws.write(5, 19, 'SALARY', style_header6) ws.write(5, 20, 'DAYS', style_header6) ws.write(5, 21, 'SALARY', style_header6) ws.write(5, 22, 'DAYS', style_header6) ws.write(5, 23, 'SALARY', style_header6) ws.write(5, 24, 'DAYS', style_header6) ws.write(5, 25, 'SALARY', style_header6) ws.write(5, 26, 'DAYS', style_header6) ws.write(5, 27, 'SALARY', style_header6) ws.write(5, 28, 'DAYS', style_header6) ws.write(5, 29, 'SALARY', style_header6) ws.write(5, 30, 'DAYS', style_header6) ws.write(5, 31, 'SALARY', style_header6) ws.write(5, 32, 'DAYS', style_header6) ws.write(5, 33, 'SALARY', style_header6) ws.write(5, 34, 'DAYS', style_header6) ws.write(5, 35, 'SALARY', style_header6) ws.write(5, 36, 'DAYS', style_header6) ws.write(5, 37, 'SALARY', style_header6) ws.write(5, 38, 'DAYS', style_header6) ws.write(5, 39, 'SALARY', style_header6) ws.write(5, 40, 'DAYS', style_header6) ws.write(5, 41, 'SALARY', style_header6) ws.write(5, 42, 'DAYS', style_header6) ws.write(5, 43, 'SALARY', style_header6) ws.write(5, 44, '', style_header6) if obj.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', True), ('type', '=', 'Employee')]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', False), ('type', '=', 'Employee')]) list_ids = list_ids + list_ids1 elif obj.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', obj.employee_id.id), ('active', '=', True), ('type', '=', 'Employee')]) else: raise osv.except_osv( _('Warning !'), _("Please Select Atleast Company Or Employee.")) if len(list_ids) == 1: query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\ "where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\ "where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) columnno = 6 no = 6 for val in temp: father_name = '' if val[4]: father_qry = "select name from family where relation='Father' and employee_id='" + str( val[4]) + "' " cr.execute(father_qry) father_temp = cr.fetchall() if father_temp: father_name = father_temp[0][0] else: father_name = ' ' ws.row(no).height = 500 doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y") ws.write(columnno, 0, val[0], style_header5) ws.write(columnno, 1, val[1], style_header5) ws.write(columnno, 2, doj, style_header5) ws.write(columnno, 3, father_name, style_header5) ws.write(columnno, 4, val[3], style_header5) ws.write(columnno, 5, 'Yes', style_header5) ws.write(columnno, 6, '', style_header5) ws.write(columnno, 7, '', style_header5) ws.write(columnno, 8, '', style_header5) ws.write(columnno, 9, '', style_header5) ws.write(columnno, 10, '', style_header5) ws.write(columnno, 11, '', style_header5) ws.write(columnno, 12, '', style_header5) ws.write(columnno, 13, '', style_header5) ws.write(columnno, 14, '', style_header5) ws.write(columnno, 15, '', style_header5) ws.write(columnno, 16, '', style_header5) ws.write(columnno, 17, '', style_header5) ws.write(columnno, 18, val[5], style_header5) ws.write(columnno, 19, val[19], style_header5) ws.write(columnno, 20, val[6], style_header5) ws.write(columnno, 21, val[20], style_header5) ws.write(columnno, 22, val[7], style_header5) ws.write(columnno, 23, val[21], style_header5) ws.write(columnno, 24, val[8], style_header5) ws.write(columnno, 25, val[22], style_header5) ws.write(columnno, 26, val[9], style_header5) ws.write(columnno, 27, val[23], style_header5) ws.write(columnno, 28, val[10], style_header5) ws.write(columnno, 29, val[24], style_header5) ws.write(columnno, 30, val[11], style_header5) ws.write(columnno, 31, val[25], style_header5) ws.write(columnno, 32, val[12], style_header5) ws.write(columnno, 33, val[26], style_header5) ws.write(columnno, 34, val[13], style_header5) ws.write(columnno, 35, val[27], style_header5) ws.write(columnno, 36, val[14], style_header5) ws.write(columnno, 37, val[28], style_header5) ws.write(columnno, 38, val[15], style_header5) ws.write(columnno, 39, val[29], style_header5) ws.write(columnno, 40, val[16], style_header5) ws.write(columnno, 41, val[30], style_header5) ws.write(columnno, 42, val[17], style_header5) ws.write(columnno, 43, val[31], style_header5) ws.write(columnno, 44, val[18], style_header5) columnno += 1 no += 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Payment Bonus.xls' }, context=context)
def generate_report(root, filename, attend_staff, absent_staff, meeting_direct, record_staff, reports, issues): book = Workbook(encoding='utf-8') sheet1 = book.add_sheet('Fota Security Knox Week Report') line_counter = 0 today = datetime.date.today().strftime('%Y/%m/%d') # header head_str = [ 'Meeting Name', 'SW 3 Group Part 5 Fota/ Security/Knox/Encription TG WeekReport', '', '', 'Meeting Date', today, 'Meeting Address', '8F Meeting Room', '', '', 'Attend Staff', attend_staff, 'Absent Staff', absent_staff, '', '', 'Meeting Direct', meeting_direct, 'Record Staff', record_staff, ] for i in range(len(head_str) / 2): sheet1.row(i).write(0, head_str[2 * i]) sheet1.row(i).write(1, head_str[2 * i + 1]) line_counter = len(head_str) / 2 + 1 # font bold font = Font() font.bold = True # pattern yellow pattern_yellow = Pattern() pattern_yellow.pattern = Pattern.SOLID_PATTERN pattern_yellow.pattern_fore_colour = 0x0D # yellow # pattern gray pattern_gray = Pattern() pattern_gray.pattern = Pattern.SOLID_PATTERN pattern_gray.pattern_fore_colour = 0x17 # gray # borders thin borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN # alignment horizontal center alig_hc = Alignment() # alig.horizontal = Alignment.HORZ_CENTER #no effect, why? f**k!!! alig_hc.horz = Alignment.HORZ_CENTER # title style style_title = XFStyle() style_title.font = font style_title.pattern = pattern_yellow style_title.alignment = alig_hc sheet1.write_merge( line_counter, line_counter, 0, 3, 'summary', style_title) line_counter += 1 # table header style sytle_tb_header = XFStyle() sytle_tb_header.font = font sytle_tb_header.pattern = pattern_gray sytle_tb_header.borders = borders sytle_tb_header.alignment = alig_hc sheet1.row(line_counter).write(0, 'Member', sytle_tb_header) sheet1.row(line_counter).write(1, 'Week Jobs', sytle_tb_header) sheet1.row(line_counter).write(2, 'Risk', sytle_tb_header) sheet1.row(line_counter).write(3, 'Next Week Plan', sytle_tb_header) line_counter += 1 # content # for i in range(line_counter,11+line_counter): i = line_counter for report in reports: # alignment alig = Alignment() alig.horz = Alignment.HORZ_CENTER alig.vert = Alignment.VERT_CENTER alig.wrap = 1 # alignment2 alig2 = Alignment() alig2.vert = Alignment.VERT_CENTER alig2.wrap = 1 # borders borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN # colors pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x2F style_content = XFStyle() style_content.alignment = alig style_content.borders = borders style_content2 = XFStyle() style_content2.alignment = alig2 style_content2.borders = borders style_content2.pattern = pattern style_content3 = XFStyle() style_content3.alignment = alig2 style_content3.borders = borders sheet1.row(i).write(0, report.who, style_content) sheet1.row(i).write(1, report.job, style_content2) sheet1.row(i).write(2, report.risk, style_content2) sheet1.row(i).write(3, report.plan, style_content3) sheet1.row(i).height_mismatch = True sheet1.row(i).height = 1500 i += 1 line_counter += len(reports) line_counter += 1 sheet1.write_merge( line_counter, line_counter, 0, 3, 'Main Issues List', style_title) line_counter += 1 sheet1.row(line_counter).write(0, 'Items', sytle_tb_header) sheet1.write_merge( line_counter, line_counter, 1, 2, 'Deatail', sytle_tb_header) sheet1.row(line_counter).write(3, 'Status', sytle_tb_header) line_counter += 1 sheet1.col(0).width = 256 * 16 sheet1.col(1).width = 256 * 50 sheet1.col(2).width = 256 * 31 sheet1.col(3).width = 256 * 46 book.save(root + '/' + filename)
def salary_deduction_category(self, cr, uid, ids, context=None): #============ ========= =============== =========================# fnt = Font() fnt.name = 'Ubuntu Medium' fnt.size = 16 fnt.style = 'Regular' #============ ======== ============= ============================# #Define the font attributes for header content_fnt = Font() content_fnt.name = 'Ubuntu Medium' 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 fnt1 = Font() fnt1.name = 'Ubuntu Medium' fnt1.size = 10 fnt1.style = 'Regular' content_fnt1 = Font() content_fnt1.name = 'Ubuntu Medium' content_fnt1.style = 'Regular' align_content1 = Alignment() align_content1.horz = Alignment.HORZ_LEFT borders1 = Borders() borders1.left = 0x1 borders1.right = 0x1 borders1.top = 0x1 borders1.bottom = 0x1 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_TOP #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x16 #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 Content fnt3 = Font() fnt3.name = 'Arial' fnt3.size = '10' fnt3.style = 'Regular' content_fnt3 = Font() content_fnt3.name = 'Arial' content_fnt3.style = 'Regular' align_content3 = Alignment() align_content3.horz = Alignment.HORZ_LEFT borders3 = Borders() borders3.left = 0x0 borders3.right = 0x0 borders3.top = 0x0 borders3.bottom = 0x0 #The text should be centrally aligned align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_TOP #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 fnt5 = Font() fnt5.name = 'Arial' fnt.size = 10 content_fnt5 = Font() content_fnt5.name = 'Arial' align_content5 = Alignment() # align_content5.horz= Alignment.HORZ_JUSTIFIED borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() # align5.horz = Alignment.HORZ_JUSTIFIED align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 fnt6 = Font() fnt6.name = 'Arial' fnt6.height = 300 fnt6.bold = True align_content6 = Alignment() align_content6.horz = Alignment.HORZ_CENTER borders6 = Borders() borders6.left = 0x02 borders6.right = 0x02 borders6.top = 0x02 borders6.bottom = 0x02 align6 = Alignment() align6.horz = Alignment.HORZ_CENTER align6.vert = Alignment.VERT_CENTER pattern6 = Pattern() pattern6.pattern6 = Pattern.SOLID_PATTERN pattern6.pattern6_fore_colour = 0x16 style_header6 = XFStyle() style_header6.font = fnt6 style_header6.pattern = pattern6 style_header6.borders = borders6 style_header6.alignment = align6 wb = Workbook() ws = wb.add_sheet('Category Wise Deduction') this = self.browse(cr, uid, ids[0], context=context) year = this.month.year_id.id month = this.month.month emp_id = this.employee_id.id company_id = this.company_id.id employee_type = this.employee_type employment_type = this.employment_type ws.row(0).height = 300 ws.row(1).height = 300 ws.row(2).height = 300 ws.col(1).width = 8000 ws.col(2).width = 6000 ws.col(3).width = 6000 ws.col(5).width = 4000 ws.col(6).width = 4000 ws.col(7).width = 4000 ws.col(8).width = 4000 ws.col(9).width = 5000 ws.col(10).width = 5000 ws.write_merge( 0, 0, 0, 11, 'COMPANY : ' + this.company_id.name + ' ' + this.company_id.street, style_header) ws.write_merge(1, 1, 0, 11, ('SALARY CHART FOR THE MONTH OF :', this.month.name), style_header) ws.write_merge(2, 2, 4, 5, 'Income', style_header1) ws.write_merge(2, 2, 6, 10, 'Deductions', style_header1) ws.write(2, 11, 'Net Amount', style_header1) ws.write(3, 0, 'PCard', style_header) ws.write(3, 1, 'Employee Name', style_header) ws.write(3, 2, 'Department Name', style_header) ws.write(3, 3, 'Designation Name', style_header) ws.write(3, 4, 'Total Salary', style_header) ws.write(3, 5, 'OT Amount', style_header) ws.write(3, 6, 'PF Deducted', style_header) ws.write(3, 7, 'TDS Deducted', style_header) ws.write(3, 8, 'ESI Deducted', style_header) ws.write(3, 9, 'Professional Tax', style_header) ws.write(3, 10, 'ADVANCE Deducted', style_header) ws.write(3, 11, 'Net Amount', style_header) i = 4 total_epf = 0.0 total_tds = 0.0 total_esi = 0.0 total_kharcha = 0.0 total_ot_salary = 0.0 total_a_gross = 0.0 total_grand_payment = 0.0 total_professional_tax = 0.0 list_val = [] list_ids = [] emp_obj = self.pool.get('hr.employee') if this.employee_id and this.employee_type: ws.write_merge(2, 2, 1, 2, ('Employee Type : ', this.employee_type), style_header1) list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id), ('active', '=', True), ('employee_type', '=', employee_type), ('type', '=', 'Employee')]) elif this.employee_id and this.employment_type: ws.write_merge(2, 2, 1, 2, ('Employment Type : ', this.employment_type), style_header1) list_ids = emp_obj.search( cr, uid, [('id', '=', emp_id), ('active', '=', True), ('employment_type', '=', employment_type), ('type', '=', 'Employee')]) elif this.company_ids and this.employee_type: ws.write_merge(2, 2, 1, 2, ('Employee Type : ', this.employee_type), style_header1) for val in this.company_ids: list_val = emp_obj.search( cr, uid, [('active', '=', True), ('company_id', '=', val.id), ('employee_type', '=', employee_type), ('type', '=', 'Employee')]) list_ids = list_val + list_ids elif this.company_ids and this.employment_type: ws.write_merge(2, 2, 1, 2, ('Employment Type : ', this.employment_type), style_header1) for val in this.company_ids: list_val = emp_obj.search( cr, uid, [('active', '=', True), ('company_id', '=', val.id), ('employment_type', '=', employment_type), ('type', '=', 'Employee')]) list_ids = list_val + list_ids elif not this.employee_type and not this.employment_type: raise osv.except_osv( ('Warning !'), ("Please Select Employee or Employment Type !!!")) elif this.employee_type: ws.write_merge(2, 2, 1, 2, ('Employee Type : ', this.employee_type), style_header1) list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('employee_type', '=', employee_type), ('type', '=', 'Employee')]) elif this.employment_type: ws.write_merge(2, 2, 1, 2, ('Employment Type : ', this.employment_type), style_header1) list_ids = emp_obj.search( cr, uid, [('active', '=', True), ('employment_type', '=', employment_type), ('type', '=', 'Employee')]) else: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('type', '=', 'Employee')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if len(list_ids) == 1: query ="select spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,sum(spl.overtime_amount+spl.sun_overtime_amount),sum(spl.days_amount+spl.other_salary_amount),spl.grand_total,spl.pro_tax_amt"\ " from salary_payment_line as spl where spl.employee_id = '"+str(list_ids[0])+"' and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' group by "\ " spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,spl.grand_total,spl.pro_tax_amt order by spl.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,sum(spl.overtime_amount+spl.sun_overtime_amount),sum(spl.days_amount+spl.other_salary_amount),spl.grand_total,spl.pro_tax_amt"\ " from salary_payment_line as spl where spl.employee_id in "+str(tuple(list_ids))+" and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' group by " \ " spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,spl.grand_total,spl.pro_tax_amt order by spl.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) for val in temp: total_epf += val[4] total_tds += val[5] total_esi += val[6] total_kharcha += val[7] total_ot_salary += val[8] total_a_gross += val[9] total_grand_payment += val[10] total_professional_tax += val[11] ws.write(i, 0, (val[1]), style_header3) ws.write(i, 1, (val[0]), style_header3) ws.write(i, 2, (val[2]), style_header3) ws.write(i, 3, (val[3]), style_header3) ws.write(i, 4, (val[9]), style_header3) ws.write(i, 5, (val[8]), style_header3) ws.write(i, 6, (val[4]), style_header3) ws.write(i, 7, (val[5]), style_header3) ws.write(i, 8, (val[6]), style_header3) ws.write(i, 9, (val[11]), style_header3) ws.write(i, 10, (val[7]), style_header3) ws.write(i, 11, (val[10]), style_header3) i = i + 1 ws.write(i, 3, 'TOTAL', style_header5) ws.write(i, 4, total_a_gross, style_header5) ws.write(i, 5, total_ot_salary, style_header5) ws.write(i, 6, total_epf, style_header5) ws.write(i, 7, total_tds, style_header5) ws.write(i, 8, total_esi, style_header5) ws.write(i, 9, total_professional_tax, style_header5) ws.write(i, 10, total_kharcha, style_header5) ws.write(i, 11, total_grand_payment, style_header5) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) ot_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Salary CHART.xls' }, context=context) return ot_report
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 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 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 if self.exportStepByStep.isChecked(): dec_sheet = book.add_sheet("Data decomposition") # decomposition data if self.exportData.isChecked(): # 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.parentWidget().currentDataSet[0]: dec_sheet.write(row, column, item, separateStyle) row += 1 # decomposition for lvl in self.parentWidget().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 if self.exportGraph.isChecked(): pass levels_sheet = book.add_sheet("Multiscale forecast") # levels data if self.exportData.isChecked(): column = 0 for lvl in self.parentWidget().processedWCoeffs: row = 0 levels_sheet.write(row, column, "Level" + str(column), headerStyle) levels_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in lvl: levels_sheet.write(row, column, float(item), style) row += 1 column += 1 if self.exportForecast.isChecked(): result_sheet = book.add_sheet("Results") if self.exportData.isChecked(): # 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.parentWidget().currentDataSet[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.parentWidget().resultingForecast: result_sheet.write(row, column, item, style) row += 1 if self.exportGraph.isChecked(): row = 0 column = 2 self.parentWidget().resultingGraph.saveFigure("forecast", format="bmp") result_sheet.insert_bitmap(RES + TEMP + "forecast.bmp", row, column) # saving xls try: book.save(unicode(fileName)) self.parentWidget().messageInfo.showInfo("Saved as " + unicode(fileName)) except Exception: self.parentWidget().messageInfo.showInfo("Could not save as " + unicode(fileName), True) except Exception, e: self.parentWidget().messageInfo.showInfo("Not enough data.", True)
################################### ######### 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) feuy1.write(0, 6, u'Nombre de champs', entete) feuy1.write(0, 7, u'Nombre d\'objets', 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 salvar(trelica_objeto): borders = Borders() bord = 2 borders.left = bord borders.right = bord borders.top = bord borders.bottom = bord bord_lateral = Borders() bord = 2 bord_lateral.left = bord bord_lateral.right = bord ## FONTES fonte_titulo = Font() fonte_titulo.name = 'Times New Roman' fonte_titulo.bold = True fonte_titulo.height = 240 fonte_titulo.borders = bord_lateral fonte_normal = Font() fonte_normal.name = 'Times New Roman' fonte_normal.borders = bord_lateral fonte_normal_problema = Font() fonte_normal_problema.name = 'Times New Roman' fonte_normal_problema.borders = bord_lateral fonte_normal_problema.colour_index = 2 #5=amarelo | 4=azul | 3=verde | 2=vermelho fonte_destaque = Font() fonte_destaque.name = 'Arial' fonte_destaque.colour_index = 4 # azul ## ESTILOS estilo_titulo = XFStyle() estilo_titulo.borders = borders estilo_titulo.font = fonte_titulo estilo_titulo.alignment.horz = 2 estilo_sub_titulo = XFStyle() estilo_sub_titulo.borders = borders estilo_sub_titulo.alignment.horz = 2 estilo_sub_titulo.font = fonte_destaque estilo_normal = XFStyle() estilo_normal.font = fonte_normal estilo_normal.num_format_str = "#,##0.00" estilo_normal.alignment.horz = 2 estilo_normal_esquerda = XFStyle() estilo_normal_esquerda.font = fonte_normal estilo_normal_tabela = XFStyle() estilo_normal_tabela.font = fonte_normal estilo_normal_tabela.borders = borders estilo_normal_tabela.alignment.horz = 2 estilo_normal_tabela.num_format_str = "#,##0.00" estilo_normal_tabela_red = XFStyle() estilo_normal_tabela_red.font = fonte_normal_problema estilo_normal_tabela_red.borders = borders estilo_normal_tabela_red.alignment.horz = 2 estilo_normal_tabela_red.num_format_str = "#,##0.00" estilo_destaque = XFStyle() estilo_destaque.font = fonte_destaque # estilo_destaque.alignment.horz = 2 estilo_destaque.alignment.vertical = 2 estilo_destaque_tabela = XFStyle() estilo_destaque_tabela.font = fonte_destaque estilo_destaque_tabela.borders = borders estilo_destaque_tabela.alignment.horz = 2 estilo_id_tabela = estilo_normal_tabela estilo_id_tabela.alignment.num_format_str = "#0" text_file = filedialog.asksaveasfile(mode='w', defaultextension=".xls", filetypes=[('excel files', '.xls')]) wb = Workbook() sheet = wb.add_sheet('Treliceca') sheet.col(2).width = 0x0d00 + 22 sheet.col(4).width = 0x0d00 + 40 sheet.col(7).width = 0x0d00 + 15 sheet.col(8).width = 0x0d00 + 15 vt = trelica_objeto.vt banzo_reto = trelica_objeto.banzo_reto # padrão de viga treliçada viga = 'Viga de Cobertura Treliçada com banzos paralelos' if vt == 1: viga = 'Viga de Transição Treliçada' if banzo_reto == 1: viga = 'Viga de Cobertura Treliçada com banzo inferior reto' # combinações de carregamentos atuando na treliça carregamento_grav = trelica_objeto.carregamentos[0] carregamento_cv = trelica_objeto.carregamentos[1] vaos = '' for ponto in trelica_objeto.pontos_vao: vaos += ' ' + str(ponto[0]) + ',' # pesos calculados da treliça peso = trelica_objeto.peso peso_dobrado = trelica_objeto.peso_dobrado peso_soldado = trelica_objeto.peso_soldado peso_linear = trelica_objeto.peso_linear peso_miscelanias = trelica_objeto.peso_miscelanias # pecas = trelica_objeto.pecas h_viga = trelica_objeto.h_viga # lista com as barras da treliça, separadas por tipo banzos_inferiores = [] banzos_superiores = [] diagonais = [] montantes = [] for barra in trelica_objeto.barras_objetos: if barra.tipo == 'diagonal': diagonais.append(barra) elif barra.tipo == 'montante': montantes.append(barra) elif barra.tipo == 'banzo-superior': banzos_superiores.append(barra) elif barra.tipo == 'banzo-inferior': banzos_inferiores.append(barra) lista_barras = [banzos_inferiores, banzos_superiores, montantes, diagonais] linha = 0 # inserindo o titulo = viga sheet.write_merge(linha, linha, 1, 11, viga, estilo_titulo) linha += 1 sheet.write_merge(linha, linha, 1, 11, 'Dados da Viga Treliçada', estilo_sub_titulo) linha += 1 sheet.write_merge(linha, linha, 8, 11, 'Combinações [kg/m]', estilo_destaque_tabela) sheet.write(linha, 1, 'Vãos[m]=', estilo_destaque) sheet.write_merge(linha, linha, 2, 4, vaos, estilo_normal) linha += 1 sheet.write(linha, 1, 'Altura [m]', estilo_destaque) sheet.write(linha, 2, h_viga, estilo_normal) sheet.write(linha, 4, 'Soldado [kg]', estilo_destaque) sheet.write(linha, 5, peso_soldado, estilo_normal) sheet.write_merge(linha, linha, 8, 9, 'Grav. ', estilo_destaque_tabela) sheet.write_merge(linha, linha, 10, 11, 'Vento', estilo_destaque_tabela) linha += 1 sheet.write(linha, 1, 'Peso [kg]', estilo_destaque) sheet.write(linha, 2, peso, estilo_normal) sheet.write(linha, 4, 'Dobrado [kg]', estilo_destaque) sheet.write(linha, 5, peso_dobrado, estilo_normal) sheet.write_merge(linha, linha, 8, 9, '1.25xCP + 1.5xSC + 1.4xSU', estilo_normal_tabela) sheet.write_merge(linha, linha, 10, 11, '1.0xCP + 1.4xCV', estilo_normal_tabela) linha += 1 sheet.write(linha, 1, 'Y [kg/m]', estilo_destaque) sheet.write(linha, 2, peso_linear, estilo_normal) sheet.write(linha, 4, 'Miscelanias [kg]', estilo_destaque) sheet.write(linha, 5, peso_miscelanias, estilo_normal) sheet.write_merge(linha, linha, 8, 9, carregamento_grav, estilo_normal_tabela) sheet.write_merge(linha, linha, 10, 11, carregamento_cv, estilo_normal_tabela) linha += 2 # Barras da treliça sheet.write_merge(linha, linha, 1, 11, 'Barras da Treliça', estilo_destaque_tabela) linha += 1 sheet.write(linha, 1, 'id', estilo_destaque_tabela) sheet.write(linha, 2, ' ', estilo_destaque_tabela) sheet.write(linha, 3, 'tipo ', estilo_destaque_tabela) sheet.write(linha, 4, 'seção ', estilo_destaque_tabela) sheet.write(linha, 5, 'Peso [kg]', estilo_destaque_tabela) sheet.write(linha, 6, 'l [m]', estilo_destaque_tabela) sheet.write(linha, 7, 'Comp [kgf]', estilo_destaque_tabela) sheet.write(linha, 8, 'Tração [kgf]', estilo_destaque_tabela) sheet.write(linha, 9, 'Ratio ', estilo_destaque_tabela) sheet.write(linha, 10, 'compr.', estilo_destaque_tabela) sheet.write(linha, 11, 'tração', estilo_destaque_tabela) linha += 1 for lista in lista_barras: for barra in lista: estilo = estilo_normal_tabela if barra.ratio > 1: estilo = estilo_normal_tabela_red else: estilo = estilo_normal_tabela sheet.write(linha, 1, barra.id, estilo) sheet.write(linha, 2, barra.tipo, estilo) sheet.write(linha, 3, barra.section.tipo, estilo) sheet.write(linha, 4, stringSecaoBarra(barra), estilo) sheet.write(linha, 5, barra.peso, estilo) sheet.write(linha, 6, barra.comprimento(), estilo) sheet.write(linha, 7, barra.compressao, estilo) sheet.write(linha, 8, barra.tracao, estilo) sheet.write(linha, 9, barra.ratio, estilo) sheet.write(linha, 10, barra.ratio_compressao, estilo) sheet.write(linha, 11, barra.ratio_tracao, estilo) linha += 1 linha = 1 sheet.write(linha, 15, 'Reações nos Apoios = [h1, v1, v2, v3, ..., vn] ', estilo_normal_esquerda) linha += 1 sheet.write(linha, 15, 'Apoios numerados conforme desenho do canvas', estilo_normal_esquerda) linha += 1 sheet.write(linha, 15, 'Numeração da esquerda, para direita', estilo_normal_esquerda) linha += 1 sheet.write(linha, 15, 'F', estilo_destaque_tabela) sheet.write_merge(linha, linha, 16, 17, 'kgf', estilo_destaque_tabela) linha += 1 sheet.write(linha, 15, ' ', estilo_destaque_tabela) sheet.write(linha, 16, 'grav', estilo_destaque_tabela) sheet.write(linha, 17, 'vento', estilo_destaque_tabela) linha += 1 reacoes_grav = trelica_objeto.reacoes_grav reacoes_cv = trelica_objeto.reacoes_cv qtd_reacoes = len(reacoes_grav) sheet.write(linha, 15, 'Fx 1', estilo_destaque_tabela) sheet.write(linha, 16, reacoes_grav[0], estilo_normal_tabela) sheet.write(linha, 17, reacoes_cv[0], estilo_normal_tabela) linha += 1 for i in range(1, qtd_reacoes): sheet.write(linha, 15, 'Fy {:.0f}'.format(i), estilo_destaque_tabela) sheet.write(linha, 16, reacoes_grav[i], estilo_normal_tabela) sheet.write(linha, 17, reacoes_cv[i], estilo_normal_tabela) linha += 1 wb.save(text_file.name)
def crear_xls(self): # item=self.tree.selection()[0] # var=self.tree.item(item, option="text") # print(var) # Workbook asing self.filename = filedialog.asksaveasfilename( initialdir="/", title="Guardar venta", filetypes=(("Archivo xls", "*.xls"), ("all files", "*.*"))) if len(self.filename) > 0: first_book = Workbook() # Sheets definition ws1 = first_book.add_sheet('venta') header_font = Font() body_font = Font() # Header font preferences header_font.name = 'Times New Roman' header_font.height = 20 * 10 header_font.bold = True # Body font preferences body_font.name = 'Arial' body_font.italic = True # Header Cells style definition header_style = XFStyle() header_style.font = header_font borders = Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 header_style.borders = borders # body cell name style definition body_style = XFStyle() body_style.font = body_font print(ws1.col(0).width) ws1.write(0, 0, '##', header_style) ws1.write(0, 1, 'DESCRIPCION', header_style) ws1.write(0, 2, 'PRECIO', header_style) ws1.write(0, 3, 'CANTIDAD', header_style) ws1.write(0, 4, 'SUBTOTAL', header_style) ws1.col(0).width = 2962 * 2 ws1.col(1).width = 2962 * 4 ws1.col(2).width = 2962 * 1 ws1.col(3).width = 2962 * 1 ws1.col(4).width = 2962 * 1 tabla_venta = self.tree2.get_children() for item, conteo in zip(tabla_venta, range(len(tabla_venta))): ws1.write(conteo + 1, 0, self.tree2.item(item)['text']) ws1.write(conteo + 1, 1, self.tree2.item(item)['values'][0], body_style) ws1.write(conteo + 1, 2, self.tree2.item(item)['values'][1], body_style) ws1.write(conteo + 1, 3, self.tree2.item(item)['values'][2], body_style) ws1.write(conteo + 1, 4, self.tree2.item(item)['values'][3], body_style) # print(self.tree2.item(item)["values"]) # print(self.tree2.item(item)['text']) # Saving file first_book.save(self.filename)
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 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 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)
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
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 button_print_sale_report(self): filename = 'Sale Wizard Report.xls' string = 'sale Report.xls' wb = xlwt.Workbook(encoding='utf-8') worksheet = wb.add_sheet(string) style = XFStyle() fnt = Font() fnt.colour_index = 0x36 fnt.bold = True fnt.width = 256 * 30 style.font = fnt style1 = XFStyle() fnt = Font() fnt.colour_index = 0x8 fnt.bold = True fnt.width = 256 * 30 style1.font = fnt worksheet.write_merge( 0, 1, 2, 3, "Transport Sale Report", xlwt.easyxf( 'font: height 200, name Arial, colour_index black, bold on, italic off; align: wrap on, vert centre, horiz center;' )) worksheet.write( 2, 0, 'Sale Order No', xlwt.easyxf( 'font: height 200, name Arial, colour_index black, bold on, italic off; align: wrap on, vert centre, horiz left;' )) worksheet.write( 2, 1, 'Customer', xlwt.easyxf( 'font: height 200, name Arial, colour_index black, bold on, italic off; align: wrap on, vert centre, horiz left;' )) worksheet.write( 2, 2, 'Confirmation Date', xlwt.easyxf( 'font: height 200, name Arial, colour_index black, bold on, italic off; align: wrap on, vert centre, horiz left;' )) worksheet.write( 2, 3, 'Product', xlwt.easyxf( 'font: height 200, name Arial, colour_index black, bold on, italic off; align: wrap on, vert centre, horiz left;' )) worksheet.write( 2, 4, 'Order Quantity', xlwt.easyxf( 'font: height 200, name Arial, colour_index black, bold on, italic off; align: wrap on, vert centre, horiz left;' )) worksheet.write( 2, 5, 'Unit', xlwt.easyxf( 'font: height 200, name Arial, colour_index black, bold on, italic off; align: wrap on, vert centre, horiz left;' )) a = 3 # sale_order_ids = self.env['sale.order'].search([]) sale_order = self.sale_order_ids print("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", sale_order) if sale_order: for sale_id in sale_order: # worksheet.write(a, 0, sale_id.name) # worksheet.write(a, 1, sale_id.partner_id.name) # worksheet.write(a, 2, sale_id.confirmation_date) for line in sale_id.order_line: worksheet.write(a, 0, sale_id.name) worksheet.write(a, 1, sale_id.partner_id.name) worksheet.write(a, 2, sale_id.confirmation_date) worksheet.write(a, 3, line.product_id.name) worksheet.write(a, 4, line.product_uom_qty) worksheet.write(a, 5, line.price_unit) a += 1 else: sale_order_ids = self.env['sale.order'].search([]) for id in sale_order_ids: if id.state == "done": # print(id) for sale_id in id: for line in sale_id.order_line: worksheet.write(a, 0, sale_id.name) worksheet.write(a, 1, sale_id.partner_id.name) worksheet.write(a, 2, sale_id.confirmation_date) worksheet.write(a, 3, line.product_id.name) worksheet.write(a, 4, line.product_uom_qty) worksheet.write(a, 5, line.price_unit) a += 1 fp = BytesIO() wb.save(fp) out = base64.encodestring(fp.getvalue()) view_report_id = self.env['view.report.sale.wizard'].create({ 'file_name': out, 'datas_fname': filename }) return { 'res_id': view_report_id.id, 'name': '', 'view_type': 'form', 'view_mode': 'form', 'res_model': 'view.report.sale.wizard', 'view_id': False, 'type': 'ir.actions.act_window', }
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