实现功能

1. 读取excel,从excel中获取入参、预期结果
2. 调用post接口，获得接口返回(json格式)
3. 比较预期结果、实际结果
4. 将比较结果回写excel，并设置失败案例的单元格颜色为红色

excel数据格式

excel数据格式

代码实现

import xlrd
from xlutils.copy import copy
import requests
import json
import xlwt

# 结果比较
def compareObj(expected, actual):
    if expected != None:
        if actual != None:
            for key in expected:
                if key not in actual:
                    return '预期结果key %s不在实际结果中'%(key)
                else:
                    expected_value = expected[key]
                    actual_value=actual[key]
                    if expected_value == actual_value:
                        continue
                    else:
                        return 'key值%s:预期结果%s和实际结果%s不相等'%(key,expected_value,actual_value)
        else:
            return '实际结果为空'
    else:
        return '预期结果为空'
    return True

url= 'http://localhost:1080/test'
file_name = 'D:\\test.xls'
data = xlrd.open_workbook(file_name)
sheet=data.sheets()[0]

wb=copy(data)
ws=wb.get_sheet(0)

for i in range(1,37):
    params = sheet.cell(i,1).value
    #print(params)
    header = {"Content-Type":"application/json"}
    res = requests.post(url = url,headers = header,data = params)
    actual = res.json()["obj"]
    expected = json.loads(sheet.cell(i,2).value)
    result = compareObj(expected,actual)
    #print(result)
    style = xlwt.easyxf('pattern: pattern solid, fore_colour red')# 设置运行失败的单元格颜色
    if result != True:
        ws.write(i, 3, result,style)
    else:
        ws.write(i, 3, result)

for i in range(4):
    ws.col(i).width = 256 * 50 #设置单元格宽度
wb.save(file_name)

运行结果

运行结果