pip install xlrd==1.2.0 
#  原因，xlrd为2.0.1版本，更新版本后，xlrd不支持xlsx格式数据的读取了，果断减低版本

# -*- coding:GBK -*-
import xlrd

wb = xlrd.oPEn_workBook("test_user_data.xlsx")  # 打开excel表
sh = wb.sheet_by_name("TestUserLogin")  # 按工作簿名定位工作表
PRint(sh.nrows)  # 有效数据行数
print(sh.ncols)  # 有效数据列数
print(sh.cell(0, 0).value)  # 输出第一行第一列的值case_name
print(sh.row_values(0))  # 输出第一行的所有值（列表形式）

# 将数据和标题组装成字典，使数据更清晰
print(dict(zip(sh.row_values(0), sh.row_values(1))))

# 遍历excel，打印所有数据
for i in range(sh.nrows):
    print(sh.row_values(i))

C:UsersyzpAppDataLocalProgramsPythonPython37python.exe D:/00test/apITestFramework/read_data.py
3
5
case_name
['case_name', 'url', 'method', 'data', 'expect_res']
{'case_name': 'test_user_login_normal', 'url': 'http://172.0.0.1:5000/api/user/login/', 'method': 'POST', 'data': '{"name": "张 三", "password": "123456"}', 'expect_res': '<h1>登录成功</h1>'}
['case_name', 'url', 'method', 'data', 'expect_res']
['test_user_login_normal', 'http://172.0.0.1:5000/api/user/login/', 'POST', '{"name": "张 三", "password": "123456"}', '<h1>登录成功</h1>']
['test_user_login_password_wrong', 'http://172.0.0.1:5000/api/user/login/', 'POST', '{"name": "张 三","password": "1234567"}', '<h1>失败，用户名或密码错误</h1>']

Process finished with exit code 0

我们的目的是获取某条用例的数据，需要3个参数，excel数据文件名（data_file），工作簿名（sheet），用例名（case_name） 如果我们只封装一个函数，每次调用（每条用例）都要打开一次excel并遍历一次，这样效率比较低。 我们可以拆分成两个函数，一个函数excel_to_list(data_file, sheet)，一次获取一个工作表的所有数据，另一个函数get_test_data(data_list, case_name)从所有数据中去查找到该条用例的数据。

 1 # -*- coding:GBK -*-
 2 import xlrd
 3 
 4 
 5 def excel_to_list(data_file, sheet):
 6     data_list = []  # 新建个空列表，来承装所有的数据
 7     wb = xlrd.open_workbook(data_file)  # 打开excel
 8     sh = wb.sheet_by_name(sheet)  # 获取工作簿
 9     header = sh.row_values(0)  # 获取标题行数据
10     for i in range(1, sh.nrows):  # 跳过标题行，从第二行开始读取数据
11         d = dict(zip(header, sh.row_values(i)))  # 将标题和每行数据组装成字典
12         data_list.append(d)
13     return data_list  # 列表嵌套字典格式，每个元素都是一个字典
14 
15 
16 def get_test_data(data_list, case_name):
17     for case_data in data_list:
18         if case_name == case_data['case_name']:   # 如果字典数据中case_name与参数一致
19             return case_data
20             # 如果查询不到会返回None
21 
22 
23 if __name__ == "__main__":  # 测试一下自己的代码
24     data_list = excel_to_list('test_user_data.xlsx', "TestUserLogin")  # 读取excel，TestUserLogin工作簿的所有数据
25     case_data = get_test_data(data_list, "test_user_login_normal")  # 查找用例'test_user_login_normal'的数据
26     print(case_data)

C:UsersyzpAppDataLocalProgramsPythonPython37python.exe D:/00test/apiTestFramework/read_excel.py
{'case_name': 'test_user_login_normal', 'url': 'http://172.0.0.1:5000/api/user/login/', 'method': 'POST', 'data': '{"name": "张 三", "password": "123456"}', 'expect_res': '<h1>登录成功</h1>'}

Process finished with exit code 0

# -*- coding:GBK -*-
import random
From flask import Flask, request, jsonify, abort

app = Flask(__name__)  # 实例化一个Flask对象


@app.route("/api/user/login/", methods=["POST"])
def login():
    if not request.JSON or not 'name' in request.json or not 'password' in request.json:
        abort(404)
    res = ["<h1>登录成功</h1>", "<h1>失败，用户名或密码错误</h1>"]
    return jsonify(random.choice(res))


if __name__ == "__main__":
    app.run()

# -*- coding:utf-8 -*-

import unittest
import requests
import json  # 用来转换excel中的json字符串为字典
from read_excel import *  # 导入read_excel中的方法


class TestUserLogin(unittest.TestCase):
    @classmethod
    def SETUPClass(cls):
        cls.data_list = excel_to_list("test_user_data.xlsx", "TestUserLogin")  # 读取该用例所有数据

    def test_user_login_normal(self):
        case_data = get_test_data(self.data_list, "test_user_login_normal")
        if not case_data:  # 有可能为None
            print("用例数据不存在")
        url = case_data.get('url')  # 从字典中取数据，excel中的标题也必须是小写url
        data = case_data.get('data')

        # 注意字符串格式，需要用json.loads()转化为字典格式
        expect_res = case_data.get('expect_res')  # 期望数据
        res = requests.post(url=url, json=json.dumps(data))  # 表单请求，数据转为字典格式
        self.assertEqual(json.loads(res.text), expect_res)  # 改为assertEqual断言


if __name__ == '__main__':  # 非必要，用于测试我们的代码
    unittest.main(verbosity=2)

结果返回1示例：
Testing started at 15:10 ...
C:UsersyzpAppDataLocalProgramsPythonPython37python.exe "C:Program FilesJetbrainsPycharm 2018.2helperspycharm_jb_unittest_runner.py" --path D:/00test/apiTestFramework/test_user_login.py
Launching unittests with arguments python -m unittest D:/00test/apiTestFramework/test_user_login.py in D:0testapiTestFramework


Ran 1 test in 0.017s

OK

Process finished with exit code 0


结果返回2示例：
Testing started at 15:16 ...
C:UsersyzpAppDataLocalProgramsPythonPython37python.exe "C:Program FilesJetBrainsPyCharm 2018.2helperspycharm_jb_unittest_runner.py" --path D:/00test/apiTestFramework/test_user_login.py
Launching unittests with arguments python -m unittest D:/00test/apiTestFramework/test_user_login.py in D:0testapiTestFramework


Ran 1 test in 0.017s

FAILED (failures=1)


<h1>登录成功</h1> != <h1>失败，用户名或密码错误</h1>

Expected :<h1>失败，用户名或密码错误</h1>
Actual   :<h1>登录成功</h1>
 <Click to see difference>

Traceback (most recent call last):
  File "C:Program FilesJetBrainsPyCharm 2018.2helperspycharmteamcitydiff_tools.py", line 32, in _patched_equals
    old(self, First, second, msg)
  File "C:UsersyzpAppDataLocalProgramsPythonPython37libunittestcase.py", line 852, in assertEqual
    assertion_func(first, second, msg=msg)
  File "C:UsersyzpAppDataLocalProgramsPythonPython37libunittestcase.py", line 1233, in assertMultiLineEqual
    self.fail(self._formatMessage(msg, standardMsg))
  File "C:UsersyzpAppDataLocalProgramsPythonPython37libunittestcase.py", line 693, in fail
    raise self.failureException(msg)
AssertionError: '<h1>失败，用户名或密码错误</h1>' != '<h1>登录成功</h1>'
- <h1>失败，用户名或密码错误</h1>
+ <h1>登录成功</h1>


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:UsersyzpAppDataLocalProgramsPythonPython37libunittestcase.py", line 59, in testPartExecutor
    yield
  File "C:UsersyzpAppDataLocalProgramsPythonPython37libunittestcase.py", line 628, in run
    testMethod()
  File "D:0testapiTestFrameworktest_user_login.py", line 24, in test_user_login_normal
    self.assertEqual(json.loads(res.text), expect_res)  # 改为assertEqual断言


Process finished with exit code 1

# -*- coding:utf-8 -*-
import random
from flask import Flask, request, jsonify, abort

app = Flask(__name__)  # 实例化一个FLASH对象


@app.route("/api/user/login/", methods=['POST'])
def login():
    if not request.json or not "name" in request.json or not "password" in request.json:
        abort(404)

    res = 
        [
            {
                "code": "100000",
                "msg": "成功",
                "data": {
                    "name": "范冰冰",
                    "password": "e10adc3949ba59abbe56e057f20f883e"
                }
            },
            {
                "code": "100001",
                "msg": "失败，用户已存在",
                "data": {
                    "name": "张 三",
                    "password": "e10adc3949ba59abbe56e057f20f883e"
                }
            }
        ]
    return jsonify(random.choice(res))


if __name__ == "__main__":
    app.run()