python自动化测试之如何解析excel文件
前言
自动化测试中我们存放数据无非是使用文件或者数据库，那么文件可以是csv，xlsx，xml，甚至是txt文件，通常excel文件往往是我们的首选，无论是编写测试用例还是存放测试数据，excel都是很方便的。那么今天我们就把不同模块处理excel文件的方法做个总结，直接做封装，方便我们以后直接使用，增加工作效率。
openpyxl
openpyxl是个第三方库，首先我们使用命令pipinstallopenpyxl直接安装
注：openpyxl操作excel时，行号和列号都是从1开始计算的
封装代码
"""
------------------------------------
@Time:2019/5/1318:00
@Auth:linux超
@File:ParseExcel.py
@IDE:PyCharm
@Motto:Realwarriors,daretofacethebleakwarning,daretofacetheincisiveerror!
------------------------------------
"""
fromopenpyxlimportload_workbook
fromopenpyxl.stylesimportFont
fromopenpyxl.styles.colorsimportBLACK
fromcollectionsimportnamedtuple
classParseExcel(object):
"""解析excel文件"""
def__init__(self,filename,sheet_name=None):
try:
self.filename=filename
self.sheet_name=sheet_name
self.wb=load_workbook(self.filename)
ifself.sheet_nameisNone:
self.work_sheet=self.wb.active
else:
self.work_sheet=self.wb[self.sheet_name]
exceptFileNotFoundErrorase:
raisee
defget_max_row_num(self):
"""获取最大行号"""
max_row_num=self.work_sheet.max_row
returnmax_row_num
defget_max_column_num(self):
"""获取最大列号"""
max_column=self.work_sheet.max_column
returnmax_column
defget_cell_value(self,coordinate=None,row=None,column=None):
"""获取指定单元格的数据"""
ifcoordinateisnotNone:
try:
returnself.work_sheet[coordinate].value
exceptExceptionase:
raisee
elifcoordinateisNoneandrowisnotNoneandcolumnisnotNone:
ifisinstance(row,int)andisinstance(column,int):
returnself.work_sheet.cell(row=row,column=column).value
else:
raiseTypeError('rowandcolumnmustbetypeint')
else:
raiseException("InsufficientCoordinateofcell!")
defget_row_value(self,row):
"""获取某一行的数据"""
column_num=self.get_max_column_num()
row_value=[]
ifisinstance(row,int):
forcolumninrange(1,column_num+1):
values_row=self.work_sheet.cell(row,column).value
row_value.append(values_row)
returnrow_value
else:
raiseTypeError('rowmustbetypeint')
defget_column_value(self,column):
"""获取某一列数据"""
row_num=self.get_max_column_num()
column_value=[]
ifisinstance(column,int):
forrowinrange(1,row_num+1):
values_column=self.work_sheet.cell(row,column).value
column_value.append(values_column)
returncolumn_value
else:
raiseTypeError('columnmustbetypeint')
defget_all_value_1(self):
"""获取指定表单的所有数据(除去表头)"""
max_row_num=self.get_max_row_num()
max_column=self.get_max_column_num()
values=[]
forrowinrange(2,max_row_num+1):
value_list=[]
forcolumninrange(1,max_column+1):
value=self.work_sheet.cell(row,column).value
value_list.append(value)
values.append(value_list)
returnvalues
defget_all_value_2(self):
"""获取指定表单的所有数据(除去表头)"""
rows_obj=self.work_sheet.iter_rows(min_row=2,max_row=self.work_sheet.max_row,
values_only=True)#指定values_only会直接提取数据不需要再使用cell().value
values=[]
forrow_tupleinrows_obj:
value_list=[]
forvalueinrow_tuple:
value_list.append(value)
values.append(value_list)
returnvalues
defget_excel_title(self):
"""获取sheet表头"""
title_key=tuple(self.work_sheet.iter_rows(max_row=1,values_only=True))[0]
returntitle_key
defget_listdict_all_value(self):
"""获取所有数据，返回嵌套字典的列表"""
sheet_title=self.get_excel_title()
all_values=self.get_all_value_2()
value_list=[]
forvalueinall_values:
value_list.append(dict(zip(sheet_title,value)))
returnvalue_list
defget_list_nametuple_all_value(self):
"""获取所有数据，返回嵌套命名元组的列表"""
sheet_title=self.get_excel_title()
values=self.get_all_value_2()
excel=namedtuple('excel',sheet_title)
value_list=[]
forvalueinvalues:
e=excel(*value)
value_list.append(e)
returnvalue_list
defwrite_cell(self,row,column,value=None,bold=True,color=BLACK):
"""
指定单元格写入数据
:paramwork_sheet:
:paramrow:行号
:paramcolumn:列号
:paramvalue:待写入数据
:parambold:加粗，默认加粗
:paramcolor:字体颜色，默认黑色
:return:
"""
try:
ifisinstance(row,int)andisinstance(column,int):
cell_obj=self.work_sheet.cell(row,column)
cell_obj.font=Font(color=color,bold=bold)
cell_obj.value=value
self.wb.save(self.filename)
else:
raiseTypeError('rowandcolumnmustbetypeint')
exceptExceptionase:
raisee
if__name__=='__main__':
pe=ParseExcel('testdata.xlsx')
#sheet=pe.get_sheet_object('testcase')
column_row=pe.get_max_column_num()
print('最大列号:',column_row)
max_row=pe.get_max_row_num()
print('最大行号:',max_row)
#
cell_value_1=pe.get_cell_value(row=2,column=3)
print('第%d行,第%d列的数据为:%s'%(2,3,cell_value_1))
cell_value_2=pe.get_cell_value(coordinate='A5')
print('A5单元格的数据为:{}'.format(cell_value_2))
value_row=pe.get_row_value(3)
print('第{}行的数据为:{}'.format(3,value_row))
value_column=pe.get_column_value(2)
print('第{}列的数据为:{}'.format(2,value_column))
#
values_1=pe.get_all_value_1()
print('第一种方式获取所有数据\n',values_1)
values_2=pe.get_all_value_2()
print('第二种方式获取所有数据\n',values_2)
title=pe.get_excel_title()
print('表头为\n{}'.format(title))
dict_value=pe.get_listdict_all_value()
print('所有数据组成的嵌套字典的列表:\n',dict_value)
#
namedtuple_value=pe.get_list_nametuple_all_value()
print('所有数据组成的嵌套命名元组的列表:\n',namedtuple_value)
pe.write_cell(1,2,'Tc_title')
#addbylinux超at2019/05/2215:58
上面这个封装如如果用来同时操作同一个excel文件的两个sheet写入数据时，会有点小bug（写完后你会发现两个表单有一个是没有数据的）
其实原因很简单：不同对象拥有自己独立的属性，当你写操作的时候其实每个对象只针对自己的表单做了保存，所以最后一个对象写完数据后，只保存了自己的表单，其他的对象的表单实际是没有保存的。针对这个问题，对上面封装的代码进行了轻微改动
"""
------------------------------------
@Time:2019/5/229:11
@Auth:linux超
@File:ParseExcel.py
@IDE:PyCharm
@Motto:Realwarriors,daretofacethebleakwarning,daretofacetheincisiveerror!
------------------------------------
"""
fromopenpyxlimportload_workbook
fromopenpyxl.stylesimportFont
fromopenpyxl.styles.colorsimportBLACK
fromcollectionsimportnamedtuple
classParseExcel(object):
"""解析excel文件"""
def__init__(self,filename):
try:
self.filename=filename
self.__wb=load_workbook(self.filename)
exceptFileNotFoundErrorase:
raisee
defget_max_row_num(self,sheet_name):
"""获取最大行号"""
max_row_num=self.__wb[sheet_name].max_row
returnmax_row_num
defget_max_column_num(self,sheet_name):
"""获取最大列号"""
max_column=self.__wb[sheet_name].max_column
returnmax_column
defget_cell_value(self,sheet_name,coordinate=None,row=None,column=None):
"""获取指定单元格的数据"""
ifcoordinateisnotNone:
try:
returnself.__wb[sheet_name][coordinate].value
exceptExceptionase:
raisee
elifcoordinateisNoneandrowisnotNoneandcolumnisnotNone:
ifisinstance(row,int)andisinstance(column,int):
returnself.__wb[sheet_name].cell(row=row,column=column).value
else:
raiseTypeError('rowandcolumnmustbetypeint')
else:
raiseException("InsufficientCoordinateofcell!")
defget_row_value(self,sheet_name,row):
"""获取某一行的数据"""
column_num=self.get_max_column_num(sheet_name)
row_value=[]
ifisinstance(row,int):
forcolumninrange(1,column_num+1):
values_row=self.__wb[sheet_name].cell(row,column).value
row_value.append(values_row)
returnrow_value
else:
raiseTypeError('rowmustbetypeint')
defget_column_value(self,sheet_name,column):
"""获取某一列数据"""
row_num=self.get_max_column_num(sheet_name)
column_value=[]
ifisinstance(column,int):
forrowinrange(1,row_num+1):
values_column=self.__wb[sheet_name].cell(row,column).value
column_value.append(values_column)
returncolumn_value
else:
raiseTypeError('columnmustbetypeint')
defget_all_value_1(self,sheet_name):
"""获取指定表单的所有数据(除去表头)"""
max_row_num=self.get_max_row_num(sheet_name)
max_column=self.get_max_column_num(sheet_name)
values=[]
forrowinrange(2,max_row_num+1):
value_list=[]
forcolumninrange(1,max_column+1):
value=self.__wb[sheet_name].cell(row,column).value
value_list.append(value)
values.append(value_list)
returnvalues
defget_all_value_2(self,sheet_name):
"""获取指定表单的所有数据(除去表头)"""
rows_obj=self.__wb[sheet_name].iter_rows(min_row=2,max_row=self.__wb[sheet_name].max_row,values_only=True)
values=[]
forrow_tupleinrows_obj:
value_list=[]
forvalueinrow_tuple:
value_list.append(value)
values.append(value_list)
returnvalues
defget_excel_title(self,sheet_name):
"""获取sheet表头"""
title_key=tuple(self.__wb[sheet_name].iter_rows(max_row=1,values_only=True))[0]
returntitle_key
defget_listdict_all_value(self,sheet_name):
"""获取所有数据，返回嵌套字典的列表"""
sheet_title=self.get_excel_title(sheet_name)
all_values=self.get_all_value_2(sheet_name)
value_list=[]
forvalueinall_values:
value_list.append(dict(zip(sheet_title,value)))
returnvalue_list
defget_list_nametuple_all_value(self,sheet_name):
"""获取所有数据，返回嵌套命名元组的列表"""
sheet_title=self.get_excel_title(sheet_name)
values=self.get_all_value_2(sheet_name)
excel=namedtuple('excel',sheet_title)
value_list=[]
forvalueinvalues:
e=excel(*value)
value_list.append(e)
returnvalue_list
defwrite_cell(self,sheet_name,row,column,value=None,bold=True,color=BLACK):
ifisinstance(row,int)andisinstance(column,int):
try:
cell_obj=self.__wb[sheet_name].cell(row,column)
cell_obj.font=Font(color=color,bold=bold)
cell_obj.value=value
self.__wb.save(self.filename)
exceptExceptionase:
raisee
else:
raiseTypeError('rowandcolumnmustbetypeint')
if__name__=='__main__':
pe=ParseExcel('testdata.xlsx')
print(pe.get_all_value_2('division'))
print(pe.get_list_nametuple_all_value('division'))
column_row=pe.get_max_column_num('division')
print('最大列号:',column_row)
max_row=pe.get_max_row_num('division')
print('最大行号:',max_row)
cell_value_1=pe.get_cell_value('division',row=2,column=3)
print('第%d行,第%d列的数据为:%s'%(2,3,cell_value_1))
cell_value_2=pe.get_cell_value('division',coordinate='A5')
print('A5单元格的数据为:{}'.format(cell_value_2))
value_row=pe.get_row_value('division',3)
print('第{}行的数据为:{}'.format(3,value_row))
value_column=pe.get_column_value('division',2)
print('第{}列的数据为:{}'.format(2,value_column))
values_1=pe.get_all_value_1('division')
print('第一种方式获取所有数据\n',values_1)
values_2=pe.get_all_value_2('division')
print('第二种方式获取所有数据\n',values_2)
title=pe.get_excel_title('division')
print('表头为\n{}'.format(title))
dict_value=pe.get_listdict_all_value('division')
print('所有数据组成的嵌套字典的列表:\n',dict_value)
namedtuple_value=pe.get_list_nametuple_all_value('division')
print('所有数据组成的嵌套命名元组的列表:\n',namedtuple_value)
pe.write_cell('division',1,2,'Tc_title')
xlrd
安装xlrd，此模块只支持读操作，如果要写需要使用xlwt或者使用xlutils配合xlrd，但是使用xlwt只能对新的excel文件进行写操作，无法对原有文件进行写，所以这里选择是用xlutils
但是还有一个问题就是，如果使用xlutils，那么我们的excel文件需要以.xls为后缀。因为以xlsx为后缀无法实现写，会报错(亲测，因为formatting_info参数还没有对新版本的xlsx的格式完成兼容)
注：xlrd操作excel时，行号和列号都是从0开始计算的
封装代码
"""
------------------------------------
@Time:2019/5/1321:22
@Auth:linux超
@File:ParseExcel_xlrd.py
@IDE:PyCharm
@Motto:Realwarriors,daretofacethebleakwarning,daretofacetheincisiveerror!
------------------------------------
"""
importxlrd
fromxlutilsimportcopy
fromcollectionsimportnamedtuple
classParseExcel(object):
#xlrd解析excel，行号和列号都是从0开始的
def__init__(self,filename,sheet):
try:
self.filename=filename
self.sheet=sheet
self.wb=xlrd.open_workbook(self.filename,formatting_info=True)
ifisinstance(sheet,str):
self.sheet=self.wb.sheet_by_name(sheet)
elifisinstance(sheet,int):
self.sheet=self.wb.sheet_by_index(sheet)
else:
raiseTypeError('sheetmustbeintorstr')
exceptExceptionase:
raisee
defget_max_row(self):
"""获取表单的最大行号"""
max_row_num=self.sheet.nrows
returnmax_row_num
defget_max_column(self):
"""获取表单的最大列号"""
min_row_num=self.sheet.ncols
returnmin_row_num
defget_cell_value(self,row,column):
"""获取某个单元格的数据"""
ifisinstance(row,int)andisinstance(column,int):
values=self.sheet.cell(row-1,column-1).value
returnvalues
else:
raiseTypeError('rowandcolumnmustbetypeint')
defget_row_values(self,row):
"""获取某一行的数据"""
ifisinstance(row,int):
values=self.sheet.row_values(row-1)
returnvalues
else:
raiseTypeError('rowmustbetypeint')
defget_column_values(self,column):
"""获取某一列的数据"""
ifisinstance(column,int):
values=self.sheet.col_values(column-1)
returnvalues
else:
raiseTypeError('columnmustbetypeint')
defget_table_title(self):
"""获取表头"""
table_title=self.get_row_values(1)
returntable_title
defget_all_values_dict(self):
"""获取所有的数据，不包括表头，返回一个嵌套字典的列表"""
max_row=self.get_max_row()
table_title=self.get_table_title()
value_list=[]
forrowinrange(2,max_row):
values=self.get_row_values(row)
value_list.append(dict(zip(table_title,values)))
returnvalue_list
defget_all_values_nametuple(self):
"""获取所有的数据，不包括表头，返回一个嵌套命名元组的列表"""
table_title=self.get_table_title()
max_row=self.get_max_row()
excel=namedtuple('excel',table_title)
value_list=[]
forrowinrange(2,max_row):
values=self.get_row_values(row)
e=excel(*values)
value_list.append(e)
returnvalue_list
defwrite_value(self,sheet_index,row,column,value):
"""写入某个单元格数据"""
ifisinstance(row,int)andisinstance(column,int):
ifisinstance(sheet_index,int):
wb=copy.copy(self.wb)
worksheet=wb.get_sheet(sheet_index)
worksheet.write(row-1,column-1,value)
wb.save(self.filename)
else:
raiseTypeError('{}mustbeint'.format(sheet_index))
else:
raiseTypeError('{}and{}mustbeint'.format(row,column))
if__name__=='__main__':
pe=ParseExcel('testdata.xls','testcase')
print('最大行号:',pe.get_max_row())
print('最大列号:',pe.get_max_column())
print('第2行第3列数据:',pe.get_cell_value(2,3))
print('第2行数据',pe.get_row_values(2))
print('第3列数据',pe.get_column_values(3))
print('表头:',pe.get_table_title())
print('所有的数据返回嵌套字典的列表:',pe.get_all_values_dict())
print('所有的数据返回嵌套命名元组的列表:',pe.get_all_values_nametuple())
pe.write_value(0,1,3,'test')
pandas
pandas是一个做数据分析的库，总是感觉在自动化测试中使用pandas解析excel文件读取数据有点大材小用，不论怎样吧，还是把pandas解析excel文件写一下把
我这里只封装了读，写的话我这有点小问题，后面改好再追加代码吧。
请先pipinstallpandas安装pandas
封装代码
"""
------------------------------------
@Time:2019/5/1314:00
@Auth:linux超
@File:ParseExcel_pandas.py
@IDE:PyCharm
@Motto:Realwarriors,daretofacethebleakwarning,daretofacetheincisiveerror!
------------------------------------
"""
importpandasaspd
classParseExcel(object):
def__init__(self,filename,sheet_name=None):
try:
self.filename=filename
self.sheet_name=sheet_name
self.df=pd.read_excel(self.filename,self.sheet_name)
exceptExceptionase:
raisee
defget_row_num(self):
"""获取行号组成的列表,从0开始的"""
row_num_list=self.df.index.values
returnrow_num_list
defget_cell_value(self,row,column):
"""获取某一个单元格的数据"""
try:
ifisinstance(row,int)andisinstance(column,int):
cell_value=self.df.ix[row-2,column-1]#ix的行参数是按照有效数据行，且从0开始
returncell_value
else:
raiseTypeError('rowandcolumnmustbetypeint')
exceptExceptionase:
raisee
defget_table_title(self):
"""获取表头，返回列表"""
table_title=self.df.columns.values
returntable_title
defget_row_value(self,row):
"""获取某一行的数据，行号从1开始"""
try:
ifisinstance(row,int):
row_data=self.df.ix[row-2].values
returnrow_data
else:
raiseTypeError('rowmustbetypeint')
exceptExceptionase:
raisee
defget_column_value(self,col_name):
"""获取某一列数据"""
try:
ifisinstance(col_name,str):
col_data=self.df[col_name].values
returncol_data
else:
raiseTypeError('col_namemustbetypestr')
exceptExceptionase:
raisee
defget_all_value(self):
"""获取所有的数据，不包括表头,返回嵌套字典的列表"""
rows_num=self.get_row_num()
table_title=self.get_table_title()
values_list=[]
foriinrows_num:
row_data=self.df.ix[i,table_title].to_dict()
values_list.append(row_data)
returnvalues_list
if__name__=='__main__':
pe=ParseExcel('testdata.xlsx','testcase')
print(pe.get_row_num())
print(pe.get_table_title())
print(pe.get_all_value())
print(pe.get_row_value(2))
print(pe.get_cell_value(2,3))
print(pe.get_column_value('Tc_title'))
总结
使用了3种方法，4个库xlrd，openpyxl，xlwt，pandas操作excel文件，个人感觉还是使用openpyxl比较适合在自动化中使用，当然不同人有不同选择，用哪个区别也不是很大。
以上3种方法，都可以拿来直接使用，不需要再做封装了！
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。