测试派 Give testers a home.

安装openpyxl包

在CMD中：pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

导入openpyxl包

from openpyxl import load_workbook

获取数据对象

wb_obj = load_workbook(filename = "case_data.xlsx")
print(wb_obj)

获取数据表的名称

sheets_obj =wb_obj.sheetnames
print(sheets_obj)

获取表对象

sheet_obj = wb_obj.worksheets[0]
print(sheet_obj)

获取行对象

rows_obj = sheet_obj.rows
print(rows_obj)

获取行对象的值

datas = list(sheet_obj.iter_rows(values_only=True))
print(datas)

获取表头和表数据

case_title = datas[0]
case_datas = datas[1:]
print(case_title)
print(case_datas)

对表头和表数据进行封装，得到字典，最终多个字典组成列表

data_list = []
for data in case_datas:
res_dict = dict(zip(case_title,data))
print(res_dict)
data_list.append(res_dict)
print(data_list)

遍历获取单元格的值

for obj in sheet_obj.rows:
for i in obj:
print(i.value)

获取指定单元格的值

cell_obj = sheet_obj["B3"]
print(cell_obj.value)
cell_obj1 = sheet_obj.cell(2,3)
print(cell_obj1.value)