python 多sheet页写入excel并发邮件
来源：互联网 发布：白金disco 知乎 编辑：程序博客网 时间：2024/06/05 14:34
#!/usr/bin/python# -*- coding: UTF-8 -*-import datetimeimport MySQLdbimport osos.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'#os.system('source /etc/profile')import sysimport xlwtimport xlrdimport smtplibfrom time import strftime, localtimefrom datetime import timedelta, dateimport calendarfrom email.mime.text import MIMETextfrom email.mime.multipart import MIMEMultipartfrom email.header import Header#sys.path.append('/usr/lib64/python2.6/site-packages/cx_Oracle.so')# 打开数据库连接mydb = MySQLdb.connect("10.18.141.52","dba","xxxxxxxxxx","ys" )print "mydb"#exit(1)# 使用cursor()方法获取操作游标mycursor = mydb.cursor()mycursor.execute('SET CHARACTER SET utf8;')mycursor.execute('SET NAMES utf8;')mycursor.execute('SET character_set_connection=utf8;')sql1 = " CALL ys.pr_get_increament_report_by_plat_class1();"columnName1 = ['平台','动漫增量（部）','电影增量（部）','电视剧增量（部）','综艺增量（部）','增量（部）sum'] #定义所有的列名，共6列style1 = xlwt.XFStyle() #设置单元格格式style1.num_format_str = 'yyyy/m/d h:mm:ss'font = xlwt.Font()font.name = 'Times New Roman'font.bold = Truefont.italic = Truefont.height = 0x00C8style1.font = fontpattern = xlwt.Pattern() # Create the Patternpattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12pattern.pattern_fore_colour = 5style1.pattern = pattern # Add Pattern to Stylewb=xlwt.Workbook(encoding='utf-8') #创建一个excel工作簿，编码utf-8，表格中支持中文sheet1=wb.add_sheet('概览',cell_overwrite_ok=True) #创建一个sheet1for c1 in range(len(columnName1)): #将列名插入表格，共6列 sheet1.write(0,c1,columnName1[c1])try: mycursor.execute(sql1) results = mycursor.fetchall() rows = len(results) for i in range(rows): for j in range(6): print results[i][j] sheet1.write(i+1,j,results[i][j]) print '-- -- -- -- -- -- -- -- -- -- -- --' print 'ok-sql1' #wb.save('增量概览.xls')except Exception , e: print emycursor.close()mycursor = mydb.cursor()mycursor.execute('SET CHARACTER SET utf8;')mycursor.execute('SET NAMES utf8;')mycursor.execute('SET character_set_connection=utf8;')sql2 = " CALL ys.pr_get_increament_report_by_plat_class2();"#sheet=wb.add_sheet('overView2')sheet2=wb.add_sheet('明细',cell_overwrite_ok=True) #创建一个sheet2columnName2 = ['视频类型','平台','部数','集数','每部平均数']for c2 in range(len(columnName2)): #将列名插入表格，共6列 sheet2.write(0,c2,columnName2[c2])try: mycursor.execute(sql2) results2 = mycursor.fetchall() rows2 = len(results2) for ii in range(rows2): for jj in range(5): print results2[ii][jj] sheet2.write(ii+1,jj,results2[ii][jj]) #print jj print '== == == == == == == == == == == ==' print 'ok-sql2' ymdhms = strftime("%y%m%d%H%M%S",localtime()) wb.save('info数据增长' + ymdhms + '.xls')except Exception , e: print emycursor.close()mydb.close()#########################以下发送邮件############################mail_host="smtp.qq.com" #设置服务器mail_user="305816339@qq.com" #用户名mail_pass="3333333" #口令 #ckkbbzzbvfkwbgji
sender = '305816339@qq.com'receivers = ['hongen@blice.cn'] # 接收邮件，可设置为你的QQ邮箱或者其他邮箱#创建一个带附件的实例message = MIMEMultipart()message['From'] = Header("py增长监控", 'utf-8')message['To'] = Header("数据增长", 'utf-8')subject = 'info数据增长'message['Subject'] = Header(subject, 'utf-8')#邮件正文内容message.attach(MIMEText('亲爱的产品同学，数据增长详细请参见附件....','plain', 'utf-8'))# 构造附件1，传送当前目录下的 test.txt 文件att1 = MIMEText(open('info数据增长' + ymdhms + '.xls', 'rb').read(), 'base64', 'utf-8')att1["Content-Type"] = 'application/octet-stream'# 这里的filename可以任意写，写什么名字，邮件中显示什么名字att1["Content-Disposition"] = 'attachment; filename="info数据增长' + ymdhms + '.xls"'message.attach(att1)try: smtpObj = smtplib.SMTP_SSL() smtpObj.connect(mail_host, 465) # 25 为 SMTP 端口号 smtpObj.login(mail_user,mail_pass) smtpObj.sendmail(sender, receivers, message.as_string()) print "邮件发送成功 "except smtplib.SMTPException,e: print e 0 0
