【详解】【记录】Python写Excel预约信息表并发送邮件

类似于本例我们写过两个脚本了，但还是遇到了一些问题。

本例是比较基础且标准的一个版本。

实现的操作是：从数据库中取出数据，写入Excel表，并发送邮件。相当简单的一个Excel表。

【发送给单人版】

#!/usr/bin/python# -*- coding: UTF-8 -*-__author__ = "$Author: wangxin.xie$"__version__ = "$Revision: 1.0 $"__date__ = "$Date: 2015-12-11 10:36$"################################################################功能： 鱼丸预约用户信息###############################################################import osimport sysimport datetimeimport xlrdimport xlwtfrom xlutils.copy import copyfrom myyutil.DBUtil import DBUtilimport smtplibfrom email.mime.text import MIMETextfrom email.mime.multipart import MIMEMultipartfrom myyutil.ScriptExecuteUtil import ScriptExecuteUtilreload(sys)sys.setdefaultencoding('utf8')#######################全局变量####################################ngTradeDBUtil = DBUtil('netgame_trade')#execute = ScriptExecuteUtil(handleDate = handleDate)#fileDir = '/usr/local/bin/myy_script/report/data/'#fileName = fileDir + execute._reportNamefileDir = 'D://'fileName = fileDir+'yuwanyuyue.xls'tempFileName = fileDir+'yuwan_temp.xls'style1 = xlwt.XFStyle()font1 = xlwt.Font()font1.height = 220font1.name = 'SimSun'style1.font = font1##################################################################def createTemplateExcel():    '''创建Excel文件模板'''    wb = xlwt.Workbook(encoding = "UTF-8", style_compression = True)    sht0 = wb.add_sheet("sheet1", cell_overwrite_ok = True)    sht0.col(0).width=5000    sht0.col(1).width=6000    sht0.col(2).width=6000    sht0.write(0, 0, '手机号码', style1)    sht0.write(0, 1, '预约时间', style1)    sht0.write(0, 2, 'IP', style1)    wb.save(tempFileName)def genYuwanAccountAppointment():    sql = "SELECT MOBILE,CREATED_DATE,IP FROM netgame_trade.YUWAN_ACCOUNT_APPOINTMENT WHERE CREATED_DATE < '2015-12-12'"    rs = ngTradeDBUtil.queryList(sql, ())    if not rs: return None    print rs    return rsdef writeInfo0(sht, rs, length):    '''写入具体数据'''    for i in range(length):        sht.write(i+1, 0, str(rs[i][0]), style1)        sht.write(i+1, 1, str(rs[i][1]), style1)        sht.write(i+1, 2, rs[i][2], style1)def writeExcel():    '''写报表'''    createTemplateExcel()    readFile = tempFileName    rb = xlrd.open_workbook(readFile, on_demand = True, formatting_info = True)    wb = copy(rb)    rs=genYuwanAccountAppointment()    length=len(rs)    sheet0 = wb.get_sheet(0)    writeInfo0(sheet0, rs, length)    wb.save(fileName)def sendEmail():    # 创建一个带附件的实例    msg = MIMEMultipart()    # 构造附件1    att1 = MIMEText(open(fileDir+'yuwanyuyue.xls', 'rb').read(), 'base64', 'gb2312')    att1["Content-Type"] = 'application/octet-stream'    # fileName以数据加日期命名    fileName="鱼丸预约信息".decode('utf-8').encode('gbk')+".csv"    att1["Content-Disposition"] = 'attachment; filename=%s'%fileName    msg.attach(att1)    # 写入邮件正文    text=" Hi,本邮件为自动信，若有任何问题请与我联系。谢谢！"    # 邮件正文乱码,所以在这里指定编码    part1 = MIMEText(text, 'plain', _charset='utf-8')    msg.attach(part1)    # 加邮件    msg['to'] = '[e-mail]'    msg['from'] = '[e-mail]'    msg['subject'] = '预约信息'.decode('utf-8')    # 发送邮件    try:        server = smtplib.SMTP()        # 数据中心测试时        server.connect('mail.xxx.com')        # 用户名,密码        server.login('[e-mail]', 'pwd')        server.sendmail(msg['from'], msg['to'], msg.as_string())        server.quit()        print '发送成功'.decode("utf-8")    except Exception, e:        print str(e)def main():    print "===%s start===%s"%(sys.argv[0], datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S"))    writeExcel()    sendEmail()    print "===%s end===%s"%(sys.argv[0], datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S"))if __name__ == '__main__':    try:        main()        #createTemplateExcel()        #execute.start(main)        #execute.addMailTask()        #execute.pushStart(fileDir)        #execute.end()    finally:        if ngTradeDBUtil: ngTradeDBUtil.close()

【发送邮件给多人版】

主要是发送邮件的函数变化了

def sendEmail():    # 创建一个带附件的实例  msg = MIMEMultipart()    # 构造附件1    att1 = MIMEText(open(fileDir+'yuwanyuyue.xls', 'rb').read(), 'base64', 'gb2312')    att1["Content-Type"] = 'application/octet-stream'    # fileName以数据加日期命名  fileName="鱼丸预约信息".decode('utf-8').encode('gbk')+".csv"    att1["Content-Disposition"] = 'attachment; filename=%s'%fileName    msg.attach(att1)    # 写入邮件正文  text=" Hi,本邮件为测试自动信，若有任何问题请与我联系。谢谢！"    # 邮件正文乱码,所以在这里指定编码  part1 = MIMEText(text, 'plain', _charset='utf-8')    msg.attach(part1)    # 加邮件  strTo = ['[e-mail]', '[e-mail]']    msg['to'] = ','.join(strTo)    msg['from'] = '[e-mail]'    msg['subject'] = '用户预约信息'.decode('utf-8')    # 发送邮件    try:        server = smtplib.SMTP()        # 数据中心测试时     server.connect('mail.xxx.com')        # 用户名,密码     server.login('[e-mail]', 'pwd')        server.sendmail(msg['from'], strTo, msg.as_string())        server.quit()        print '发送成功'.decode("utf-8")    except Exception, e:        print str(e)

不一样的地方就三行代码

  strTo = ['[e-mail]', '[e-mail]']    msg['to'] = ','.join(strTo)

   server.sendmail(msg['from'], strTo, msg.as_string())