python：requests请求进行接口测试，通过xlrd读取excel测试用例，并利用xlutils把执行结果写入excel用例中

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

"""
1、读取excel
2、构建对应请求
3、写入执行结果到excel
"""

#打开原excel用例
exceldir = r"C:\Users\Administrator\Desktop\testcase01.xls"
excel = xlrd.open_workbook(exceldir)
case_sheet = excel.sheets()[0]
rows = case_sheet.nrows

#写入excel函数
old_excel = xlrd.open_workbook(exceldir, formatting_info=True)
new_excel = copy(old_excel)
def write_execl(excel_result):
    test_case_sheet = new_excel.get_sheet(0)
    test_case_sheet.write(i, 11, excel_result)

#get请求函数
def get_requests(url,get_data,auth,code,msg):
    r = requests.get(url=url, params=get_data, auth=auth)
    result = r.json()
    if result["status"] == code and result["message"] == msg:
        print(result["status"],":code一致", )
        print(result["message"],":message一致", )
        excel_result = "通过"
    else:
        excel_result = "失败"
    write_execl(excel_result)

#for循环读取excel用例
for i in range(2,rows):
    url = "http://127.0.0.1:8000/api/get_event_list/"
    get_data = json.loads(case_sheet.cell_value(i,6))
    user = case_sheet.cell_value(i,7)
    pwd = case_sheet.cell_value(i,8)
    auth = (user,pwd)
    code = int(case_sheet.cell_value(i,9))
    msg = case_sheet.cell_value(i,10)
    get_requests(url,get_data,auth,code,msg)

#对最后写入执行结果的excel保存操作
new_excel.save(r"C:\Users\Administrator\Desktop\testcase-new.xls")