使用Python, 快速读取文件作为传参, 调用接口, 接口返回值写入Excel
例如: post接口, 代码如下
import requests,json,time,datetime,requests
import openpyxl,xlwt
class autoScript():
def excel_sign(self):
# 打开Excel文件
url ='https://***.sit.com/open/gwApi/ext/user/pt'
wb = openpyxl.load_workbook('/Users/01***42/Python/apitest_django/base/common/data.xlsx')
sheet = wb["账号数据"]
# 循环遍历每个单元格,调用接口并将结果写入Excel
for row in range(2,5):
phone_number = sheet.cell(row=row, column=1).value
userId = sheet.cell(row=row, column=2).value
account_name = sheet.cell(row=row, column=3).value #带86
datas={"phone":account_name,"accountSystemCodes":["SA"]}
datas = json.dumps(datas)
print(datas)
headers = {"Content-Type": 'application/json; charset=UTF-8', 'Connection': 'close'}
s = requests.Session()
r = s.post(url, headers=headers, data=datas, verify=False)
# 解析返回的JSON数据
response_data = r.json()['msg']
print(response_data)
response=str(response_data)
# 将结果写入Excel文件中相应的单元格(假设结果在返回JSON的"result"字段中)
sheet.cell(row=int(row),column=7).value=datas
sheet.cell(row=int(row),column=8).value=response
# 保存修改后的Excel文件
wb.save('/Users/01***2/Python/apitest_django/base/common/data.xlsx')
wb.close()
return
如果是get接口, 代码如下
from django.contrib import messages
import openpyxl,xlwt
class casType():
def cas_serviceId(self):
# 打开Excel文件
wb = openpyxl.load_workbook('/Users/01**2/Python/apitest_django/base/common/Type.xlsx')
sheet = wb["c测试"]
# 循环遍历每个单元格,调用接口并将结果写入Exce
for row in range(2,5):
serviceId = sheet.cell(row=row, column=1).value
url ='https://c.sit.com/login?service=http%3A%2F%2F'+serviceId+'%2Fadmin%2Flogin'
print(url)
response = requests.get(url)
# print(response.text)
response_data = response.text
if "不清楚?" in response_data:
datas = 1
else:
datas = 0
# 将结果写入Excel文件中相应的单元格(假设结果在返回JSON的"result"字段中)
sheet.cell(row=int(row),column=2).value=datas#需修改
wb.save('/Users/01**2/Python/apitest_django/base/common/Type.xlsx')
wb.close()
return