from openpyxl import load_workbook
from common.contants import case_file
class ReadExcel:
def __init__(self,filename,sheetname=None):
self.filename=filename
self.sheetname=sheetname
def get_cases(self):
'''获取所有测试用例'''
wb=load_workbook(self.filename) #打开指定文件
if self.sheetname is None: #判断是否是None,不要用==,要用is
ws=wb.active #默认激活第一页
else:
ws=wb[self.sheetname] #打开sheet页
header_data=tuple(ws.iter_rows(max_row=1))[0] # 获取表头信息,转为元组
other_data=tuple(ws.iter_rows(min_col=2))#获取其他行数据,转为元组
test_data=[]
for data in other_data: #将两个元组合成字典,返回嵌套字典的列表
data_dict=dict(zip(header_data,data))
test_data.append(data_dict)
return test_data
def get_row_data(self,row):
'''获取某一行数据'''
return self.get_cases()[row-1]
def write_data(self,row,actual,result):
'''将数据写入excel'''
wb=load_workbook(self.filename)
if self.sheetname is None: #判断是否是None,不要用==,要用is
ws=wb.active
else:
ws=wb[self.sheetname]
if isinstance(row,int) and 2 <=row <= ws.max_row:
ws.cell(row=row,column=6,values=actual)
ws.cell(row=row,column=7,value=actual)
ws.save(self.filename)
else:
print('传入的行号有误,行号应为大于1的整数!')
if __name__ =="__main__":
filename=case_file
print(ReadExcel(filename).get_row_data(3))
python 操作excel openpyxl
最新推荐文章于 2024-01-06 12:38:08 发布