python读取excel,做接口自动化测试

#读取excel
from openpyxl import load_workbook
class Doexcel:
def init(self, file_name, sheet_name):
self.file_name = file_name
self.sheet_name = sheet_name
# 获取一个表单对象
self.sheet_obj = load_workbook(self.file_name)[self.sheet_name]
self.max_row = self.sheet_obj.max_row
def get_data(self, i, j):
“”"
根据传入的坐标获取值
:param i: X坐标
:param j: Y坐标
:return:
“”"
return self.sheet_obj.cell(i, j).value

#回写数据
def write_back(self,i,result,Testresult):
    wb=load_workbook(self.file_name)
    sheet=wb[self.sheet_name]
    sheet.cell(i,7).value=result
    sheet.cell(i,8).value=Testresult
    wb.save(self.file_name)

if name == ‘main’:
#查看下excel中的竖行，横行的数据
print(Doexcel(r’D:test项目阶段二test_datatestdata.xlsx’,‘Sheet1’).get_data(1,6))

##读取excel接口数据

import unittest
import requests
from 项目阶段三.Comment.do_excel import Doexcel
data_1=Doexcel(r’D:test项目阶段三test_datatestdata.xlsx’,‘四诊合参’)
suite = unittest.TestSuite()
class testdemo(unittest.TestCase):
def test_01(self):
for i in range(2,data_1.max_row+1):
headers = {
‘User-Agent’: ‘apifox/1.0.0 (https://www.apifox.cn)’,
‘Content-Type’: ‘application/json’
}
r=requests.post(data_1.get_data(i,2),data_1.get_data(i,4).encode(),headers=headers)
print(r.json()[“code”])
print(data_1.get_data(i,6))
try:
#self.assertEqual(str(data_1.get_data(i,6)),str(r.json()[“code”]))
self.assertIn(str(data_1.get_data(i, 6)), str(r.json()))
print(“断言成功：===”)
Testresult = “pass”
except AssertionError as e:
print(“断言失败：=”.format(e))
Testresult = “fail”
Doexcel(r’D:test项目阶段三test_datatestdata.xlsx’, ‘四诊合参’).write_back(i, str(r.json()), Testresult)

if name == ‘main’:
unittest.main()