python数据写入到excel不同sheet_python 多sheet页写入excel并发邮件

#!/usr/bin/python

# -*- coding: UTF-8 -*-

import datetime

import MySQLdb

import os

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

#os.system('source /etc/profile')

import sys

import xlwt

import xlrd

import smtplib

from time import strftime, localtime

from datetime import timedelta, date

import calendar

from email.mime.text import MIMEText

from email.mime.multipart import MIMEMultipart

from 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 = True

font.italic = True

font.height = 0x00C8

style1.font = font

pattern = xlwt.Pattern() # Create the Pattern

pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12

pattern.pattern_fore_colour = 5

style1.pattern = pattern # Add Pattern to Style

wb=xlwt.Workbook(encoding='utf-8') #创建一个excel工作簿，编码utf-8，表格中支持中文

sheet1=wb.add_sheet('概览',cell_overwrite_ok=True) #创建一个sheet1

for 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 e

mycursor.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) #创建一个sheet2

columnName2 = ['视频类型','平台','部数','集数','每部平均数']

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 e

mycursor.close()

mydb.close()

#########################以下发送邮件############################

mail_host="smtp.qq.com" #设置服务器

mail_user="305816339@qq.com" #用户名

mail_pass="3333333" #口令 #ckkbbzzbvfkwbgjisender = '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