import xlwt # 关于样式 style_head = xlwt.XFStyle() # 初始化样式 red_style_head = xlwt.XFStyle() # 初始化样式 green_style_head = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 初始化字体相关 font.name = "微软雅黑" font.bold = True font.colour_index = 0 # TODO 必须是数字索引 font_s = xlwt.Font() # 初始化字体相关 font_s.name = "微软雅黑" font_s.bold = True font_s.colour_index = 1 # TODO 必须是数字索引 red_bg = xlwt.Pattern() # 初始背景图案 red_bg.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x1 red_bg.pattern_fore_colour = 2 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray green_bg = xlwt.Pattern() # 初始背景图案 green_bg.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x1 green_bg.pattern_fore_colour = 3 # 设置字体 style_head.font = font red_style_head.font = font_s green_style_head.font = font_s # 设置背景
# xsfile = r'./MoliAccurancyData/TestCase5Domain.xls' xsfile = r'E:\Moli\测试用例\python脚本\Moli精度脚本\Moli精度数据\US\USTestCase5Domain.xls' #北美测试集 # xsfile = r'E:\Moli\测试用例\python脚本\Moli精度脚本\Moli精度数据\IN\TestCase5Domain.xls' #印度测试集 # xsfile = r'E:\Moli\测试用例\python脚本\Moli精度脚本\测试集\线上测试全集\TestCase5Domain.xls' #线上数据全集 # xsfile = r'E:\Moli\测试用例\python脚本\Moli精度脚本\测试集\线上测试全集\TestCase5Domain全集(未筛选上线subintent).xls' #线上数据全集 workbook = xlrd.open_workbook(xsfile, formatting_info=True, encoding_override='utf-8') worksheet = workbook.sheet_by_name('TestCase') cbook = copy(workbook) csheet = cbook.get_sheet('TestCase') # 设置字体颜色为红色 style_red = xlwt.XFStyle() #初始化excel全局样式 font_red = xlwt.Font() #初始化font样式 font_red.colour_index = 2 #为font进行赋值 style_red.font = font_red #将赋值好的font 传递给excel全局样式 # 设置字体颜色为绿色 style_green = xlwt.XFStyle() font_green = xlwt.Font() font_green.colour_index = 50 style_green.font = font_green # 引入时间戳作为userid ticks = time.time() URL = r'http://10.110.147.198:9090/moliAgent/caap/centralcontrol' row = 2
def render_data(self, request): import xlwt from django.http import HttpResponse, HttpResponseRedirect from mysite.urls import tmpDir, tmp_url import os rn = request.REQUEST.get("reportname", '') title = rn and rn or self.title sheet_name = u"%s_%s" % ( 'Sheet', datetime.datetime.now().strftime("%Y%m%d%H%M%S")) file_path = tmpDir() + "/report_file" if not os.path.exists(file_path): os.makedirs(file_path) if self.head == "": #print self.data[0] heads = [k for k in self.data[0].keys()] else: heads = self.head lines = [xls_format(a) for a in self.cells] lines.insert(0, xls_format(self.head)) filecode = request.REQUEST.get('filecode', 'gb18030') #编码 wb = xlwt.Workbook(encoding=u"%s" % filecode) ws = wb.add_sheet(sheet_name) fnt = xlwt.Font() fnt.name = 'Arial' #fnt.colour_index = 4 #fnt.bold = True borders = xlwt.Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 align = xlwt.Alignment() align.horz = xlwt.Alignment.HORZ_CENTER align.vert = xlwt.Alignment.VERT_CENTER style = xlwt.XFStyle() style.font = fnt style.borders = borders style.alignment = align row_index = 0 for row in lines: col_index = 0 for col in row: ws.write(row_index, col_index, col, style) ws.col(col_index).width = 0x0d00 + 2000 col_index += 1 row_index += 1 filename = "%(d1)s_%(d2)s.xls" % { "d1": u"%s" % title, "d2": datetime.datetime.now().strftime("%Y%m%d%H%M%S") } # response = HttpResponse(mimetype=u"application/vnd.ms-excel; charset=utf-8") # response["Content-Disposition"] = u"attachment;" # filename=%(d1)s_%(d2)s.xls"%{ # "d1":title, # "d2":datetime.datetime.now().strftime("%Y%m%d%H%M%S") # } # wb.save(response) # return response wb.save(u"%s/%s" % (file_path, filename)) f = "/" + tmp_url() + "report_file/" + filename return HttpResponseRedirect(f.encode("utf-8"))
def write_medium_rebate_invoice_excel(orders): xls = xlwt.Workbook(encoding='utf-8') style = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 为样式创建字体 font.name = 'Times New Roman' # 选择字体 font.bold = True # 是否加粗 style.font = font # 为样式设置字体 # font.height = 500 #设置高度 al = xlwt.Alignment() al.horz = xlwt.Alignment.HORZ_CENTER # 设置水平居中 al.vert = xlwt.Alignment.VERT_CENTER # 设置垂直居中 al.wrap = xlwt.Alignment.WRAP_AT_RIGHT # 设置文字可以换行 style.alignment = al sheet = xls.add_sheet("Sheet") keys = [ u'代理/直客', u'客户名称', u'Campaign', u'合同号', u'合同总金额', u'媒体总金额', u'媒体返点总金额', u'已开发票总金额', u'未开发票总金额', u'申请通过个数', u'投放媒体', u'发票时间', u'公司名称', u'税号', u'公司地址', u'联系电话', u'银行账号', u'开户行', u'发票内容', u'发票金额', u'发票类型', u'发票号', u'回款时间' ] h = 0 for k in range(len(keys)): sheet.write(h, k, keys[k]) h += 1 for order in orders: invoice_passes = [ x for x in MediumRebateInvoice.query.filter_by(client_order=order) if x.invoice_status == INVOICE_STATUS_PASS ] sheet.write_merge(h, h + len(invoice_passes) - 1, 0, 0, order.agent.name, style) sheet.write_merge(h, h + len(invoice_passes) - 1, 1, 1, order.client.name, style) sheet.write_merge(h, h + len(invoice_passes) - 1, 2, 2, order.campaign, style) sheet.write_merge(h, h + len(invoice_passes) - 1, 3, 3, str(order.contract), style) sheet.write_merge(h, h + len(invoice_passes) - 1, 4, 4, str(order.money), style) sheet.write_merge(h, h + len(invoice_passes) - 1, 5, 5, str(order.mediums_money2), style) sheet.write_merge(h, h + len(invoice_passes) - 1, 6, 6, str(order.mediums_rebate_money), style) sheet.write_merge(h, h + len(invoice_passes) - 1, 7, 7, str(order.mediums_rebate_invoice_pass_sum), style) sheet.write_merge( h, h + len(invoice_passes) - 1, 8, 8, str(order.mediums_rebate_money - order.mediums_rebate_invoice_pass_sum), style) sheet.write_merge( h, h + len(invoice_passes) - 1, 9, 9, str(len(order.get_medium_rebate_invoice_by_status(0))), style) for invoice in invoice_passes: sheet.write(h, 10, invoice.medium.name) sheet.write(h, 11, invoice.create_time.strftime('%Y-%m-%d')) sheet.write(h, 12, invoice.company) sheet.write(h, 13, invoice.tax_id) sheet.write(h, 14, invoice.address) sheet.write(h, 15, invoice.phone) sheet.write(h, 16, invoice.bank_id) sheet.write(h, 17, invoice.bank) sheet.write(h, 18, invoice.detail) sheet.write(h, 19, invoice.money) sheet.write(h, 20, INVOICE_TYPE_CN[invoice.invoice_type]) sheet.write(h, 21, invoice.invoice_num) sheet.write(h, 22, str(invoice.back_time_cn)) h += 1 return xls
def get_result(self): data = self.read()[0] start_date = data.get('date_start', False) end_date = data.get('date_end', False) if start_date and end_date and end_date < start_date: raise Warning(_("End date should be greater than start date!")) res_user = self.env["res.users"].browse(self._uid) # Create Inventory Export report in Excel file. workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet 1') font = xlwt.Font() font.bold = True header = xlwt.easyxf('font: bold 1, height 280') # start_date = datetime.strptime(str(context.get("date_from")), DEFAULT_SERVER_DATE_FORMAT) # start_date_formate = start_date.strftime('%d/%m/%Y') # end_date = datetime.strptime(str(context.get("date_to")), DEFAULT_SERVER_DATE_FORMAT) # end_date_formate = end_date.strftime('%d/%m/%Y') # start_date_to_end_date = tools.ustr(start_date_formate) + ' To ' + tools.ustr(end_date_formate) style = xlwt.easyxf('align: wrap yes') worksheet.row(0).height = 500 worksheet.row(1).height = 500 for x in range(0, 41): worksheet.col(x).width = 6000 borders = xlwt.Borders() borders.top = xlwt.Borders.MEDIUM borders.bottom = xlwt.Borders.MEDIUM border_style = xlwt.XFStyle() # Create Style border_style.borders = borders border_style1 = xlwt.easyxf('font: bold 1') border_style1.borders = borders style = xlwt.easyxf('align: wrap yes', style) ids_location = [] ids_categ = [] ids_product = [] where_end_date_awal = " sm.date is null " where_start_date = " 1=1 " if start_date: where_start_date = " sm.date + interval '7 hour' >= '%s 00:00:00' " % start_date where_end_date_awal = " sm.date + interval '7 hour' < '%s 00:00:00' " % start_date where_end_date = " 1=1 " if end_date: where_end_date = " sm.date + interval '7 hour' <= '%s 23:59:59'" % end_date where_end_date_opn = " 1=1 " if end_date: where_end_date_opn = " si.date::text LIKE '%s%s'" % (end_date, "%") where_location = " 1=1 " if ids_location: where_location = """(sm.location_id in %s or sm.location_dest_id in %s)""" % (str( tuple(ids_location)).replace( ',)', ')'), str(tuple(ids_location)).replace(',)', ')')) where_location_opn = " sil.location_id = 12 " where_categ = " 1=1 " if ids_categ: where_categ = "pt.categ_id in %s" % str(tuple(ids_categ)).replace( ',)', ')') where_product = " 1=1 " if ids_product: where_product = "pp.id in %s" % str(tuple(ids_product)).replace( ',)', ')') query = """ select pp.id as prod_id, pt.default_code as prod_code, pt.name as prod_name, pu.name as uom, sum(coalesce(awal.qty,0)) as saldo_awal, sum(coalesce(saldo_masuk.qty,0)) as masuk, sum(coalesce(saldo_keluar.qty,0)) as keluar, sum(coalesce(prod_adj.qty,0)) as adjusment, sum(coalesce(opn.qty,0)) as opname from product_product pp left join ( select sm.product_id, sum(case when sl.name = 'Pre-Production' and dl.name != 'Pre-Production' then sm.product_qty*-1 when sl.name != 'Pre-Production' and dl.name = 'Pre-Production' then sm.product_qty else 0 end) as qty from stock_move sm left join stock_location sl on sl.id = sm.location_id left join stock_location dl on dl.id = sm.location_dest_id left join product_product pp on pp.id=sm.product_id left join product_template pt on pt.id=pp.product_tmpl_id left join uom_uom pu on pu.id= sm.product_uom where """ + where_end_date_awal + """ and """ + where_location + """ and sm.state = 'done' group by sm.product_id ) awal on awal.product_id=pp.id left join ( select sm.product_id, sum(sm.product_qty) as qty from stock_move sm left join stock_location sl on sl.id = sm.location_id left join stock_location dl on dl.id = sm.location_dest_id left join product_product pp on pp.id=sm.product_id left join product_template pt on pt.id=pp.product_tmpl_id left join uom_uom pu on pu.id= sm.product_uom where sl.name != 'Pre-Production' and dl.name = 'Pre-Production' and sm.state = 'done' and sm.inventory_id is null and """ + where_start_date + """ and """ + where_end_date + """ and """ + where_location + """ group by sm.product_id ) saldo_masuk on saldo_masuk.product_id=pp.id left join ( select sm.product_id, sum(sm.product_qty) as qty from stock_move sm left join stock_location sl on sl.id = sm.location_id left join stock_location dl on dl.id = sm.location_dest_id left join product_product pp on pp.id=sm.product_id left join product_template pt on pt.id=pp.product_tmpl_id left join uom_uom pu on pu.id= sm.product_uom where sl.name = 'Pre-Production' and dl.name != 'Pre-Production' and sm.state = 'done' and sm.inventory_id is null and """ + where_start_date + """ and """ + where_end_date + """ and """ + where_location + """ group by sm.product_id ) saldo_keluar on saldo_keluar.product_id=pp.id left join ( select sm.product_id, sum(case when sl.name = 'Pre-Production' and dl.name != 'Pre-Production' and inventory_id is not null then sm.product_qty*-1 when sl.name != 'Pre-Production' and dl.name = 'Pre-Production' and inventory_id is not null then sm.product_qty else 0 end ) as qty from stock_move sm left join stock_location sl on sl.id = sm.location_id left join stock_location dl on dl.id = sm.location_dest_id left join product_product pp on pp.id=sm.product_id left join product_template pt on pt.id=pp.product_tmpl_id left join uom_uom pu on pu.id= sm.product_uom where """ + where_start_date + """ and """ + where_end_date + """ and """ + where_location + """ and sm.state = 'done' group by sm.product_id ) prod_adj on prod_adj.product_id=pp.id left join ( select sil.product_id, sum(sil.product_qty) as qty from stock_inventory_line sil INNER JOIN stock_inventory si on sil.inventory_id = si.id INNER JOIN stock_location sl on sl.id = sil.location_id left join product_product pp on pp.id=sil.product_id left join product_template pt on pt.id=pp.product_tmpl_id left join uom_uom pu on pu.id= sil.product_uom_id where """ + where_end_date_opn + """ and """ + where_location_opn + """ and si.state = 'done' group by sil.product_id ) opn on opn.product_id=pp.id left join product_template pt on pt.id=pp.product_tmpl_id left join uom_uom pu on pt.uom_id=pu.id left join product_category pc on pt.categ_id=pc.id where """ + where_categ + """ and """ + where_product + """ and pc.report_type = 'bahan_baku' and (coalesce(awal.qty,0) != 0 or coalesce(saldo_masuk.qty,0) != 0 or coalesce(saldo_keluar.qty,0) != 0 or coalesce(opn.qty,0) != 0) group by pt.default_code, pt.name, pp.id , pu.name order by pt.name asc """ list_data = [] company = self.env.user.company_id.name start_date_format = start_date.strftime('%d/%m/%Y') end_date_format = end_date.strftime('%d/%m/%Y') self._cr.execute(query) vals = self._cr.fetchall() no = 1 for val in vals: saldo_akhir = float(val[4]) + float(val[5]) - \ float(val[6]) + float(val[7]) list_data.append({ 'kode_barang': val[1], 'nama_barang': val[2], 'satuan': val[3], 'jumlah': saldo_akhir, 'keterangan': "" }) no += 1 hasil = list_data return hasil
worksheet.col(statu).width = 256 * 12 worksheet.col(backup).width = 256 * 22 #创建一个样式---------------------------- stylefirstRow = xlwt.XFStyle() stylefirstRow.borders = borders pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['gold'] #设置单元格背景色为黄色 stylefirstRow.pattern = pattern al = xlwt.Alignment() al.horz = 0x02 # 设置水平居中 al.vert = 0x01 # 设置垂直居中 al.wrap = 1 #自动换行 stylefirstRow.alignment = al font = xlwt.Font() # 为样式创建字体 #font.name = 'Times New Roman' font.name = u'微软雅黑' #font.bold = True # 黑体 #font.underline = True # 下划线 #font.italic = True # 斜体字 font.height = 0x00C8 * 1.6 # C8 in Hex (in decimal) = 10 points in height. #font.colour_index=48#蓝色 #font.colour_index=2#红色 #font.colour_index=59#灰色 #font.colour_index=44#浅蓝色 stylefirstRow.font = font # 设定样式 #第二行样式---------------------------- stylesecondRow = xlwt.XFStyle() stylesecondRow.borders = borders font2 = xlwt.Font() # 为样式创建字体
result_file_name = result_file_path + '\\result.xls' if not os.path.exists(testfilename): #check if trace file present print '\n\n\nINPUT LOG FILE NOT FOUND \n\n' sys.exit() if not os.path.exists(result_file_path): os.mkdir(result_file_path) file = open(result_file_name, 'w') #open the test file file.close() file = open(testfilename, 'r') #open the test file contents_of_trace_file = file.read() #store its contents file.close() #close the file style = xlwt.easyxf("", "###,###") #1000 seperator enable font = xlwt.Font() #Create Style for headings font.bold = True style_for_heading = xlwt.XFStyle() style_for_heading.font = font pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 7 style_for_heading.pattern = pattern borders = xlwt.Borders() borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN style_for_heading.borders = borders starting_indicator = 'start_xti_profiler_' #define starting_indicator
def check_report(self): cr, uid, context = self.env.args context = dict(context) fl = StringIO() workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet 1') font = xlwt.Font() font.bold = True header = xlwt.easyxf('font: bold 1, height 180;') header1 = xlwt.easyxf( 'font: bold on, height 200;borders : top thin, bottom thin, left thin, right thin;align: wrap off , vert centre, horiz center;' ) header2 = xlwt.easyxf( 'font: bold on, height 180;borders : top thin, bottom thin, left thin, right thin;align: wrap on , vert centre, horiz center;' ) align_left = xlwt.easyxf( 'font: height 200, bold on, name Arial, colour_index periwinkle;borders : top thin, bottom thin, left thin, right thin; align: wrap on,vert centre, horiz left;' ) align_border = xlwt.easyxf( 'font: height 180, name Arial, colour_index black;borders : top thin, bottom thin, left thin, right thin; align: wrap on,vert centre, horiz left;' ) wiz_rec = self.browse(self._ids[0]) bed_obj = self.env['beds.beds'] visa_obj = self.env['visa.quota'] room_obj = self.env['room.room'] list_new = [] country_dict = {} for country in wiz_rec.nationality_ids: acco_dict = {} country_id = country.id visa_ids = visa_obj.search([('nationality_id', '=', country_id)]) for visa in visa_ids: acco_name = visa.accommodation_id.name acc_dict = {} company_dict = {} visa_total = visa.number_of_quota visa_avail = visa.quota_available visa_occupied = visa_total - visa_avail country_comp_total = 0 room_ids = room_obj.search([('accommodation_id', '=', visa.accommodation_id.id)]) for room in room_ids: room_id = room occupied_beds = bed_obj.search([ ('employee_id.emp_country_id', '=', visa.nationality_id.id), ('employee_id', '!=', False), ('room_id', '=', room_id.id) ]) occu_beds = [] counter = 0 for bed in occupied_beds: bed_dict = {} counter += 1 bed_dict['sr_no'] = counter or '' bed_dict[ 'id_no'] = bed.employee_id.identification_id or '' bed_dict['bed_no'] = bed.name or '' bed_dict['name'] = bed.employee_id.name or '' bed_dict['wp_number'] = bed.employee_id.wp_number or '' bed_dict['com'] = bed.employee_id.company_id.name or '' bed_dict['dialect'] = bed.employee_id.dialect or '' bed_dict['country'] = False bed_dict['location'] = False bed_dict['room'] = False bed_dict['bold'] = False occu_beds.append(bed_dict) if occu_beds: company_dict[room.name] = occu_beds if acco_name in acco_dict.keys(): acco_dict[acco_name]['room_list'].append(company_dict) else: acco_dict[acco_name] = {'room_list': [company_dict]} country_dict[country.name] = acco_dict final_dict = {} final_list = [] for con_key, con_val in country_dict.iteritems(): country = False con_dict = { 'sr_no': con_key, 'bed_no': '', 'id_no': '', 'name': '', 'wp_number': '', 'com': '', 'dialect': '', 'country': True, 'location': False, 'room': False, 'bold': True } for loc_key, loc_val in con_val.iteritems(): location = False room_bold_dict = { 'sr_no': loc_key, 'bed_no': '', 'id_no': '', 'name': '', 'wp_number': '', 'com': '', 'dialect': '', 'country': False, 'location': True, 'room': False, 'bold': True } for room in loc_val.get('room_list', [{}]): for room_key, room_value in room.iteritems(): room_dict = { 'sr_no': room_key, 'bed_no': '', 'id_no': '', 'name': '', 'wp_number': '', 'com': '', 'dialect': '', 'country': False, 'location': False, 'room': True, 'bold': True } if not country: final_list.append(con_dict) country = True if not location: final_list.append(room_bold_dict) location = True final_list.append(room_dict) final_list += room_value row = 1 for lst in final_list: if lst['country'] == True: worksheet.write_merge( row, row + 1, 0, 3, 'LIST OF "' + lst['sr_no'] + '" WORKERS ACCOMMODATION', align_left) row += 2 if lst['location'] == True: worksheet.write(row, 0, 'LOCATION :', header1) worksheet.write(row, 1, lst['sr_no'], align_left) row += 1 if lst['room'] == True: worksheet.write(row, 0, 'ROOM :', header1) worksheet.write(row, 1, lst['sr_no'], align_left) row += 2 worksheet.write_merge(row, row + 1, 0, 0, 'NO', header2) worksheet.write_merge(row, row + 1, 1, 1, 'BED NO', header2) worksheet.write_merge(row, row + 1, 2, 2, 'CODE NO', header2) worksheet.write_merge(row, row + 1, 3, 3, 'NAME', header2) worksheet.write_merge(row, row + 1, 4, 4, 'W/P NUMBER', header2) worksheet.write_merge(row, row + 1, 5, 5, 'COM', header2) worksheet.write_merge(row, row + 1, 6, 6, 'DIALECT', header2) worksheet.write_merge(row, row + 1, 7, 7, 'SITE', header2) worksheet.write_merge(row, row + 1, 8, 8, 'Date of Application', header2) worksheet.write_merge(row, row + 1, 9, 9, 'REMARKS', header2) row += 2 if lst['bold'] == False: worksheet.write(row, 0, lst['sr_no'], align_border) worksheet.write(row, 1, lst['bed_no'], align_border) worksheet.write(row, 2, lst['id_no'], align_border) worksheet.write(row, 3, lst['name'], align_border) worksheet.write(row, 4, lst['wp_number'], align_border) worksheet.write(row, 5, lst['com'], align_border) worksheet.write(row, 6, lst['dialect'], align_border) worksheet.write(row, 7, 'static', align_border) worksheet.write(row, 8, 'static', align_border) worksheet.write(row, 9, 'static', align_border) row += 2 workbook.save(fl) fl.seek(0) buf = base64.encodestring(fl.read()) ctx = dict(context) ctx.update({'file': buf}) return { 'type': 'ir.actions.act_window', 'view_type': 'form', 'view_mode': 'form', 'res_model': 'acco.nationality.report', 'target': 'new', 'context': ctx, }
def __init__(self): """ init """ # init excel self.workbook = xlwt.Workbook(encoding = 'utf-8') self.worksheet_p4 = self.workbook.add_sheet("p4(anakin2 vs tensorRT)") # set excel style alignment = xlwt.Alignment() # Create Alignment alignment.horz = xlwt.Alignment.HORZ_CENTER alignment.vert = xlwt.Alignment.VERT_CENTER alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT # word is black self.style = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style.font = font self.style.alignment = alignment # word is red self.style_red = xlwt.XFStyle() font_red = xlwt.Font() font_red.name = 'PT Mono' font_red.height = 240 font_red.colour_index = 2 self.style_red.font = font_red self.style_red.alignment = alignment # background is color col = self.worksheet_p4.col(0) col.width = 256 * 25 self.style_background0 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background0.font = font self.style_background0.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 22 self.style_background0.pattern = pattern self.worksheet_p4.write(0, 0, "Net_Name", self.style_background0) col = self.worksheet_p4.col(1) col.width = 256 * 15 self.style_background1 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background1.font = font self.style_background1.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 26 self.style_background1.pattern = pattern self.worksheet_p4.write(0, 1, "Batch_Size", self.style_background1) col = self.worksheet_p4.col(2) col.width = 256 * 15 self.style_background2 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background2.font = font self.style_background2.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 44 self.style_background2.pattern = pattern self.worksheet_p4.write(0, 2, "Library", self.style_background2) col = self.worksheet_p4.col(3) col.width = 256 * 20 self.style_background3 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background3.font = font self.style_background3.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 42 self.style_background3.pattern = pattern self.worksheet_p4.write(0, 3, "RT latency (ms)", self.style_background3) col = self.worksheet_p4.col(4) col.width = 256 * 20 self.style_background4 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background4.font = font self.style_background4.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 47 self.style_background4.pattern = pattern self.worksheet_p4.write(0, 4, "RT Memory (MB)", self.style_background4) col = self.worksheet_p4.col(5) col.width = 256 * 15 self.style_background5 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background5.font = font self.style_background5.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 44 self.style_background5.pattern = pattern self.worksheet_p4.write(0, 5, "Library", self.style_background5) col = self.worksheet_p4.col(6) col.width = 256 * 20 self.style_background6 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background6.font = font self.style_background6.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 42 self.style_background6.pattern = pattern self.worksheet_p4.write(0, 6, "anakin2 Latency (ms)", self.style_background6) col = self.worksheet_p4.col(7) col.width = 256 * 20 self.style_background7 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background7.font = font self.style_background7.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 47 self.style_background7.pattern = pattern self.worksheet_p4.write(0, 7, "anakin2 Memory (MB)", self.style_background7) col = self.worksheet_p4.col(8) col.width = 256 * 20 self.style_background8 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background8.font = font self.style_background8.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 53 self.style_background8.pattern = pattern self.worksheet_p4.write(0, 8, "anakin/tensorRT\nlatency", self.style_background8) col = self.worksheet_p4.col(9) col.width = 256 * 20 self.style_background9 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background9.font = font self.style_background9.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 53 self.style_background9.pattern = pattern self.worksheet_p4.write(0, 9, "anakin/tensorrt\nmemory", self.style_background9) col = self.worksheet_p4.col(10) col.width = 256 * 20 self.style_background10 = xlwt.XFStyle() # Create Style font = xlwt.Font() font.name = 'PT Mono' font.height = 240 font.colour_index = 0 self.style_background10.font = font self.style_background10.alignment = alignment pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 53 self.style_background10.pattern = pattern self.worksheet_p4.write(0, 10, "accuracy rate\n(%)", self.style_background10)
def print_xls(self): report_obj = self.env[ 'report.flexiretail_com_advance.report_financial'] data = {} data['form'] = \ self.read( ['account_report_id', 'date_from_cmp', 'date_to_cmp', 'journal_ids', 'filter_cmp', 'target_move'])[0] for field in ['account_report_id']: if isinstance(data['form'][field], tuple): data['form'][field] = data['form'][field][0] comparison_context = self._build_comparison_context(data) # res['data']['form']['comparison_context'] = comparison_context if comparison_context.get('date_from'): comparison_context['date_from'] = str(self.date_from_cmp) if comparison_context.get('date_to'): comparison_context['date_to'] = str(self.date_to_cmp) datas = {} datas['account_report_id'] = [ self.account_report_id.id, 'Profit and Loss' ] datas['company_id'] = [self.company_id.id, self.company_id.name] # datas['comparison_context'] = { # 'date_from':str(self.date_from), # 'date_to':str(self.date_to), # 'journal_ids':False, # 'state':self.target_move, # 'strict_range': comparison_context.get('strict_range') if comparison_context.get('strict_range') else False, # } datas['comparison_context'] = comparison_context datas['date_from'] = str(self.date_from) if self.date_from else False datas['date_from_cmp'] = str( self.date_from_cmp) if self.date_from_cmp else False datas['date_to'] = str(self.date_to) if self.date_to else False datas['date_to_cmp'] = str( self.date_to_cmp) if self.date_to_cmp else False datas['debit_credit'] = str( self.debit_credit) if self.debit_credit else False datas['enable_filter'] = self.enable_filter datas['filter_cmp'] = self.filter_cmp datas['id'] = self.id datas['journal_ids'] = False datas['label_filter'] = self.label_filter datas['target_move'] = self.target_move datas['used_context'] = { 'company_id': 1, 'date_from': str(self.date_from) if self.date_from else False, 'date_to': str(self.date_to) if self.date_to else False, 'journal_ids': False, 'lang': self.env.user.lang, 'state': self.target_move, 'strict_range': comparison_context.get('strict_range') if comparison_context.get('strict_range') else False, } lines = report_obj.get_account_lines(datas) styleP = xlwt.XFStyle() stylePC = xlwt.XFStyle() styleBorder = xlwt.XFStyle() fontbold = xlwt.XFStyle() normal_style = xlwt.XFStyle() alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_CENTER alignment_lft = xlwt.Alignment() alignment_lft.horz = xlwt.Alignment.HORZ_LEFT alignment_rgt = xlwt.Alignment() alignment_rgt.horz = xlwt.Alignment.HORZ_RIGHT font = xlwt.Font() fontP = xlwt.Font() fontN = xlwt.Font() borders = xlwt.Borders() borders.bottom = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN borders.left = xlwt.Borders.THIN font.bold = False fontP.bold = True styleP.font = font # stylePC.font = fontP stylePC.alignment = alignment_lft fontbold.alignment = alignment_rgt styleBorder.font = fontP stylePC.font = fontP # fontbold.font = fontP styleBorder.alignment = alignment styleBorder.borders = borders workbook = xlwt.Workbook(encoding="utf-8") worksheet = workbook.add_sheet("Aged Payable") worksheet.col(0).width = 5600 worksheet.col(1).width = 5600 worksheet.col(2).width = 5600 worksheet.col(3).width = 5600 worksheet.write_merge(0, 2, 0, 3, self.company_id.name + '\n' + self.company_id.email + '\n' + self.company_id.phone, style=styleBorder) worksheet.write_merge( 3, 3, 0, 3, 'Balance Sheet' if self._context.get('from_balance_sheet') else 'Profit and Loss', style=styleBorder) worksheet.write_merge( 4, 4, 0, 3, 'Target Moves : ' + 'All Entries' if self.target_move == 'all' else 'Target Moves : All Posted Entries') if self.date_from and self.date_to: worksheet.write_merge( 5, 5, 0, 3, 'Date From : ' + str(self.date_from) + ' ' + 'Date To : ' + str(self.date_to)) i = 8 if self.debit_credit: fontbold.font = fontP worksheet.write_merge(7, 7, 0, 0, 'Name', stylePC) worksheet.write_merge(7, 7, 1, 1, 'Debit', stylePC) worksheet.write_merge(7, 7, 2, 2, 'Credit', stylePC) worksheet.write_merge(7, 7, 3, 3, 'Balance', fontbold) for each in lines: fontbold.font = fontN normal_style.font = fontN if not each.get('level') > 3: fontbold.font = fontP normal_style.font = fontP if each.get('level') != 0: worksheet.write_merge(i, i, 0, 0, each.get('name'), normal_style) worksheet.write_merge( i, i, 1, 1, formatLang( self.env, float(each.get('debit')), currency_obj=self.env.user.company_id.currency_id), fontbold) worksheet.write_merge( i, i, 2, 2, formatLang( self.env, float(each.get('credit')), currency_obj=self.env.user.company_id.currency_id), fontbold) worksheet.write_merge( i, i, 3, 3, formatLang( self.env, float(each.get('balance')), currency_obj=self.env.user.company_id.currency_id), fontbold) i = i + 1 if not self.enable_filter and not self.debit_credit: fontbold.font = fontP worksheet.write_merge(7, 7, 0, 0, 'Name', stylePC) worksheet.write_merge(7, 7, 1, 1, 'Balance', fontbold) for each in lines: fontbold.font = fontN normal_style.font = fontN if not each.get('level') > 3: fontbold.font = fontP normal_style.font = fontP if each.get('level') != 0: worksheet.write_merge(i, i, 0, 0, each.get('name'), normal_style) worksheet.write_merge( i, i, 1, 1, formatLang( self.env, float(each.get('balance')), currency_obj=self.env.user.company_id.currency_id), fontbold) i = i + 1 if self.enable_filter and not self.debit_credit: fontbold.font = fontP worksheet.write_merge(7, 7, 0, 0, 'Name', stylePC) worksheet.write_merge(7, 7, 1, 1, 'Debit', fontbold) worksheet.write_merge( 7, 7, 2, 2, str(self.label_filter) if self.label_filter else '', fontbold) for each in lines: if each.get('level') != 0: fontbold.font = fontN normal_style.font = fontN if not each.get('level') > 3: fontbold.font = fontP normal_style.font = fontP worksheet.write_merge(i, i, 0, 0, each.get('name'), normal_style) worksheet.write_merge( i, i, 1, 1, formatLang( self.env, float(each.get('balance')), currency_obj=self.env.user.company_id.currency_id), fontbold) worksheet.write_merge( i, i, 2, 2, formatLang( self.env, float(each.get('balance_cmp')), currency_obj=self.env.user.company_id.currency_id), fontbold) i = i + 1 file_data = io.BytesIO() workbook.save(file_data) self.write({ 'state': 'get', 'data': base64.encodestring(file_data.getvalue()), 'name': 'balance_sheet.xls' if self._context.get('from_balance_sheet') else 'profit_loss.xls' }) return { 'name': 'Profit & Loss', 'type': 'ir.actions.act_window', 'res_model': 'accounting.report.profit.loss', 'view_mode': 'form', 'view_type': 'form', 'res_id': self.id, 'target': 'new' }
def __init__(self,file_name ='',sheet='AutoTestResult',debug_flag='1'): self.wbk=xlwt.Workbook(encoding = 'gb18030') self.sheet = self.wbk.add_sheet(sheet,cell_overwrite_ok=True) self.filename = self.save_result(file_name) self.tmp_modulename = '' self.start_module_row = 13 self.dstRst_total_mod ={'DoneNum':'0','OKNum':'0','NGNum':'0'} self.mod_row_offset = 0 try: font1 = xlwt.Font() font1.name = 'Times New Roman' font1.bold = True borders1 = xlwt.Borders() borders1.left = xlwt.Borders.DASHED borders1.right = xlwt.Borders.DASHED borders1.bottom = xlwt.Borders.DASHED borders1.top = xlwt.Borders.DASHED self.style1 = xlwt.XFStyle() self.style1.font = font1 self.style1.borders = borders1 self.style2 = xlwt.easyxf('pattern: pattern solid, fore_colour sea_green;') font2 = xlwt.Font() font2.name = 'SimSun' font2.height = 0x00E6 font2.bold = True self.style2.font = font2 self.style2.borders = borders1 self.style3 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;') font3 = xlwt.Font() font3.bold = True font3.name = 'SimSun' font3.height = 0x00C8 self.style3.font = font3 self.style3.borders = borders1 self.style4 = xlwt.easyxf('pattern: pattern solid, fore_colour ice_blue;') font4 = xlwt.Font() font4.name = 'SimSun' font4.height = 0x00C8 font4.bold = True self.style4.font = font4 self.style4.borders = borders1 self.style5 = xlwt.easyxf('pattern: pattern solid, fore_colour light_blue;') self.style5.font = font4 self.style5.borders = borders1 self.style6 = xlwt.XFStyle() font5 = xlwt.Font() font5.name = 'Verdana' font5.bold = True font5.underline = xlwt.Font.UNDERLINE_DOUBLE font5.colour_index = 4 self.style6.font =font5 self.style6.borders = borders1 self.style7 = xlwt.XFStyle() font6 = xlwt.Font() font6.name = 'Verdana' font6.bold = True font6.colour_index = 6 self.style7.font =font6 self.style7.borders = borders1 self.style8 = xlwt.XFStyle() font7 = xlwt.Font() font7.name = 'Verdana' font7.bold = True font7.colour_index = 8 self.style8.font =font7 self.style8.borders = borders1 self.style9 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;') borders2 = xlwt.Borders() borders2.left = xlwt.Borders.DASHED borders2.right = xlwt.Borders.DASHED borders2.bottom = xlwt.Borders.DASHED borders2.top = xlwt.Borders.DASHED borders2.bottom_colour = 0x40 borders2.top_colour = 0x40 self.style9.font = font1 self.style9.borders = borders2 self.style10 = xlwt.easyxf('pattern: pattern solid, fore_colour sea_green;') font8 = xlwt.Font() font8.height = 0x00E6 font8.bold = True self.style10.font = font8 self.style10.borders = borders2 self.style11 = xlwt.XFStyle() font9 = xlwt.Font() font9.name = 'Verdana' font9.bold = True font9.colour_index = 0x28 self.style11.font =font9 self.style11.borders = borders1 except Exception,e: log_print(e) pass
# for reading excel docs import xlrd book = xlrd.open_workbook(excel_file_name) #print "The number of worksheets is", book.nsheets #print "Worksheet name(s):", book.sheet_names() sh = book.sheet_by_index(0) #print sh.name, sh.nrows, sh.ncols global current_check current_check = "blank" global section section = "blank" #for writing to excell docs import xlwt import time font0 = xlwt.Font() font0.name = 'Times New Roman' font0.colour_index = 3 # 2 is red, 3 is green font0.bold = True style0 = xlwt.XFStyle() style0.font = font0 wb = xlwt.Workbook() user_answer = "BLANK" from xlutils.copy import copy from xlrd import * w = copy(open_workbook(excel_file_name)) global first_check first_check = 17 global check_number
def data_excel_write(listData, queue, file_path): result_data = [] for item in listData: temp = list(filter(lambda x: x.orderno == item.orderno, result_data)) if len(temp) == 0: result_data.append(item) pass # 创建Excel工作薄,编码方式为:utf-8 myWorkbook = xlwt.Workbook(encoding='utf-8') # 添加Excel工作表 mySheet = myWorkbook.add_sheet('对比结果', cell_overwrite_ok=True) # 使用样式 style = xlwt.XFStyle() # 创建一个样式 # 为样式创建字体 font = xlwt.Font() font.name = '宋体' font.bold = True # 是否加粗,默认值为false不加粗 style.font = font # 设置对齐方式,文本的对齐方式 alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平居中 alignment.vert = xlwt.Alignment.VERT_CENTER # 垂直居中 style.alignment = alignment mySheet.write(0, 0, "订单号", style) mySheet.write(0, 1, "购买数量-平台", style) mySheet.write(0, 2, "购买数量-OTA", style) mySheet.write(0, 3, "核销数量-平台", style) mySheet.write(0, 4, "核销数量-OTA", style) mySheet.write(0, 5, "退款数量-平台", style) mySheet.write(0, 6, "退款数量-平台", style) mySheet.write(0, 7, "对比时间-线上", style) mySheet.write(0, 8, "对比时间-OTA", style) mySheet.write(0, 9, "异常原因", style) if len(listData) == 0: mySheet.write(1, 0, '没毛病!') queue.put('没有异常单,完全没毛病!') pass else: rowIndex = 1 for item in listData: mySheet.write(rowIndex, 0, item.orderno) mySheet.write(rowIndex, 1, item.buycnt) mySheet.write(rowIndex, 2, item.buycntota) mySheet.write(rowIndex, 3, item.checkcnt) mySheet.write(rowIndex, 4, item.checkcntota) mySheet.write(rowIndex, 5, item.refundcnt) mySheet.write(rowIndex, 6, item.refundcntota) mySheet.write(rowIndex, 7, item.comtime) mySheet.write(rowIndex, 8, item.comtimeota) mySheet.write(rowIndex, 9, item.reason) rowIndex += 1 queue.put("异常单: 订单号: %s \t 异常原因:%s" % (item.orderno, item.reason)) pass file_dir = file_path[0:file_path.rfind('/', 0)] file_name = file_path[file_path.rfind('/', 0):] fullPath = file_dir + '/' + ('对比结果%s%s' % ( int(time.time()), file_name[1:], )) myWorkbook.save(fullPath) queue.put('结果文件地址:' + fullPath) pass
def SankeyExport( self, Year, Path, Element ): # Export data for given year in excel format for the D3.js Circular Sankey method """ Exports MFAsystem to xls Template for the Circular Sankey method.""" TimeIndex = Year - self.Time_Start myfont = xlwt.Font() myfont.bold = True mystyle = xlwt.XFStyle() mystyle.font = myfont Result_workbook = xlwt.Workbook(encoding='ascii') Result_worksheet = Result_workbook.add_sheet('Nodes') Result_worksheet.write(0, 0, label='Name', style=mystyle) Result_worksheet.write(0, 1, label='Color', style=mystyle) Result_worksheet.write(0, 2, label='Orientation', style=mystyle) Result_worksheet.write(0, 3, label='Width', style=mystyle) Result_worksheet.write(0, 4, label='Height', style=mystyle) Result_worksheet.write(0, 5, label='x_position', style=mystyle) Result_worksheet.write(0, 6, label='y_position', style=mystyle) for m in range(0, len(self.ProcessList)): if self.ProcessList[m].Graphical is None: raise ValueError( 'Graphical properties of process number {foo} are not set. No export to Sankey possible, as position of process on canvas etc. needs is not specified.' .format(foo=m)) Result_worksheet.write(m + 1, 0, label=self.ProcessList[m].Graphical['Name']) Result_worksheet.write( m + 1, 1, label=self.ProcessList[m].Graphical['Color']) Result_worksheet.write( m + 1, 2, label=self.ProcessList[m].Graphical['Angle']) Result_worksheet.write( m + 1, 3, label=self.ProcessList[m].Graphical['Width']) Result_worksheet.write( m + 1, 4, label=self.ProcessList[m].Graphical['Height']) Result_worksheet.write(m + 1, 5, label=self.ProcessList[m].Graphical['xPos']) Result_worksheet.write(m + 1, 6, label=self.ProcessList[m].Graphical['yPos']) Result_worksheet = Result_workbook.add_sheet('Flows') Result_worksheet.write(0, 0, label='StartNode', style=mystyle) Result_worksheet.write(0, 1, label='EndNode', style=mystyle) Result_worksheet.write(0, 2, label='Value', style=mystyle) Result_worksheet.write(0, 3, label='Color', style=mystyle) for key in self.FlowDict: Result_worksheet.write(m + 1, 0, label=self.FlowDict[key].P_Start) Result_worksheet.write(m + 1, 1, label=self.FlowDict[key].P_End) Result_worksheet.write(m + 1, 2, label=float( self.Flow_Sum_By_Element(key)[TimeIndex, Element])) Result_worksheet.write(m + 1, 3, label=self.FlowDict[key].Color) Result_workbook.save(Path + self.Name + '_' + str(TimeIndex) + '_' + str(Element) + '_Sankey.xls')
return style styleOK = xlwt.easyxf() pattern = xlwt.Pattern() # 一个实例化的样式类 pattern.pattern = xlwt.Pattern.SOLID_PATTERN # 固定的样式 pattern.pattern_fore_colour = xlwt.Style.colour_map['white'] # 背景颜色 borders = xlwt.Borders() # 为样式创建边框 borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 font = xlwt.Font() # 为样式创建字体 font.name = '华文中宋' font.bold = False font.height = 240 styleOK.pattern = pattern styleOK.borders = borders styleOK.font = font #检测输入表格并计算它们的数量 filenames = [] outputdir = ".\输入表格" for item in os.listdir(outputdir): if item.split('.')[-1] != 'py' and item.split('.')[ -1] == 'xlsx' or item.split('.')[-1] == 'csv' and os.path.isdir( item) == False or item.split('.')[-1] == 'xls':
def export_xls(result): url = '/static/export/%s/%s.xls' % (time.strftime('%Y%m'), time.strftime('%Y%m%d-%H%M%S')) filename = 'xd_REST' + url export_dir, _ = os.path.split(filename) if not os.path.exists(export_dir): os.makedirs(export_dir) #写文件 wbk = xlwt.Workbook(encoding='utf-8') sheet1 = wbk.add_sheet('sheet1', cell_overwrite_ok=True) #字体 font = xlwt.Font() font.name = 'Calibri' #Calibri font.height = 220 #11 #设置默认字体 xlwt.Style.default_style.font = font #合并居中分格 style = xlwt.XFStyle() style.alignment.vert = xlwt.Alignment.VERT_CENTER style.font = font i = 0 for row in result: #输出表头 if i == 0: export_headers(sheet1, row) current_row = [] for header in g.rpt_headers: col = row[header.name] if col == None: col = '' if isinstance(col, datetime.datetime): if col.year >= 1900: col = col.strftime("%Y-%m-%d %H:%M:%S") else: col = '{0.year:04d}-{0.month:02d}-{0.day:02d} {0.hour:02d}:{0.minute:02d}:{0.second:02d}'.format( col) current_row.append(col) #分组合并单元格 if g.rpt_report.rowspan == 1: if i == 0: rowspan = [2] * len(current_row) else: for col in g.grouped_cols: if current_row[col] != prev_row[col]: r1 = rowspan[col] r2 = i + 1 c1 = c2 = col rowspan[col] = r2 + 1 if r2 - r1 > 1: sheet1.write_merge(r1, r2, c1, c2, prev_row[col], style=style) #小计 if len(g.grouped_cols) > 0: if i == 0: subtotal = acc_init(current_row) else: different = False for col in g.grouped_cols: if current_row[col] != prev_row[col]: different = True break if different: #切换分组,输出小计行,重新开始小计 subtotal[0] = '小计' writerow(sheet1, i, subtotal) subtotal = acc_init(current_row) i = i + 1 if g.rpt_report.rowspan == 1: for col in range(len(rowspan)): rowspan[col] = i + 2 else: #小计 subtotal = acc(subtotal, current_row) #合计 if g.rpt_report.acc_total == 1: if i == 0: total = acc_init(current_row) else: total = acc(total, current_row) prev_row = current_row writerow(sheet1, i, current_row) #行号自增 i = i + 1 #最后一个分组合并单元格 if i > 0 and g.rpt_report.rowspan == 1: for col in g.grouped_cols: r1 = rowspan[col] r2 = i + 1 c1 = c2 = col rowspan[col] = r2 + 1 if r2 - r1 > 1: sheet1.write_merge(r1, r2, c1, c2, prev_row[col], style=style) #最后一个小计 if i > 0 and len(g.grouped_cols) > 0: subtotal[0] = '小计' writerow(sheet1, i, subtotal) i = i + 1 #输出合计 if i > 0 and g.rpt_report.acc_total == 1: total[0] = '合计' writerow(sheet1, i, total) if i == 0: sheet1.write(0, 0, '根据过滤条件,查找不到任何数据') wbk.save(filename) return redirect(url)
] = read_bds_sheet(ARINC_format_labels, ARINC_deformat_labels, bds_sheet_name, bds_formating, bds_format) #Open Excel MICD template file for writing appdir = os.path.dirname(sys.argv[0]) if appdir == '': appdir = '.' MICD_template_file = xlrd.open_workbook(filename=appdir + '/ICD_template.xls', formatting_info=True, logfile=sys.stdout, verbosity=0) MICD_file = xlutils.copy.copy(MICD_template_file) MICD_title_style = xlwt.XFStyle() MICD_title_style.font = xlwt.Font() MICD_title_style.font.bold = True MICD_title_style.pattern = xlwt.Pattern() MICD_title_style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN MICD_title_style.pattern.pattern_fore_colour = 7 MICD_title_style.alignment = xlwt.Alignment() MICD_title_style.alignment.wrap = True MICD_title_style.alignment.vert = 0 #Create basic FUN_IN and the title line MICD_fun_in_titles = [ 'Name', 'Type', 'Unit', 'Description', 'Convention', 'Dim1', 'Dim2', 'Com Format', 'Com Mode', 'From', 'Refresh\nRate', 'Min', 'Max', 'Enum', 'Consumed\nIf', 'Aircraft Signal\nName', 'Interface\nLevel', 'Status (SSM/FS/Refresh)', 'Simulation\nLevel [1]', 'Init\nValue', 'Custom', 'Comment', 'Last Modification'
#列幅 newSheet_1_column_1 = newsheet_1.col(2) newSheet_1_column_1.width = 5000 # Alignment c_align = xlwt.Alignment() c_align.horz = xlwt.Alignment.HORZ_CENTER # 背景 c_pattern = xlwt.Pattern() c_pattern.pattern = xlwt.Pattern.SOLID_PATTERN c_pattern.pattern_fore_colour = 0x16 #Font c_font_1 = xlwt.Font() c_font_1.bold = True c_font_1.colour_index = 0x35 c_style_1 = xlwt.XFStyle() c_style_1.font = c_font_1 c_style_1.num_format_str = 'yyyy-mm-dd' c_font_2 = xlwt.Font() c_font_2.name = 'Arial' c_style_2 = xlwt.XFStyle() c_style_2.font = c_font_2 c_style_2.alignment = c_align c_style_2.pattern = c_pattern # 上書きはダメ? #Exception: Attempt to overwrite cell: sheetname='Newsheet_1' rowx=1 colx=0
def Writexls(addlist, resultdict): CapTime = time.strftime('%Y_%m_%d-%H_%M_%S', time.localtime()) #取个时间 # 公共表格属性 # 对齐方式:上下居中,左右居中 Alignment = xlwt.Alignment() Alignment.horz = 0x02 Alignment.vert = 0x02 # 字体:consolas 14号 Font = xlwt.Font() Font.name = 'Consolas' Font.height = 20 * 14 # 结果为NO的单元格(地址可用)颜色-绿色 PatternYes = xlwt.Pattern() PatternYes.pattern = xlwt.Pattern.SOLID_PATTERN PatternYes.pattern_fore_colour = xlwt.Style.colour_map['green'] # 结果为Yes的单元格(地址不可用)颜色-红色 PatternNo = xlwt.Pattern() PatternNo.pattern = xlwt.Pattern.SOLID_PATTERN PatternNo.pattern_fore_colour = xlwt.Style.colour_map['red'] # 设置地址可用时的格式 StlYes = xlwt.XFStyle() StlYes.pattern = PatternYes StlYes.alignment = Alignment StlYes.font = Font # 设置地址不可用的格式 StlNo = xlwt.XFStyle() StlNo.pattern = PatternNo StlNo.alignment = Alignment StlNo.font = Font # 设置第一行的格式 StlTitle = xlwt.XFStyle() StlTitle.alignment = Alignment StlTitle.font = Font ListLenth = len(addlist) OutXls = xlwt.Workbook() #创建一个列表,将每个sheet作为一个对象放入列表中。多sheet写入的情况,在此处卡了很久。 SheetList = [] for i in range(0, ListLenth): SheetList.append(OutXls.add_sheet(addlist[i].replace('/', '_'))) #两个列表元素,双循环,省事。 for sht, addr in zip(SheetList, addlist): # 写表头:描述是哪个网段 # worksheet.write_merge(开始行,结束行, 开始列, 结束列, '内容', style) abc = '' #这个就是用来处理个字段没啥用。 for i in addr.split('.')[:3]: abc = abc + i + '.' sht.write_merge(0, 0, 1, 16, addr, style=StlTitle) for i in range(0, 16): for j in range(0, 16): if i * 16 + j == 0 or i * 16 + j == 255: sht.write(i + 1, j + 1, label=str(i * 16 + j), style=StlNo) else: if resultdict[abc + str(i * 16 + j)] == 'No': sht.write(i + 1, j + 1, label=str(i * 16 + j), style=StlYes) elif resultdict[abc + str(i * 16 + j)] == 'Yes': sht.write(i + 1, j + 1, label=str(i * 16 + j), style=StlNo) else: sht.write(i + 1, j + 1, label=str(i * 16 + j)) OutXls.save(r'/root/Mping/Xls_file/%s_IPused.xls' % CapTime) return
# mysqlcursor.execute('''SELECT DISTINCT(date) from temp where date=DATE(DATE_SUB(NOW(), INTERVAL %s DAY))''',(inter)) # # dateList=mysqlcursor.fetchall() # # dateList=list() rowNumber = 1 # for one_temp_date in dateList: # if one_temp_date[0] is not None: # print one_temp_date[0] m = 1 today = datetime.date.today() oneday = datetime.timedelta(days=1) yesterday = today - oneday print yesterday ws = w.add_sheet(str(yesterday).decode('utf-8'), cell_overwrite_ok=True) font = xlwt.Font() # Create the Font font.name = 'Times New Roman' borders = xlwt.Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 borders.bottom_colour = 0x3A style = xlwt.XFStyle() style.borders = borders alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_CENTER alignment.vert = xlwt.Alignment.VERT_CENTER style = xlwt.XFStyle() style.borders = borders style.alignment = alignment
from xlutils.copy import copy import xlrd import xlwt temp_excel = xlrd.open_workbook(r"C:\Users\Admin\Desktop\heping.xls",formatting_info=True) temp_sheet = temp_excel.sheet_by_index(0) print(temp_sheet.cell(2,2).value) new_excel = copy(temp_excel) new_sheet = new_excel.get_sheet(0) style = xlwt.XFStyle() #设置字体 font = xlwt.Font() font.name = "微软雅黑" font.bold = True font.height = 360 style.font = font #设置边框 borders = xlwt.Borders() borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN style.borders = borders #设置对齐方式 alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_CENTER alignment.vert = xlwt.Alignment.VERT_CENTER style.alignment = alignment
def action_excel(self, cr, uid, ids, chart_account_id, form=None, context=None): if not ids: return {'type_hr': 'ir.actions.act_window_close'} if context is None: context = {} wb = pycel.Workbook(encoding='utf-8') style_cabecera = pycel.easyxf('font: colour black, bold True;' 'align: vertical center, horizontal center;' ) style_header = pycel.easyxf('font: bold True;' 'align: vertical center, horizontal center, wrap on;' 'borders: left 1, right 1, top 1, bottom 1;') view_style = pycel.easyxf('font: bold True, height 160;' 'align: vertical center, horizontal left, wrap on;') linea_izq = pycel.easyxf('font: colour black, height 150;' 'align: vertical center, horizontal left, wrap on;' ) linea_izq_n = pycel.easyxf('font: colour black, height 140;' 'align: vertical center, horizontal left, wrap on;' ) linea_der = pycel.easyxf('font: colour black, height 150;' 'align: vertical center, horizontal right;' ) ws = wb.add_sheet('HOJA DE BALANCE') ws.show_grid = False ws.header_str = u"&LFecha de Impresion: &D Hora: &T&RPagina &P de &N" ws.footer_str = u"" compania = self.pool.get('res.users').browse(cr, uid, uid).company_id # x0 = 11 ws.write_merge(1, 1, 1, 5, compania.name, style_cabecera) ws.write_merge(2, 2, 1, 5, 'Direccion: ' + compania.partner_id.street, style_cabecera) ws.write_merge(3, 3, 1, 5, 'Ruc: ' + compania.partner_id.part_number, style_cabecera) ws.write_merge(5, 5, 1, 5, datetime.today().strftime('%Y-%m-%d'), style_cabecera) if form['filter'] == 'filter_period': period_from = 'Desde:' + self.pool.get('account.period').browse(cr, uid, form['period_from']).name period_to = 'Hasta' + self.pool.get('account.period').browse(cr, uid, form['period_to']).name ws.write(6, 2, period_from, style_cabecera) ws.write(6, 4, period_to, style_cabecera) elif form['filter'] == 'filter_date': ws.write(6, 2, ('Desde:' + form['date_from']), style_cabecera) ws.write(6, 4, ('Hasta:' + form['date_to']), style_cabecera) ws.fit_num_pages = 1 ws.fit_height_to_pages = 0 ws.fit_width_to_pages = 1 ws.portrait = 1 align = pycel.Alignment() align.horz = pycel.Alignment.HORZ_RIGHT align.vert = pycel.Alignment.VERT_CENTER font1 = pycel.Font() font1.colour_index = 0x0 font1.height = 140 linea_izq_n.width = 150 #Formato de Numero style = pycel.XFStyle() style.num_format_str = '#,##0.00' style.alignment = align style.font = font1 #Formato de Numero Saldo font = pycel.Font() font.bold = True font.colour_index = 0x27 style1 = pycel.XFStyle() style1.num_format_str = '#,##0.00' style1.alignment = align style1.font = font font2 = pycel.Font() font2.bold = True font2.colour_index = 0x0 style2 = pycel.XFStyle() style2.num_format_str = '#,##0.00' style2.alignment = align style2.font = font2 style3 = pycel.XFStyle() style3.num_format_str = '0' style3.alignment = align style3.font = font1 #info = self.get_payroll(cr, uid, form) xi = 8 # Cabecera de Cliente sec = 1 # if type_hr == 'rol': ws.write(xi, 1, 'Codigo', style_header) ws.write(xi, 2, 'Cuenta', style_header) ws.write(xi, 3, 'Cod.Matriz', style_header) ws.write(xi, 4, 'Cuenta Matriz', style_header) ws.write(xi, 5, 'Saldo Ant.', style_header) ws.write(xi, 6, 'Debe', style_header) ws.write(xi, 7, 'Haber', style_header) ws.write(xi, 8, 'Saldo', style_header) # lst_titulos = self.get_titulos_report(cr, uid, form) col_t = 8 # for titulo in lst_titulos: # col_t+=1 # ws.write(xi, col_t, titulo, style_header) xi += 1 # seq = 0 rf = rr = ri = 0 account_pool = self.pool.get('account.account') ctx = dict() if context['filter'] != 'filter_no': cr.execute('SELECT MIN(date_start) FROM account_period WHERE fiscalyear_id = %s', (form['fiscalyear_id'],)) dict_date = cr.dictfetchall() if context['filter'] == 'filter_period': date_from = self.pool.get('account.period').browse(cr, uid, context['period_from']).date_start else: date_from = form['date_from'] if date_from > dict_date[0]['min']: date_from = datetime.strptime(date_from, "%Y-%m-%d") - relativedelta(days=1) date_from = date_from.strftime("%Y-%m-%d") ctx['fiscalyear'] = form['fiscalyear_id'] ctx['date_from'] = dict_date[0]['min'] ctx['date_to'] = date_from ctx['state'] = form['target_move'] lines = self.lines(cr, uid, [chart_account_id], False, form, context=context) for linea in lines: # seq += 1 # ws.write(xi, 1, seq, linea_center) account = account_pool.browse(cr, uid, linea['id'], ctx) ws.write(xi, 1, account.code, linea_izq if account.type != 'view' else view_style) ws.write(xi, 2, account.name, linea_izq if account.type != 'view' else view_style) ws.write(xi, 3, account.code_tiw, linea_izq if account.type != 'view' else view_style) ws.write(xi, 4, account.name_tiw, linea_izq if account.type != 'view' else view_style) ws.write(xi, 5, account.balance if ctx else 0.00, linea_der) ws.write(xi, 6, linea.get('debit', ''), linea_der) ws.write(xi, 7, linea.get('credit', ''), linea_der) ws.write(xi, 8, linea.get('balance', ''), linea_der) col = 8 xi += 1 ws.col(0).width = 2000 ws.col(1).width = 5800 ws.col(2).width = 9900 ws.col(3).width = 5000 ws.col(4).width = 6900 ws.col(5).width = 6500 ws.col(6).width = 5500 ws.col(7).width = 5500 ws.col(8).width = 5500 ws.col(9).width = 5500 ws.col(10).width = 6500 ws.col(11).width = 6500 ws.col(12).width = 6500 ws.col(13).width = 6500 ws.col(14).width = 6500 ws.col(15).width = 6500 ws.col(16).width = 6500 ws.col(17).width = 6500 ws.col(18).width = 6500 ws.col(19).width = 6500 ws.col(20).width = 6500 ws.col(21).width = 6500 ws.col(22).width = 6500 ws.col(23).width = 6500 ws.col(24).width = 6500 ws.col(25).width = 6500 ws.col(26).width = 6500 ws.col(27).width = 6500 ws.col(28).width = 6500 ws.col(29).width = 6500 ws.col(30).width = 6500 ws.row(8).height = 750 buf = cStringIO.StringIO() # name = "%s%s%s.xls" % (path, "Reporte_RR_HH", datetime.datetime.now()) # exists_path = os.path.exists(path) # print 'esta ruta existe?', exists_path # print('sys.argv[0] =', sys.argv[0]) # a = sys.path # pathname = os.path.dirname(os.getcwd()) # print('path =', a) # print('full path =', os.path.abspath(pathname)) try: # wb.save(name) # raise Warning('Archivo salvado correctamente') buf = cStringIO.StringIO() wb.save(buf) out = base64.encodestring(buf.getvalue()) buf.close() data_fname = "Balance de Comprobacion_%s.xls" archivo = '/opt/temp/' + data_fname res_model = 'account.balance.report' id = ids and type(ids) == type([]) and ids[0] or ids self.load_doc(cr, uid, out, id, data_fname, archivo, res_model) return self.write(cr, uid, ids, {'data': out, 'txt_filename': data_fname, 'name': 'Reporte_Balance.xls'}) # return self.write(cr, uid, ids, {'data': out, 'txt_filename': name}) except ValueError: raise Warning('Error a la hora de salvar el archivo')
def font_style(position='left', bold=0, fontos=0, font_height=200, border=0, color=False): font = xlwt.Font() font.name = 'Verdana' font.bold = bold font.height = font_height center = xlwt.Alignment() center.horz = xlwt.Alignment.HORZ_CENTER center.vert = xlwt.Alignment.VERT_CENTER center.wrap = xlwt.Alignment.VERT_JUSTIFIED left = xlwt.Alignment() left.horz = xlwt.Alignment.HORZ_LEFT left.vert = xlwt.Alignment.VERT_CENTER left.wrap = xlwt.Alignment.VERT_JUSTIFIED borders = xlwt.Borders() borders.right = 1 borders.left = 1 borders.top = 1 borders.bottom = 1 orient = xlwt.Alignment() orient.orie = xlwt.Alignment.ORIENTATION_90_CC style = xlwt.XFStyle() if border == 1: style.borders = borders if fontos == 'red': font.colour_index = 2 style.font = font else: style.font = font if position == 'center': style.alignment = center else: style.alignment = left if color == 'grey': badBG = xlwt.Pattern() badBG.pattern = badBG.SOLID_PATTERN badBG.pattern_fore_colour = 22 style.pattern = badBG if color == 'red': badBG = xlwt.Pattern() badBG.pattern = badBG.SOLID_PATTERN badBG.pattern_fore_colour = 5 style.pattern = badBG if color == 'yellow': badBG = xlwt.Pattern() badBG.pattern = badBG.SOLID_PATTERN badBG.pattern_fore_colour = 0x0D style.pattern = badBG return style
def get_xls_file(self): ''' The method used to call download file of wizard @self : Record Set @api.multi : The decorator of multi @return: Return of wizard of action in dictionary ----------------------------------------------------- ''' cpf_binary_obj = self.env['cpf.binary.wizard'] cr, uid, context = self.env.args if context is None: context = {} context = dict(context) payment_wiz_data = self.read([]) data = {} if payment_wiz_data: data = payment_wiz_data[0] start_date = data.get('date_start', False) or False stop_date = data.get('date_stop', False) or False end_date = data.get('date_stop', False) or False emp_ids = data.get('employee_ids', False) or [] if start_date >= end_date: raise ValidationError(_("You must be enter start date less than end date !")) for employee in self.env['hr.employee'].browse(emp_ids): if not employee.identification_id: raise ValidationError(_('There is no identification no define for %s employee.' % (employee.name))) context.update({'employee_id': emp_ids, 'date_start': start_date, 'date_stop': end_date}) company_data = self.env['res.users'].browse(uid).company_id if data.get("export_report", False) == "pdf": data.update({'currency': " " + tools.ustr(company_data.currency_id.symbol), 'company': company_data.name}) for employee in self.env['hr.employee'].browse(data.get('employee_ids')): if not employee.identification_id: raise Warning(_('There is no identification no define for %s employee.' % (employee.name))) return self.env['report'].with_context(landscape=True).get_action(self, 'sg_hr_report.report_payment_advice') else: wbk = xlwt.Workbook() sheet = wbk.add_sheet('sheet 1', cell_overwrite_ok=True) font = xlwt.Font() font.bold = True bold_style = xlwt.XFStyle() bold_style.font = font border = xlwt.easyxf('font: bold on, color black; align: wrap no; borders: left thin, right thin, top thin, bottom thin; pattern: pattern solid, fore_color white;') borders = xlwt.easyxf('font: bold off, color black; align: wrap no; borders: left thin, right thin, top thin, bottom thin; pattern: pattern solid, fore_color white;') style = xlwt.easyxf('align: wrap no') style.num_format_str = '#,##0.00' new_style = xlwt.easyxf('font: bold on; align: wrap no') new_style.num_format_str = '#,##0.00' # static data sheet.write(0, 4, company_data.name) sheet.write(1, 3, company_data.street or '') sheet.write(2, 3, str(company_data and company_data.street2 or ' ') + ' ' + str(company_data and company_data.country_id and company_data.country_id.name or '') + ' ' + str(company_data.zip) or '') sheet.write(3, 4, 'Tel : ' + str(company_data.phone or ' ') + ',' + 'Fax : ' + str(company_data.fax or '')) sheet.write(4, 5, 'PAYMENT ADVICE') sheet.write(6, 0, 'MANDATORY REF NO. : ' + str(company_data.company_code or '')) sheet.write(7, 0, 'VOLUNTARY REF NO. : ') sheet.write(8, 0, company_data.name) sheet.write(9, 0, '6 Jalan Kilang #04-00') sheet.write(6, 8, 'SUBM MODE') sheet.write(7, 8, 'DATE') sheet.write(6, 10, ':') sheet.write(7, 10, ':') sheet.write(6, 11, 'INTERNAL') sheet.write(12, 0, 'PART 1 : Payment Details For ', bold_style) sheet.write(13, 8, 'AMOUNT' , bold_style) sheet.write(13, 10, 'NO. OF EMPLOYEE', bold_style) sheet.write(15, 0, '1. CPF Contribution') sheet.write(16, 1, 'Mandatory Contribution') sheet.write(17, 1, 'Voluntary Contribution') sheet.write(18, 0, '2. B/F CPF late Payment interest') sheet.write(19, 0, 'Interest charged on last payment') sheet.write(20, 0, '3. Late payment interest on CPF Contribution') sheet.write(21, 0, '4. Late payment penalty for Foreign Worker Levy') sheet.write(22, 0, '5. Foreign Worker Levy') sheet.write(23, 0, '6. Skills Development Levy') sheet.write(24, 0, '7. Donation to Community Chest') sheet.write(25, 0, '8. Mosque Building & Mendaki Fund (MBMF)') sheet.write(26, 0, '9. SINDA Fund') sheet.write(27, 0, '10. CDAC Fund') sheet.write(28, 0, '11. Eurasian Community Fund (EUCF)') # total sheet.write(30, 7, 'Total', bold_style) # static data sheet.write(31, 4, 'Please fill in cheque details if you are paying by cheque') sheet.write(32, 4, 'BANK') sheet.write(32, 5, ':') sheet.write(33, 4, 'CHEQUE NO.') sheet.write(33, 5, ':') sheet.write(34, 4, 'THE EMPLOYER HEREBY GUARANTEES') sheet.write(35, 4, 'THE ACCURACY') sheet.write(36, 4, 'OF THE CPF RETURNS FOR') sheet.write(37, 4, 'AS SHOWN ON THE SUBMITTED DISKETTE.') sheet.write(39, 4, 'EMPLOYER\'S AUTHORIZED SIGNATORY') sheet.write(42, 0, 'PART 2 : Contribution Details For', bold_style) # data header sheet.write(44, 0, 'Employee Name', border) sheet.write(43, 3, 'CPF', border) sheet.write(44, 3, 'Account No.', border) sheet.write(43, 4, 'Mandatory CPF', border) sheet.write(44, 4, 'Contribution', border) sheet.write(43, 5, 'Voluntary CPF', border) sheet.write(44, 5, 'Contribution', border) sheet.write(43, 6, 'Last', border) sheet.write(44, 6, 'Contribution', border) # sheet.write(41, 8, 'CPF', bold_style) # sheet.write(42, 8, 'Status', bold_style) sheet.write(43, 8, 'MBMF', border) sheet.write(44, 8, 'Fund', border) sheet.write(43, 9, 'SINDA', border) sheet.write(44, 9, 'Fund', border) sheet.write(43, 10, 'CDAC', border) sheet.write(44, 10, 'Fund', border) sheet.write(43, 11, 'ECF', border) sheet.write(44, 11, 'Fund', border) sheet.write(43, 12, 'SDL', border) sheet.write(44, 12, 'Fund', border) sheet.write(43, 13, 'Ordinary', border) sheet.write(44, 13, 'Wages', border) sheet.write(43, 14, 'Additional', border) sheet.write(44, 14, 'Wages', border) sheet.write(43, 0, '', border) sheet.write(43, 1, '', border) sheet.write(43, 2, '', border) sheet.write(43, 7, '', border) sheet.write(44, 1, '', border) sheet.write(44, 2, '', border) sheet.write(44, 7, '', border) emp_obj = self.env['hr.employee'] payslip_obj = self.env['hr.payslip'] hr_contract_obj = self.env['hr.contract'] category_ids = self.env['hr.employee.category'].search([]) start_row = raw_no = 45 # start_date = self.date_start or False # stop_date = self.date_stop or False # employee_ids_lst = context.get('employee_id',False) # print"\n\nemployee_list_ids:\n\n", employee_ids_lst month_dict = {'01':'January', '02':'February', '03': 'March', '04':'April', '05':'May', '06':'June', '07':'July', '08':'August', '09':'September', '10':'October', '11': 'November', '12': 'December'} period = month_dict.get(start_date.split('-')[1]) + ', ' + start_date.split('-')[0] sheet.write(36, 7, period) sheet.write(7, 11, datetime.strptime(start_date, DEFAULT_SERVER_DATE_FORMAT).strftime('%d-%m-%Y')) sheet.write(42, 2, period, bold_style) sheet.write(12, 2, period, bold_style) t_cpfsdl_amount = t_p_cpf_sdl_amount = t_p_fwl_amount = t_p_cpf_amount = t_gross_amount = t_ecf_amount = t_cdac_amount = t_sinda_amount = t_mbmf_amount = t_cpf_amount = 0.00 total_additional_amount = total_cpfsdl_amount = total_p_cpf_amount = total_gross_amount = total_ecf_amount = total_cdac_amount = total_sinda_amount = total_mbmf_amount = total_cpf_amount = 0.00 emp_cpfsdl_amount = emp_sdl_amount = emp_ecf_amount = emp_fwl_amount = emp_cdac_amount = emp_sinda_amount = emp_mbmf_amount = emp_cpf_amount = 0.00 # no category join_date = start_date emply_ids = emp_obj.search([('id', 'in', emp_ids), ('category_ids', '=', False)]) do_total = False for emp_record in emply_ids: payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id), ('date_from', '>=', start_date), ('date_from', '<=', stop_date), ('state', 'in', ['draft', 'done', 'verify'])]) previous_date = cpf_binary_obj._default_previous_date(start_date) previous_payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id)], order='date_from ASC', limit=1) if previous_payslip_ids: join_date = previous_payslip_ids.date_from while(join_date <= previous_date[0]): previous_payslip_ids = payslip_obj.search([ ('employee_id', '=', emp_record.id), ('date_from', '>=', previous_date[0]), ('date_from', '<=', previous_date[1]), ('state', 'in', ['draft', 'done', 'verify'])]) if previous_payslip_ids: break else: previous_date = cpf_binary_obj._default_previous_date(previous_date[0]) # if not payslip_ids: # raise ValidationError(_('There is no payslip details between selected date %s and %s') %(previous_date[0], previous_date[1])) additional_amount = cpfsdl_amount = p_cpf_amount = gross_amount = ecf_amount = fwl_amount = cdac_amount = sinda_amount = mbmf_amount = cpf_amount = 0.00 for payslip_rec in payslip_ids: for line in payslip_rec.line_ids: if line.register_id.name == 'CPF': cpf_amount += line.amount if line.register_id.name == 'CPF - MBMF': mbmf_amount += line.amount if line.register_id.name == 'CPF - SINDA': sinda_amount += line.amount if line.register_id.name == 'CPF - CDAC': cdac_amount += line.amount if line.register_id.name == 'CPF - ECF': ecf_amount += line.amount if line.register_id.name == 'CPF - FWL': fwl_amount += line.amount t_p_fwl_amount += line.amount if line.register_id and line.register_id.name == 'BONUS': gross_amount -= line.amount if line.category_id.code == 'GROSS': gross_amount += line.amount if line.code == 'CPFSDL': cpfsdl_amount += line.amount t_p_cpf_sdl_amount += line.amount if line.register_id and line.register_id.name == 'BONUS': additional_amount += line.amount if not gross_amount: continue if not cpf_amount and not mbmf_amount and not sinda_amount and not cdac_amount and not ecf_amount and not cpfsdl_amount: continue sheet.write(raw_no, 0, payslip_rec.employee_id and payslip_rec.employee_id.name or '', borders) sheet.write(raw_no, 1, '', borders) sheet.write(raw_no, 2, '', borders) sheet.write(raw_no, 3, payslip_rec.employee_id and payslip_rec.employee_id.identification_id or '', borders) # previous cpf if previous_payslip_ids: if payslip_rec.date_from != payslip_rec.date_from: for previous_line in previous_payslip_ids.line_ids: if previous_line.register_id.name == 'CPF': p_cpf_amount += previous_line.amount # Counts Employee if fwl_amount: emp_fwl_amount += 1 if cpf_amount != 0: emp_cpf_amount += 1 if mbmf_amount != 0: emp_mbmf_amount += 1 if sinda_amount != 0: emp_sinda_amount += 1 if cdac_amount != 0: emp_cdac_amount += 1 if ecf_amount != 0: emp_ecf_amount += 1 if cpfsdl_amount != 0: emp_sdl_amount += 1 # writes in xls file do_total = True sheet.write(raw_no, 4, "%.2f" % (cpf_amount or 0.00), borders) t_cpf_amount += cpf_amount total_cpf_amount += cpf_amount sheet.write(raw_no, 5, "%.2f" % (0.00), borders) # sheet.write(raw_no, 6, borders) sheet.write(raw_no, 7, '', borders) sheet.write(raw_no, 8, "%.2f" % (mbmf_amount or 0.00), borders) t_mbmf_amount += mbmf_amount total_mbmf_amount += mbmf_amount sheet.write(raw_no, 9, "%.2f" % (sinda_amount or 0.00), borders) t_sinda_amount += sinda_amount total_sinda_amount += sinda_amount sheet.write(raw_no, 10, "%.2f" % (cdac_amount or 0.00), borders) t_cdac_amount += cdac_amount total_cdac_amount += cdac_amount sheet.write(raw_no, 11, "%.2f" % (ecf_amount or 0.00), borders) t_ecf_amount += ecf_amount total_ecf_amount += ecf_amount sheet.write(raw_no, 12, "%.2f" % (cpfsdl_amount or 0.00), borders) total_cpfsdl_amount += cpfsdl_amount t_cpfsdl_amount += cpfsdl_amount sheet.write(raw_no, 13, "%.2f" % (gross_amount or 0.00), borders) sheet.write(raw_no, 14, "%.2f" % (additional_amount or 0.00), borders) t_gross_amount += gross_amount total_gross_amount += gross_amount total_additional_amount += additional_amount sheet.write(raw_no, 6, "%.2f" % (p_cpf_amount or 0.00), borders) t_p_cpf_amount += p_cpf_amount total_p_cpf_amount += p_cpf_amount #sheet.write(raw_no, 6, 0.00, style) contract_id = hr_contract_obj.search([('employee_id', '=', emp_record.id), '|', ('date_end','>=', payslip_rec.date_from), ('date_end','=',False)]) old_contract_id = hr_contract_obj.search([('employee_id', '=', emp_record.id), ('date_end','<=', payslip_rec.date_from) ]) for contract in contract_id: if payslip_rec.employee_id.active == False: sheet.write(raw_no, 7, 'Left', border) elif contract.date_start >= payslip_rec.date_from and not old_contract_id.ids: sheet.write(raw_no, 7, 'New Join', border) else: sheet.write(raw_no, 7, 'Existing', border) raw_no += 1 if do_total: raw_no = raw_no + 1 sheet.write(raw_no, 0, 'Total Employee:', borders) sheet.write(raw_no, 1, '', borders) sheet.write(raw_no, 2, '', borders) sheet.write(raw_no, 3, '', borders) start_row = start_row + 1 sheet.write(raw_no, 4, "%.2f" %(total_cpf_amount), borders) # cpf sheet.write(raw_no, 5, "%.2f" % (0.00), borders) # v_cpf sheet.write(raw_no, 6, "%.2f" % (total_p_cpf_amount or 0.00), borders) # p_cpf sheet.write(raw_no, 7, '', borders) sheet.write(raw_no, 8, "%.2f" % (total_mbmf_amount or 0.00), borders) # MBPF sheet.write(raw_no, 9, "%.2f" % (total_sinda_amount or 0.00), borders) # SINDA sheet.write(raw_no, 10, "%.2f" % (total_cdac_amount or 0.00), borders) # CDAC sheet.write(raw_no, 11, "%.2f" % (total_ecf_amount or 0.00), borders) # ECF sheet.write(raw_no, 12, "%.2f" % (total_cpfsdl_amount or 0.00), borders) # CPFSDL sheet.write(raw_no, 13, "%.2f" % (total_gross_amount or 0.00), borders) # O_WAGE sheet.write(raw_no, 14, "%.2f" % (total_additional_amount), borders) # sheet.write(raw_no, 13, xlwt.Formula("sum(N" + str(start_row) + ":N" + str(raw_no - 1) + ")"), new_style) # AD_WAGE # emp by category start_row = raw_no = raw_no + 2 emp_rec = emp_obj.search([('id', 'in', emp_ids), ('category_ids', '!=', False)]) for category in category_ids: emp_flag= False total_additional_amount = total_cpfsdl_amount = total_p_cpf_amount = total_gross_amount = total_ecf_amount = total_cdac_amount = total_sinda_amount = total_mbmf_amount = total_cpf_amount = 0.00 for emp_record in emp_rec: if (emp_record.category_ids and emp_record.category_ids[0].id != category.id) or not emp_record.category_ids: continue payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id), ('date_from', '>=', start_date), ('date_from', '<=', stop_date), ('state', 'in', ['draft', 'done', 'verify'])]) previous_date = cpf_binary_obj._default_previous_date(start_date) previous_payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id)], order='date_from ASC', limit=1) if previous_payslip_ids: join_date = previous_payslip_ids.date_from while(join_date <= previous_date[0]): previous_payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id), ('date_from', '>=', previous_date[0]), ('date_from', '<=', previous_date[1]), ('state', 'in', ['draft', 'done', 'verify'])]) if previous_payslip_ids: break else: previous_date = cpf_binary_obj._default_previous_date(previous_date[0]) # if not payslip_ids: # raise ValidationError(_('There is no payslip details between selected date %s and %s') %(previous_date[0], previous_date[1])) additional_amount = cpfsdl_amount = p_cpf_amount = gross_amount = ecf_amount = fwl_amount = cdac_amount = sinda_amount = mbmf_amount = cpf_amount = 0.00 for payslip_rec in payslip_ids: for line in payslip_rec.line_ids: if line.register_id.name == 'CPF': cpf_amount += line.amount if line.register_id.name == 'CPF - MBMF': mbmf_amount += line.amount if line.register_id.name == 'CPF - SINDA': sinda_amount += line.amount if line.register_id.name == 'CPF - CDAC': cdac_amount += line.amount if line.register_id.name == 'CPF - ECF': ecf_amount += line.amount if line.register_id.name == 'CPF - FWL': fwl_amount += line.amount t_p_fwl_amount += line.amount if line.register_id and line.register_id.name == 'BONUS': gross_amount -= line.amount if line.category_id.code == 'GROSS': gross_amount += line.amount if line.code == 'CPFSDL': cpfsdl_amount += line.amount t_p_cpf_sdl_amount += line.amount if line.register_id and line.register_id.name == 'BONUS': additional_amount += line.amount if not gross_amount: continue if not cpf_amount and not mbmf_amount and not sinda_amount and not cdac_amount and not ecf_amount and not cpfsdl_amount: t_p_fwl_amount -= fwl_amount continue sheet.write(raw_no, 0, payslip_rec.employee_id and payslip_rec.employee_id.name or '', borders) sheet.write(raw_no, 1, '', borders) sheet.write(raw_no, 2, '', borders) sheet.write(raw_no, 3, payslip_rec.employee_id and payslip_rec.employee_id.identification_id or '', borders) # previous cpf if previous_payslip_ids: if payslip_rec.date_from != payslip_rec.date_from: for previous_line in previous_payslip_ids.line_ids: if previous_line.register_id.name == 'CPF': p_cpf_amount += previous_line.amount # Counts Employee if fwl_amount: emp_fwl_amount += 1 if cpf_amount != 0: emp_cpf_amount += 1 if mbmf_amount != 0: emp_mbmf_amount += 1 if sinda_amount != 0: emp_sinda_amount += 1 if cdac_amount != 0: emp_cdac_amount += 1 if ecf_amount != 0: emp_ecf_amount += 1 if cpfsdl_amount != 0: emp_sdl_amount += 1 # writes in xls file emp_flag = True sheet.write(raw_no, 4, "%.2f" % (cpf_amount or 0.00), borders) t_cpf_amount += cpf_amount total_cpf_amount += cpf_amount sheet.write(raw_no, 5, "%.2f" % (0.00), borders) # sheet.write(raw_no, 6, borders) sheet.write(raw_no, 7, '', borders) sheet.write(raw_no, 8, "%.2f" % (mbmf_amount or 0.00), borders) t_mbmf_amount += mbmf_amount total_mbmf_amount += mbmf_amount sheet.write(raw_no, 9, "%.2f" % (sinda_amount or 0.00), borders) t_sinda_amount += sinda_amount total_sinda_amount += sinda_amount sheet.write(raw_no, 10, "%.2f" % (cdac_amount or 0.00), borders) t_cdac_amount += cdac_amount total_cdac_amount += cdac_amount sheet.write(raw_no, 11, "%.2f" % (ecf_amount or 0.00), borders) t_ecf_amount += ecf_amount total_ecf_amount += ecf_amount sheet.write(raw_no, 12, "%.2f" % (cpfsdl_amount or 0.00), borders) t_cpfsdl_amount += cpfsdl_amount total_cpfsdl_amount += cpfsdl_amount sheet.write(raw_no, 13, "%.2f" % (gross_amount or 0.00), borders) sheet.write(raw_no, 14, "%.2f" % (additional_amount or 0.00), borders) t_gross_amount += gross_amount total_gross_amount += gross_amount sheet.write(raw_no, 6, "%.2f" % (p_cpf_amount or 0.00), borders) t_p_cpf_amount += p_cpf_amount total_p_cpf_amount += p_cpf_amount total_additional_amount += additional_amount #sheet.write(raw_no, 6, 0.00, style) contract_id = hr_contract_obj.search([('employee_id', '=', emp_record.id), '|', ('date_end','>=', payslip_rec.date_from), ('date_end','=',False)]) old_contract_id = hr_contract_obj.search([('employee_id', '=', emp_record.id), ('date_end','<=', payslip_rec.date_from)]) for contract in contract_id: if payslip_rec.employee_id.active == False: sheet.write(raw_no, 7, 'Left', borders) elif contract.date_start >= payslip_rec.date_from and not old_contract_id.ids: sheet.write(raw_no, 7, 'New Join', borders) else: sheet.write(raw_no, 7, 'Existing', borders) raw_no += 1 if emp_flag: raw_no = raw_no + 1 sheet.write(raw_no, 0, 'Total %s :' % category.name , borders) sheet.write(raw_no, 1, '', borders) sheet.write(raw_no, 2, '', borders) sheet.write(raw_no, 3, '', borders) start_row = start_row + 1 sheet.write(raw_no, 4, "%.2f" % (total_cpf_amount or 0.00), borders) # cpf sheet.write(raw_no, 5, "%.2f" % (0.00), borders) # v_cpf sheet.write(raw_no, 6, "%.2f" % (total_p_cpf_amount or 0.00), borders) # p_cpf sheet.write(raw_no, 7, '', borders) sheet.write(raw_no, 8, "%.2f" % (total_mbmf_amount or 0.00), borders) # MBPF sheet.write(raw_no, 9, "%.2f" % (total_sinda_amount or 0.00), borders) # SINDA sheet.write(raw_no, 10, "%.2f" % (total_cdac_amount or 0.00), borders) # CDAC sheet.write(raw_no, 11, "%.2f" % (total_ecf_amount or 0.00), borders) # ECF sheet.write(raw_no, 12, "%.2f" % (total_cpfsdl_amount or 0.00), borders) # ECF sheet.write(raw_no, 13, "%.2f" % (total_gross_amount or 0.00), borders) # O_WAGE sheet.write(raw_no, 14, "%.2f" % (total_additional_amount), borders) raw_no = raw_no + 2 start_row = start_row+ 3 # amount columns sheet.write(16, 8,(t_cpf_amount or 0.00), style) # cpf sheet.write(17, 8, 0.00, style) sheet.write(18, 8, 0.00, style) sheet.write(19, 8, 0.00, style) sheet.write(20, 8, 0.00, style) sheet.write(21, 8, 0.00, style) sheet.write(22, 8, t_p_fwl_amount, style) sheet.write(23, 8, t_p_cpf_sdl_amount, style) sheet.write(24, 8, 0.00, style) sheet.write(25, 8, (t_mbmf_amount or 0.00), style) # MBPF sheet.write(26, 8,(t_sinda_amount or 0.00), style) # SINDA sheet.write(27, 8,(t_cdac_amount or 0.00), style) # CDAC sheet.write(28, 8,(t_ecf_amount or 0.00), style) # ECF # no of employee sheet.write(16, 10, emp_cpf_amount) sheet.write(17, 10, 0) sheet.write(18, 10, 0) sheet.write(19, 10, 0) sheet.write(20, 10, 0) sheet.write(21, 10, 0) sheet.write(22, 10, emp_fwl_amount) sheet.write(23, 10, emp_sdl_amount) sheet.write(24, 10, 0) sheet.write(25, 10, emp_mbmf_amount) sheet.write(26, 10, emp_sinda_amount) sheet.write(27, 10, emp_cdac_amount) sheet.write(28, 10, emp_ecf_amount) sheet.write(30, 8, xlwt.Formula("sum(I17:I29)"), new_style) # Total wbk.save(tempfile.gettempdir() + "/payslip.xls") file = open(tempfile.gettempdir() + "/payslip.xls", "rb") out = file.read() file.close() res = base64.b64encode(out) if not start_date and stop_date: return '' end_date = datetime.strptime(stop_date, DEFAULT_SERVER_DATE_FORMAT) monthyear = end_date.strftime('%m%Y') file_name = "Payment Advice " + monthyear + '.xls' module_rec = cpf_binary_obj.create({'name': file_name, 'xls_file' : res}) return {'name': _('Payment Advice Report'), 'res_id' : module_rec.id, 'view_type': 'form', "view_mode": 'form', 'res_model': 'cpf.binary.wizard', 'type': 'ir.actions.act_window', 'target': 'new', 'context': context}
def get_result(self): cr, uid, context = self.env.args if context is None: context = {} context = dict(context) data = self.read()[0] start_date = data.get('date_start', False) end_date = data.get('date_end', False) if start_date and end_date and end_date < start_date: raise Warning(_("End date should be greater than start date!")) res_user = self.env["res.users"].browse(uid) # Create Inventory Export report in Excel file. workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet 1') font = xlwt.Font() font.bold = True header = xlwt.easyxf('font: bold 1, height 280') # start_date = datetime.strptime(str(context.get("date_from")), DEFAULT_SERVER_DATE_FORMAT) # start_date_formate = start_date.strftime('%d/%m/%Y') # end_date = datetime.strptime(str(context.get("date_to")), DEFAULT_SERVER_DATE_FORMAT) # end_date_formate = end_date.strftime('%d/%m/%Y') # start_date_to_end_date = tools.ustr(start_date_formate) + ' To ' + tools.ustr(end_date_formate) style = xlwt.easyxf('align: wrap yes') worksheet.row(0).height = 500 worksheet.row(1).height = 500 for x in range(0, 41): worksheet.col(x).width = 6000 borders = xlwt.Borders() borders.top = xlwt.Borders.MEDIUM borders.bottom = xlwt.Borders.MEDIUM border_style = xlwt.XFStyle() # Create Style border_style.borders = borders border_style1 = xlwt.easyxf('font: bold 1') border_style1.borders = borders style = xlwt.easyxf('align: wrap yes', style) ids_location = [] ids_categ = [] ids_product = [] where_end_date_awal = " sm.date is null " where_start_date = " 1=1 " if start_date: where_start_date = " sm.date + interval '7 hour' >= '%s 00:00:00' " % start_date where_end_date_awal = " sm.date + interval '7 hour' < '%s 00:00:00' " % start_date where_end_date = " 1=1 " if end_date: where_end_date = " sm.date + interval '7 hour' <= '%s 23:59:59'" % end_date where_location = " 1=1 " if ids_location: where_location = """(sm.location_id in %s or sm.location_dest_id in %s)""" % (str( tuple(ids_location)).replace( ',)', ')'), str(tuple(ids_location)).replace(',)', ')')) where_categ = " 1=1 " if ids_categ: where_categ = "pt.categ_id in %s" % str(tuple(ids_categ)).replace( ',)', ')') where_product = " 1=1 " if ids_product: where_product = "pp.id in %s" % str(tuple(ids_product)).replace( ',)', ')') query = """ SELECT pp.default_code as kode_barang, pt.name as nama_barang, uu.name as satuan, sml.qty_done, sml.state, sm.name FROM stock_move sm LEFT JOIN product_product pp ON pp.id=sm.product_id LEFT JOIN uom_uom uu ON uu.id=sm.product_uom LEFT JOIN product_template pt ON pt.id=pp.product_tmpl_id INNER JOIN stock_move_line sml ON sm.id=sml.move_id where """ + where_start_date + """ and """ + where_end_date + """ -- AND sm.state = 'assigned' and qty_done = 0 and sm.raw_material_production_id is not null AND sml.state = 'assigned' and sm.production_id is not null AND sml.qty_done != 0 ORDER BY sm.id DESC """ list_data = [] company = self.env.user.company_id.name start_date_format = start_date.strftime('%d/%m/%Y') end_date_format = end_date.strftime('%d/%m/%Y') self._cr.execute(query) vals = self._cr.fetchall() no = 1 for val in vals: list_data.append({ 'kode_barang': val[0], 'nama_barang': val[1], 'satuan': val[2], 'jumlah': val[3], 'keterangan': val[5] }) no += 1 hasil = list_data return hasil
def Report(): workbook = xlwt.Workbook() staly_1 =xlwt.XFStyle() font1 = xlwt.Font() font1.name = u'宋体' font1.bold = False font1.colour_index = 0 staly_1.font = font1 sheet1=workbook.add_sheet(u'体检报告信息',cell_overwrite_ok=True) sheet2=workbook.add_sheet(u'体检分科检查表',cell_overwrite_ok=True) sheet3=workbook.add_sheet(u'体检检查项目表',cell_overwrite_ok=True) sheet4=workbook.add_sheet(u'体检业务表',cell_overwrite_ok=True) sheet5=workbook.add_sheet(u'体检结论表',cell_overwrite_ok=True) sheet6=workbook.add_sheet(u'疾病诊断与阳性筛查',cell_overwrite_ok=True) sheet1_data =[ u"序号",u"体检号",u"档案号",u"身份证",u"姓名",u"拼音首码",u"0男1女",u"出生日期",u"年龄" ,u"电子邮件",u"电话",u"登记日期",u"体检完成日期",u"综述",u"结论",u"建议",u"预约单号"] #体检报告信息 sheet1_loog = len(sheet1_data) for a in range(len(sheet1_data)): p = sheet1_data[a]; sheet1.write(0,a,sheet1_data[a],staly_1) sheet2_data = [u"序号",u"体检号",u"科室编号",u"科室名称",u"科室类型",u"科室标准代码",u"科室小结",u"科室医生", u"录入日期"]#体检分科检查表 sheet2_loog = len(sheet2_data) for a in range(len(sheet2_data)): p = sheet2_data[a]; sheet2.write(0,a,sheet2_data[a],staly_1) sheet3_data = [u"序号",u"体检号",u"收费项目代码",u"科室编号",u"科室标准代码",u"检查项目编号",u"检查项目标准代码", u"检查项目名称",u"检查项目单位",u"参考范围",u"检查结果描述",u"检验结果",u"数字结果",u"结果说明",u"排序"]#体检检查项目表 sheet3_loog = len(sheet3_data) for a in range(len(sheet3_data)): p = sheet3_data[a]; sheet3.write(0,a,sheet3_data[a],staly_1) sheet4_data = [u"序号",u"体检号",u"收费项目代码",u"收费项目名称",u"科室标准代码",u"科室编码",u"检查医生名称", u"检查日期"]#体检业务表 sheet4_loog = len(sheet4_data) for a in range(len(sheet4_data)): p = sheet4_data[a]; sheet4.write(0,a,sheet4_data[a],staly_1) sheet5_data = [u"序号",u"体检号",u"科室编号",u"科室标准代码",u"结论词标准代码",u"结论编号",u"结论名称",]#体检结论表 sheet5_loog = len(sheet5_data) for a in range(len(sheet5_data)): p = sheet5_data[a]; sheet5.write(0,a,sheet5_data[a],staly_1) sheet6_data = [u"序号",u"体检号",u"疾病诊断与阳性筛查",u"描述",u"阳性标准代码",u"阳性标准名称"]#疾病诊断与阳性筛查 sheet6_loog = len(sheet6_data) for a in range(len(sheet6_data)): p = sheet6_data[a]; sheet6.write(0,a,sheet6_data[a],staly_1) # ————————————————————————————————内容—————————————————————————————— user = sql_user() chickID =user[0] #体检报告号 user_name = user[1].decode('UTF-8')#体检报告姓名 print chickID,user_name item_number = "00330001" #体检项目编号 item_name = user[1] #体检项目名 code = ident_generator() print code item_unit = "U/ml" #项目单位 result = '62' #项目值 sheet1_data =[ "1",chickID,chickID,code,user_name,"","1","19701216","24" ,"","18011111132","20170510","20170510",u"综述,综述综述","","","P121702050028"] #体检报告信息 sheet1_loog = len(sheet1_data) for a in range(len(sheet1_data)): p = sheet1_data[a]; sheet1.write(1,a,sheet1_data[a],staly_1) sheet2_data = ["1",chickID,"0024",u"心电图","0","0024",u"******",u"刘大有", "20170209"]#体检分科检查表 sheet2_loog = len(sheet2_data) for a in range(len(sheet2_data)): p = sheet2_data[a]; sheet2.write(1,a,sheet2_data[a],staly_1) #------------------------------------------项目表----------------------------------------- cur = sql_conn().cursor() aa = cur.execute("select*from student") for i in range(aa): pp = cur.fetchone() item_number = pp[1] item_name = pp[2] item_unit=pp[3] result = '62' #项目值 code = ident_generator() sheet3_data = ['1',chickID,u"A709","0024","0024",item_number,item_number, item_name,'',"0-28","",item_unit,item_unit,"",""]#体检检查项目表 for a in range(len(sheet3_data)): p = sheet3_data[a] sheet3.write((i+1),a,sheet3_data[a],staly_1) cur.close() #------------------------------------------项目表----------------------------------------- sheet4_data = ["1",chickID,u"A709",u"肝功1号",u"0024",u"0024",u"刘大有", "20170210"]#体检业务表 sheet4_loog = len(sheet4_data) for a in range(len(sheet4_data)): p = sheet4_data[a]; sheet4.write(1,a,sheet4_data[a],staly_1) sheet6_data = ["1",chickID,u"测试",u"建议:忌烟酒","G288" ,u""]#疾病诊断与阳性筛查 sheet6_loog = len(sheet6_data) for a in range(len(sheet6_data)): p = sheet6_data[a]; sheet6.write(1,a,sheet6_data[a],staly_1) Room = "D:\\\\"+chickID+".xls" workbook.save(Room) # return Room,user_name,code return Room
def savefile(outfilename, dataval): wb = xlwt.Workbook() ws = wb.add_sheet('SYN信息') # title设置 titlestyle = xlwt.XFStyle() # 设置字体 titlefont = xlwt.Font() titlefont.name = 'SimSun' titlefont.height = 20 * 11 titlestyle.font = titlefont # 标题单元格对齐方式 titlealignment = xlwt.Alignment() # 水平对齐方式和垂直对齐方式 titlealignment.horz = xlwt.Alignment.HORZ_CENTER titlealignment.vert = xlwt.Alignment.VERT_CENTER # 自动换行 titlealignment.wrap = 1 titlestyle.alignment = titlealignment # 单元格背景设置 titlepattern = xlwt.Pattern() titlepattern.pattern = xlwt.Pattern.SOLID_PATTERN titlepattern.pattern_fore_colour = xlwt.Style.colour_map[ 'sky_blue'] # 设置单元格背景颜色为蓝 titlestyle.pattern = titlepattern # 单元格边框 titileborders = xlwt.Borders() titileborders.left = 1 titileborders.right = 1 titileborders.top = 1 titileborders.bottom = 1 titileborders.left_colour = 0x40 titlestyle.borders = titileborders # 设置标题 ws.write(0, 0, '序号', titlestyle) ws.write(0, 1, '端口号', titlestyle) ws.write(0, 2, 'IP地址', titlestyle) ws.write(0, 3, '数量', titlestyle) ws.write(0, 4, '动作', titlestyle) ws.write(0, 5, '备注', titlestyle) # 设置列高度 ws.row(0).height_mismatch = True ws.row(0).height = int(20 * 40) # 设置列宽度 ws.col(0).width = int(256 * 8) ws.col(1).width = int(256 * 10) ws.col(2).width = int(256 * 46) ws.col(3).width = int(256 * 17) ws.col(4).width = int(256 * 17) ws.col(5).width = int(256 * 17) contentstyle = xlwt.XFStyle() contentalignment = xlwt.Alignment() contentalignment.wrap = 1 # 水平对齐方式和垂直对齐方式 contentalignment.horz = xlwt.Alignment.HORZ_CENTER contentalignment.vert = xlwt.Alignment.VERT_CENTER contentstyle.alignment = contentalignment # 写入数据 for i in range(len(result)): serials = i + 1 ws.write(serials, 0, str(serials), contentstyle) ws.write(serials, 1, dataval[i][0], contentstyle) ws.write(serials, 2, dataval[i][1], contentstyle) ws.write(serials, 3, dataval[i][2], contentstyle) ws.write(serials, 4, '开启', contentstyle) # 保存excel文件 wb.save(outfilename)
def print_report(self): vals = {} if self.non_moving_product_days <= 0: raise Warning(_("Please Enter Days To Proceed.")) final_non_moving_data = {'days': self.non_moving_product_days} warehouse_ids_list = self.warehouse_ids or self.env[ 'stock.warehouse'].search([]) sale_category_ids_list = self.sale_category_ids or self.env[ 'product.category'].search([]) if self.warehouse_ids: warehouse_name = ", ".join( [each.name for each in self.warehouse_ids]) final_non_moving_data.update({'warehouse': warehouse_name}) LocationObj = self.env['stock.location'].sudo() customer_location_ids = LocationObj.search([('usage', '=', 'customer') ]).ids for each in warehouse_ids_list: location_ids = LocationObj.search([('usage', '=', 'internal'), ('location_id', 'child_of', each.lot_stock_id.id)]) query = """ select product_id,location_id from stock_move where location_id in %s and state = 'done' and date >= '%s 00:00:00' and date <= '%s 23:59:59' and location_dest_id in %s """ % ("(%s)" % ",".join(map(str, location_ids.ids)), (date.today() - timedelta(days=self.non_moving_product_days) ), date.today(), "(%s)" % ",".join(map(str, customer_location_ids))) self._cr.execute(query) stocK_move_data = self._cr.dictfetchall() product_list = [each.get('product_id') for each in stocK_move_data] stock_quant_query = ''' select sq.product_id,pt.default_code,sl.id,pt.name,pc.name as category,sq.quantity,sl.complete_name from stock_quant sq LEFT JOIN stock_location sl on sl.id = sq.location_id LEFT JOIN product_product pp on pp.id = sq.product_id LEFT JOIN product_template pt on pt.id = pp.product_tmpl_id LEFT JOIN product_category pc on pc.id = pt.categ_id where pt.categ_id in %s and sq.location_id in %s and sq.product_id %s ''' % ("(%s)" % ",".join(map(str, sale_category_ids_list.ids)), "(%s)" % ",".join(map(str, location_ids.ids)), "not in (%s)" % ",".join(map(str, product_list)) if product_list else "is not null") self._cr.execute(stock_quant_query) stock_quant_data = self._cr.dictfetchall() productobj = self.env['product.product'] if self.group_by_category: for data in stock_quant_data: product = productobj.browse(data.get('product_id')) data.update({ 'sell_price': product.list_price, 'unit_price': product.standard_price }) if data.get('category') not in vals: vals[data.get('category')] = [data] else: vals[data.get('category')].append(data) else: for data in stock_quant_data: product = productobj.browse(data.get('product_id')) data.update({ 'sell_price': product.list_price, 'unit_price': product.standard_price }) if each.id not in vals: vals[each.id] = [data] else: vals[each.id].append(data) vals = OrderedDict(sorted(vals.items(), key=lambda t: t[0])) final_non_moving_data.update({ 'data': vals, 'group_by': self.group_by_category, 'currency': self.env.user.company_id.currency_id.symbol, 'current_date': date.today().strftime('%d/%m/%Y') }) if not vals: raise Warning(_('NO Stock Found')) if self.report_type == 'pdf': if self.env.context.get('send_mail'): final_non_moving_data.update({'break': False}) vals_new = {'products': final_non_moving_data} template_id = self.env.ref( 'flexiretail_com_advance.non_moving_stock_report') pdf = template_id.render_qweb_pdf(self, data=vals_new) values = base64.b64encode(pdf[0]) attachment_id = self.env['ir.attachment'].sudo().create({ 'datas': values, 'name': 'Non Moving Stock Report', 'datas_fname': 'Non Moving Stock.pdf' }) return attachment_id else: final_non_moving_data.update({'break': True}) vals_new = {'products': final_non_moving_data} return self.env.ref( 'flexiretail_com_advance.non_moving_stock_report' ).report_action(self, data=vals_new) elif self.report_type == 'xls': workbook = xlwt.Workbook() stylePC = xlwt.XFStyle() worksheet = workbook.add_sheet('Non Moving Stock Report') bold = xlwt.easyxf( "font: bold on; pattern: pattern solid, fore_colour gray25;") alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_CENTER stylePC.alignment = alignment alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_CENTER alignment_num = xlwt.Alignment() alignment_num.horz = xlwt.Alignment.HORZ_RIGHT horz_style = xlwt.XFStyle() horz_style.alignment = alignment_num align_num = xlwt.Alignment() align_num.horz = xlwt.Alignment.HORZ_RIGHT horz_style_pc = xlwt.XFStyle() horz_style_pc.alignment = alignment_num style1 = horz_style font = xlwt.Font() font1 = xlwt.Font() borders = xlwt.Borders() borders.bottom = xlwt.Borders.THIN font.bold = True font1.bold = True font.height = 500 stylePC.font = font style1.font = font1 stylePC.alignment = alignment pattern = xlwt.Pattern() pattern1 = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern1.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['gray25'] pattern1.pattern_fore_colour = xlwt.Style.colour_map['gray25'] stylePC.pattern = pattern style1.pattern = pattern worksheet.write_merge(0, 1, 0, 5, 'Non Moving Stock Report', style=stylePC) worksheet.col(2).width = 5600 worksheet.write_merge( 3, 3, 0, 2, 'Non Moving Product in Last ' + str(self.non_moving_product_days) + ' Days', bold) row = 5 if self.warehouse_ids: worksheet.write(row, 0, 'Warehouses', bold) worksheet.write_merge(row, row, 1, 2, warehouse_name) row = row + 2 list1 = [ 'Default Code', 'Product', 'Location', 'Quantity', 'Cost Price', 'Sale Price' ] if self.group_by_category == True: for j in vals: worksheet.write(row, 0, 'Category', bold) worksheet.write(row, 1, j) row = row + 1 for i in range(len(list1)): if list1[i] in ['Cost Price', 'Sale Price']: worksheet.write( row, i, list1[i] + "(" + self.env.user.company_id.currency_id.symbol + ")", style1) elif list1[i] == 'Quantity': worksheet.write(row, i, list1[i], style1) else: worksheet.write(row, i, list1[i], bold) row = row + 1 for dict in vals.get(j): worksheet.col(0).width = 4000 worksheet.write(row, 0, dict.get('default_code')) worksheet.col(1).width = 6000 worksheet.write(row, 1, dict.get('name')) worksheet.col(2).width = 8000 worksheet.write(row, 2, dict.get('complete_name')) worksheet.write(row, 3, dict.get('quantity'), horz_style_pc) worksheet.col(4).width = 4500 worksheet.write( row, 4, str('%.2f' % (dict.get('unit_price'))) + str(self.env.user.company_id.currency_id.symbol), horz_style_pc) worksheet.col(5).width = 4500 worksheet.write( row, 5, str('%.2f' % (dict.get('sell_price'))) + str(self.env.user.company_id.currency_id.symbol), horz_style_pc) row = row + 1 row = row + 1 else: for i in range(len(list1)): if list1[i] in ['Cost Price', 'Sale Price']: worksheet.write( row, i, list1[i] + "(" + self.env.user.company_id.currency_id.symbol + ")", style1) elif list1[i] == 'Quantity': worksheet.write(row, i, list1[i], style1) else: worksheet.write(row, i, list1[i], bold) row = row + 1 for j in vals: for dict in vals.get(j): worksheet.col(0).width = 4000 worksheet.write(row, 0, dict.get('default_code')) worksheet.col(1).width = 6000 worksheet.write(row, 1, dict.get('name')) worksheet.col(2).width = 8000 worksheet.write(row, 2, dict.get('complete_name')) worksheet.write(row, 3, dict.get('quantity'), horz_style_pc) worksheet.col(4).width = 4500 worksheet.write( row, 4, str('%.2f' % (dict.get('unit_price'))) + str(self.env.user.company_id.currency_id.symbol), horz_style_pc) worksheet.col(5).width = 4500 worksheet.write( row, 5, str('%.2f' % (dict.get('sell_price'))) + str(self.env.user.company_id.currency_id.symbol), horz_style_pc) row = row + 1 file_data = BytesIO() workbook.save(file_data) self.write({ 'data': base64.encodestring(file_data.getvalue()), 'file_name': 'Report - %s.xls' % (datetime.now().strftime('%Y-%m-%d %H:%M:%S')), 'state': 'done' }) attachment_id = self.env['ir.attachment'].sudo().create({ 'datas': self.data, 'name': self.file_name, 'datas_fname': 'Non Moving Stock.xls' }) if self.env.context.get('send_mail'): return attachment_id else: return { 'name': ('Non Moving Stock'), 'view_type': 'form', 'view_mode': 'form', 'res_model': 'non.moving.stock', 'res_id': self.id, 'target': 'new', 'type': 'ir.actions.act_window', }
def __init__(self): font0 = xlwt.Font() font0.name = 'Times New Roman' font0.colour_index = 2 font0.height = 400 font0.bold = True self.font0 = font0 style0 = xlwt.XFStyle() style0.font = font0 self.style0 = style0 font1 = xlwt.Font() font1.name = 'Times New Roman' font1.height = 300 self.font1 = font1 style1 = xlwt.XFStyle() style1.font = font1 self.style1 = style1 self.date = datetime.date.today().strftime("%d.%m.%Y") # Если таблица была до этого if os.path.exists(constants.dir): self.users = [] self.tags = [] self.ids = [] rb = xlrd.open_workbook(constants.dir, formatting_info=True) sheet = rb.sheet_by_index(0) for tag in sheet.row_values(0): self.tags.append(tag) # Считываем данные из таблицы lis = self.tags for i in range(1, sheet.nrows): self.ids.append(int(sheet.row_values(i)[1])) j = 0 user = {} for col in sheet.row_values(i): l = lis[j] user[l] = col j += 1 self.users.append(user) # Преобразование тегов self.tags = self.tags[7:-1] for tag in constants.key_words: if not tag in self.tags: self.tags.append(tag) self.wb = xlwt.Workbook() ws = self.wb.add_sheet('Users', cell_overwrite_ok=True) ws.write(0, 0, "Дата", style0) ws.write(0, 1, "ID", style0) ws.write(0, 2, "Имя", style0) ws.write(0, 3, "Фамилия", style0) ws.write(0, 4, "Телефон", style0) ws.write(0, 5, "Дата рождения", style0) ws.write(0, 6, "Город", style0) i = 7 if os.path.exists(constants.dir): for tag in self.tags: ws.write(0, i, tag, style0) i += 1 else: for tag in constants.key_words: ws.write(0, i, tag, style0) i += 1 ws.write(0, i, "Статус 1 или 0", style0) self.ws = ws
#保存每个用户的最小值。 while (j > 0): maxAttendanceDate.setdefault(table.cell(j, 3).value.encode("utf-8")+(str(datetime.datetime.fromtimestamp(((table.cell(j, 8).value)-70*365-19)*86400-8*3600))).split(" ")[0],datetime.datetime.fromtimestamp(((table.cell(j, 8).value)-70*365-19)*86400-8*3600)); j = j -1; """ 设置Excel格式。 """ newWorkbook = xlwt.Workbook(); newTable = newWorkbook.add_sheet('Attendance'.decode(),cell_overwrite_ok=True); # 表头字体样式以及大小。 headStyle = xlwt.XFStyle(); headFont = xlwt.Font(); headFont.bold = True; headFont.height = 360; headFont.name = u'微软雅黑'; headStyle.font = headFont; # 表格内容样式以及大小。 style = xlwt.XFStyle(); font = xlwt.Font(); font.height = 300; style.font = font; # 表格内时间数据格式。 style = xlwt.XFStyle(); style.num_format_str = 'yyyy/m/d h:mm AM/PM'