参考资料
- python 操作 excel 的第三方库：http://www.python-excel.org/
- openpyxl 官方文档：openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.1.1 documentation
安装
pip install openpyxl
在pycharm中直接安装
课堂代码
# -*- coding: utf-8 -*-
# @Time : 2020-11-05 15:13
# @Author : feier
# @File : excel_demo.py
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
# 实例化一个工作簿的对象
wb = Workbook()
# 定义excel文件名
dest_filename = 'empty_book.xlsx'
# 选择要操作的sheet
ws1 = wb.active
# 重新命名sheet
ws1.title = "range names"
# 循环写入数据
for row in range(1, 40):
# 从工作表的底部追加值
ws1.append(range(600))
# 新建sheet
ws2 = wb.create_sheet(title="Pi")
# 在指定单元格当中写入数据
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
# 用cell方法写入数据
_ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
# 取单元格中的值
print(ws3['AA10'].value)
# 保存文件
wb.save(filename = dest_filename)
from openpyxl import load_workbook
# 打开已经创建好的excel文件
wb = load_workbook(filename = 'empty_book.xlsx')
# 通过名称指定sheet
sheet_ranges = wb['range names']
# 读取指定单元格中的值
print(sheet_ranges['D18'].value)
"""
接口测试测试用例
表头：用例编号、请求方式、url、预期结果、实际结果
"""
import requests
from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook
def creat_testcase():
wb = Workbook()
sheet = wb.worksheets[0]
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"
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="baidu")
wb.save("cases.xlsx")
wb.close()
def read_testcase():
wb = load_workbook("cases.xlsx")
sheet = wb.worksheets[0]
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):
wb = load_workbook("cases.xlsx")
sheet = wb.worksheets[0]
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)