自动化执行Excel用例优化

V3.0
把响应的数据格式转为json
读取Excel数据，可以读取任意行。[001,002-005,006]

# -*- coding: utf-8 -*-
# 2022/1/21
# LiuRenHong
import xlrd
import json
"""
V1.0
读取指定行列数据
实现用例筛选功能
脚本代码和用例分离
V2.0
按照用户需求，读取任意行任意列数据----可变数量参数，*args元组
V3.0
把响应的数据格式转为json
读取Excel数据，可以读取任意行。[001,002-005,006]

"""
epath = "C:\\Users\PC\PycharmProjects\IamGod\data\Delivery_System_V1.5.xls"
def get_excel_data_v1(excelPath, sheetName,  moduleName):
    resList = []#存放结果
    #formatting_info=True 保持样式，打开xls文件
    workbook = xlrd.open_workbook(excelPath, formatting_info=True)
    # print(workbook.sheet_names())#展示所有的表单
    worksheet = workbook.sheet_by_name(sheetName)
    #读取一行数据
    # print(worksheet.row_values(0))
    #读取一列数据
    print(worksheet.col_values(0))
    # print(worksheet.cell(0, 1).value)
    #筛选用例
    idx = 0#行的初始值
    for one in worksheet.col_values(0):
        moduleList = []
        if moduleName in one:
            #获取对应值
            a = worksheet.cell(idx, 9).value
            b = worksheet.cell(idx, 11).value
            resList.append((a,b))
        idx = idx+1
    return resList

def get_excel_data_v2(excelPath, sheetName, caseName, *args):
    resList = []
    argList = []
    workBook = xlrd.open_workbook(excelPath, formatting_info=True)
    workSheet = workBook.sheet_by_name(sheetName)
    print(workSheet.col_values(0))
    for i in args:
        # workSheet.row_values(0).index(i)获取下标
        argList.append(workSheet.row_values(0).index(i))
    print("可变数量参数对应的列为：", argList)
    idx = 0
    for one in workSheet.col_values(0):
        if caseName in one:
            getColData = []#---------------------------必须每次都初始化
            for i in argList:
                res = workSheet.cell(idx, i).value
                getColData.append(res)
            resList.append(getColData)
        idx = idx + 1
    return resList

def is_json(str1):
    try:
        json.loads(str1)
    except ValueError:
        return False
    return True

def get_excel_data_v3(excelPath, sheetName, caseName, *args, runCase=['all']):
    resList = []
    idx = 0
    workBook = xlrd.open_workbook(excelPath, formatting_info=True)
    workSheet = workBook.sheet_by_name(sheetName)
    colList = []
    for one in args:
        colList.append(workSheet.row_values(0).index(one))
    # print("可变数量参数对应的下标是",colList)
    runList = []
    if runCase[0] == 'all':
        runList = workSheet.col_values(0)
    else:
        for i in runCase:
            if '-' in i:
                start_str = i.split('-')[0]
                end_str = i.split('-')[-1]
                for i in range(int(start_str),int(end_str)+1):
                    runList.append(caseName+f"{i:0>3}")
            else:
                runList.append(caseName+f"{i:0>3}")
    # print("选择某几行用例执行",runList)

    for one in workSheet.col_values(0):
        # print("caseName和列名分别是", caseName, one)
        if (caseName in one) and (one in runList):
            get_Col_list = []
            for i in colList:
                # get_Col_list = []
                a = workSheet.cell(idx, i).value
                if is_json(a):
                    a = json.loads(a)
                get_Col_list.append(a)
            resList.append(get_Col_list)
        idx = idx + 1##############################################
        #严重错误，不可以把不同的循环放到同一层级
    return resList

if __name__ == '__main__':
    # res = get_excel_data_v1(epath, "登录模块", "Login")
    # res = get_excel_data_v2(epath, "登录模块", "Login", "URL", "请求参数", "预期结果")
    res = get_excel_data_v3(epath, "登录模块", "Login", "用例编号", "URL", "请求参数", "预期结果", runCase=["001","002-004","006"])
    for i in res:
        print(i)

# -*- coding: utf-8 -*-
# 2022/1/24
# LiuRenHong

from tools.excelControl import get_excel_data_v3
from libs.login import Login
from configs.confg import epath
import pytest
import os

class TestLogin:
    # for i in res:
    #     # print("预期结果------",i)
    #     res1 = Login().login(i[2])
    #     # print("实际响应结果-----",res1)
    #     if i[3]["msg"] == res1["msg"]:
    #         print("严重通过")
    #     else:
    #         print("验证不通过")
    res = get_excel_data_v3(epath, "登录模块", "Login", "请求参数", "响应预期结果")
    @pytest.mark.parametrize(["reqData", "reqBody"],res)
    def test_login_f(self,reqData, reqBody):
        resResult = Login().login(reqData)
        assert reqBody["msg"] == resResult["msg"]

if __name__ == '__main__':
    """
    pytest 运行结果里  
    .  成功
    F  断言失败
    E  语法报错!
    """
    try:
        for one in os.listdir("../reports/tmp"):
            if ".json" in one:
                os.remove(f"../reports/tmp/{one}")#删除旧文件
    except:
        print("第一次执行报告文件，自动创建文件夹")
    pytest.main(['test_login.py','-s','--alluredir','../reports/tmp'])
    os.system("allure serve ../reports/tmp")