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 
"%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("
xlseng.filecreate():")
 #xlseng.filecreate('newesheet1,newesheet2,newesheet3')
 #打開文件
 print("xlseng.open():")
 xlseng.open()
 #添加sheet頁
 print("
xlseng.addsheet():")
 xlseng.addsheet('addsheet1,addsheet2,addsheet3')
 #