Python之操作Excel文件

本操作都是基于xlrd和pyExcelerator库对excel文件进行操作的

#!/usr/bin/env python# -*- encoding: utf-8 -*-# __author__ = 'vinman'import xlrdimport pyExceleratorimport osimport sysreload(sys)sys.setdefaultencoding('utf8')class ExcelRead(object):"""通过外部库xlrd操作Excel表格文件目前只有读操作(没有写操作)默认是打开第一个Excel文件里面的第一个表"""def __init__(self, filename):try:self.workbook = xlrd.open_workbook(filename)except:print('No such file or directory: %s' % filename)returnself.select_sheet_by_index(0)@propertydef get_sheets_count(self):return self.workbook.nsheetsdef select_sheet_by_index(self, num):"""通过下标索引选择表"""count = self.get_sheets_countif num >= count:print ('此Excel文件只有%s个表,没有索引为%s的表' % (count, num))return Falseself.sheet = self.workbook.sheets()[num]# self.sheet = self.workbook.sheet_by_index(num)return Truedef select_sheet_by_name(self, sheetname):"""通过表名来选择表"""try:self.sheet = sheet_by_name(sheetname)return Trueexcept XLRDError:print (u'没有找到名为%s的表' % sheetname)return False@propertydef get_rows_count(self):"""作为一个属性,不能使用函数的调用方式get_rows_count()获取行数数"""return self.sheet.nrows@propertydef get_cols_count(self):"""作为一个属性,不能使用函数的调用方式get_cols_count()获取列数"""return self.sheet.ncolsdef get_row(self, row):"""获取一行的数据,行数是从1开始返回一个列表"""if row < 1 or row > self.get_rows_count:print (u'要获取的行参数有误')return Nonereturn self.sheet.row_values(row-1)def get_col(self, col):"""获取一列的数据,列数是从1或'A'开始返回一个列表"""if isinstance(col, str):col = ord(col)if col >= 65 and col <= 90:col -= 64else:col -= 96if col < 1 or col > self.get_cols_count:print (u'要获取的列参数有误')return Nonereturn self.sheet.col_values(col-1)def get_cell(self, row, col=None):"""获取单元格的值excel.get_cell('C18')excel.get_cell('c18')excel.get_cell(18, 'C')excel.get_cell(18, 'c')excel.get_cell(18, 3)"""if col is None and isinstance(row, str):col = row[0]row = int(row[1:])if row < 1 or row > self.get_rows_count:print (u'要获取的单元格参数[行号]有误')return Noneif isinstance(col, str):col = ord(col)if col >= 65 and col <= 90:col -= 64else:col -= 96if col < 1 or col > self.get_cols_count:print (u'要获取的列参数[列号]有误')return Nonereturn self.sheet.cell_value(row-1, col-1)# return self.sheet.row(row-1)[col-1].value# return self.sheet.col(col-1)[row-1].valueclass ExcelWrite(object):"""通过外部库pyExcelerator操作Excel表格文件目前只有写操作(没有读操作)实例化对象可以传入文件名也可以不传入文件名,不传入就要显式调用save(filename)进行保存传入或者保存的文件名不能是已存在的(为防止覆盖,对存在的文件不进行保存)同名无效"""def __init__(self, filename=None):self.workbook = Noneself.filename = filenameself.sheet_list = []self.isSave = False # 用来标识是否已经显式执行过save()了self.isExist = False # 用来标识是否已经存在同名文件if self.filename is None:print (u'请显式调用save(filename)进行保存')elif os.path.exists(self.filename):print (u'文件%s已存在,为防止覆盖文件,请更换文件名' % self.filename)self.filename = Noneself.isExist = Truereturnelse:print (u'无需显式调用save(filename)保存,对象在释放时会自动保存为%s' % self.filename)self.create_workbook()def __del__(self):"""析构函数,当没有显式执行过save()并且文件名非空且不是同名文件时会执行保存函数"""if self.filename is not None and self.isSave is False:self.save()def create_workbook(self):"""创建一个工作簿,在实例化中会自动创建"""if self.isExist:returnif self.workbook is None:self.workbook = pyExcelerator.Workbook()def add_sheet(self, sheetname=None):"""添加一张表到工作簿注：不能同名,可以不带名(默认为Sheet1,Sheet2,Sheet3...)同名无效"""if self.workbook is None:self.create_workbook()isExist = False # 用来标识是否存在同名的Sheet,True表示存在if sheetname is None:sheetname = 'Sheet' + str(len(self.sheet_list)+1)if sheetname in self.sheet_list:print (u'已存在名为%s的Sheet,请更改sheet名' % sheetname)isExist = Truesheet = Sheet(self.workbook, sheetname, isExist)if isExist is False:self.sheet_list.append(sheetname)return sheetdef save(self, filename=None):"""保存工作簿到文件同名无效"""if self.isExist:returnif filename is None:if self.filename is None:print (u'请输入文件名进行保存')return Falseelse:filename = self.filenameelse:if os.path.exists(filename):print (u'当前文件已存在,为防止覆盖文件,请更换文件名进行保存')return Falseif len(self.sheet_list) > 0:self.workbook.save(filename)print (u'保存成功,共创建了%d张表,实际保存在%s' % (len(self.sheet_list), filename))self.isSave = Truereturn Trueelse:print (u'没有创建工作表,无法进行保存')self.isSave = Truereturn False