本文实例讲述了Python实现的Excel文件读写类。分享给大家供大家参考。具体如下:
#coding=utf-8 ####################################################### #filename:ExcelRW.py #author:defias #date:2015-4-27 #function:read or write excel file ####################################################### import xlrd import xlwt import xlutils.copy import os.path class XlsEngine(): \"\"\" The XlsEngine is a class for excel operation Usage: xlseng = XlsEngine(\'filePath\') \"\"\" def __init__(self,xlsname): \"\"\" define class variable \"\"\" self.xls_name = xlsname #file name self.xlrd_object = None #workbook object self.isopentrue = False #file open flag def open(self): \"\"\" open a xls file 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 %s failed.\'%self.xls_name) def info(self): \"\"\" show xls file information Usage: xlseng.info() \"\"\" if self.isopentrue == True: for sheetname in self.xlrd_object.sheet_names(): worksheet = self.xlrd_object.sheet_by_name(sheetname) print(\'%s:(%d row,%d col).\'%(sheetname,worksheet.nrows,worksheet.ncols)) else: print(\'file %s is not open.\'%self.xls_name) def readcell(self,sheetname=\'sheet1\',rown=0,coln=0): \"\"\" read file\'s a cell content Usage: xlseng.readcell(\'sheetname\',rown,coln) \"\"\" try: if self.isopentrue == True: worksheets = self.xlrd_object.sheet_names() if sheetname not in worksheets: print(\'%s is not exit.\'%sheetname) return False 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 %s is not open.\'%self.xls_name) except: print(\'readcell is false! please check sheetn rown and coln is right.\') def readrow(self,sheetname=\'sheet1\',rown=0): \"\"\" read file\'s a row content Usage: xlseng.readrow(\'sheetname\',rown) \"\"\" try: if self.isopentrue == True: worksheets = self.xlrd_object.sheet_names() if sheetname not in worksheets: print(\'%s is not exit.\'%sheetname) return False 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 %s is not open.\'%self.xls_name) except: print(\'readrow is false! please check sheetn rown is right.\') def readcol(self,sheetname=\'sheet1\',coln=0): \"\"\" read file\'s a col content Usage: xlseng.readcol(\'sheetname\',coln) \"\"\" try: if self.isopentrue == True: worksheets = self.xlrd_object.sheet_names() if sheetname not in worksheets: print(\'%s is not exit.\'%sheetname) return False 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 %s is not open.\'%self.xls_name) except: print(\'readcol is false! please check sheetn coln is right.\') def writecell(self,value=\'\',sheetn=0,rown=0,coln=0): \"\"\" write a cell to file,other cell is not change Usage: xlseng.writecell(\'str\',sheetn,rown,coln) \"\"\" try: if self.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(\'writecell value:%s to [sheet:%s,row:%s,col:%s] is ture.\'%(value,sheetn,rown,coln)) else: print(\'file %s is not open.\'%self.xls_name) except: print(\'writecell is false! please check.\') def writerow(self,values=\'\',sheetn=0,rown=0,coln=0): \"\"\" write a row to file,other row and cell is not change Usage: xlseng.writerow(\'str1,str2,str3...strn\',sheetn,rown.coln) \"\"\" try: if self.isopentrue == True: xlrd_objectc = xlutils.copy.copy(self.xlrd_object) worksheet = xlrd_objectc.get_sheet(sheetn) values = values.split(\',\') for value in values: worksheet.write(rown,coln,value) coln += 1 xlrd_objectc.save(self.xls_name) print(\'writerow values:%s to [sheet:%s,row:%s,col:%s] is ture.\'%(values,sheetn,rown,coln)) else: print(\'file %s is not open.\'%self.xls_name) except: print(\'writerow is false! please check.\') def writecol(self,values=\'\',sheetn=0,rown=0,coln=0): \"\"\" write a col to file,other col and cell is not change Usage: xlseng.writecol(\'str1,str2,str3...\',sheetn,rown.coln) \"\"\" try: if self.isopentrue == True: xlrd_objectc = xlutils.copy.copy(self.xlrd_object) worksheet = xlrd_objectc.get_sheet(sheetn) values = values.split(\',\') for value in values: worksheet.write(rown,coln,value) rown += 1 xlrd_objectc.save(self.xls_name) print(\'writecol values:%s to [sheet:%s,row:%s,col:%s] is ture.\'%(values,sheetn,rown,coln)) else: print(\'file %s is not open.\'%self.xls_name) except: print(\'writecol is false! please check.\') def filecreate(self,sheetnames=\'sheet1\'): \"\"\" create a empty xlsfile Usage: filecreate(\'sheetname1,sheetname2...\') \"\"\" try: if os.path.isfile(self.xls_name): print(\'%s is exit.\'%self.xls_name) return False workbook = xlwt.Workbook() sheetnames = sheetnames.split(\',\') for sheetname in sheetnames: workbook.add_sheet(sheetname,cell_overwrite_ok=True) workbook.save(self.xls_name) print(\'%s is created.\'%self.xls_name) except: print(\'filerator is false! please check.\') def addsheet(self,sheetnames=\'sheet1\'): \"\"\" add sheets to a exit xlsfile Usage: addsheet(\'sheetname1,sheetname2...\') \"\"\" try: if self.isopentrue == True: worksheets = self.xlrd_object.sheet_names() xlrd_objectc = xlutils.copy.copy(self.xlrd_object) sheetnames = sheetnames.split(\',\') for sheetname in sheetnames: if sheetname in worksheets: print(\'%s is exit.\'%sheetname) return False for sheetname in sheetnames: xlrd_objectc.add_sheet(sheetname,cell_overwrite_ok=True) xlrd_objectc.save(self.xls_name) print(\'addsheet is ture.\') else: print(\"file %s is not open \\n\"%self.xls_name) except: print(\'addsheet is false! please check.\') \"\"\" def chgsheet(self,sheetn,values): def clear(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列写字符串数据‘I am writecell writed\'(默认向第一个sheet页的第1行第1列写空字符串) print(\"\\nxlseng.writecell():\") xlseng.writecell(\'I am writecell writed\',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程序设计有所帮助。