Python使用推导式读取excel中sheet表单的所有内容
# 表格格式如下
# 代码示例
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
执行效果
{'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'}
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
编辑 (opens new window)
上次更新: 2021/08/15, 14:23:41
- 01
- Python实现对字符串的加解密02-25
- 02
- Python3对大文件中指定字符进行排序再写入到新的文件10-24
- 03
- Ubuntu下配置adb环境连接Android设备进行调试08-17