1、在excel里写上接口的参数

序号	接口名	url	请求方法	headers	请求参数
1	直考列表	/before/edutra/directPlan/qryPage	post	{"Content-Type": "application/json"}	{"current":1,"size":10,"type":0}
2	新培列表	/before/edutra/etNewPlan/queryPage	post	{"Content-Type": "application/json"}	{"current":1,"size":10,"type":0}

2、写脚本  读取excel文件，请求后将返回的参数保存在excel里

import pandas as pd
import requests
import json

"""
# 写入excel例子
list1 = [1,2,3,4,5]
list2 = ['a','b','c','d','e']
list3 = ['北京','深圳','上海','南京','成都']
list4 = [21,22,23,24,25]
list5 = [12000,13000,14000,15000,16000]

writeData = {
    '序号':list1,
    '人员':list2,
    '地址':list3,
    '年龄':list4,
    '月薪':list5
}

fwriter = pds.DataFrame(writeData)
fwriter.to_excel("test.xlsx", index=False)
"""


"""
preurl = "https://***.***.com"
url = "/***/edutra/***/firm/statistics"
u = preurl + url
headers = {"Content-Type": "application/json",}
data = {"userCode":"91530802MA6PT18X2D"}
print(u)
method = 'post'
if method == 'post':
    r = requests.post(url=u, headers=headers, data=json.dumps(data))
    print(r.text)
"""


list_num=[]
list_name=[]
list_url=[]
list_result=[]

df = pd.read_excel(r"test1case.xlsx",engine='openpyxl')  # 读取文件，读出来的格式为dataframe
records = df.to_dict(orient='records')  # 将dataframe转换为字典格式




print(len(records))
for re in records:
    list_num.append(re["序号"])
    list_name.append(re["接口名"])
    list_url.append(re["url"])
    try:
        preurl = re["prefix"]
        url = re["url"]
        u = preurl + url
        try:
            headers = json.loads(re["headers"].replace("'", '"'))
        except:
            headers = None
            print("请求头为空")
        # print(headers)

        try:
            p = json.loads(re["请求参数"])  # 先将字符串转换为python对象，在请求里在用dumps（）方法转为json
        except:
            print("请求参数为空")
        # print(p)

        method = re["请求方法"]
        # print("调用方法：" + method)

        if method == 'post':    # post方法的实现
            if headers is None:    # 有请求头 有参数
                print("header为空")
                r = requests.post(url=u)
                # print(r.text)

            else:
                print("header不为空")   # 请求头 参数为空
                r = requests.post(url=u, headers=headers, data=json.dumps(p))

            # print(r.text)
            # print(r.request.headers)
            # print(r.request.body)
            list_result.append(r.text)
        # elif method == 'get':    # get请求方法的实现

    except:
        list_result.append("请求出错，请检查脚本")
        print("请求出错，请检查脚本")


print(list_num)
print(list_name)
print(list_url)
print(list_result)



result = {
    "序号": list_num,
    "接口名":list_name,
    "url": list_url,
    "result": list_result
}
fwriter = pd.DataFrame(result)
fwriter.to_excel("test.xlsx", index=False)