Python实现的Excel文件读写类
作者:defias 时间:2022-03-15 19:43:24
本文实例讲述了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,Excel
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
jRaiser与jQuery的冲突问题
2010-07-27 12:36:00
教你pycharm运行Django第一个项目
2021-08-25 16:40:07
![](https://img.aspxhome.com/file/2023/9/80669_0s.png)
微信小程序上传图片功能(附后端代码)
2023-07-24 04:21:40
![](https://img.aspxhome.com/file/2023/9/55969_0s.jpg)
html网页颜色表大全(苏昱)
2008-01-01 15:52:00
![](https://img.aspxhome.com/file/UploadPic/20081/1/200811162311168s.gif)
ASP进阶教程Ⅶ:留言簿设置密码验证
2008-10-13 09:02:00
聊聊golang中多个defer的执行顺序
2023-09-03 09:23:22
Python删除指定目录下过期文件的2个脚本分享
2022-09-11 19:01:10
自定义asp错误信息的显示
2007-09-13 12:33:00
零基础写python爬虫之urllib2使用指南
2022-10-17 04:00:44
![](https://img.aspxhome.com/file/2023/4/70214_0s.png)
Python读取图片为16进制表示简单代码
2021-07-24 09:34:15
![](https://img.aspxhome.com/file/2023/1/71381_0s.png)
python实现在图片上画特定大小角度矩形框
2023-03-16 22:03:14
![](https://img.aspxhome.com/file/2023/6/65036_0s.jpg)
做新产品?悠着点
2009-03-01 13:10:00
python脚本定时发送邮件
2023-08-07 16:36:02
![](https://img.aspxhome.com/file/2023/0/59290_0s.jpg)
日式酒店电梯面板设计
2008-06-08 13:23:00
![](https://img.aspxhome.com/file/UploadPic/20086/8/20086813301313s.jpg)
ASP新闻分页,将一篇过长的文章分页,生成静态页面
2011-04-10 11:14:00
PL/SQL 类型格式转换
2009-02-26 11:07:00
网页布局的位置重心与位置间的对比关系
2007-10-15 19:20:00
![](https://img.aspxhome.com/file/UploadPic/200710/15/20071015192410224s.jpg)
Python爬虫之获取心知天气API实时天气数据并弹窗提醒
2023-04-17 14:40:58
![](https://img.aspxhome.com/file/2023/0/68870_0s.png)
golang中net的tcp服务使用
2023-08-30 10:54:14
![](https://img.aspxhome.com/file/2023/8/93658_0s.webp)
用CSS设计多种文本框与按钮样式风格
2007-08-10 13:11:00
![](https://img.aspxhome.com/file/UploadPic/20072/20072311391742s.gif)