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程序设计有所帮助。