使用xlwt生成Excel2003的class，xlwtexcel2003,# coding: ut
# coding: utf-8# xlswriter.pyfrom xlwt import *from schconst import *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 = Workbook() self.sheets = {} self.font = Font() self.alignment = Alignment() self.borders = Borders() self.xfstyle = XFStyle() self.xstyle = easyxf('alignment:horz center,vert center;font: colour red, bold False;borders:left 0x01,right 0x01,top 0x01,bottom 0x01;') self.vstyle = easyxf('alignment:horz center,vert center;font: colour black, bold False;borders:left 0x01,right 0x01,top 0x01,bottom 0x01;') self.ostyle = easyxf('alignment:horz center,vert center;font: colour black, bold True;borders:left 0x01,right 0x01,top 0x01,bottom 0x01;') #创建sheet 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'] = 1 #添加单元格 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 cellstyle(self): #设定字体 self.font.name = 'Verdana' self.font.bold = True #设定居中 self.alignment.horz = Alignment.HORZ_CENTER self.alignment.vert = Alignment.VERT_CENTER #设定边线 self.borders.left = 0x01 self.borders.right = 0x01 self.borders.top = 0x01 self.borders.bottom = 0x01 #定义样式 self.xfstyle.alignment = self.alignment self.xfstyle.font = self.font self.xfstyle.borders = self.borders return self.xfstyle #添加非合并的单元格 def writerow(self, row, sheet_name='sheet'): if sheet_name not in self.sheets: self.create_sheet(sheet_name) if self.sheets[sheet_name]['rows'] >= 65534: self.save() self.create_sheet(sheet_name) for ci, col in enumerate(row): if col == TRM_STATUS_OFFLINE: self.sheets[sheet_name]['sheet'].write(self.sheets[sheet_name]['rows'], ci, self.cell(col) ,self.xstyle) elif col == TRM_STATUS_ONLINE: self.sheets[sheet_name]['sheet'].write(self.sheets[sheet_name]['rows'], ci, self.cell(col) if type(col) != ExcelFormula.Formula else col,self.vstyle) else: self.sheets[sheet_name]['sheet'].write(self.sheets[sheet_name]['rows'], ci, self.cell(col) if type(col) != ExcelFormula.Formula else col,self.ostyle) self.sheets[sheet_name]['rows'] += 1 #添加合并的单元格 def writemergerow(self, merge_value='', sheet_name='sheet',row1=0,row2=0,column1=0,column2=0): if sheet_name not in self.sheets: # Create if does not exist self.create_sheet(sheet_name) self.sheets[sheet_name]['sheet'].write_merge(row1, row2, column1, column2, merge_value,self.cellstyle()) self.sheets[sheet_name]['sheet'].col(1).width = 0x0d00 #添加多行单元格 def writerows(self, rows, sheet_name='sheet'): if rows == None: return for row in rows: self.writerow(row, sheet_name) #保存文件 def save(self): self.wbk.save(self.file)#测试数据#if __name__ == '__main__':# # test# xlswriter = XLSWriter(u'长沙.xls')# xlswriter.writemergerow(merge_value= u'日期',sheet_name=u'基本信息',row1=0, row2=0, column1=0, column2=0)# xlswriter.writemergerow(merge_value= '2013-12',sheet_name=u'基本信息',row1=0, row2=0, column1=1, column2=1)# xlswriter.writemergerow(merge_value= '2013-12-01',sheet_name=u'基本信息',row1=0, row2=0, column1=2, column2=4)# xlswriter.writerow(['终端名', '在线/次数', '早', '中', '晚'], sheet_name=u'基本信息')# xlswriter.writerow(['TRM01', '10/93', 'V', 'V', 'X'], sheet_name=u'基本信息')# xlswriter.writerow(['TRM02', '10/93', 'V', 'V', 'X'], sheet_name=u'基本信息')# xlswriter.writerow(['TRM03', '10/93', 'V', 'V', 'X'], sheet_name=u'基本信息') # don't forget to save data to disk# xlswriter.save()# print 'finished.'
评论关闭