python导出excel格式的oracle数据报表(字段名和内容支持中文字符)

python导出oracl e excel报表（字段名和内容支持中

文字符）

1.需要预先安装两个python模块：

cx_Oracle、xlsxwriter

2.实现代码（环境是python 2.7）

#!/usr/bin/env python

#coding:utf-8

import cx_Oracle # cx_Oracle 用于访问oracle和导出数据

import xlsxwriter # xlsxwriter 用户生成xlsx文件

import time

import sys

from email.mime.text import MIMEText # 导入邮件模块

from email.mime.multipart import MIMEMultipart

import smtplib

reload(sys)

sys.setdefaultencoding("gbk")

con = cx_Oracle.connect("aaa/1234@orcl")

cursor = con.cursor()

#定义SQL脚本

sql ='''

select a.id 工号,

姓名,

b. performance 绩效,

b.month 月份

'''.decode('utf-8').encode('gbk')

query1 = cursor.execute(sql)

title = [i[0] for i in query1.description]

date_now=time.strftime("%Y%m%d",time.localtime())

#文件名及其路径

report_name='/excel/' + "业务数据".decode('utf-8').encode('gbk') + date_now + '.xlsx'

#生成xlsx格式oracle查询统计报表

workbook = xlsxwriter.Workbook(report_name, {'constant_memory': True})

worksheet = workbook.add_worksheet()

print time.ctime()

data = cursor.fetchall()

print time.ctime()

worksheet.write_row(0, 0, title)

for row, row_date in enumerate(data):

worksheet.write_row(row+1, 0, row_date)

print time.ctime()

cursor.close()

con.close()

workbook.close()

#以下代码实现发送邮件

msg = MIMEMultipart()

#定义附件名

att1_name="月度统计".decode('utf-8').encode('gbk') + date_now + '.xlsx'

#读入附件，report_name

att1 = MIMEText(open(report_name, 'rb').read(), 'base64', 'gb2312')

att1["Content-Type"] = 'application/octet-stream'

att1["Content-Disposition"] = 'attachment; filename=%s' % att1_name.encode('gbk') msg.attach(att1)

msg['to']='*******************'

msg['from']='****************'

msg['subject'] = "月度统计".decode('utf-8').encode('gbk')

try:

server = smtplib.SMTP()

server.connect('')

server.login('****************','123456')#邮箱账号密码

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

server.quit()

print 'successful.'

except Exception, e:

print str(e)