Python实现的Excel文件读写类
本文实例讲述了Python实现的Excel文件读写类。分享给大家供大家参考。具体如下:
#coding=utf-8 ####################################################### #filename:ExcelRW.py #author:defias #date:2015-4-27 #function:readorwriteexcelfile ####################################################### importxlrd importxlwt importxlutils.copy importos.path classXlsEngine(): """ TheXlsEngineisaclassforexceloperation Usage: xlseng=XlsEngine('filePath') """ def__init__(self,xlsname): """ defineclassvariable """ self.xls_name=xlsname#filename self.xlrd_object=None#workbookobject self.isopentrue=False#fileopenflag defopen(self): """ openaxlsfile Usage: xlseng.open() """ try: self.xlrd_object=xlrd.open_workbook(self.xls_name) self.isopentrue=True print('[%s,%s].'%(self.isopentrue,self.xlrd_object)) except: self.isopentrue=False self.xlrd_object=None print('open%sfailed.'%self.xls_name) definfo(self): """ showxlsfileinformation Usage: xlseng.info() """ ifself.isopentrue==True: forsheetnameinself.xlrd_object.sheet_names(): worksheet=self.xlrd_object.sheet_by_name(sheetname) print('%s:(%drow,%dcol).'%(sheetname,worksheet.nrows,worksheet.ncols)) else: print('file%sisnotopen.'%self.xls_name) defreadcell(self,sheetname='sheet1',rown=0,coln=0): """ readfile'sacellcontent Usage: xlseng.readcell('sheetname',rown,coln) """ try: ifself.isopentrue==True: worksheets=self.xlrd_object.sheet_names() ifsheetnamenotinworksheets: print('%sisnotexit.'%sheetname) returnFalse worksheet=self.xlrd_object.sheet_by_name(sheetname) cell=worksheet.cell_value(rown,coln) print('[file:%s,sheet:%s,row:%s,col:%s]:%s.'%(self.xls_name,sheetname,rown,coln,cell)) else: print('file%sisnotopen.'%self.xls_name) except: print('readcellisfalse!pleasechecksheetnrownandcolnisright.') defreadrow(self,sheetname='sheet1',rown=0): """ readfile'sarowcontent Usage: xlseng.readrow('sheetname',rown) """ try: ifself.isopentrue==True: worksheets=self.xlrd_object.sheet_names() ifsheetnamenotinworksheets: print('%sisnotexit.'%sheetname) returnFalse worksheet=self.xlrd_object.sheet_by_name(sheetname) row=worksheet.row_values(rown) print('[file:%s,sheet:%s,row:%s]:%s.'%(self.xls_name,sheetname,rown,row)) else: print('file%sisnotopen.'%self.xls_name) except: print('readrowisfalse!pleasechecksheetnrownisright.') defreadcol(self,sheetname='sheet1',coln=0): """ readfile'sacolcontent Usage: xlseng.readcol('sheetname',coln) """ try: ifself.isopentrue==True: worksheets=self.xlrd_object.sheet_names() ifsheetnamenotinworksheets: print('%sisnotexit.'%sheetname) returnFalse worksheet=self.xlrd_object.sheet_by_name(sheetname) col=worksheet.col_values(coln) print('[file:%s,sheet:%s,col:%s]:%s.'%(self.xls_name,sheetname,coln,col)) else: print('file%sisnotopen.'%self.xls_name) except: print('readcolisfalse!pleasechecksheetncolnisright.') defwritecell(self,value='',sheetn=0,rown=0,coln=0): """ writeacelltofile,othercellisnotchange Usage: xlseng.writecell('str',sheetn,rown,coln) """ try: ifself.isopentrue==True: xlrd_objectc=xlutils.copy.copy(self.xlrd_object) worksheet=xlrd_objectc.get_sheet(sheetn) worksheet.write(rown,coln,value) xlrd_objectc.save(self.xls_name) print('writecellvalue:%sto[sheet:%s,row:%s,col:%s]isture.'%(value,sheetn,rown,coln)) else: print('file%sisnotopen.'%self.xls_name) except: print('writecellisfalse!pleasecheck.') defwriterow(self,values='',sheetn=0,rown=0,coln=0): """ writearowtofile,otherrowandcellisnotchange Usage: xlseng.writerow('str1,str2,str3...strn',sheetn,rown.coln) """ try: ifself.isopentrue==True: xlrd_objectc=xlutils.copy.copy(self.xlrd_object) worksheet=xlrd_objectc.get_sheet(sheetn) values=values.split(',') forvalueinvalues: worksheet.write(rown,coln,value) coln+=1 xlrd_objectc.save(self.xls_name) print('writerowvalues:%sto[sheet:%s,row:%s,col:%s]isture.'%(values,sheetn,rown,coln)) else: print('file%sisnotopen.'%self.xls_name) except: print('writerowisfalse!pleasecheck.') defwritecol(self,values='',sheetn=0,rown=0,coln=0): """ writeacoltofile,othercolandcellisnotchange Usage: xlseng.writecol('str1,str2,str3...',sheetn,rown.coln) """ try: ifself.isopentrue==True: xlrd_objectc=xlutils.copy.copy(self.xlrd_object) worksheet=xlrd_objectc.get_sheet(sheetn) values=values.split(',') forvalueinvalues: worksheet.write(rown,coln,value) rown+=1 xlrd_objectc.save(self.xls_name) print('writecolvalues:%sto[sheet:%s,row:%s,col:%s]isture.'%(values,sheetn,rown,coln)) else: print('file%sisnotopen.'%self.xls_name) except: print('writecolisfalse!pleasecheck.') deffilecreate(self,sheetnames='sheet1'): """ createaemptyxlsfile Usage: filecreate('sheetname1,sheetname2...') """ try: ifos.path.isfile(self.xls_name): print('%sisexit.'%self.xls_name) returnFalse workbook=xlwt.Workbook() sheetnames=sheetnames.split(',') forsheetnameinsheetnames: workbook.add_sheet(sheetname,cell_overwrite_ok=True) workbook.save(self.xls_name) print('%siscreated.'%self.xls_name) except: print('fileratorisfalse!pleasecheck.') defaddsheet(self,sheetnames='sheet1'): """ addsheetstoaexitxlsfile Usage: addsheet('sheetname1,sheetname2...') """ try: ifself.isopentrue==True: worksheets=self.xlrd_object.sheet_names() xlrd_objectc=xlutils.copy.copy(self.xlrd_object) sheetnames=sheetnames.split(',') forsheetnameinsheetnames: ifsheetnameinworksheets: print('%sisexit.'%sheetname) returnFalse forsheetnameinsheetnames: xlrd_objectc.add_sheet(sheetname,cell_overwrite_ok=True) xlrd_objectc.save(self.xls_name) print('addsheetisture.') else: print("file%sisnotopen\n"%self.xls_name) except: print('addsheetisfalse!pleasecheck.') """ defchgsheet(self,sheetn,values): defclear(self): """ if__name__=='__main__': #初始化对象 xlseng=XlsEngine('E:\\Code\\Python\\test2.xls') #新建文件,可以指定要新建的sheet页面名称,默认值新建sheet1 #print("\nxlseng.filecreate():") #xlseng.filecreate('newesheet1,newesheet2,newesheet3') #打开文件 print("xlseng.open():") xlseng.open() #添加sheet页 print("\nxlseng.addsheet():") xlseng.addsheet('addsheet1,addsheet2,addsheet3') #输出文件信息 print("\nxlseng.info():") xlseng.info() #读取sheet1页第3行第3列单元格数据(默认读取sheet1页第1行第1列单元格数据) print("\nxlseng.readcell():") xlseng.readcell('sheet1',2,2) #读取sheet1页第2行的数据(默认读取sheet1页第1行的数据) print("\nxlseng.readrow():") xlseng.readrow('sheet1',1) #读取sheet1页第3列的数据(默认读取sheet1页第1列的数据) print("\nxlseng.readcol():") xlseng.readcol('sheet1',2) #向第一个sheet页的第2行第4列写字符串数据‘Iamwritecellwrited'(默认向第一个sheet页的第1行第1列写空字符串) print("\nxlseng.writecell():") xlseng.writecell('Iamwritecellwrited',0,1,3) #向第一个sheet页写一行数据,各列的值为‘rowstr1,rowstr2,rowstr3',从第3行第4列开始写入(默认向第一个sheet页写一行数据,值为‘',从第1行第1列开始写入) print("\nxlseng.writerow():") xlseng.writerow('rowstr1,rowstr2,rowstr3',0,2,3) #向第一个sheet页写一列数据,各行的值为‘colstr1,colstr2,colstr3,colstr4',从第4行第4列开始写入(默认向第一个sheet页写一列数据,值为‘',从第1行第1列开始写入) print("\nxlseng.writecol():") xlseng.writecol('colstr1,colstr2,colstr3,colstr4',0,3,3)
希望本文所述对大家的Python程序设计有所帮助。