Python实现的Excel文件读写类

yipeiwu_com5年前Python基础

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

相关文章

Python安装与基本数据类型教程详解

Python安装与基本数据类型教程详解

人生苦短,我选Python。 Python比其他的语言来说真的简洁多了,很多时候想做的东西都有对应的模块可以导入,平时玩点小东西真心不错。 首先讲一下安装,其实没什么好讲的,点点点点点,...

python Crypto模块的安装与使用方法

python Crypto模块的安装与使用方法

前言 最开始想尝试在windows下面安装python3.6,虽然python安装成功,但在安装Cryto模块用pip3 install pycrypto老是会报错。老夫搞了半天,最终决...

pytorch 归一化与反归一化实例

ToTensor中就有转到0-1之间了。 # -*- coding:utf-8 -*- import time import torch from torchvisi...

Python中使用bidict模块双向字典结构的奇技淫巧

快速入门 模块提供三个类来处理一对一映射类型的一些操作 'bidict', 'inverted', 'namedbidict' >>> import bidict...

Pandas删除数据的几种情况(小结)

开始之前,pandas中DataFrame删除对象可能存在几种情况 1、删除具体列 2、删除具体行 3、删除包含某些数值的行或者列 4、删除包含某些字符、文字的行或者列 本文就针对这...