python接口测试直接提取Excel请求参数

#!/usr/bin/env python
# -*-coding:utf-8-*-
# author:BeeLu
import requests
import json
import hashlib
import ast
from random import randint

def ruzhu_add(indata):
    url = r'http://test.*********************'
    access_token = "*************"
    # if inTelstate:
    #         tel_num = '13'+str(randint(10000000000,9999999999))
    #         indata["phone"] = tel_mun   #随机生成手机

    headers = {
        "Content-Type": "application/json"
    }
    #data = json.loads(indata) # 变为为字典格式
    data = ast.literal_eval(indata) # 变为为字典格式,当json.loads不能转换成功时使用这种方法
    data = sorted(data.items(), key=lambda d: d[0], reverse=False) # 字典顺序排序,排完序变成list
    data = dict(data)  # 格式装换 list转换为字典
    params = ""
    for i in data:
        params += i + "=" + data[i] + '&' # 循环取data的数据并&连接起来
    params = params + "access_token=" + access_token
    key_sign = hashlib.md5(params.encode(encoding="utf-8")).hexdigest() # md5加密,有中文要改变encode
    data1 = {
            "publickKey": "*************",
            "privateKey": "*************",
            "key_sign": key_sign
            }
    data.update(data1) # 将data1中的字典数据加到data中去
    response = requests.post(url=url, data=json.dumps(data), headers=headers) # 请求格式为json需要将data转为json格式,然后请先请求
    response = response.json()
    return response

#!/usr/bin/env python
# -*-coding:utf-8-*-
# author:BeeLu
import xlrd
import xlwt
from xlutils.copy import copy
from yunshanfuruzhu.ruzhuadd import ruzhu_add
import json
import sys

# 读取表格
ExcelDir = r"C:\Users\Administrator\Desktop\**********.xls"
# 打开表格
workbook = xlrd.open_workbook(ExcelDir, formatting_info=True)

# 读取子表内容
worksheet = workbook.sheet_by_index(0)

workbookcopy = copy(workbook)
wrsheet = workbookcopy.get_sheet(0)

# 自动化执行
for one in range(1, 4):
    celldata = worksheet.cell(one, 1).value
    # cellexp = worksheet.cell(one, 2).value
    # idnum = worksheet.cell(one, 0)value
    resp = ruzhu_add(celldata)
    resp = str(resp)

    # if response.msg ==cellexp['msg']:
    #     print('idnum-->成功')
    #     excel_rule ='pass'
    # else:
    #     print('idnum-->失败')
    #     excel_rule = 'false'
# 测试结果写入表
    # 复制表格
    wrsheet.write(one, 2, resp)
# worksheetcopy.write(one, 3, excel_rule)
# 保存
workbookcopy.save(r"C:\Users\Administrator\Desktop\*******2.0.xls")