Python实现的Excel文件读写类

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