Python使用推导式读取excel中sheet表单的所有内容

表格格式如下

2-d59c018e3d724e0091f27c6ff79b3723

代码示例

from openpyxl import load_workbook  # 对已经存在的excel进行读写操作
import os


def read_data_from_excel(file_name, sheet_name):
    # 创建一个workbook对象
    wb = load_workbook(filename=file_name)
    ws = wb[sheet_name]
    column = ws.max_column + 1
    # print('column', column)  # column 9
    row = ws.max_row + 1  # row 11
    # print('row', row)
    data = ({ws.cell(1, j).value: ws.cell(i, j).value for j in range(1, column)} for i in range(2, row))
    return data


if __name__ == '__main__':
    current_dir_path = os.path.dirname(__file__)
    # print(current_dir_path)
    return_data = read_data_from_excel(file_name=current_dir_path + os.sep + 'data.xlsx',
                                       sheet_name='register')
    # print(return_data)
    for i in return_data:
        print(i)

执行效果

{'case_id': 1, 'title': '正常注册-全不为空', 'url': '/member/register', 'method': 'post', 'input_data': '{"mobilephone":"${unregistered_mobilephone}","pwd":"123456","regname":"hahahha"}', 'expected': '注册成功', 'actual': '{"status":1,"code":"10001","data":null,"msg":"注册成功"}', 'result': 'PASS'}
{'case_id': 2, 'title': '正常注册用户名为空', 'url': '/member/register', 'method': 'post', 'input_data': '{"mobilephone": "${unregistered_mobilephone}", "pwd": "123456", "regname": ""}', 'expected': '注册成功', 'actual': '{"status":1,"code":"10001","data":null,"msg":"注册成功"}', 'result': 'PASS'}
{'case_id': 3, 'title': '重复注册', 'url': '/member/register', 'method': 'post', 'input_data': '{"mobilephone": "${investors_login_mobile}", "pwd": "123456789", "regname": "test_rabbit"}', 'expected': '手机号码已被注册', 'actual': '{"status":0,"code":"20110","data":null,"msg":"手机号码已被注册"}', 'result': 'PASS'}
{'case_id': 4, 'title': '手机号为空', 'url': '/member/register', 'method': 'post', 'input_data': '{"mobilephone": "", "pwd": "123456789", "regname": "test_rabbit"}', 'expected': '手机号不能为空', 'actual': '{"status":0,"code":"20103","data":null,"msg":"手机号不能为空"}', 'result': 'PASS'}
{'case_id': 5, 'title': '密码为空', 'url': '/member/register', 'method': 'post', 'input_data': '{"mobilephone": "18513115964", "pwd": "", "regname": "test_rabbit"}', 'expected': '密码不能为空', 'actual': '{"status":0,"code":"20103","data":null,"msg":"密码不能为空"}', 'result': 'PASS'}
{'case_id': 6, 'title': '密码长度小于6位', 'url': '/member/register', 'method': 'post', 'input_data': '{"mobilephone": "${unregistered_mobilephone}", "pwd": "12345", "regname": "test_rabbit"}', 'expected': '密码长度必须为6~18', 'actual': '{"status":0,"code":"20108","data":null,"msg":"密码长度必须为6~18"}', 'result': 'PASS'}
{'case_id': 7, 'title': '密码长度大于18', 'url': '/member/register', 'method': 'post', 'input_data': '{"mobilephone": "${unregistered_mobilephone}", "pwd": "1234567891012457864", "regname": "test_rabbit"}', 'expected': '密码长度必须为6~18', 'actual': '{"status":0,"code":"20108","data":null,"msg":"密码长度必须为6~18"}', 'result': 'PASS'}
{'case_id': 8, 'title': '手机号不足11位', 'url': '/member/register', 'method': 'post', 'input_data': '{"mobilephone": "1531311", "pwd": "1234567", "regname": "test_rabbit"}', 'expected': '手机号码格式不正确', 'actual': '{"status":0,"code":"20109","data":null,"msg":"手机号码格式不正确"}', 'result': 'PASS'}
{'case_id': 9, 'title': '手机号包大于11位', 'url': '/member/register', 'method': 'post', 'input_data': '{"mobilephone": "1761164745142", "pwd": "123456789", "regname": "test_rabbit"}', 'expected': '手机号码格式不正确', 'actual': '{"status":0,"code":"20109","data":null,"msg":"手机号码格式不正确"}', 'result': 'PASS'}