本文实例讲述了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程序设计有所帮助。