#!/usr/bin/env python # -*- coding:utf-8 -*- import os,xlsxwriter,datetime import ConfigParser from send_mail import send_mail path = os.path.split(os.path.realpath(__file__))[0] ###***获取配置文件中的变量 conf_file = path + "\\"+"conf.ini" config=ConfigParser.ConfigParser() config.read(conf_file)
class RepotData(object): def day_file(self,d,project,sheet_list_d,project_list_code): path = os.path.split(os.path.realpath(__file__))[0] day_path = path+"\\"+project+"\\"+project_list_code+"\\" if os.path.exists(day_path) == 0: os.mkdir(day_path) re_path = day_path+"\\day\\" if os.path.exists(re_path) == 0: os.mkdir(re_path) # 本月汇总 now_month = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y-%m') # 今日汇总 now_day = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y-%m-%d') dayYMD = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y%m%d') #now = now_day.strftime('%Y-%m-%d') #格式化输出 # 创建表单实例 project_Zn = config.get(project,'projcet_Zn') CNNAME = config.get(project_list_code,"CNNAME") file = u"%s(%s)%s.xlsx"%(project_Zn,CNNAME,dayYMD) # 路径+文件名字 work = xlsxwriter.Workbook(re_path+"/"+file) # 生成工作表,默认表名Sheet1 worksheet1 = work.add_worksheet(u'汇总') worksheet2 = work.add_worksheet(u'访问数据') worksheet3 = work.add_worksheet(u'点播数据') worksheet4 = work.add_worksheet(u'分时段订购') #worksheet5 = work.add_worksheet(u'订购失败统计') # 设置单元格格式 #border：边框，align:对齐方式，bg_color：背景颜色，font_size：字体大小，bold：字体加粗 top = work.add_format({'border':1,'align':'center','bg_color':'cccccc','font_size':12,'bold':True}) top_two = work.add_format({'border':1,'align':'center','bg_color':'green','font_size':10,'bold':True}) bold = work.add_format({'bold':True,'border':1,'align':'center','font_size':12}) # 开始写入日报模版 for sheet in sheet_list_d: if sheet == "worksheet1": # 设置标题格式,合并 worksheet1.merge_range('A1:P1',u'基础数据',top) worksheet1.merge_range('Q1:T1',u'分析数据',top) # 设置单元格长度 worksheet1.set_column("A:S",13) worksheet1.set_column("H:J",18) worksheet1.set_column("M:O",18) TopTwo={ "A2":u"日期", "B2":u"地市", "C2":u"订购成功数", "D2":u"取消数", "E2":u"订购失败数", "F2":u"用户访问量", "G2":u"访问用户数", "H2":u"新用户访问用户数", "I2":u"非包月访问用户数", "J2":u"包月访问用户数", "K2":u"播放量", "L2":u"点播用户数", "M2":u"新用户点播用户数", "N2":u"非包月点播用户数", "O2":u"包月点播用户数", "P2":u"播放时长", "Q2":u"留存用户数", "R2":u"包月净增", "S2":u"订购成功率", "T2":u"订购转化率" } for i in TopTwo: worksheet1.write(i,TopTwo[i],top) # Module Successfly # 基础数据 AS=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T'] count = 3 data = d[sheet] lcyhs=0 for vlue_list in data[0]: vlue_list = list(vlue_list) #获取订购成功数 djcgs = vlue_list[2] #获取订购数 djs = vlue_list[0] # 订购 净增 djjz = djcgs-vlue_list[3] vlue_list.append(djjz) # 订购 成功率 if djcgs == 0 and djs == 0: djcgl = "0.00%" vlue_list.append(djcgl) elif djs == 0: djcgl = "0.00%" vlue_list.append(djcgl) else: djcgl = str(float("%.2f"%(djcgs/djs))*100)+ "%" vlue_list.append(djcgl) # 订购 转化率 if djcgs == 0 and vlue_list[6] == 0: djzhl = "0.00%" vlue_list.append(djzhl) elif vlue_list[6] == 0: djzhl = "0.00%" vlue_list.append(djzhl) else: djzhl = str(float("%.2f"%(djcgs/vlue_list[6]))*100)+ "%" vlue_list.append(djzhl) for index,vlue in enumerate(vlue_list[1:]) : eval(sheet).write("%s%s"%(AS[index+1],count),vlue,bold) count = count + 1 eval(sheet).write("%s%s"%(AS[0],count-1),now_day,bold) worksheet1.write("A3",now_month,bold) worksheet1.write("B3",u"本月汇总",bold) #worksheet1.write("Q3",lcyhs,bold) elif sheet == "worksheet2": # 访问数据 worksheet2.set_column("A:J",18) TopTwo={ "A1":u"日期", "B1":u"地市", "C1":u"入口标识", "D1":u"访问总量", "E1":u"访问用户数", "F1":u"非包月访问用户数", "G1":u"包月访问用户数", "H1":u"订购发起次数", "I1":u"订购成功数", "J1":u"订购转化率" } for i in TopTwo: worksheet2.write(i,TopTwo[i],top) data = d[sheet] AS = ['A', 'B', 'C', 'D', 'E','F','G','H','I','J'] count = 2 PM = 0 for vlue_list in data[0]: vlue_list = list(vlue_list) with open(path+"/accid.ini","r") as f: ff = f.readlines() for line in ff: #print(line.split()) ssid = line.split()[0] ssid_name = line.split()[1] if vlue_list[1] == ssid: vlue_list[1] = ssid_name continue for index,vlue in enumerate(vlue_list): eval(sheet).write("%s%s" % (AS[index + 1], count), vlue, bold) count = count + 1 eval(sheet).write("%s%s" % (AS[0], count - 1), now_day, bold) elif sheet == "worksheet3": # 点播数据 取值 前100名 worksheet3.set_column("A:F",13) worksheet3.set_column("B:B",20) TopTwo={ "A1":u"日期", "B1":u"资源名称", "C1":u"播放量", "D1":u"点播用户数", "E1":u"播放时长", "F1":u"排名", } for i in TopTwo: worksheet3.write(i,TopTwo[i],top) # 写入数据 data = d[sheet] AS = ['A', 'B', 'C', 'D', 'E', 'F'] count = 2 PM = 0 for vlue_list in data[0]: PM = PM + 1 vlue_list = list(vlue_list) vlue_list.append(PM) for index,vlue in enumerate(vlue_list) : eval(sheet).write("%s%s"%(AS[index+1],count),vlue,bold) count = count + 1 eval(sheet).write("%s%s"%(AS[0],count-1),now_day,bold) elif sheet == "worksheet4": worksheet4.set_column("A:G",16) TopTwo={ "A1":u"日期", "B1":u"小时", "C1":u"订购发起次数", "D1":u"订购成功数", "E1":u"访问数", "F1":u"非包月用户数", "G1":u"订购转化率", } for i in TopTwo: worksheet4.write(i,TopTwo[i],top) data = d[sheet] AS = ['A', 'B', 'C', 'D', 'E', 'F','G'] count = 2 for vlue_list in data[0]: vlue_list = list(vlue_list) # if vlue_list[1] == 0 and vlue_list[3] == 0: # djzhl = 0 # vlue_list.append(djzhl) # elif vlue_list[3] == 0: # djzhl = "0" # vlue_list.append(djzhl) # else: # djzhl = str(float("%.2f" % (vlue_list[1] / vlue_list[3])) * 100) + "%" # vlue_list.append(djzhl) for index, vlue in enumerate(vlue_list): eval(sheet).write("%s%s" % (AS[index + 1], count), vlue, bold) count = count + 1 eval(sheet).write("%s%s" % (AS[0], count - 1), now_day, bold) work.close() return re_path+file,file