# coding=utf-8
import requests, xlrd, pymysql, random, xlutils, copy, time, openpyxl


def readexcel(file_path):

    try:
        book=xlrd.open_workbook(file_path)
    except Exception as e:
        print("文件路径不对")
        return e
    else:
        # 获取第一张表
        sheet1 = book.sheets()[0]
        rows = sheet1.nrows  # 获取表的总列数
        # 保存每一条case
        case_list=[]
        for i in range(rows):
            if i !=0:
                case_list.append(sheet1.row_values(i))

        interfacetest(case_list,file_path)


def interfacetest (case_list,file_path):
    # 存放测试结果的list
    res_flags=[]
    # 存放请求报文的list
    request_url=[]
    # 存放响应的list
    responses=[]
    # 遍历测试用例
    for case in case_list:
        ‘‘‘
        遍历每一条case
        ‘‘‘
        try:
            part=case[0]
            case_id=case[1]
            interfacename=case[2]
            method=case[3]
            url=case[4]
            param=case[5]
            res_check=case[6]
        except Exception as e:
            print("测试用例格式错误,%s" %e)
        if param==" ":
            new_url=url
            request_url.append(new_url)
        else:
            new_url=url+"?"+param.replace(";","&")
            request_url.append(new_url)
        if method.upper()=="GET":
            results=requests.get(new_url).text
            print(results)
            res_flag = readRes(results, res_check)
            res_flags.append(res_flag)
            responses.append(results)
        else:
            results = requests.post(new_url).text
            print(results)
            res_flag=readRes(results,res_check)
            res_flags.append(res_flag)
            responses.append(results)
    recordexcel(file_path,res_flags,request_url,responses)



def readRes(res,res_check):
    ‘‘‘
    :param res: 返回报文
    :param res_check: 预期结果
    :return: 是否通过
    ‘‘‘
    res=res.replace(":","=")
    res_check=res_check.split(";")
    for s in res_check:
        if s in res:
            pass
        else:
            return "返回参数与要求不一致"+str(s)
    return "pass"


def recordexcel(file_path,res_flag,request_url,responses):
    ‘‘‘
    将结果写入excel
    :param file_path:
    :param res_flag:
    :param request_url:
    :param responses:
    :return:
    ‘‘‘
    book=xlrd.open_workbook(file_path)
    new_book=copy.copy(book)
    sheet=new_book.sheet_by_index(0)
    i=1
    for flag,url,response in zip(res_flag,request_url,responses):
        sheet.write(i,8,u‘%s‘ %flag)
        sheet.write(i, 9, u‘%s‘ % url)
        sheet.write(i, 10,u‘%s‘ % response)
        i+=1
    new_book.save(‘%s 测试结果.xls‘ %time.shiftime("%Y%m%d%H%M%S"))


readexcel("C:\\Users\\admin\\Desktop\\0116.xlsx")