Python 使用openpyxl,requests读取exce

Excel内容如下：

具体实现
1、pycharm安装openpyxl

pip install openpyxl

2、requests库写接口函数
requests_util.py

import json as _json
import jsonpath
import requests
from common.log_util import logger


class HttpClient():
   __headers = {"content-type": "application/json;charset=UTF-8"}

   def __init__(self):
       self.__session = requests.session()

   def get(self, url, **kwargs):
       return self.__request(url, 'GET', **kwargs)

   def post(self, url, data=None, json=None, **kwargs):
       return self.__request(url, 'POST', data, json, **kwargs)

   def __request(self, url, method, data=None, json=None, **kwargs):
       headers = kwargs.get("headers")
       params = kwargs.get("params")

       if headers:
           self.__headers.update(headers)
       self.__request_log(url, method, data, json, params, self.__headers)
       resp = None
       if method == 'GET':
           resp = self.__session.get(url, **kwargs)
       elif method == 'POST':
           resp = requests.post(url, data, json, **kwargs)
       self.__response_log(resp)
       response_dicts = dict()
       response_dicts['text'] = resp.text
       return _json.loads(response_dicts['text'])

   def __request_log(self, url, method, data=None, json=None, params=None, headers=None):
       logger.info("接口请求地址：{}".format(url))
       logger.info("接口请求方式: {}".format(method))
       logger.info("接口请求头: {}".format(_json.dumps(headers, indent=4, ensure_ascii=False)))
       logger.info("接口请求 params 参数: {}".format(_json.dumps(params, indent=4, ensure_ascii=False)))
       logger.info("接口请求体 data 参数 : {}".format(_json.dumps(data, indent=4, ensure_ascii=False)))
       logger.info("接口请求体 json 参数: {}".format(_json.dumps(json, indent=4, ensure_ascii=False)))

   def __response_log(self, resp):
       try:
           logger.info("响应报文 : {}".format(resp.text, ensure_ascii=False))
       except Exception as e:
           logger.error('系统错误：{}'.format(e))

3、读取excel
excel_util.py

import openpyxl
from common import EXCEL_FILE, MEMBER_SERVICE_URL

class OperationExcel:
   def __init__(self,file_name=None,sheet_id=None):
       if file_name:
           self.file_name = file_name
           self.sheet_id = sheet_id
       else:
           self.file_name = EXCEL_FILE
           self.sheet_id = 0
           self.data = self.get_data()


   #获取sheets的内容
   def get_data(self):
       wb = openpyxl.load_workbook(self.file_name)
       tables = wb.worksheets[self.sheet_id]  # 获取表单
       return tables

   # 获取单元格的行数
   def get_lines(self):
       tables = self.data
       return tables.max_row
   #获取某一个单元格的内容
   def get_cell_value(self,row,col):
       return self.data.cell(row,col).value

#封装获取常量
class global_var:
   id = '1'
   case_id = '2'
   name = '3'
   url = '4'
   method = '5'
   header = '6'
   params = '7'
   json = '8'
   verify = '9'


def get_id():
   return global_var.Id
def get_case_id():
   return global_var.case_id
def get_request_name():
   return global_var.name
def get_url():
   return global_var.url
def get_method():
   return global_var.method
def get_header():
   return global_var.header
def get_params():
   return global_var.params
def get_json():
   return global_var.json
def get_verify():
   return global_var.verify

class Getheadervalue():
   def __init__(self,header):
       self.header = header
   def get_header_value(seif,header):
       return {
           "Content-Type":"application/json;charset=UTF-8"
       }


class GetData:
   def __init__(self):
       self.opera_excel = OperationExcel()
   # 获取excel行数，就是case的个数
   def get_case_lines(self):
       return self.opera_excel.get_lines()

   # 是否携带header
   def is_header(self,row):
       col = int(get_header())
       header = self.opera_excel.get_cell_value(row, col)
       return eval(header)

   # 获取请求方式
   def get_request_method(self,row):
       col = int(get_method())
       request_method = self.opera_excel.get_cell_value(row,col)
       return request_method

   # 获取url
   def get_request_url(self,row):
       col = int(get_url())
       url = MEMBER_SERVICE_URL+self.opera_excel.get_cell_value(row,col)
       return url

   # 获取case_id
   def get_case_id(self,row):
       col = int(get_case_id())
       case = self.opera_excel.get_cell_value(row,col)
       if case == None:
           return None
       return case

   # 获取请求数据
   def get_request_params(self,row):
       col = int(get_params())
       data = self.opera_excel.get_cell_value(row,col)
       if data == None:
           return None
       return eval(data)

   # 获取json报文数据
   def get_request_json(self,row):
       col = int(get_json())
       json_data = self.opera_excel.get_cell_value(row,col)
       if json_data == None:
           return None
       return eval(json_data)

   # 获取json path数据
   def get_verify_data(self,row):
       col = int(get_verify())
       verify_data = self.opera_excel.get_cell_value(row,col)
       if verify_data == None:
           return None
       return verify_data

4、执行接口测试
可放置在requests_util.py中

class RunHttp:
   def __init__(self):
       self.data = GetData()

   # 程序执行的
   def go_on_run(self,case=None):
       res = None
       rows_count = self.data.get_case_lines()
       for i in range(2, rows_count+1):
           case_id = self.data.get_case_id(i)
           if case_id == case:
               url = self.data.get_request_url(i)
               method = self.data.get_request_method(i)
               params = self.data.get_request_params(i)
               header = self.data.is_header(i)
               json = self.data.get_request_json(i)
               verify = self.data.get_verify_data(i)
               if method == 'get':
                   res = HttpClient().get(url=url, headers=header, params=params)
               elif method == 'post':
                   res = HttpClient().post(url=url, json=json, headers=header, params=params)
               verifystr = jsonpath.jsonpath(res, verify)
       return verifystr

httpRun = RunHttp()

if __name__ == '__main__':
   res=httpRun.go_on_run('customer_list_by_ouid')
   print(res)

也可参考python+openpyxl+excel实现接口测试自动化