Python读excel（个人写法经验）

python

xlrd

先导入：import xlrdimport sysreload(sys)sys.setdefaultencoding('utf-8')

class OpExcel(object): '''读取excel''' def __init__(self, file_path): super(OpExcel, self).__init__() self.file_path=file_path self.Excel = xlrd.open_workbook(file_path) self.tables = self.Excel.sheet_names() def get_tableindex(self,table):#获取excel需要操作的sheet的索引值 if table in self.tables: index_data=self.tables.index(table) return (index_data) else: print (table+u'不是excel的sheet名称') return False

def get_data(self):#获取excel所有数据已字典形式保存，按行取excle={}for table in self.tables:all_value=[]self.sheet=self.Excel.sheet_by_name(table)for i in range(self.sheet.nrows):all_value.append(self.sheet.row_values(i))excle[table]=all_valuereturn excle

def get_rowdate(self,table,nrow=0,mcol=0):#从M列开始获取第N行数据self.sheet=self.Excel.sheet_by_name(table)data=self.sheet.row_values(nrow)return (data[mcol:])def get_coldate(self,table,nrow=0,mcol=0):#从N行开始获取第M列数据self.sheet=self.Excel.sheet_by_name(table)data=self.sheet.col_values(mcol)return (data[nrow:])

def get_cellate(self,table,nrow=0,mcol=0):#获取某个单元格数据和类型#ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 errorself.sheet=self.Excel.sheet_by_name(table)type=self.sheet.cell(nrow,mcol).ctypeif (type == 3):data_value = xlrd.xldate_as_tuple(self.sheet.cell(nrow,mcol).value,self.Excel.datemode)value = datetime(*data_value).strftime('%Y/%m/%d %H:%M:%S') else:value=self.sheet.cell(nrow,mcol).valuedata=[type,value]return (data)def get_mergedcell(self,table):#获取合并单元格有值得行和列self.sheet=self.Excel.sheet_by_name(table)data = []for (nrow,rowhigh,mcol,colhigh) in self.sheet.merged_cells:data.append([nrow,mcol])return (data)

def get_ctype3(self,table):#检查每一个单元格的ctype:0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error#输出ctype=3的单元格nrows=self.get_nrows(table)mcols=self.get_mcols(table)data=[]for nrow in range(nrows):for mcol in range(mcols):type=self.sheet.cell(nrow,mcol).ctypeif (type==3):data.append([nrow,mcol])if data !=[]:return dataelse:return False

def get_datarow(self,table,data,cols=0):#根据内容查找nrow行if type(data)==str or type(data)==unicode:mcol=Nonenlist=[]datanrow={}table_data=self.get_coldate(table,0,cols)print(u'开始查找数据')for nrow,row_data in enumerate(table_data):if row_data == data:nlist.append(nrow)print ('%s is in the %s Row'%(data,str(nrow)))if nlist!=[] :datanrow[data]=nlistreturn (datanrow)else:print (u'没匹配到数据')return Falseelse:print('Value type is wrong')return False