步骤：

1.导入数据

2.读取数据(readxlsx.py)

from openpyxl import load_workbook
class UseExcel():
    def get_TestExcel(self):
        #打开表
        workbook = load_workbook('D:\\pythonstudy\\1\\request01\\datademo\\520.xlsx')
        #定位表单
        sheet = workbook['Sheet1']
        print(sheet.max_row)  #3行
        print(sheet.max_column) #3列
        test_data = [] #把所有行的数据放到列表中
        for i in range(2,sheet.max_row+1):
            sub_data = {}   #把每行的数据放到字典中
            for j in range(1,sheet.max_column+1):
                sub_data[sheet.cell(1,j).value] = sheet.cell(i,j).value
            test_data.append(sub_data) #拼接每行单元格的数据
        return test_data
t = UseExcel()
f = t.get_TestExcel()
print(f)

3.请求接口，获取响应状态码(request_xlsx.py)

import requests
from readdemo.readxlsx import UseExcel
ue = UseExcel()
lists = ue.get_TestExcel()
class RequestsExcelClass():
    def requestsExcelMethod(self):
        item = []
        for excel_i in lists:
            if excel_i['method'] =='get':
                rr = requests.get(excel_i['url'],params=excel_i['params'])
                item.append(rr)
            else:
                rr = requests.post(excel_i['url'], params=excel_i['params'])
                item.append(rr)
        return item
rc = RequestsExcelClass()
print(rc.requestsExcelMethod())

4.编写测试用例，断言实际结果与200是否一致

5.生成测试报告(testexcel.py)

import pytest,allure,os
from request_demo.request_xlsx import RequestsExcelClass
rc = RequestsExcelClass().requestsExcelMethod()
class TestClass():
    def test001(self):
        for c in rc:
            assert c ==200
if __name__ == '__main__':
    pytest.main(['--alluredir', 'report/result', 'testexcel.py'])
    split = 'allure ' + 'generate ' + './report/result ' + '-o ' + './report/html ' + '--clean'
    os.system(split)