一、excel 数据以列表嵌套字典形式展示

excel数据：

以列表嵌套字典形式展示：

from openpyxl import load_workbook
filename = r'E:learn_pythonpri_packagedatacase_01.xlsx'
#打开excel
wb = load_workbook(filename)
#读取工作簿
sheet = wb['login']
#列表形式
data = list(sheet.values)
#列表嵌套字典形式
dict_data = [dict(zip(data[0],i)) for i in data[1:]]
print(dict_data)

输出

二、从excel读取数据并导入测试用例中(以登录函数为例)

excel封装：

from openpyxl import load_workbook
def read_excel_dict(filename,sheetname):
    wb = load_workbook(filename)
    sheet = wb[sheetname]
    excel_data = list(sheet.values)
    title = excel_data[0]
    rows = excel_data[1:]
    new_rows = [dict(zip(title,row)) for row in rows]
    return new_rows

被测函数：

def get_login(username,password):
    if username is None or password is None:
        return {"code":"400","msg":"用户名或密码为空"}
    if username == "yuz" and password == "123456":
        return {"code": "200", "msg": "登录成功"}
    return {"code": "300", "msg": "用户名或密码错误"}

读取excel并导入测试用例：

import unittest
import json
from func_login import get_login
from ddt import ddt,data
from common.excel_package import read_excel_dict
excel_data = read_excel_dict(r'E:learn_pythonpri_packagedatacase_01.xlsx','login')
@ddt
class TestLogin(unittest.TestCase):

    @data(*excel_data)
    def test_01(self,row):
        paramers = json.loads(row['data'])
        username = paramers["username"]
        password = paramers["password"]
        expected = json.loads(row['expected'])
        actual = get_login(username,password)
        self.assertEqual(expected["msg"],actual["msg"])

三、从excel读取数据并导入测试用例中(以登录网址url为例)

excel数据

读取excel数据并导入测试用例中

import unittest
import json
import requests
from requests import request
from ddt import ddt,data
from common.excel_package import read_excel_dict
bef_url = 'http://api.lemonban.com:8766/futureloan'
excel_data = read_excel_dict(r'E:learn_pythonpri_packagedatacase_02.xlsx','login')
@ddt
class Testlogin_url(unittest.TestCase):

    @data(*excel_data)
    def test_01(self,info):
        url = bef_url + info['url']
        method = info['method']
        json_data = json.loads(info['json'])
        headers = json.loads(info['headers'])
        expected = json.loads(info['expected'])
        resp = requests.request(url=url,
                                method=method,
                                headers=headers,
                                json=json_data)
        actual = resp.json()
        self.assertEqual(expected['msg'],actual['msg'])

更多相关阅读《最完整的Python操作Excel数据封装函数》