接上篇安装好了cx_Oracle.msi MySql.msi 下载安装 xlwt-0.7.5.tar.gz， 到安装目录下 命令窗口cmd下执行 python setup.py install即可
被引用的文件：
# coding: utf-8# xlswriter.py# import xlwtclass 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 = fileself.encoding = encodingself.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'] + 1else:sheet_index = 0self.sheets[sheet_name] = {'header': []}self.sheets[sheet_name]['index'] = sheet_indexself.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'] = 0def 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 sdef writerow(self, row,xlsstyle, sheet_name='sheet'):if sheet_name not in self.sheets:# Create if does not existself.create_sheet(sheet_name)if self.sheets[sheet_name]['rows'] == 0:self.sheets[sheet_name]['header'] = rowif self.sheets[sheet_name]['rows'] >= 65534:self.save()# create new sheet to avoid being greater than 65535 linesself.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=0x0d00self.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'] += 1def 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__':# testxlswriter = XLSWriter(u'陕西.xls')ft=xlwt.Font()ft.height =0x00C8ft.bold = Trueft1=xlwt.Font()ft1.bold=Falsestyle0=xlwt.XFStyle()style0.font=ftstyle1=xlwt.XFStyle()style1.font=ft1xlswriter.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 diskxlswriter.save()print 'finished.'
连接oracle并生成excel
#! /usr/bin/env python#coding=utf-8import xlwt,cx_Oracle,datetime,MySQLdbfrom 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-onedayreturn yesterdayprint 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=sqlrslist=[]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=1newplan=' '#注意，这里的变量passtype passpap 即为导出后的excel前两列值 ulipad编辑器此处不能用中文，，未解决 经测试 EitPlus编辑器正常 如：passtype=u'再批'passtype='Reactive'domain=1passpap='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+passtyperslist=[]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 diskft=xlwt.Font()ft.height =0x00C8 ft.bold = Trueft1=xlwt.Font()ft1.bold=Falsestyle0=xlwt.XFStyle()style0.font=ftstyle1=xlwt.XFStyle()style1.font=ft1createdate=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.'
控制台输出：
你有没有这样的感觉，坐在一列火车上，
