开心学习
当前位置：脚本大全 > > 正文

pythonexcel生成报表（python生成每日报表数据Excel并邮件发送的实例）

时间：2022-03-31 00:08:58类别：脚本大全

pythonexcel生成报表

python生成每日报表数据Excel并邮件发送的实例

逻辑比较简单 ，直接上代码 

定时发送直接使用了win服务器的定时任务来定时执行脚本

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
    		•
  • #coding:utf-8
  • from __future__ import liision
  • import pymssql,sys,datetime,xlwt
  • import smtplib
  • from email.mime.text import MIMEText
  • from email.mime.multipart import MIMEMultipart
  • from email.header import Header
  •  
  • reload(sys)
  • sys.setdefaultencoding("utf-8")
  •  
  •  
  • class MSSQL:
  •   def __init__(self,host,user,pwd,db):
  •     self.host = host
  •     self.user = user
  •     self.pwd = pwd
  •     self.db = db
  •  
  •   def __GetConnect(self):
  •     if not self.db:
  •       raise(NameError,"")
  •     self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")
  •     cur = self.conn.cursor()
  •     if not cur:
  •       raise(NameError,"")
  •     else:
  •       return cur
  •  
  •   def ExecQuery(self,sql):
  •     cur = self.__GetConnect()
  •     cur.execute(sql)
  •     resList = cur.fetchall()
  •  
  •     #
  •     self.conn.close()
  •     return resList
  •  
  •   def ExecNonQuery(self,sql):
  •     cur = self.__GetConnect()
  •     cur.execute(sql)
  •     self.conn.commit()
  •     self.conn.close()
  •     
  •   
  •   def write_data_to_excel(self,name,sql):
  •  
  •     # 将sql作为参数传递调用get_data并将结果赋值给result,(result为一个嵌套元组)
  •     result = self.ExecQuery(sql)
  •     # 实例化一个Workbook()对象(即excel文件)
  •     wbk = xlwt.Workbook()
  •     # 新建一个名为Sheet1的excel sheet。此处的cell_overwrite_ok =True是为了能对同一个单元格重复操作。
  •     sheet = wbk.add_sheet('Sheet1',cell_overwrite_ok=True)
  •     # 获取当前日期，得到一个datetime对象如：(2016, 8, 9, 23, 12, 23, 424000)
  •     today = datetime.date.today()
  •     yesterday = today - datetime.timedelta(days=1)
  •     # 将获取到的datetime对象仅取日期如：2016-8-9
  •     yesterdaytime = yesterday.strftime("%Y-%m-%d")
  •     # 遍历result中的没个元素。
  •     for i in xrange(len(result)):
  •       #对result的每个子元素作遍历，
  •       for j in xrange(len(result[i])):
  •         #将每一行的每个元素按行号i,列号j,写入到excel中。
  •         sheet.write(i,j,result[i][j])
  •     # 以传递的name+当前日期作为excel名称保存。
  •     filename = name+str(yesterdaytime)+'.xls'
  •     wbk.save(filename)
  •     return filename
  •  
  •  
  •  
  • ms = MSSQL(host="122.229.*.*",user="root",pwd="root",db="test")
  •  
  • today = datetime.date.today()
  • yesterday = today - datetime.timedelta(days=1)
  • yesterdayStart = yesterday.strftime("%Y-%m-%d") + ' 00:00:00'
  • yesterdayEnd = yesterday.strftime("%Y-%m-%d") + ' 23:59:59'
  • print yesterdayStart
  • preCheckCountSuccesSql = "select count(1) FROM tb_crmorders WHERE type =1 and result = 'true' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  • preCheckUseridSuccesSql = "select count(DISTINCT userid) FROM tb_crmorders WHERE type =1 and result = 'true' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  • preCheckCountErrorSql = "select count(1) FROM tb_crmorders WHERE type =1 and result = 'false' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  • preCheckUseridErrorSql = "select count(DISTINCT userid) FROM tb_crmorders WHERE type =1 and result = 'false' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  •  
  • orderSucessCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult = 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  • orderErrorCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult IS NOT NULL and notifyresult != 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  • unsubscribeSucessCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult = 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  • unsubscribeErrorCountSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult IS NOT NULL and notifyresult != 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  •  
  • orderKadanSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult IS NULL and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  • unsubscribeKadanSql = "select count(1) FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult IS NULL and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  • preCherkKeyList =['CRM预校验成功单子数量：','CRM预校验成功账号数量：','CRM预校验失败单子数量：','CRM预校验失败账号数量：','订购的订单数 成功:','订购的订单数 失败:','订购卡单数:','退订的订单数 成功:','退订的订单数 失败:','退订卡单数:']
  • preCherkL = {'CRM预校验成功单子数量：' :preCheckCountSuccesSql ,'CRM预校验成功账号数量：' :preCheckUseridSuccesSql ,'CRM预校验失败单子数量：' :preCheckCountErrorSql ,'CRM预校验失败账号数量：' :preCheckUseridErrorSql}
  • preCherkL['订购的订单数 成功:'] = orderSucessCountSql
  • preCherkL['订购的订单数 失败:'] = orderErrorCountSql
  • preCherkL['订购卡单数:'] = orderKadanSql
  • preCherkL['退订的订单数 成功:'] = unsubscribeSucessCountSql
  • preCherkL['退订的订单数 失败:'] = unsubscribeErrorCountSql
  • preCherkL['退订卡单数:'] = unsubscribeKadanSql
  •  
  • mailMessageText =''
  •  
  • for key in preCherkKeyList:
  •   reslist = ms.ExecQuery(preCherkL[key])
  •   for i in reslist:
  •     for n in i:
  •       mailMessageText = mailMessageText + key + bytes(n) + '\n'
  •  
  •  
  • crmOrderHandleTimeSql = "select addtime , notifytime FROM tb_crmorders WHERE type =2 and action =1 and result = 'true' and notifyresult =0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  • crmunsubscribeHandleTimeSql = "select addtime , notifytime FROM tb_crmorders WHERE type =2 and action =2 and result = 'true' and notifyresult =0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'";
  •  
  • crmOrderHandle = ms.ExecQuery(crmOrderHandleTimeSql)
  • orderCount = len(crmOrderHandle)
  • if orderCount != 0:
  •   totleTime = 0
  •   for temp in crmOrderHandle:
  •     addtime = temp[0]
  •     notifytime = temp[1]
  •     
  • #     adddate = datetime.datetime.strptime(addtime,"%Y-%m-%d %H:%M:%S")
  • #     notifydate =datetime.datetime.strptime(notifytime, "%Y-%m-%d %H:%M:%S")
  •     chazhi = (notifytime - addtime).seconds / 60
  •     totleTime = float(totleTime) + float(chazhi)
  •   mailMessageText = mailMessageText + '订购平均处理时长:' + bytes(float(totleTime)/orderCount) + '分' + '\n'
  •  
  • crmunsubscribeHandle = ms.ExecQuery(crmunsubscribeHandleTimeSql)
  • subscribeCount = len(crmunsubscribeHandle)
  • if subscribeCount != 0:
  •   subscribetotleTime = 0
  •   for temp in crmunsubscribeHandle:
  •     addtime = temp[0]
  •     notifytime = temp[1]
  • #     adddate = datetime.datetime.strptime(addtime, "%Y-%m-%d %H:%M:%S")
  • #     notifydate = datetime.datetime.strptime(notifytime, "%Y-%m-%d %H:%M:%S")
  •     chazhi = (notifytime - addtime).seconds / 60
  •     subscribetotleTime = float(subscribetotleTime) + float(chazhi)
  •   mailMessageText = mailMessageText + '退订平均处理时长:' + bytes(float(subscribetotleTime)/subscribeCount) + '分' + '\n'
  • mailMessageText = mailMessageText + '附件为 ：预校验失败订单，订购/退订失败订单，卡单订单' + '\n'
  •  
  • print mailMessageText
  •  
  • #生成excel文件
  •  
  • preCheckErrorname = 'preCheckError'
  • preCerroeFile = ms.write_data_to_excel(preCheckErrorname, "select ordercode,userid,productid,action,msg FROM tb_crmorders WHERE type =1 and result = 'false' and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'")
  •  
  • orderErrorname = 'orderFalse'
  • ordererroeFile = ms.write_data_to_excel(orderErrorname, "select ordercode,userid,productid,action,sg,notifyresult,notifymsg FROM tb_crmorders WHERE type =2  and result = 'true' and notifyresult IS NOT NULL and notifyresult != 0 and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'")
  •  
  • kadanname = 'noSynchMsg'
  • kadanFile = ms.write_data_to_excel(kadanname, "select ordercode,userid,productid,sg,action FROM tb_crmorders WHERE type =2 and result = 'true' and notifyresult IS NULL and addtime BETWEEN '" + yesterdayStart +"' and '"+yesterdayEnd + "'")
  • # 第三方 SMTP 服务
  • mail_host="###@163.com" #设置服务器
  • mail_user=##"  #用户名
  • mail_pass="##"  #口令
  •  
  •  
  • sender = '###@163.com'
  • receivers = ['##@qq.com'] # 接收邮件，可设置为你的QQ邮箱或者其他邮箱
  •  
  • #创建一个带附件的实例
  • message = MIMEMultipart()
  •  
  • message['From'] = Header("测试", 'utf-8')
  • message['To'] = Header(" , ".join(receivers), 'utf-8')
  •  
  • subject = 'CRM订单日数据' + yesterday.strftime('%Y-%m-%d')
  • message['Subject'] = Header(subject, 'utf-8')
  •  
  • #邮件正文内容
  • message.attach(MIMEText(mailMessageText, 'plain', 'utf-8'))
  • #设置邮件名片（html格式）
  • # html = file('qianming.html').read().decode("utf-8")
  • # message.attach(MIMEText(html, 'html', 'utf-8'))
  •  
  • # 构造附件1，传送当前目录下的preCerroeFile 文件
  • att1 = MIMEText(open(preCerroeFile, 'rb').read(), 'base64', 'utf-8')
  • att1["Content-Type"] = 'application/octet-stream'
  • # 这里的filename可以任意写，写什么名字，邮件中显示什么名字
  • att1["Content-Disposition"] = 'attachment; filename=' + preCerroeFile
  • message.attach(att1)
  •  
  •  
  • att2 = MIMEText(open(ordererroeFile, 'rb').read(), 'base64', 'utf-8')
  • att2["Content-Type"] = 'application/octet-stream'
  • att2["Content-Disposition"] = 'attachment; filename='+ordererroeFile
  • message.attach(att2)
  •  
  •  
  • att3 = MIMEText(open(kadanFile, 'rb').read(), 'base64', 'utf-8')
  • att3["Content-Type"] = 'application/octet-stream'
  • att3["Content-Disposition"] = 'attachment; filename='+kadanFile
  • message.attach(att3)
  •  
  • try:
  •   smtpObj = smtplib.SMTP()
  •   smtpObj.connect(mail_host, 25)  # 25 为 SMTP 端口号
  •   smtpObj.login(mail_user,mail_pass)
  •   smtpObj.sendmail(sender, receivers, message.as_string())
  •   print "邮件发送成功"
  • except smtplib.SMTPException,e:
  •   print "Error: 无法发送邮件" + repr(e)
    		•

    • 以上这篇python生成每日报表数据(Excel)并邮件发送的实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持开心学习网。

    原文链接：https://blog.csdn.net/ymlkl/article/details/75126374

    标签：
    报表 Python Excel 邮件
    上一篇下一篇

    猜您喜欢

    热门推荐

    © 2021 开心学习