参考资料
- http://www.python-excel.org/
- openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.1.1 documentation
openpyxl
安装
pip install openpyxl
官网demo
- 写excel
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
# 实例化一个工作簿对象
wb = Workbook()
# 定义excel文件名
file_name = 'book.xlsx'
# 选取当前工作的sheet对象
sheet1 = wb.active
# 为sheet命名
sheet1.title = 'names'
# 循环写入数据
for row in range(1, 40):
sheet1.append(range(600))
# 新创建sheet
sheet2 = wb.create_sheet(title='Pi')
# 在指定单元格里写入数据
sheet2['F5'] = 3.14
# 新建第三个sheet
sheet3 = wb.create_sheet(title='data')
# 指定行和列的范围,循环写入数据
for row in range(10, 20):
for col in range(27, 54):
_ = sheet3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
# 获取sheet3 AA行10列数据
print(sheet3['AA10'].value)
# 保存文件
wb.save(filename=file_name)
# 关闭文件
wb.close()
- 读excel
from openpyxl import load_workbook
# 打开已经创建好的excel
wb = load_workbook(filename='book.xlsx')
# 通过名称指定sheet
# sheet = wb['names']
# 通过sheet的索引去选择
sheet = wb.worksheets[0]
# 读取指定单元格的值
print(sheet['D18'].value)
# 获取excel行数
row_num = sheet.max_row
print(row_num)
测试用例管理实战
import requests
from openpyxl import Workbook, load_workbook
def creat_testcase():
# 实例化workbook对象
wb = Workbook()
# 选取当前的sheet对象
sheet = wb.active
# 为sheet取名
sheet.title = 'test_case'
# 定义测试用例的表头
case_title = ['用例编号','请求方式','url','预期结果','实际结果']
# 写入表头(一行数据)
sheet.append(case_title)
# 用单元格插入数据的方式,写第一条测试用例
sheet["A2"] = 1
sheet["B2"] = "GET"
sheet["C2"] = "https://www.baidu.com"
sheet["D2"] = "baidu"
# 用循环的方式插入5条测试用例
for row in range(3,8):
for col in range(1, 5):
if col ==1:
sheet.cell(row=row, column=col, value=row-1)
elif col == 2:
sheet.cell(row=row, column=col, value="GET")
elif col == 3:
sheet.cell(row=row, column=col, value="https://www.baidu.com")
else:
sheet.cell(row=row, column=col, value="百度")
# 保存数据
wb.save('cases.xlsx')
# 关闭excel
wb.close()
def read_testcase():
# 打开excel文件
wb = load_workbook("cases.xlsx")
# 选择sheet
sheet = wb.get_sheet_by_name('test_case')
# 获取文件行数
case_num = sheet.max_row
# 定义接口数据列表
interface_list = []
# 循环获取表格当中的内容
for row in range(2, case_num+1):
r1 = []
for col in range(2, 5):
if col == 2:
method = sheet.cell(row, col).value
r1.append(method)
elif col == 3:
url = sheet.cell(row, col).value
r1.append(url)
else:
expected = sheet.cell(row, col).value
r1.append(expected)
interface_list.append(r1)
return interface_list
def write_result(test_result):
# 打开excel
wb = load_workbook('cases.xlsx')
sheet = wb.get_sheet_by_name('test_case')
for row in range(2,8):
sheet.cell(row=row, column=5, value=test_result[row-2])
wb.save('cases.xlsx')
wb.close()
def test_baidu():
creat_testcase()
interface_list = read_testcase()
# 定义测试结果
test_result = []
# 循环执行接口测试
for i in interface_list:
r = requests.request(method=i[0], url=i[1])
print(r.text)
try:
assert i[2] in r.text
test_result.append('pass')
except:
test_result.append('fail')
print(test_result)
# 回写测试结果
write_result(test_result)