oracle数据查询生成EXCEL并发送邮件python3

import smtplib
import email.mime.multipart
import email.mime.text
import email.mime.base
import os.path
import cx_Oracle
import xlwt
import time


#生成excel文件名
v_curr_time = time.strftime('%Y%m%d%H%M%S',time.localtime(time.time()))
v_file_name = '文件名-'+ v_curr_time + '.xls '


#连接数据库
conn = cx_Oracle.connect('用户名/密码@数据库')
cur1 = conn.cursor()


#组查询语句，如果是多行结尾需要加反斜杠连接
v_sql = ("XXX")
print (v_sql)
cur1.execute(v_sql)
rows = cur1.fetchall()
v_cnt = len(rows)


#生成excel文件
book=xlwt.Workbook()
sheet1=book.add_sheet('Sheet1')


#把列名当作一行数据写入
sheet1.write(0,0,'列名1')
sheet1.write(0,1,'列名2')
sheet1.write(0,2,'列名3')




#当查出多列数据时，需要一个单元格一个单元格的写入，要不然这四列就会写到excel的一个单元格里
for i in range(len(rows)):
    for j in range(5):
        #print (rows[j])
        #print ("--------")
        sheet1.write(i+1,j,rows[j])


book.save(v_file_name)
cur1.close()
conn.close()


#邮件信息
From = "邮件地址"
To = "邮件地址"
file_name = v_file_name


server = smtplib.SMTP("发件服务器")
server.login("发件邮箱","邮箱密码") #仅smtp服务器需要验证时


# 构造MIMEMultipart对象做为根容器
main_msg = email.mime.multipart.MIMEMultipart()


# 构造MIMEText对象做为邮件显示内容并附加到根容器
#v_str = "备份日志，数据共" + v_cnt + "行"
text_msg = email.mime.text.MIMEText("XXX数据，请查收，谢谢。")
main_msg.attach(text_msg)


# 构造MIMEBase对象做为文件附件内容并附加到根容器
contype = v_file_name
maintype, subtype = contype.split(' ')


## 读入文件内容并格式化
data = open(file_name, 'rb')
file_msg = email.mime.base.MIMEBase(maintype, subtype)
file_msg.set_payload(data.read( ))
data.close( )
email.encoders.encode_base64(file_msg)


## 设置附件头
basename = os.path.basename(file_name)
file_msg.add_header('Content-Disposition',
 'attachment', filename = basename)
main_msg.attach(file_msg)


# 设置根容器属性
main_msg['From'] = From
main_msg['To'] = To
main_msg['Subject'] = "xxx文档 "
main_msg['Date'] = email.utils.formatdate( )


# 得到格式化后的完整文本
fullText = main_msg.as_string( )


# 用smtp发送邮件
try:
    server.sendmail(From, To, fullText)
    print ("发送成功")
except Exception as e:
        print ("发送失败")
        print (str(e))
finally:
    server.quit()

补充个excel文本框文本格式的def，自用款

def set_stype(col="",num =12,name="宋体".decode('utf8'),height=200,bold=False,color_index = 4, wrap = 0x01,horz = 0x02, vert = 0x01,left = 0x01,right =0x01,top = 0x01,bottom=0x01):
    style = xlwt.XFStyle()
    font = xlwt.Font()
    alignment = xlwt.Alignment()
    border = xlwt.Borders()
    #font
    font.name = name
    font.bold = bold
    font.color_index = color_index
    font.height = height
    style.font = font
    #alignment
    alignment.wrap = wrap
    alignment.horz = horz
    alignment.vert = vert
    style.alignment = alignment
    #width
    if col=="":
        l_errno = 0
    else:
        col.width = 256 * num
    #border
    border.left = left
    border.right = right
    border.top = top
    border.bottom = bottom
    style.borders = border
    return style

mark

HAOHAOXUEXI,HAOHAOXUEXI,

HAOHAOXUEXI,HAOHAOXUEXI,HAOHAOXUEXI,

HAOHAOXUEXI,HAOHAO

HAOHAOXUEXI,HAOHAOXUEXI,HAOHAOXUE

挺有意思的謝謝分享

pandas