PYTHON导出EXCEL格式的ORACLE数据报表(字段名和内容支持中文字符)

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

文字符）

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

cx_Oracle、xlsxwriter

2.实现代码（环境是python2.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']='pymanager01@'

msg['from']='pytest01@'

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

try:

server=smtplib.SMTP()

server.connect('')

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

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

server.quit()

print'successful.'

except Exception,e:

print str(e)