使用xlsxwriter生成Excel2007的class，,# coding: ut
# coding: utf-8# xlswriter.pyimport xlsxwriterfrom schconst import *class XLSWriter(object): """A XLS writer that produces XLSX files from unicode data. """ def __init__(self, file, encoding='utf-8'): # must specify the encoding of the input data, utf-8 default. self.encoding = encoding self.wbk = xlsxwriter.Workbook(file) self.sheets = {} #头信息格式化 self.header_format = self.wbk.add_format({'bold': True,'align': 'center','valign': 'vcenter','border': 1}) #在线格式化 self.online_format = self.wbk.add_format({'align': 'center','border': 1,'valign': 'vcenter'}) #终端名称格式化 self.trm_format = self.wbk.add_format({'bold': True,'align': 'left','border': 1,'valign': 'vcenter'}) #离线格式化 self.offline_format = self.wbk.add_format({'align': 'center','valign': 'vcenter','border': 1,'font_color':'red'}) #合并单元格格式化 self.merge_format = self.wbk.add_format({'bold': 1,'border': 1,'align': 'center','valign': 'vcenter'}) #创建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 = self.sheets.__len__() self.sheets[sheet_name] = {'header': []} self.sheets[sheet_name]['index'] = sheet_index self.sheets[sheet_name]['sheet'] = self.wbk.add_worksheet(sheet_name) 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 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.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.offline_format) elif col == TRM_STATUS_ONLINE or col == TRM_STATUS_NODATA: self.sheets[sheet_name]['sheet'].write(self.sheets[sheet_name]['rows'], ci, self.cell(col),self.online_format) elif '/' in col or col == u'终端名' or col == u'早' or col == u'中' or col == u'晚': self.sheets[sheet_name]['sheet'].write(self.sheets[sheet_name]['rows'], ci, self.cell(col),self.header_format) else: self.sheets[sheet_name]['sheet'].write(self.sheets[sheet_name]['rows'], ci, self.cell(col),self.trm_format) self.sheets[sheet_name]['rows'] += 1 #固定行数与列数 def freeze(self,sheet_name='sheet',row=0,column=0): if sheet_name not in self.sheets: self.create_sheet(sheet_name) self.sheets[sheet_name]['sheet'].freeze_panes(row, column) #两行，一列固定 #设定row行，column列的宽度为height_value def setcolheight(self,sheet_name='sheet',firstcol=0,lastcol=0,height_value=0): if sheet_name not in self.sheets: self.create_sheet(sheet_name) self.sheets[sheet_name]['sheet'].set_column(firstcol, lastcol, height_value)#第0行，0列宽度设置为30 #写标题 def writetitle(self, title_value='', sheet_name='sheet',row1=0,column1=0): if sheet_name not in self.sheets: self.create_sheet(sheet_name) self.sheets[sheet_name]['sheet'].write_string(row1,column1 ,title_value,self.header_format) #添加合并的单元格 def writemergerow(self, merge_value='', sheet_name='sheet',row1=0,row2=0,column1=0,column2=0): if sheet_name not in self.sheets: self.create_sheet(sheet_name) self.sheets[sheet_name]['sheet'].merge_range(row1,column1, row2, column2, merge_value,self.header_format) #添加多行单元格 def writerows(self, rows, sheet_name='sheet'): if rows == None: return for row in rows: self.writerow(row, sheet_name) #保存文件 def close(self): self.wbk.close()#测试数据#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.'
评论关闭