Python读取Excel,并写入Excel_python 读取,写入 excel文件

使用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