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)

来源：https://blog.csdn.net/ymlkl/article/details/75126374