【python】 python 查询oracle数据生成excel

接上篇安装好了cx_Oracle.msi  MySql.msi 下载安装 xlwt-0.7.5.tar.gz， 到安装目录下 命令窗口cmd下执行 python setup.py install即可

被引用的文件：

# coding: utf-8
# xlswriter.py

# http://pypi.python.org/pypi/xlwt
import xlwt
class XLSWriter(object):
    """A XLS writer that produces XLS files from unicode data.
    """
    def __init__(self, file, encoding='utf-8'):
        # must specify the encoding of the input data, utf-8 default.
        self.file = file
        self.encoding = encoding
        self.wbk = xlwt.Workbook()
        self.sheets = {}
        
    def create_sheet(self, sheet_name='sheet'):
        """Create new sheet
        """
        if sheet_name in self.sheets:
            sheet_index = self.sheets[sheet_name]['index'] + 1
        else:
            sheet_index = 0
            self.sheets[sheet_name] = {'header': []}
        self.sheets[sheet_name]['index'] = sheet_index
        self.sheets[sheet_name]['sheet'] = self.wbk.add_sheet('%s%s' % (sheet_name, sheet_index if sheet_index else ''), cell_overwrite_ok=True)
        self.sheets[sheet_name]['rows'] = 0

    def cell(self, s):
        if isinstance(s, basestring):
            if not isinstance(s, unicode):
                s = s.decode(self.encoding)
        elif s is None:
            s = ''
        else:
            s = str(s)
        return s

    def writerow(self, row,xlsstyle, sheet_name='sheet'):
        if sheet_name not in self.sheets:
            # Create if does not exist
            self.create_sheet(sheet_name)
    
        if self.sheets[sheet_name]['rows'] == 0:
            self.sheets[sheet_name]['header'] = row

        if self.sheets[sheet_name]['rows'] >= 65534:
            self.save()
            # create new sheet to avoid being greater than 65535 lines
            self.create_sheet(sheet_name)
            if self.sheets[sheet_name]['header']:
                self.writerow(self.sheets[sheet_name]['header'], sheet_name)
        for ci, col in enumerate(row):
		    #self.sheets[sheet_name]['sheet'].col(col).width=0x0d00
            self.sheets[sheet_name]['sheet'].write(self.sheets[sheet_name]['rows'], ci, self.cell(col) if type(col) != xlwt.ExcelFormula.Formula else col,xlsstyle)
        self.sheets[sheet_name]['rows'] += 1
            
    def writerows(self, rows,style, sheet_name='sheet'):
        for row in rows:
            self.writerow(row,style, sheet_name)

    def save(self):
        self.wbk.save(self.file)
        
if __name__ == '__main__':
    # test
    xlswriter = XLSWriter(u'陕西.xls')

    ft=xlwt.Font()
    ft.height =0x00C8 
    ft.bold = True

    ft1=xlwt.Font()
    ft1.bold=False

    style0=xlwt.XFStyle()
    style0.font=ft

    style1=xlwt.XFStyle()
    style1.font=ft1

    xlswriter.writerow(['姓名', '年龄', '电话', 'QQ'], style0,sheet_name=u'基本信息')
    xlswriter.writerow(['张三', '30', '13512345678', '123456789'],style1, sheet_name=u'基本信息')
    
    xlswriter.writerow(['学校', '获得学位', '取得学位时间'], style0,sheet_name=u'学习经历')
    xlswriter.writerow(['西安电子科技大学', '学士', '2009'],style1, sheet_name=u'学习经历')
    xlswriter.writerow(['西安电子科技大学', '硕士', '2012'], style1,sheet_name=u'学习经历')
    
    xlswriter.writerow(['王五', '30', '13512345678', '123456789'],style1, sheet_name=u'基本信息')
    # don't forget to save data to disk
    xlswriter.save()
    print 'finished.'

连接oracle并生成excel

#! /usr/bin/env python
#coding=utf-8
import xlwt,cx_Oracle,datetime,MySQLdb
from XLSWriter import XLSWriter

__s_date = datetime.date(1899, 12, 31).toordinal()-1
'''
Excel中的日期为浮点数则转为标准日期格式
'''
def getdate(date):
	if isinstance(date, float):
		date = int(date)
        d = datetime.date.fromordinal(__s_date + date)
	return d.strftime("%Y%m%d")
def getYesterday():
	'''
	昨天
	'''
	today=datetime.date.today()
	oneday=datetime.timedelta(days=1)
	yesterday=today-oneday
	return yesterday
print getYesterday().strftime("%Y-%m-%d")
'''
获取GIPAP、TIPAP新批再批患者名单
'''
def getGipapTipapNewReactivePass (sql):
	try:
		db=cx_Oracle.connect("user","pwd",'192.168.1.1:1521/orcl')
		cursor=db.cursor()
		SQLTEXT=sql
		rslist=[]
		rs=cursor.execute(SQLTEXT)
		rslist=rs.fetchall()
	except MySQLdb.Error,e:
		print "Mysql Error %d: %s" % (e.args[0], e.args[1]) 
		cursor.close()
		db.close()
	return rslist

	
	
'''
将查询结果集写入xls文件
'''
def writeDateToXls(xlaname,style,paptype,papname):
    gsql=u"select t.pchinesename,t.pmobile,t.pphone,t.pplanbegindate,d.dname from (select m.mrpatient,m.mrplanbegintime,m.mrplanendtime,m.mrendtime,m.mrbegintime from tb_ m "
    mailtype=1
    newplan=' '
    #注意，这里的变量passtype passpap 即为导出后的excel前两列值 ulipad编辑器此处不能用中文，未解决  经测试 EitPlus编辑器正常 如：passtype=u'再批'
    passtype='Reactive'
    domain=1
    passpap='gipap'
    gsql+=u"where m.mrmailtype="+str(mailtype)+ str(newplan) +" and f_domain_by_pid(m.mrpatient)="+str(domain)+" and status='1' and m.mrendtime =(to_char(trunc(sysdate-1),'yyyy-mm-dd'))) a "
    gsql+="left join tb_ t on t.pid=a.mrpatient "
    gsql+="left join dm_ p on p.pid=t.pplan "
    gsql+="left join tb_ e on e.eid=t.pcsa "
    gsql+="left join tb_ j on j.jemployee=t.pcsa "
    gsql+="left join tb_ d on d.did=j.jdepartment "
    gsql+="where d.dstatus='A' and j.jstatus='A' and e.estatus='A' "
    print gsql
    #print papname+passtype
    rslist=[]
    rslist=getGipapTipapNewReactivePass(gsql)
    print len(rslist)	
    xlswriter.writerow(['批注类型','药品名称','患者姓名','手机','固话','批准时间（援助开始时间）','发药点'],style0, sheet_name=papname+passtype)
    #这里设置样式
    for p in rslist:
		xlswriter.writerow([passtype,passpap,
		'' if p[0] is None else p[0].decode('gbk').encode('utf-8'),
		'' if p[1] is None else p[1].decode('gbk').encode('utf-8'),
		'' if p[2] is None else p[2].decode('gbk').encode('utf-8'),
		'' if p[3] is None else p[3].decode('gbk').encode('utf-8'),
		'' if p[4] is None else p[4].decode('gbk').encode('utf-8')],style, sheet_name=papname+passtype)
        
    del rslist[:]
		
if __name__ == '__main__':
	
	#don't forget to save data to disk
	ft=xlwt.Font()
	ft.height =0x00C8 
	ft.bold = True

	ft1=xlwt.Font()
	ft1.bold=False

	style0=xlwt.XFStyle()
	style0.font=ft

	style1=xlwt.XFStyle()
	style1.font=ft1
	createdate=str(datetime.datetime.now().strftime('%Y%m%d' ))
	xlsname=u'GIPAP_NEW_PATIENT_再批患者'+str(createdate)+'.xls'
	
	#xlswriter=XLSWriter(xlsname)
	xlswriter=XLSWriter(u'F:\\payton\\再批患者报告\\'+xlsname)
	writeDateToXls(xlsname,style1,'Reactive','GIPAP')
	xlswriter.save()

	print 'finished.'

控制台输出：

excel导出数据：