實(shí)現(xiàn)功能

1. 讀取excel,從excel中獲取入?yún)?、預(yù)期結(jié)果
2. 調(diào)用post接口，獲得接口返回(json格式)
3. 比較預(yù)期結(jié)果、實(shí)際結(jié)果
4. 將比較結(jié)果回寫(xiě)excel，并設(shè)置失敗案例的單元格顏色為紅色

excel數(shù)據(jù)格式

excel數(shù)據(jù)格式

代碼實(shí)現(xiàn)

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

# 結(jié)果比較
def compareObj(expected, actual):
    if expected != None:
        if actual != None:
            for key in expected:
                if key not in actual:
                    return '預(yù)期結(jié)果key %s不在實(shí)際結(jié)果中'%(key)
                else:
                    expected_value = expected[key]
                    actual_value=actual[key]
                    if expected_value == actual_value:
                        continue
                    else:
                        return 'key值%s:預(yù)期結(jié)果%s和實(shí)際結(jié)果%s不相等'%(key,expected_value,actual_value)
        else:
            return '實(shí)際結(jié)果為空'
    else:
        return '預(yù)期結(jié)果為空'
    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')# 設(shè)置運(yùn)行失敗的單元格顏色
    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 #設(shè)置單元格寬度
wb.save(file_name)

運(yùn)行結(jié)果

運(yùn)行結(jié)果