Python實現的Excel檔案讀寫類

本文執行個體講述了Python實現的Excel檔案讀寫類。分享給大家供大家參考。具體如下：

#coding=utf-8########################################################filename:ExcelRW.py#author:defias#date:2015-4-27#function:read or write excel file#######################################################import xlrdimport xlwtimport xlutils.copy import os.pathclass 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列寫Null 字元串)  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程式設計有所協助。

