1.编写读取xls函数方法。file_utils.py

# 读取xls
def pares_xls_file(filepath, sheetname):
    xls_sheet = xlrd.open_workbook(filepath)  # r"D:\untitled\API_Project\data\驱动原件.xls"
    work_sheet = xls_sheet.sheet_by_name(sheetname)  # 'JH21207-134'
    row = work_sheet.nrows  # 总行数
    # 遍历所有xls数据
    test_data = []
    for x in range(1, row):
        testcase_data = []
        for y in range(0, 8):
            testcase_data.append(work_sheet.cell(x, y).value)
        test_data.append(testcase_data)
    return test_data

2.调用遍历方法，使用@pytest.mark.parametrize进行参数化，然后根据表格状态判断用例是否执行，最后再断言返回信息。test_excel.py

# 用例层
class TestLoginCase(object):
    # 调用遍历用例方法
    test_data = pares_xls_file("D:/untitled/API_Project/data/驱动原件.xls", 'JH21207-134')
    # allure 中提取字段展示为title
    @allure.title("{CASE_NAME}--用例状态:{execute}")
    @pytest.mark.parametrize('CASE_ID,CASE_NAME,execute,url,method,topic_data,code,result', test_data)
    def test_001(self, CASE_ID, CASE_NAME, execute, url, method, topic_data, code, result):
        """
        :param CASE_ID: 用例ID
        :param CASE_NAME: 用例名称
        :param execute: 用例状态
        :param url: 请求URL
        :param method: 请求方式
        :param topic_data: 入参
        :param code:出参
        :param result:执行结果
        """
        if execute == "有效":
            print("此条用例选择的是{}执行接口测试。".format(execute))
            if method == 'POST':
                headers = {"Content-Type": "application/json"}
                res = requests.post(url, headers=headers, params=json.loads(topic_data))
                assert res.status_code == 200
                myLogger.info(('返回结果是{}'.format(res.text)))
            else:
                headers = {"Content-Type": "application/json"}
                res = requests.get(url, headers=headers, params=json.loads(topic_data))
                assert res.status_code == 200
                myLogger.info(('返回结果是{}'.format(res.text)))
        else:
            print("此条用例选择的是{}因此不执行。".format(execute))


if __name__ == "__main__":
    pytest.main()

3.pytest.ini配置文件

[pytest]

addopts = -sv --alluredir report --clean-alluredir

testpaths = case/text_excel.py

pytest_classes = Test*

pytest_functions = test_*

4.执行用例层 run_api.py

import os
from shutil import copy
import pytest


if __name__ == '__main__':
    pytest.main()
    os.system('allure generate report --clean')
    os.system('allure serve report')