python3 excel title 智能识别,python - 接口自动化测试 - ReadExcel - 读取测试数据封装...

python - 接口自动化测试 - ReadExcel - 读取测试数据封装

# -*- coding:utf-8 -*-

'''

@project: ApiAutoTest

@author: Jimmy

@file: read_excel.py

@ide: PyCharm Community Edition

@time: 2018-12-20 10:34

@blog: https://www.cnblogs.com/gotesting/

'''

import openpyxl

import os

from Common.contants import *

class Case:

def __init__(self):

self.case_id = None

self.url = None

self.data = None

self.title = None

self.method = None

self.expected = None

class ReadExcel:

def __init__(self,file_name):

try:

self.filename = os.path.join(data_dir,file_name)

self.wb = openpyxl.load_workbook(self.filename)

except FileNotFoundError as e:

print('{0} not found , please check file path'.format(self.filename))

raise e

def get_cases(self,sheet_name):

sheet = self.wb[sheet_name]

max_row = sheet.max_row

test_cases = []

for r in range(2,max_row+1):

case = Case() # 实例化一个data对象，用于存放读取的测试数据

case.case_id = sheet.cell(row=r,column=1).value

case.title = sheet.cell(row=r,column=2).value

case.method = sheet.cell(row=r,column=3).value

case.url = sheet.cell(row=r,column=4).value

case.data = sheet.cell(row=r,column=5).value

case.expected = sheet.cell(row=r,column=6).value

test_cases.append(case)

return test_cases

# 获取到workbook里面所有的sheet名称的列表

def get_sheet_name(self):

return self.wb.sheetnames

# 根据sheet_name定位到sheet，根据case_id定位到行，写入result

def write_result(self,sheet_name,case_id,actual,result):

sheet = self.wb[sheet_name]

max_row = sheet.max_row

for r in range(2,max_row+1):

# 获取第r行第1列的 case_id值

case_id_r = sheet.cell(row=r,column=1).value

# 判断excel读取的当前行的case_id_r是否与传入的case_id相同

if case_id_r == case_id:

sheet.cell(row=r,column=7).value = actual

sheet.cell(row=r,column=8).value = result

self.wb.save(filename=self.filename)

break

python - 接口自动化测试 - ReadExcel - 读取测试数据封装相关教程