python根据excel生成报表_python生成每日报表数据(Excel)并邮件发送的实例

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

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

#coding:utf-8

from __future__ import division

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)并邮件发送的实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持我们。

本文标题: python生成每日报表数据(Excel)并邮件发送的实例

本文地址: http://www.cppcns.com/jiaoben/python/251676.html