我python不是很好,所以我也是仿着各位大佬们抄的,使用的是python3,然后剩下的,环境之类的就自己看着办了,我下面直接上代码了,主要是给我自己复习用的了哈,执行肯定是没有问题了的,剩下的具体看报错了,因为后面需要改成当天的日期来生成excel 文件,本来之前我是用shell 来改名的,后来想了下,干脆尝试下看看 python 能不能直接改,能改就最好了,结果最后,确实能改,哈哈
#!/usr/bin/env python3 # coding: utf-8 import os import xlwt import pymysql import datetime #####特别注意事项:class中的def函数,一定要跟上面的位置进行保持一致,缩进中千万不要用tab,一定要用空格,而且要和上面的内容格式保持一致,不然有的你找错!!!!!! # 在线客服数据统计 class MysqlToExcel(object): def __init__(self): self.host = '127.0.0.1' self.user = 'root' self.passwd = 'gxbw2020' self.db_name = 'contactcenter_zhengshi' self.port = 3306 self.time_now = datetime.datetime.now().strftime("%Y-%m-%d") self.file_name = self.time_now + '在线客服数据统计.xls' ###########查询sheet1############################################## def get_query_results1(self): sql1 = "select l.record_date 记录日期,u.uname 用户名,o.name 部门名,op.name 区县,opp.name 市级 from uk_agentstatus_log l left join uk_user u on l.user_id = u.id left join uk_organ o on o.id = u.ORGAN left join uk_organ op on o.parent = op.id left join uk_organ opp on op.parent = opp.id where l.record_date = (select date_format(now(),'%Y-%m-%d')) order by l.record_date,opp.name,op.name,o.name" conn = pymysql.connect( host=self.host, user=self.user, passwd=self.passwd, port=self.port, database=self.db_name, charset='utf8', cursorclass=pymysql.cursors.DictCursor ) cur = conn.cursor() # 创建游标 cur.execute(sql1) # 执行sql命令 result1 = cur.fetchall() # 获取执行的返回结果 # print(result) cur.close() conn.close() # 关闭mysql 连接 return result1 ###########查询sheet2#################################### def get_query_results2(self): sql2 = "select uo3.name as 市级,uo2.name as 县级,count(s.aiservice) 人工接入量,0 as '机器人接入量' from uk_agentservice s left join uk_user u on s.agentno=u.id left join uk_organ uo1 on uo1.id=u.organ left join uk_organ uo2 on uo2.id=uo1.PARENT left join uk_organ uo3 on uo3.id=uo2.parent where s.createtime BETWEEN curdate( ) AND date_sub(curdate(),interval - 1 day) and s.aiservice='0' and s.agentno != ' ' and u.id is not null group by uo2.name union all select'机器人' as 市级,'机器人' as 县区,0 as '人工接入量',count(s.aiservice) 机器人接入量 from uk_agentservice s where s.createtime BETWEEN curdate( ) AND date_sub(curdate(),interval - 1 day) and s.aiservice='1'" conn = pymysql.connect( host=self.host, user=self.user, passwd=self.passwd, port=self.port, database=self.db_name, charset='utf8', cursorclass=pymysql.cursors.DictCursor ) cur = conn.cursor() # 创建游标 cur.execute(sql2) # 执行sql命令 result2 = cur.fetchall() # 获取执行的返回结果 # print(result) cur.close() conn.close() # 关闭mysql 连接 return result2 ###############查询sheet3##################################################### def get_query_results3(self): sql3 = "select u.uname as '用户名',count(s.result = '很满意' or null) as '非常满意',count(s.result = '满意' or null) as '满意',count(s.result = '不满意' or null) as '不满意' from uk_agentstatis s left join uk_user u on u.id = s.agentno where s.time BETWEEN curdate( ) AND date_sub(curdate(),interval - 1 day) group by u.uname;" conn = pymysql.connect( host=self.host, user=self.user, passwd=self.passwd, port=self.port, database=self.db_name, charset='utf8', cursorclass=pymysql.cursors.DictCursor ) cur = conn.cursor() # 创建游标 cur.execute(sql3) # 执行sql命令 result3 = cur.fetchall() # 获取执行的返回结果 cur.close() conn.close() # 关闭mysql 连接 return result3 #################查询sheet4###################################################### def get_query_results4(self): sql4 = "select distinct uo2.name as '未在线的县区' from uk_user u left join uk_organ uo1 on uo1.id=u.organ left join uk_organ uo2 on uo2.id=uo1.parent where uo2.name not in( select DISTINCT uo2.name from (select organ from uk_user where id in (select user_id from uk_agentstatus_log where record_date = (select date_format(now(),'%Y-%m-%d')))) t left join uk_organ uo1 on t.organ=uo1.id left join uk_organ uo2 on uo1.parent=uo2.id where uo1.name like '%局端%' and uo1.name not like '%二线%') and uo1.name like '%局端%' and uo2.name != '广西壮族自治区' and uo1.name not like '%二线%';" conn = pymysql.connect( host=self.host, user=self.user, passwd=self.passwd, port=self.port, database=self.db_name, charset='utf8', cursorclass=pymysql.cursors.DictCursor ) cur = conn.cursor() # 创建游标 cur.execute(sql4) # 执行sql命令 result4 = cur.fetchall() # 获取执行的返回结果 cur.close() conn.close() # 关闭mysql 连接 return result4 #################查询sheet5######################################################## def get_query_results5(self): sql5 = "select count(uac.nsrsbh) as '转QQ数量', usa.swjgdm as '税务机关代码', usa.swjgmc as '税务机关名称' from uk_agent_connect uac left join uk_sw_area usa on left(uac.swjgdm,7) = left(usa.swjgdm,7) where uac.requestTime like (select concat(curdate(),'%')) and uac.serviceType = 'qq' and uac.nsrsbh is not null and uac.nsrsbh != 'undefined' and uac.swjgdm not like '%,%' and usa.swjgdm is not null and TIME(uac.requestTime) between '08:30:00' and '17:00:00' and TIME(uac.requestTime) not between '12:00:00' and '15:00:00' group by usa.swjgdm order by count(uac.nsrsbh) desc;" conn = pymysql.connect( host=self.host, user=self.user, passwd=self.passwd, port=self.port, database=self.db_name, charset='utf8', cursorclass=pymysql.cursors.DictCursor ) cur = conn.cursor() # 创建游标 cur.execute(sql5) # 执行sql命令 result5 = cur.fetchall() # 获取执行的返回结果 cur.close() conn.close() # 关闭mysql 连接 return result5 #####################生成表格############################################## def generate_table(self): """ 生成excel表格 :return: """ # 删除已存在的文件 if os.path.exists(self.file_name): os.remove(self.file_name) result1 = self.get_query_results1() result2 = self.get_query_results2() result3 = self.get_query_results3() result4 = self.get_query_results4() result5 = self.get_query_results5() ## print(result) #if not result1: # print("查询结果为空") # return False # 创建excel对象 f = xlwt.Workbook() sheet1 = f.add_sheet('Sheet1', cell_overwrite_ok=True) sheet2 = f.add_sheet('Sheet2', cell_overwrite_ok=True) sheet3 = f.add_sheet('Sheet3', cell_overwrite_ok=True) sheet4 = f.add_sheet('Sheet4', cell_overwrite_ok=True) sheet5 = f.add_sheet('Sheet5', cell_overwrite_ok=True) # 列字段 column_names1 = ['记录日期','用户名','部门名','区县','市级'] column_names2 = ['市级','县级','人工接入量','机器人接入量'] column_names3 = ['用户名','非常满意','满意','不满意'] column_names4 = ['未在线的县区'] column_names5 = ['转QQ数量','税务机关代码','税务机关名称'] # 写第一行,也就是列所在的行 for e in range(0, len(column_names1)): sheet1.write(0, e, column_names1[e]) for i in range(0, len(column_names2)): sheet2.write(0, i, column_names2[i]) for j in range(0, len(column_names3)): sheet3.write(0, j, column_names3[j]) for g in range(0, len(column_names4)): sheet4.write(0, g, column_names4[g]) for k in range(0, len(column_names5)): sheet5.write(0, k, column_names5[k]) # 修改一下python生成excel的时间戳格式 dateFormat = xlwt.XFStyle() dateFormat.num_format_str = 'yyyy/mm/dd' # 写入多行 num1 = 0 # 计数器 for e in result1: sheet1.write(num1 + 1, 0, e['记录日期'],dateFormat) #给记录日期进行格式的转换,不再显示5位数字 sheet1.write(num1 + 1, 1, e['用户名']) sheet1.write(num1 + 1, 2, e['部门名']) sheet1.write(num1 + 1, 3, e['区县']) sheet1.write(num1 + 1, 4, e['市级']) num1 += 1 # 自增1 num2 = 0 # 计数器 for i in result2: sheet2.write(num2 + 1, 0, i['市级']) sheet2.write(num2 + 1, 1, i['县级']) sheet2.write(num2 + 1, 2, i['人工接入量']) sheet2.write(num2 + 1, 3, i['机器人接入量']) num2 += 1 # 自增1 num3 = 0 # 计数器 for j in result3: sheet3.write(num3 + 1, 0, j['用户名']) sheet3.write(num3 + 1, 1, j['非常满意']) sheet3.write(num3 + 1, 2, j['满意']) sheet3.write(num3 + 1, 3, j['不满意']) num3 += 1 num4 = 0 # 计数器 for g in result4: sheet4.write(num4 + 1, 0, g['未在线的县区']) num4 += 1 num5 = 0 # 计数器 for k in result5: sheet5.write(num5 + 1, 0, k['转QQ数量']) sheet5.write(num5 + 1, 1, k['税务机关代码']) sheet5.write(num5 + 1, 2, k['税务机关名称']) num5 += 1 ################################################################# # 保存文件 f.save(self.file_name) # 判断文件是否存在 if not os.path.exists(self.file_name): print("生成excel失败") return False print("生成excel成功") return True if __name__ == '__main__': MysqlToExcel().generate_table()