python访问excel的类_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程序设计有所帮助。

本文原创发布php中文网，转载请注明出处，感谢您的尊重！