def css2excel(css): #custom_css = 'font: name "%s", %s on'%(current_value['font-family'].split(",")[0], current_value['font-weight'].split(",")[0]) #export_sheet.write(row_idx, col_idx, current_value['data'], xlwt.easyxf('font: italic on; pattern: pattern solid, fore-colour grey25')) xf_list = [] fnt = Font() borders = Borders() pattern = Pattern() align = Alignment() process_css = { 'font-family' : [fnt, "name" , lambda x : x.split(",")[0]], 'color' : [fnt, "colour_index", lambda x : excel_color_map.get(x,0)+8], 'font-weight' : [fnt, "bold", lambda x : x.upper() == 'BOLD'], #'font-weight' : [fnt, "bold", lambda x : x == '700'], 'text-align' : [align, "horz", lambda x : {'left':align.HORZ_LEFT, 'right':align.HORZ_RIGHT, 'center':align.HORZ_CENTER, 'justified': align.HORZ_JUSTIFIED}[x]], 'background-color' : [pattern,"pattern_fore_colour", lambda x: excel_color_map.get(x,16)+8], } #TODO process_css -> css for i in process_css.keys(): #print process_css[i][0] ,".",process_css[i][1], " => " , css[i] ," | ", process_css[i][2](css[i]) setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i])) style = XFStyle() style.font = fnt borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style.borders = borders style.pattern = pattern style.pattern.pattern = 1 style.alignment = align return style
def set_style(name, height, bg_Color=0, bold=False): # 设置字体 style = xlwt.XFStyle() font = xlwt.Font() font.name = name font.bold = bold font.color_index = 4 font.height = height style.font = font # 设置边框 borders = xlwt.Borders() # 细实线:1,小粗实线:2,细虚线:3,中细虚线:4,大粗实线:5,双线:6,细点虚线:7 # 大粗虚线:8,细点划线:9,粗点划线:10,细双点划线:11,粗双点划线:12,斜点划线:13 borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 style.borders = borders if bg_Color != 0: pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = bg_Color # 设置单元格背景色为黄色 style.pattern = pattern return style
def setStyle(n): pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = n style = XFStyle() style.pattern = pattern return style
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")
class Column(Cell): title_fnt = Font() title_fnt.name = 'Calibri' title_fnt.bold = True title_fnt.height = 12 * 20 # font size 12 obj_fnt = Font() obj_fnt.name = 'Calibri' obj_fnt.height = 12 * 20 # font size 12 title_aln = Alignment() title_aln.horz = Alignment.HORZ_CENTER title_aln.vert = Alignment.VERT_CENTER title_aln.wrap = Alignment.WRAP_AT_RIGHT title_ptn = Pattern() title_ptn.pattern = Pattern.SOLID_PATTERN title_ptn.pattern_fore_colour = 0x2C def __init__(self, text, left_border=False, right_border=False, use_pattern=False, width=0x0E00, func=lambda obj: ""): super(Column, self).__init__(text, self.title_fnt, self.title_aln, Borders(), self.title_ptn, width=width) self.obj_style = XFStyle() self.obj_style.alignment = self.title_aln self.obj_style.borders = Borders() self.obj_style.borders.left = Borders.HAIR self.obj_style.borders.right = Borders.HAIR self.obj_style.borders.top = Borders.HAIR self.obj_style.borders.bottom = Borders.HAIR self.border.left = Borders.HAIR self.border.right = Borders.HAIR self.border.top = Borders.THICK self.border.bottom = Borders.THICK if left_border: self.obj_style.borders.left = Borders.THICK self.border.left = Borders.THICK if right_border: self.obj_style.borders.right = Borders.THICK self.border.right = Borders.THICK self.obj_style.font = self.obj_fnt if use_pattern: self.obj_style.pattern = self.pattern self.func = func
def writeXls(self, file_name): #写入Excel表格 movie_list = [ '上映年份', '片名', '导演', '编剧', '主演', '类型', '制片国家地区', '语言', '又名', '上映日期', '片长', 'IMDB链接/影片地址', '评分', '评价人数', '页面网址', '短评', '话题' ] xls = ExcelWrite.Workbook() sheet = xls.add_sheet("Sheet1") #写入表1 style = XFStyle() pattern = Pattern() # 创建一个模式 pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型 pattern.pattern_fore_colour = 0x16 #设置其模式单元格背景色 # 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on... style.pattern = pattern for i in range(len(movie_list)): #写入首行信息,为表头,表示列名 sheet.write(0, i, movie_list[i], style) sheet.col(i).width = 5240 actors_list = ['姓名', '性别', '星座', '年龄', '出生地', '职业', '简介'] sheet2 = xls.add_sheet("Sheet2") style2 = XFStyle() style2.pattern = pattern for i in range(len(actors_list)): # 写入首行信息,为表头,表示列名 sheet2.write(0, i, actors_list[i], style2) sheet2.col(i).width = 5140 try: #连接数据库读取数据 conn = pymysql.connect(host='localhost', user='******', password='******', db='mytest', port=3306, charset='utf8') cur = conn.cursor() sql = 'select * from movies' cur.execute(sql) row = 0 for movie_info in cur.fetchall(): #遍历数据库中每行信息,一行表示一部电影的所有信息 row = row + 1 #第0行为表头,不添加数据,因此从第一列开始写入 for i in range(len(movie_info) - 1): #对于一行信息进行遍历,分别存入每列 sheet.write(row, i, movie_info[i + 1]) sql = "select id,name,sex,star,date_format(from_days(to_days(now())-to_days(birthday)),'%Y')+0,place,job,message from actors" cur.execute(sql) row = 0 for actor_info in cur.fetchall(): # 遍历数据库中每行信息,一行表示一部电影的所有信息 row = row + 1 # 第0行为表头,不添加数据,因此从第一列开始写入 for i in range(len(actor_info) - 1): # 对于一行信息进行遍历,分别存入每列 sheet2.write(row, i, actor_info[i + 1]) xls.save(file_name) #写入完成,存储 cur.close() conn.close() wx.MessageBox('数据已导出到桌面!!!', caption="导出成功") except: wx.MessageBox('数据导出失败!!!', caption="导出失败")
def writeCycleInfo2File(self): workbook = xlwt.Workbook() # set header style headerStyle = xlwt.XFStyle() headerFont = xlwt.Font() headerFont.bold = True headerStyle.font = headerFont headerPattern = Pattern() headerPattern.pattern = Pattern.SOLID_PATTERN headerPattern.pattern_fore_colour = 50 # green headerStyle.pattern = headerPattern # write run result to excel file for threadKey in self.cycleInfo.keys(): sheet = workbook.add_sheet(threadKey, True) headerText = ["cycleNumber", "liveIndividuals", "deadIndividuals", "newBorn", "newDeath", "newDeathFromFight", "newDeathFromNatural", "newDeathFromStarve", "breedTimes", "fightTimes", "fightSuccessTimes", "fightPeaceTimes", "fightFailureTimes", "FleeSuccessTimes", "defendTimes", "defendSuccessTimes", "defendPeaceTimes", "defendFailureTimes", "popAvgHungryLevel", "popAvgAge", "popAvgLifespan", "popAvgFightCapability", "popAvgAttackPossibility", "popAvgDefendPossibility", "popAvgTotalBreedingTimes"] for i in range(0, len(headerText)): sheet.write(0, i, headerText[i], headerStyle) rowNum = 1 threadValue = self.cycleInfo[threadKey] for cycleNumber in threadValue.keys(): cycleInfo = threadValue[cycleNumber] sheet.write(rowNum, 0, cycleNumber) sheet.write(rowNum, 1, cycleInfo.liveIndividuals) sheet.write(rowNum, 2, cycleInfo.deadIndividuals) sheet.write(rowNum, 3, cycleInfo.newBorn) sheet.write(rowNum, 4, cycleInfo.newDeath) sheet.write(rowNum, 5, cycleInfo.newDeathFromFight) sheet.write(rowNum, 6, cycleInfo.newDeathFromNatural) sheet.write(rowNum, 7, cycleInfo.newDeathFromStarve) sheet.write(rowNum, 8, cycleInfo.breedTimes) sheet.write(rowNum, 9, cycleInfo.fightTimes) sheet.write(rowNum, 10, cycleInfo.fightSuccessTimes) sheet.write(rowNum, 11, cycleInfo.fightPeaceTimes) sheet.write(rowNum, 12, cycleInfo.fightFailureTimes) sheet.write(rowNum, 13, cycleInfo.FleeSuccessTimes) sheet.write(rowNum, 14, cycleInfo.defendTimes) sheet.write(rowNum, 15, cycleInfo.defendSuccessTimes) sheet.write(rowNum, 16, cycleInfo.defendPeaceTimes) sheet.write(rowNum, 17, cycleInfo.defendFailureTimes) sheet.write(rowNum, 18, cycleInfo.popAvgHungryLevel) sheet.write(rowNum, 19, cycleInfo.popAvgAge) sheet.write(rowNum, 20, cycleInfo.popAvgLifespan) sheet.write(rowNum, 21, cycleInfo.popAvgFightCapability) sheet.write(rowNum, 22, cycleInfo.popAvgAttackPossibility) sheet.write(rowNum, 23, cycleInfo.popAvgDefendPossibility) sheet.write(rowNum, 24, cycleInfo.popAvgTotalBreedingTimes) rowNum += 1 workbook.save("EvolutionSimulationResult_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".xls")
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 export_to_excel(self): if not os.path.isdir('./export_excel'): # 导出结果存储目录 os.makedirs('./export_excel') save_path = os.path.join('./export_excel', self.select_table + '.xls') head_list = ['学号', '姓名', '是否出勤', '出勤时间'] xls = ExcelWrite.Workbook() # 创建Excel控制对象 sheet = xls.add_sheet("Sheet1") # 创建被写入的表格sheet1 style = XFStyle() pattern = Pattern() # 创建一个模式 pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型 pattern.pattern_fore_colour = 0x16 # 设置其模式单元格背景色 # 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on... style.pattern = pattern for col in range(len(head_list)): # 写入首行信息,为表头,表示列名 sheet.write(0, col, head_list[col], style) sheet.col(col).width = 4240 try: # 连接数据库读取数据 conn, cursor = DataManageUI.connect_to_sql() sql = 'select * from `%s`' % self.select_table cursor.execute(sql) row = 0 stu_data = cursor.fetchall() for stu_info in stu_data: # 遍历数据库中每行信息,一行表示一部电影的所有信息 stu_info = list(stu_info) if stu_info[3]: stu_info[3] = stu_info[3].strftime('%Y/%m/%d %H:%M:%S') row = row + 1 # 第0行为表头,不添加数据,因此从第一列开始写入 for col in range(len(stu_info)): # 对于一行信息进行遍历,分别存入每列 sheet.write(row, col, stu_info[col]) xls.save(save_path) # 写入完成,存储 cursor.close() conn.close() text = 'Success!' informativeText = '<b>课程{}签到表 导出成功! 目标路径:./export_excel</b>'.format( self.select_table) DataRecordUI.callDialog(QMessageBox.Information, text, informativeText, QMessageBox.Ok) except Exception as e: print(e) text = 'Error!' informativeText = '<b>导出失败!</b>' DataRecordUI.callDialog(QMessageBox.Critical, text, informativeText, QMessageBox.Ok)
def init_sheet_catalog(sheet): global config_inner_items global config_multimedia_items global config_glass_items style = xlwt.XFStyle() # 创建一个样式对象,初始化样式 al = xlwt.Alignment() al.horz = 0x02 # 设置水平居中 al.vert = 0x01 # 设置垂直居中 style.alignment = al font = xlwt.Font() # 为样式创建字体 font.name = 'Times New Roman' font.bold = True # 黑体 # font.underline = True # 下划线 # font.italic = True # 斜体字 style.font = font # 设定样式 pattern = Pattern() # 创建一个模式 pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型 pattern.pattern_fore_colour = 3 # 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on... style.pattern = pattern row_index = 2 # config_inner = config_data['内部配置'] # config_inner_items = list(config_inner.keys()) sheet.write_merge(row_index, row_index + len(config_inner_items) -1 , 0, 0+0, "内部配置", style) for index in range(len(config_inner_items)): sheet.write(row_index + index , 1, config_inner_items[index]) row_index = row_index + len(config_inner_items) # config_multimedia = config_data['多媒体配置'] # config_multimedia_items = list(config_multimedia.keys()) sheet.write_merge(row_index, row_index + len(config_multimedia_items)-1 , 0, 0+0, "多媒体配置", style) for index in range(len(config_multimedia_items)): sheet.write(row_index + index , 1, config_multimedia_items[index]) row_index = row_index + len(config_multimedia_items) # config_glass = config_data['玻璃/后视镜'] # config_glass_items = list(config_glass.keys()) sheet.write_merge(row_index ,row_index + len(config_glass_items)-1, 0, 0+0, "玻璃/后视镜", style) for index in range(len(config_glass_items)): sheet.write(row_index + index , 1, config_glass_items[index]) row_index = row_index + len(config_glass_items)
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
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 css2excel(css): fnt = Font() borders = Borders() pattern = Pattern() align = Alignment() process_css = { 'font-family': [fnt, "name", lambda x: x.split(",")[0]], 'font-size': [fnt, "height", lambda x: get_font_height(x.upper())], 'color': [fnt, "colour_index", lambda x: match_color_index(x.upper())], 'font-weight': [fnt, "bold", lambda x: x == 'bold'], 'font-style': [fnt, "italic", lambda x: x == 'italic'], 'text-align': [align, "horz", lambda x: get_horizontal_align(x, align)], 'vertical-align': [align, "vert", lambda x: get_vertical_align(x, align)], 'background-color': [ pattern, "pattern_fore_colour", lambda x: match_color_index(x.upper()) ], } for i in css.keys(): if i in process_css.keys(): setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i].strip())) style = XFStyle() style.font = fnt borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style.borders = borders style.pattern = pattern style.pattern.pattern = 1 style.alignment = align style.num_format_str = '#,##0.00' return style
def 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 transData(res,pars): m_f=int(pars['f']) max_refer=4 k_1=['1.显示cookie唯一数&总数','1.显示ip唯一数&总数','1.点击cookie唯一数&总数','1.点击ip唯一数&总数','1.一个cookie对应各3个不同ip数的个数','1.一个cookie对应各4个不同ip数的个数','1.一个cookie对应各大于4个不同ip数的个数'] k_2=['2.显示点击重合'] k_3=['3.点击频次高的cookie所对应的显示数','3.显示频次高的cookie所对应的点击数']# k_4=['4.refer显示数&点击数'] data=[] (imp_cookie_dis,imp_cookie_all)=transNULL(res[k_1[0]][0].split('\t')) (imp_ip_dis,imp_ip_all)=transNULL(res[k_1[1]][0].split('\t')) (clk_cookie_dis,clk_cookie_all)=transNULL(res[k_1[2]][0].split('\t')) (clk_ip_dis,clk_ip_all)=transNULL(res[k_1[3]][0].split('\t')) (one_cookie_3_ip_dis,one_cookie_3_ip_all)=transNULL(res[k_1[4]][0].split('\t')) (one_cookie_4_ip_dis,one_cookie_4_ip_all)=transNULL(res[k_1[5]][0].split('\t')) (one_cookie_b_4_ip_dis,one_cookie_b_4_ip_all)=transNULL(res[k_1[6]][0].split('\t')) (imp_join_clk_cookie_dis,imp_join_clk_cookie_clk)=transNULL(res[k_2[0]][0].split('\t')) (tb_imp_f_list,tb_imp_f_sum,sum_imp_str)=transDataList_imp(res[k_3[0]],m_f,imp_cookie_dis,imp_cookie_all) (tb_clk_f_list,tb_clk_f_sum,sum_clk_str)=transDataList_clk(res[k_3[1]],m_f,imp_cookie_dis,imp_cookie_all) (tb_referer_list,sum_referer_str)=transDataList_referer(res[k_4[0]],max_refer,imp_cookie_all,clk_cookie_all) #排查3 data.append(transCellData('A5',imp_ip_all))#IP显示总数 data.append(transCellData('B5',imp_cookie_all))#cookie显示总数 data.append(transCellData_ext('C5',imp_ip_all,imp_cookie_all,1))#显示差值 data.append(transCellData('D5',clk_ip_all))#IP点击总数 data.append(transCellData('E5',clk_cookie_all))#cookie点击总数 data.append(transCellData_ext('F5',clk_ip_all,clk_cookie_all,1))#点击差值 #排查3 判断 conclusion_str='' if imp_ip_all==imp_cookie_all and clk_ip_all==clk_cookie_all: conclusion_str='正常' else: conclusion_str='异常' conclusion_str=uniCode(conclusion_str) ts=transCellData_summary('D2',conclusion_str) ##ts[1].borders.right = Borders.THICK ? ts[1].alignment.vert = Alignment.VERT_CENTER data.append(ts) data.append(transCellData('A10',imp_ip_dis))#IP唯一显示数 data.append(transCellData('B10',imp_cookie_dis))#cookie唯一显示数 data.append(transCellData_ext('C10',imp_cookie_dis,imp_ip_dis,1))#显示差异比例 data.append(transCellData('D10',clk_ip_dis))#IP唯一点击数 data.append(transCellData('E10',clk_cookie_dis))#cookie唯一点击数 data.append(transCellData_ext('F10',clk_cookie_dis,clk_ip_dis,1))#点击差异比例 ts=transCellData_ext('A12',one_cookie_3_ip_dis,0,imp_cookie_dis) ts[1].num_format_str='0.00%' data.append(ts)#一个cookie对应3个ip的cookie占比 ts=transCellData_ext('B12',one_cookie_3_ip_all,0,imp_cookie_all) ts[1].num_format_str='0.00%' data.append(ts)#其显示占比 ts=transCellData_ext('C12',one_cookie_4_ip_dis,0,imp_cookie_dis) ts[1].num_format_str='0.00%' data.append(ts)#一个cookie对应4个ip的cookie占比 ts=transCellData_ext('D12',one_cookie_4_ip_all,0,imp_cookie_all) ts[1].num_format_str='0.00%' data.append(ts)#其显示占比 ts=transCellData_ext('E12',one_cookie_b_4_ip_dis,0,imp_cookie_dis) ts[1].num_format_str='0.00%' data.append(ts)#一个cookie对应4个ip以上的cookie占比 ts=transCellData_ext('F12',one_cookie_b_4_ip_all,0,imp_cookie_all) ts[1].num_format_str='0.00%' data.append(ts)#其显示占比 data.append(transCellData('A19',clk_cookie_all))#点击总数 data.append(transCellData('B19',imp_cookie_all))#显示cookie总数(唯一) data.append(transCellData('C19',clk_cookie_all))#点击cookie总数(唯一) data.append(transCellData_ext('D19',clk_cookie_all,imp_cookie_all,1 ))#cookie重合数1 data.append(transCellData('E19',imp_join_clk_cookie_clk))#cookie重合数2 data.append(transCellData_ext('F19',clk_cookie_all,imp_join_clk_cookie_clk,1)) #无显示cookie ts=transCellData_ext('G19',clk_cookie_dis,imp_join_clk_cookie_dis,clk_cookie_dis) ts[1].num_format_str='0.00%' data.append(ts) #无显示cookie占比 ts=transCellData_ext('H19',clk_cookie_all,imp_join_clk_cookie_clk,clk_cookie_all) ts[1].num_format_str='0.00%' data.append(ts) #点击数占总体 intindex=27 for tt in tb_imp_f_list: ts=transCellData('A%d'%intindex,tt[0]) if tt[0]<0: ts[0][2]= len(tb_imp_f_list)-1 ts[1].num_format_str='0+' data.append(ts) data.append(transCellData('B%d'%intindex,tt[1])) ts=transCellData_ext('C%d'%intindex,tt[2],0,1) ts[1].num_format_str='0.00%' data.append(ts) data.append(transCellData('D%d'%intindex,tt[3])) ts=transCellData_ext('E%d'%intindex,tt[4],0,1) ts[1].num_format_str='0.00%' data.append(ts) data.append(transCellData('F%d'%intindex,tt[5])) data.append(transCellData('G%d'%intindex,tt[6])) intindex+=1 intindex=37 pattern = Pattern() pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF pattern.pattern_fore_colour = 0x37 ts=transCellData('B%d'%intindex,tb_imp_f_sum[0][1]) ts[1].pattern=pattern data.append(ts) ts=transCellData_ext('C%d'%intindex,tb_imp_f_sum[0][2],0,1) ts[1].num_format_str='0.00%' ts[1].pattern=pattern data.append(ts) ts=transCellData('D%d'%intindex,tb_imp_f_sum[0][3]) ts[1].pattern=pattern data.append(ts) ts=transCellData_ext('E%d'%intindex,tb_imp_f_sum[0][4],0,1) ts[1].num_format_str='0.00%' ts[1].pattern=pattern data.append(ts) ts=transCellData('F%d'%intindex,tb_imp_f_sum[0][5]) ts[1].pattern=pattern data.append(ts) ts=transCellData('G%d'%intindex,tb_imp_f_sum[0][6]) ts[1].pattern=pattern data.append(ts) intindex=39 for tt in tb_clk_f_list: ts=transCellData('A%d'%intindex,tt[0]) if tt[0]<0: ts[0][2]= len(tb_imp_f_list)-1 ts[1].num_format_str='0+' data.append(ts) data.append(transCellData('B%d'%intindex,tt[1])) ts=transCellData_ext('C%d'%intindex,tt[2],0,1) ts[1].num_format_str='0.00%' data.append(ts) data.append(transCellData('D%d'%intindex,tt[3])) ts=transCellData_ext('E%d'%intindex,tt[4],0,1) ts[1].num_format_str='0.00%' data.append(ts) data.append(transCellData('F%d'%intindex,tt[5])) data.append(transCellData('G%d'%intindex,tt[6])) intindex+=1 intindex=49 ts=transCellData('B%d'%intindex,tb_clk_f_sum[0][1]) ts[1].pattern=pattern data.append(ts) ts=transCellData_ext('C%d'%intindex,tb_clk_f_sum[0][2],0,1) ts[1].num_format_str='0.00%' ts[1].pattern=pattern data.append(ts) ts=transCellData('D%d'%intindex,tb_clk_f_sum[0][3]) ts[1].pattern=pattern data.append(ts) ts=transCellData_ext('E%d'%intindex,tb_clk_f_sum[0][4],0,1) ts[1].num_format_str='0.00%' ts[1].pattern=pattern data.append(ts) ts=transCellData('F%d'%intindex,tb_clk_f_sum[0][5]) ts[1].pattern=pattern data.append(ts) ts=transCellData('G%d'%intindex,tb_clk_f_sum[0][6]) ts[1].pattern=pattern data.append(ts) intindex=53 for tt in tb_referer_list: data.append(transCellData_str('B%d'%intindex,tt[0])) ts=transCellData_ext('E%d'%intindex,tt[1],0,1) ts[1].num_format_str='0.00%' data.append(ts) ts=transCellData_ext('G%d'%intindex,tt[2],0,1) ts[1].num_format_str='0.00%' data.append(ts) ts=transCellData_ext('I%d'%intindex,tt[3],0,1) ts[1].num_format_str='0.00%' data.append(ts) intindex+=1 ##################summary#################### test_str='有%d个url产生了%s的显示及%s的点击,点击率约%s'%(max_refer,sum_referer_str[0],sum_referer_str[1],sum_referer_str[2]) test_str=uniCode(test_str) ts=transCellData_summary('D52',test_str) data.append(ts) test_str='%d频次以上Cookie占比%s;\n%d频次以上显示数占比%s'%(m_f,sum_imp_str[0],m_f,sum_imp_str[1]) test_str=uniCode(test_str) ts=transCellData_summary('D24',test_str) ts[1].borders.right = Borders.NO_LINE ts[1].borders.bottom = Borders.NO_LINE ts[1].alignment.wrap = 1 data.append(ts) test_str='点击%d次及以上的Cookie占比%s;\n点击%d次及以上的点击数占比%s'%(m_f,sum_clk_str[0],m_f,sum_clk_str[1]) test_str=uniCode(test_str) ts=transCellData_summary('D25',test_str) ts[1].borders.right = Borders.NO_LINE ts[1].borders.top = Borders.NO_LINE ts[1].alignment.wrap = 1 data.append(ts) if (float(clk_cookie_dis)==0): test_str='没有点击Cookie' else: if clk_cookie_dis!=0: val1_=100*(float(clk_cookie_dis)-float(imp_join_clk_cookie_dis))/float(clk_cookie_dis) else: val1_=0 if clk_cookie_all!=0: val2_=100*(float(clk_cookie_all)-float(imp_join_clk_cookie_clk))/float(clk_cookie_all) else: val2_=0 test_str='%s的点击Cookie没有显示;点击数占总比%s'%('%0.2f'%val1_+'%','%0.2f'%val2_+'%') test_str=uniCode(test_str) ts=transCellData_summary('D17',test_str) ##ts[1].borders.right = Borders.THICK ? ts[1].alignment.vert = Alignment.VERT_CENTER data.append(ts) val1_=100*sumPercent([one_cookie_3_ip_dis ,one_cookie_4_ip_dis ,one_cookie_b_4_ip_dis ],imp_cookie_dis) val2_=100*sumPercent([one_cookie_3_ip_all, one_cookie_4_ip_all, one_cookie_b_4_ip_all], imp_cookie_all) test_str='1个Cookie对应2个以上IP的Cookie占比%s;\n显示占总体%s'%('%0.2f'%val1_+'%','%0.2f'%val2_+'%') test_str=uniCode(test_str) ts=transCellData_summary('D8',test_str) ts[1].alignment.wrap = 1 data.append(ts) return data
def report_get(self, cr, uid, ids, context=None): this = self.browse(cr, uid, ids) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height = 275 #Define the font attributes for header content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 220 align_content = Alignment() align_content.horz = Alignment.HORZ_LEFT borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_LEFT align.vert = Alignment.VERT_TOP align.wrap = Alignment.WRAP_AT_RIGHT #The text should be right aligned align1 = Alignment() align1.horz = Alignment.HORZ_RIGHT align1.vert = Alignment.VERT_TOP align1.wrap = Alignment.WRAP_AT_RIGHT #The content should be left aligned align2 = Alignment() align2.horz = Alignment.HORZ_LEFT align2.vert = Alignment.VERT_TOP align2.wrap = Alignment.WRAP_AT_RIGHT #The content should be right aligned align3 = Alignment() align3.horz = Alignment.HORZ_RIGHT align3.vert = Alignment.VERT_TOP align3.wrap = Alignment.WRAP_AT_RIGHT #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x17 #We set the backgroundcolour here pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0xFF #We set the backgroundcolour here pattern3 = Pattern() pattern3.pattern = Pattern.SOLID_PATTERN pattern3.pattern_fore_colour = 0xFF #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 #apply the above settings to the row(1) header style_header1 = XFStyle() style_header1.font = fnt style_header1.pattern = pattern1 style_header1.borders = borders style_header1.alignment = align1 #apply the above settings to the content style_content_left = XFStyle() style_content_left.font = fnt style_content_left.pattern = pattern2 style_content_left.borders = borders style_content_left.alignment = align2 style_content_right = XFStyle() style_content_right.font = fnt style_content_right.pattern = pattern3 style_content_right.borders = borders style_content_right.alignment = align3 style_content = XFStyle() style_content.alignment = align_content style_content.font = content_fnt wb = Workbook() ws = wb.add_sheet("Sheet 1") ws.row(0).height = 3500 ws.write(0, 0, "(CO1)", style_header) ws.col(0).width = 2000 ws.write(0, 1, "Month & Year (CO2)", style_header) ws.col(1).width = 4500 ws.write(0, 2, "Buyer's TIN (CO3)", style_header) ws.col(2).width = 4500 ws.write(0, 3, "Buyer's Name (CO4)", style_header) ws.col(3).width = 10000 ws.write(0, 4, "Interstate Branch/Consignment Transfer (CO5)", style_header) ws.col(4).width = 4500 ws.write(0, 5, "Export Out Of India (CO6)", style_header) ws.col(5).width = 4500 ws.write(0, 6, "High Sea Sales (CO7)", style_header) ws.col(6).width = 4500 ws.write(0, 7, "ISS - Goods Type (CO8)", style_header) ws.col(7).width = 4500 ws.write(0, 8, "ISS - Form Type (I) (CO9)", style_header) ws.col(8).width = 4500 ws.write(0, 9, "ISS - Rate Of Tax (C1O)", style_header) ws.col(9).width = 4500 ws.write(0, 10, "ISS - Sales Price (Excluding CST) (C11)", style_header) ws.col(10).width = 4500 ws.write(0, 11, "ISS - Central Sales Tax (C12)", style_header) ws.col(11).width = 4500 ws.write(0, 12, "ISS - Total (C13)", style_header) ws.col(12).width = 4500 ws.write(0, 13, "Local Sale - Type Of Sale (C14)", style_header) ws.col(13).width = 4500 ws.write(0, 14, "Local Sale - Rate Of Tax (C15)", style_header) ws.col(14).width = 4500 ws.write(0, 15, "Local Sale - Sale Price (Excluding VAT) (C16)", style_header) ws.col(15).width = 4500 ws.write(0, 16, "Local Sale - OutPut Tax (C17)", style_header) ws.col(16).width = 4500 ws.write(0, 17, "Local Sale - Total (Including VAT) (C18)", style_header) ws.col(17).width = 4500 ws.row(1).height = 400 ws.write(1, 0, "0", style_header1) ws.write(1, 1, "0", style_header1) ws.write(1, 2, "0", style_header1) ws.write(1, 3, "", style_header1) ws.write(1, 4, "0.00", style_header1) ws.write(1, 5, "0.00", style_header1) ws.write(1, 6, "0.00", style_header1) ws.write(1, 7, "", style_header1) ws.write(1, 8, "", style_header1) ws.write(1, 9, "0.00", style_header1) ws.write(1, 10, "0.00", style_header1) ws.write(1, 11, "0.00", style_header1) ws.write(1, 12, "0.00", style_header1) ws.write(1, 13, "", style_header1) ws.write(1, 14, "0.00", style_header1) ws.write(1, 15, "0.00", style_header1) ws.write(1, 16, "0.00", style_header1) ws.write(1, 17, "0.00", style_header1) inv_obj = self.pool.get('account.invoice') line_obj = self.pool.get('account.invoice.line') row = 2 count = 1 value = 0.0 sum1 = sum2 = sum3 = sum4 = sum5 = sum6 = 0.0 period = [] for each in this: if each.start_period_id.name > each.end_period_id.name: raise osv.except_osv( _('Warning !'), _("Start period must be less than end period !")) cr.execute("select id from account_period where name between '" + str(each.start_period_id.name) + "' and '" + str(each.end_period_id.name) + "'") temp = cr.fetchall() for val in temp: if val: period.append(val[0]) inv_ids = inv_obj.search(cr, uid, [('type', '=', 'out_invoice'), ('state', 'not in', ['draft', 'cancel']), ('period_id', 'in', period)]) line_ids = line_obj.search(cr, uid, [('invoice_id', 'in', inv_ids)]) for line in line_obj.browse(cr, uid, line_ids): ws.row(row).height = 400 ws.write(row, 0, count, style_content_right) ws.write( row, 1, line.invoice_id.period_id and line.invoice_id.period_id.name or False, style_content_right) ws.write(row, 2, line.partner_id.tin_no or '', style_content_right) ws.write(row, 3, line.partner_id.name, style_content_left) for tax in line.invoice_line_tax_id: value = tax.amount * 100 if line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower( ) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower( ) == 'delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower( ) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower( ) == 'delhi': ws.write(row, 14, round(value, 2), style_content_right) ws.write(row, 15, round(line.price_subtotal, 2), style_content_right) sum1 += round(line.price_subtotal, 2) ws.write(row, 16, round(line.price_subtotal * tax.amount, 2), style_content_right) sum2 += round(line.price_subtotal * tax.amount, 2) ws.write( row, 17, round( line.price_subtotal + (line.price_subtotal * tax.amount), 2), style_content_right) sum3 += round( line.price_subtotal + (line.price_subtotal * tax.amount), 2) else: ws.write(row, 9, round(value, 2), style_content_right) ws.write(row, 10, round(line.price_subtotal, 2), style_content_right) sum4 += round(line.price_subtotal, 2) ws.write(row, 11, round(line.price_subtotal * tax.amount, 2), style_content_right) sum5 += round(line.price_subtotal * tax.amount, 2) ws.write( row, 12, round( line.price_subtotal + (line.price_subtotal * tax.amount), 2), style_content_right) sum6 += round( line.price_subtotal + (line.price_subtotal * tax.amount), 2) row += 1 count += 1 row += 2 ws.row(row).height = 400 ws.write(row, 0, "", style_header1) ws.write(row, 1, "", style_header1) ws.write(row, 2, "", style_header1) ws.write(row, 3, "", style_header1) ws.write(row, 4, "", style_header1) ws.write(row, 5, "", style_header1) ws.write(row, 6, "", style_header1) ws.write(row, 7, "", style_header1) ws.write(row, 8, "", style_header1) ws.write(row, 9, "", style_header1) ws.write(row, 10, sum4, style_header1) ws.write(row, 11, sum5, style_header1) ws.write(row, 12, sum6, style_header1) ws.write(row, 13, "", style_header1) ws.write(row, 14, "", style_header1) ws.write(row, 15, sum1, style_header1) ws.write(row, 16, sum2, style_header1) ws.write(row, 17, sum3, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'data': out, 'name': 'MARAnnexure2B.xls' }, context=context)
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 ot_register_report(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.pattern1_fore_colour = 0x1F #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 wb = Workbook() ws = wb.add_sheet('OT Register Report') 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 ws.row(0).height = 300 ws.row(1).height = 300 ws.row(2).height = 300 ws.col(20).width = 6000 ws.write_merge( 0, 0, 4, 10, 'COMPANY : ' + this.company_id.name + ' ' + this.company_id.street, style_header) ws.write_merge(1, 1, 4, 10, ('SALARY REPORT FOR THE MONTH OF : ', this.month.name), style_header) ws.write(2, 0, 'PCard', style_header) ws.col(0).width = 4000 ws.write(2, 1, 'Employee Name', style_header) ws.col(1).width = 8000 ws.write(2, 2, 'Department Name', style_header) ws.col(2).width = 5000 ws.write(2, 3, 'Designation Name', style_header) ws.col(3).width = 5000 ws.write(2, 4, 'Total Working Day', style_header) ws.col(4).width = 5000 ws.write(2, 5, 'CL + EL', style_header) ws.col(5).width = 3000 ws.write(2, 6, 'Compensatory Leave', style_header) ws.col(6).width = 5000 ws.write(2, 7, 'Sick Leave', style_header) ws.col(7).width = 4000 ws.write(2, 8, 'Absent', style_header) ws.col(8).width = 4000 ws.write(2, 9, 'Shift Hours Worked', style_header) ws.col(9).width = 5000 ws.write(2, 10, 'Overtime Hours', style_header) ws.col(10).width = 5000 ws.write(2, 11, 'Total Working Hours', style_header) ws.col(11).width = 5000 ws.write(2, 12, 'Avg Weekly Working Hour', style_header) ws.col(12).width = 6000 ws.write(2, 13, 'Avg Daily Working Hour', style_header) ws.col(13).width = 6000 ws.write(2, 14, 'Total Monthly Gross', style_header) ws.col(14).width = 5000 ws.write(2, 15, 'Gross Salary Paid', style_header) ws.col(15).width = 5000 ws.write(2, 16, 'OT Salary', style_header) ws.col(16).width = 5000 ws.write(2, 17, 'Total Gross Salary', style_header) ws.col(17).width = 5000 i = 3 total_days = 0.0 total_cl_el = 0.0 total_compensatory = 0.0 total_sick = 0.0 total_absent = 0.0 total_shift_hours = 0.0 total_ot_hours = 0.0 total_working_hours = 0.0 total_weekly_working_hour = 0.0 total_daily_working_hour = 0.0 total_monthly_gross = 0.0 total_gross_salary_paid = 0.0 total_ot_salary = 0.0 total_gross_salary = 0.0 emp_obj = self.pool.get('hr.employee') if this.employee_id and this.company_id: list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id), ('company_id', '=', company_id), ('type', '=', 'Employee'), ('active', '=', True)]) elif this.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('type', '=', 'Employee'), ('active', '=', True)]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('type', '=', 'Employee'), ('active', '=', False)]) list_ids = list_ids + list_ids1 elif this.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id), ('type', '=', 'Employee'), ('active', '=', True)]) if len(list_ids) == 1: query ="select spl.sinid,spl.employee_name,spl.department_name,spl.job_name,(spl.work_day+spl.factory_work),(spl.casual_leave+spl.earned_leave),spl.compensatory_leave,spl.sick_leave,(spl.month_days-(spl.work_day+spl.factory_work+spl.casual_leave+spl.earned_leave+spl.compensatory_leave+spl.sick_leave+spl.holiday_leave+spl.week_leave)),"\ "((spl.work_day+spl.factory_work) * 8),(spl.over_time + spl.sun_over_time),(((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time)),"\ "spl.month_days,"\ "round(((((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time))/(spl.work_day+spl.factory_work)),2) ,(spl.basic + spl.other_salary),(spl.days_amount+spl.other_salary_amount),spl.total_overtime_amount,"\ "(spl.days_amount+spl.other_salary_amount+spl.total_overtime_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)+"' order by spl.sinid" cr.execute(query) temp = cr.fetchall() else: query ="select spl.sinid,spl.employee_name,spl.department_name,spl.job_name,(spl.work_day+spl.factory_work),(spl.casual_leave+spl.earned_leave),spl.compensatory_leave,spl.sick_leave,(spl.month_days-(spl.work_day+spl.factory_work+spl.casual_leave+spl.earned_leave+spl.compensatory_leave+spl.sick_leave+spl.holiday_leave+spl.week_leave)),"\ "((spl.work_day+spl.factory_work) * 8),(spl.over_time + spl.sun_over_time),(((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time)),"\ "spl.month_days,"\ "round(((((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time))/(spl.work_day+spl.factory_work)),2) ,(spl.basic + spl.other_salary),(spl.days_amount+spl.other_salary_amount),spl.total_overtime_amount,"\ "(spl.days_amount+spl.other_salary_amount+spl.total_overtime_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)+"' 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: if val[4] >= 7: working_hour = round((val[11] / (val[12] - val[8])) * 7, 2) else: working_hour = 0.00 total_days += val[4] total_cl_el += val[5] total_compensatory += val[6] total_sick += val[7] total_absent += val[8] total_shift_hours += val[9] total_ot_hours += val[10] total_working_hours += val[11] total_daily_working_hour += val[13] total_monthly_gross += val[14] total_gross_salary_paid += val[15] total_ot_salary += val[16] total_gross_salary += val[17] total_weekly_working_hour += working_hour ws.write(i, 0, val[0], style_header3) ws.write(i, 1, val[1], style_header3) ws.write(i, 2, val[2], style_header3) ws.write(i, 3, val[3], style_header3) ws.write(i, 4, val[4], style_header3) ws.write(i, 5, val[5], style_header3) ws.write(i, 6, val[6], style_header3) ws.write(i, 7, val[7], style_header3) ws.write(i, 8, val[8], style_header3) ws.write(i, 9, val[9], style_header3) ws.write(i, 10, val[10], style_header3) ws.write(i, 11, val[11], style_header3) ws.write(i, 12, working_hour, style_header3) ws.write(i, 13, val[13], style_header3) ws.write(i, 14, val[14], style_header3) ws.write(i, 15, val[15], style_header3) ws.write(i, 16, val[16], style_header3) ws.write(i, 17, val[17], style_header3) i = i + 1 ws.write(i, 0, '', style_header3) ws.write(i, 1, '', style_header3) ws.write(i, 2, '', style_header3) ws.write(i, 3, 'TOTAL', style_header1) ws.write(i, 4, total_days, style_header1) ws.write(i, 5, total_cl_el, style_header1) ws.write(i, 6, total_compensatory, style_header1) ws.write(i, 7, total_sick, style_header1) ws.write(i, 8, total_absent, style_header1) ws.write(i, 9, total_shift_hours, style_header1) ws.write(i, 10, total_ot_hours, style_header1) ws.write(i, 11, total_working_hours, style_header1) ws.write(i, 12, total_weekly_working_hour, style_header1) ws.write(i, 13, total_daily_working_hour, style_header1) ws.write(i, 14, total_monthly_gross, style_header1) ws.write(i, 15, total_gross_salary_paid, style_header1) ws.write(i, 16, total_ot_salary, style_header1) ws.write(i, 17, total_gross_salary, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) ot_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'OT Register Report.xls' }, context=context) return ot_report
def model_run(self, model_context): from decimal import Decimal from xlwt import Font, Borders, XFStyle, Pattern, Workbook from camelot.view.import_utils import (RowData, ColumnMapping, ColumnSelectionAdmin) from camelot.view.utils import (local_date_format, local_datetime_format, local_time_format) from camelot.view import action_steps # # Select the columns that need to be exported # admin = model_context.admin all_fields = admin.get_all_fields_and_attributes() field_choices = [(f, entity_fa['name']) for f, entity_fa in all_fields.items()] row_data = RowData(1, [None] * len(all_fields)) mapping = ColumnMapping(len(all_fields), [row_data], admin, [field for field, _fa in admin.get_columns()]) mapping_admin = ColumnSelectionAdmin(len(all_fields), admin, field_choices) yield action_steps.ChangeObject(mapping, mapping_admin) columns = [] for i in range(len(all_fields)): field = getattr(mapping, 'column_%i_field' % i) if field != None: columns.append((field, all_fields[field])) # # setup worksheet # yield action_steps.UpdateProgress(text=_('Create worksheet')) workbook = Workbook() worksheet = workbook.add_sheet('Sheet1') # # keep a global cache of styles, since the number of styles that # can be used is limited. # styles = dict() freeze = lambda d: tuple(sorted(d.iteritems())) def get_style( font_specs=dict(), border_specs=dict(), pattern=None, num_format_str=None, ): style_key = (freeze(font_specs), freeze(border_specs), pattern, num_format_str) try: return styles[style_key] except KeyError: style = XFStyle() style.font = Font() for key, value in font_specs.items(): setattr(style.font, key, value) style.borders = Borders() for key, value in border_specs.items(): setattr(style.borders, key, value) if pattern: style.pattern = pattern if num_format_str: style.num_format_str = num_format_str styles[style_key] = style return style # # write style # title_style = get_style( dict(font_name=self.font_name, bold=True, height=240)) worksheet.write(0, 0, admin.get_verbose_name_plural(), title_style) # # create some patterns and formats # date_format = local_date_format() datetime_format = local_datetime_format() time_format = local_time_format() header_pattern = Pattern() header_pattern.pattern = Pattern.SOLID_PATTERN header_pattern.pattern_fore_colour = 0x16 # # write headers # field_names = [] for i, (name, field_attributes) in enumerate(columns): verbose_name = unicode(field_attributes.get('name', name)) field_names.append(name) font_specs = dict(font_name=self.font_name, bold=True, height=200) border_specs = dict(top=0x01) name = unicode(name) if i == 0: border_specs['left'] = 0x01 elif i == len(columns) - 1: border_specs['right'] = 0x01 header_style = get_style(font_specs, border_specs, header_pattern) worksheet.write(2, i, verbose_name, header_style) if len(name) < 8: worksheet.col(i).width = 8 * 375 else: worksheet.col(i).width = len(verbose_name) * 375 # # write data # offset = 3 for j, obj in enumerate(model_context.get_collection(yield_per=100)): dynamic_attributes = admin.get_dynamic_field_attributes( obj, field_names) row = offset + j if j % 100 == 0: yield action_steps.UpdateProgress( j, model_context.collection_count) for i, ((_name, attributes), delta_attributes) in enumerate( zip(columns, dynamic_attributes)): attributes.update(delta_attributes) value = attributes['getter'](obj) format_string = '0' if value != None: if isinstance(value, Decimal): value = float(str(value)) if isinstance(value, (unicode, str)): if attributes.get('translate_content', False) == True: value = ugettext(value) # handle fields of type code elif isinstance(value, list): value = u'.'.join(value) elif isinstance(value, float): precision = attributes.get('precision', 2) format_string = '0.' + '0' * precision elif isinstance(value, int): format_string = '0' elif isinstance(value, datetime.date): format_string = date_format elif isinstance(value, datetime.datetime): format_string = datetime_format elif isinstance(value, datetime.time): format_string = time_format else: value = unicode(value) else: # empty cells should be filled as well, to get the # borders right value = '' font_specs = dict(font_name=self.font_name, height=200) border_specs = dict() if i == 0: border_specs['left'] = 0x01 elif i == len(columns) - 1: border_specs['right'] = 0x01 if (row - offset + 1) == model_context.collection_count: border_specs['bottom'] = 0x01 style = get_style(font_specs, border_specs, None, format_string) worksheet.write(row, i, value, style) min_width = len(unicode(value)) * 300 worksheet.col(i).width = max(min_width, worksheet.col(i).width) yield action_steps.UpdateProgress(text=_('Saving file')) filename = action_steps.OpenFile.create_temporary_file('.xls') workbook.save(filename) yield action_steps.UpdateProgress(text=_('Opening file')) yield action_steps.OpenFile(filename)
def writePopulationInfo2File(self): workbook = xlwt.Workbook() # set header style headerStyle = xlwt.XFStyle() headerFont = xlwt.Font() headerFont.bold = True headerStyle.font = headerFont headerPattern = Pattern() headerPattern.pattern = Pattern.SOLID_PATTERN headerPattern.pattern_fore_colour = 50 # green headerStyle.pattern = headerPattern # write run result to excel file for thread in self.dreamland.populationThreadPlayers: sheet = workbook.add_sheet(thread.THREAD_NAME, True) headerText = ["name", "gender", "generation", "gene", "parents", "age", "birthTime", "deathTime", "deathCause", "populationThreat", "hungryLevel", "lifeStatus", "coordinateX", "coordinateY", "slotCode", "isBusy", "fightTimes", "fleeSuccessTimes", "fleeFailureTimes", "breedTimes", "moveHistory", "lifespan", "fightCapability", "attackPossibility", "defendPossibility", "totalBreedingTimes"] for i in range(0, len(headerText)): sheet.write(0, i, headerText[i], headerStyle) rowNum = 1 if thread.THREAD_TYPE == "Animal": for ind in (thread.group + thread.dead): sheet.write(rowNum, 0, ind.name) sheet.write(rowNum, 1, ind.gender) sheet.write(rowNum, 2, ind.generation) sheet.write(rowNum, 3, Recorder.list2String(ind.gene.geneDigits)) sheet.write(rowNum, 4, ind.parents) sheet.write(rowNum, 5, ind.age) sheet.write(rowNum, 6, ind.birthTime) sheet.write(rowNum, 7, ind.deathTime) sheet.write(rowNum, 8, ind.deathCause) sheet.write(rowNum, 9, ind.populationThreat) sheet.write(rowNum, 10, ind.hungryLevel) sheet.write(rowNum, 11, ind.lifeStatus) sheet.write(rowNum, 12, ind.coordinateX) sheet.write(rowNum, 13, ind.coordinateY) sheet.write(rowNum, 14, ind.slotCode) sheet.write(rowNum, 15, ind.isBusy) sheet.write(rowNum, 16, ind.fightTimes) sheet.write(rowNum, 17, ind.fleeSuccessTimes) sheet.write(rowNum, 18, ind.fleeFailureTimes) sheet.write(rowNum, 19, ind.breedTimes) sheet.write(rowNum, 20, Recorder.dict2String(ind.moveHistory)) sheet.write(rowNum, 21, ind.lifespan) sheet.write(rowNum, 22, ind.fightCapability) sheet.write(rowNum, 23, ind.attackPossibility) sheet.write(rowNum, 24, ind.defendPossibility) sheet.write(rowNum, 25, ind.totalBreedingTimes) rowNum += 1 elif thread.THREAD_TYPE == "Plant": for ind in (thread.group + thread.dead): sheet.write(rowNum, 0, ind.name) sheet.write(rowNum, 5, ind.age) sheet.write(rowNum, 6, ind.birthTime) sheet.write(rowNum, 7, ind.deathTime) sheet.write(rowNum, 8, ind.deathCause) sheet.write(rowNum, 9, ind.populationThreat) sheet.write(rowNum, 11, ind.lifeStatus) sheet.write(rowNum, 12, ind.coordinateX) sheet.write(rowNum, 13, ind.coordinateY) sheet.write(rowNum, 14, ind.slotCode) sheet.write(rowNum, 15, ind.isBusy) sheet.write(rowNum, 16, ind.fightTimes) sheet.write(rowNum, 21, ind.lifespan) sheet.write(rowNum, 22, ind.fightCapability) sheet.write(rowNum, 23, ind.attackPossibility) sheet.write(rowNum, 24, ind.defendPossibility) rowNum += 1 workbook.save("EvolutionSimulationIndividuals_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".xls")
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
from datetime import date from xlwt import Workbook, XFStyle, Borders, Pattern, Font fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THICK borders.right = Borders.THICK borders.top = Borders.THICK borders.bottom = Borders.THICK pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x0A style = XFStyle() style.num_format_str='YYYY-MM-DD' style.font = fnt style.borders = borders style.pattern = pattern book = Workbook() sheet = book.add_sheet('A Date') sheet.write(1,1,date(2009,3,18),style) book.save('date.xls')
def 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 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 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 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 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 crawl_one_simple(sheet, rule, idx, url, new_rows, brand_id): try: # 样式 style = XFStyle() pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map[ 'yellow'] # 设置单元格背景色为黄色 style.pattern = pattern # 首先检查数据库,提取出url中的id parts = url.split('?', 1) prefix = parts[0] found_one = "" if len(parts) > 1: parts = parts[1].split('&') for one in parts: if one.startswith('id='): found_one = one break if len(found_one) > 0: rows = list( CrawlProduct.objects.filter( Q(url__contains=found_one) | Q(tmall_id__iexact=found_one))) if len(rows) > 0: # 已有商品 row = rows[0] sheet.write(idx, 0, url) sheet.write(idx, 1, row.sku) sheet.write(idx, 2, row.title) sheet.write(idx, 3, row.subtitle) sheet.write(idx, 4, row.colors) return # 新商品 r = requests.get(url) sheet.write(idx, 0, url) if int(r.status_code) == 200: content = etree.HTML(r.text) # 标题 nodes = content.xpath(rule[0]) h1 = "" for one in nodes: one = one.strip() if len(one) > 0: h1 = h1 + one sheet.write(idx, 2, h1) # 副标题 nodes = content.xpath(rule[1]) h2 = "" for one in nodes: one = one.strip() if len(one) > 0: h2 = h2 + one sheet.write(idx, 3, h2) # 款号 nodes = content.xpath(rule[2]) sku = '' for one in nodes: one = one.strip() if len(one) > 0: for key in keywords: one = one.replace(key, "") sku = sku + one.strip() sheet.write(idx, 1, sku, style=style) # 颜色 # colors=content.xpath("//div[@class='tb-sku']//ul[contains(@data-property,'颜色')]/li/@title") colors = content.xpath(rule[3]) _color = "" if colors: _color = ",".join(colors) sheet.write(idx, 4, _color) # 尺码 # sizes new_rows.append( CrawlProduct(brand_id=brand_id, sku=sku, title=h1, subtitle=h2, colors=_color, url=url, status=CrawlStatus.ONLINE)) print("%s,%s,%s,%s,%s" % (url, sku, h1, h2, _color)) else: sts_code = int(r.status_code) if sts_code >= 300: new_rows.append( CrawlProduct(brand_id=brand_id, sku='', title='', subtitle='', colors='', url=url, status=CrawlStatus.TODO)) # except RequestException as e: except Exception as e: traceback.print_exc() # 当时爬取失败 new_rows.append( CrawlProduct(brand_id=brand_id, sku='', title='', subtitle='', colors='', url=url, status=CrawlStatus.TODO))
filename = 'TestData2.xls' #检测当前目录下是否有TestData2.xls文件,如果有则清除以前保存文件 if os.path.exists(filename): os.remove(filename) print(time.strftime("%Y-%m-%d", time.localtime(time.time()))) #打印读取到当前系统时间 wbk = Workbook(encoding='utf-8') sheet = wbk.add_sheet('new sheet 1', cell_overwrite_ok=True) #第二参数用于确认同一个cell单元是否可以重设值。 style = XFStyle() #赋值style为XFStyle(),初始化样式 Line_data = ('测试表') #创建一个Line_data列表,并将其值赋为测试表 for i in range(0x00, 0xff): # 设置单元格背景颜色 pattern = Pattern() # 创建一个模式 pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型 pattern.pattern_fore_colour = i # 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on... style.pattern = pattern # 将赋值好的模式参数导入Style sheet.write_merge(i, i, 0, 2, Line_data, style) #以合并单元格形式写入数据,即将数据写入以第1/2/3列合并德单元格内 for i in range(0x00, 0xff): # 设置单元格内字体样式 fnt = Font() # 创建一个文本格式,包括字体、字号和颜色样式特性 fnt.name = '微软雅黑' # 设置其字体为微软雅黑, 'SimSun' # 指定“宋体” fnt.colour_index = i # 设置其字体颜色 fnt.bold = True style.font = fnt #将赋值好的模式参数导入Style sheet.write_merge(i, i, 3, 5, Line_data, style) #以合并单元格形式写入数据,即将数据写入以第4/5/6列合并德单元格内