openpyxl使用介绍

概况

最近，公司需要搭建接口测试框架，希望用excel来维护用例
yaml、json不好吗？要是这样，就可以直接用httprunner，但是吧，你懂的

那就先看看python怎么操作excel吧。python操作excel的库很多，可参看Working with Excel Files in Python，这里主要介绍也比较推荐使用 openpyxl

openpyxl

• 官方文档
• 源码Source code

openpyxl安装及使用

安装

pip3 install openpyxl

基本使用

excel 组成关系：workbook -> sheet -> cell

workbook及sheet创建及获取

• 常用函数或属性
  • wb.active
  • ws.title
  • wb.save

from openpyxl import Workbook

wb = Workbook()

# workbook至少有一个sheet，默认为Sheet
ws = wb.active  # 默认是第一个sheet
print(1, ws)

# 创建新的sheet
ws1 = wb.create_sheet()  # 创建sheet， title为Sheet1。sheet未指定title时，默认title依次为Sheet,Sheet1,Sheet2...
ws2 = wb.create_sheet("new_sheet")  # 创建sheet，sheet title为 new_sheet
ws3 = wb.create_sheet("new_sheet_2", 0)  # 创建sheet插入在第一个

# 获取sheet title
print(2, ws2.title)

# 修改sheet title
ws3.title = "rename_sheet"

# 获取表格所有的sheet title
print(3, wb.sheetnames)
for sheet in wb:
    print(sheet.title)

# 根据sheet title来选取sheet
ws = wb['new_sheet']
print(ws)

# 复制sheet
source = wb["new_sheet"]
target = wb.copy_worksheet(source)

wb.save('test.xlsx')

sheet数据操作

• 常用函数或属性
  • ws.cell、ws.cell.value
  • ws.append
  • ws.rows、ws.values
  • ws.iter_rows(values_only=True)、ws.iter_cols(values_only=True)

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active

# 写入cell值
# 方式1：x行A列
ws['A1'] = "100"
ws['A2'] = 200

# 方式2：使用 Worksheet.cell()方法，指定row行（int），column列（int)
ws.cell(row=3, column=1, value=300)
ws.cell(row=4, column=1).value = 400  # 获取cell后赋值
c = ws['A5']
c.value = 500

print(ws.cell(2, 1).value)

# 方式3：写入多个cell的值
ws.append([1, 2, 3])

'''
get_column_letter(idx, ) 将数字转换成列值 如 3 -> 'C'
'''
# 使用循环写入
ws2 = wb.create_sheet(title="Data")
for row in range(10, 20):
    for col in range(27, 54):
        _ = ws2.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))

# 读取cell值
# 方式1：ws.values 或 ws.rows
print(ws.values)
for row in ws.values:
    print(row)  # 行， 元组
    for value in row:
        print(value)

for row in ws.rows:
    for cell in row:
        print(cell.value)

# 方式2：通过iter_rows,iter_cols
for row in ws.iter_rows(values_only=True):  # values_only=True返回仅为value
    print(row, '~~~')  # 行，元组
    for value in row:
        print(value, "+++")

for cols in ws.iter_cols(values_only=True):
    print(cols, "~~~")  # 列，元组
    for value in cols:
        print(value, "---")

wb.save('test.xlsx')

sheet数据其他操作

from openpyxl import Workbook
import datetime

wb = Workbook()
ws = wb.active

ws["A1"] = datetime.datetime(2021, 12, 12)
print(ws["A1"].value)
print(ws["A1"].number_format)


# 切片访问单元格区域
cell_range = ws['A1':'C2']
for rows in cell_range:
    print(rows)
    for i in rows:
        print(i.value)

colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]

wb.save("text.xlsx")

打开现有excel文件

• load_workbook()
• ws.max_row
• ws.max_column

from openpyxl import load_workbook, Workbook

wb = load_workbook('test.xlsx')
ws = wb.active
print(ws.max_row)
print(ws.max_column)

Read-only 和 Write-only 模式

• wb.close()

from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']

for row in ws.rows:
    for cell in row:
        print(cell.value)

# 读取文件后使用close关闭文档
wb.close()

不同于普通的workbook， read-only workbook会使用懒加载的方式加载，因此这种方式加载的workbook必须明确使用close()方法来关闭

from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet()

for irow in range(100):
    ws.append(['%d' % i for i in range(200)])

wb.save('new_big_file.xlsx')

不同于普通的workbook，新创建的write-only workbook默认不包含任何worksheet，worksheet 必须通过create_sheet() 方法来创建
在write-only workbook中，行的添加只能通过append()添加
在write-only workbook 只能保存一次，之后的对该workbook的操作则会报错

demo实战

约定用例格式api.xlsx如：

import requests
from openpyxl import Workbook, load_workbook


def read_testcase():
    wb = load_workbook("testcases/api.xlsx")
    ws = wb.get_sheet_by_name('test_case')
    case_num = ws.max_row

    interface_list = []

    # 循环获取表格当中的内容
    for row in range(2, case_num + 1):
        case = []
        for col in range(2, 5):
            if col == 2:
                url = ws.cell(row, col).value
                case.append(url)
            elif col == 3:
                method = ws.cell(row, col).value
                case.append(method)
            else:
                expected_res = ws.cell(row, col).value
                case.append(expected_res)
        interface_list.append(case)
    return interface_list


def write_test_result(test_result):
    wb = load_workbook('testcases/api.xlsx')
    ws = wb.get_sheet_by_name('test_case')
    case_num = ws.max_row
    for row in range(2, case_num + 1):
        ws.cell(row=row, column=5, value=test_result[row - 2])
    wb.save('testcases/api.xlsx')
    wb.close()


def test_baidu():
    interface_list = read_testcase()
    print(interface_list)
    test_result = []
    
    # 循环执行接口测试
    for i in interface_list:
        r = requests.request(method=i[1], url=i[0])
        try:
            assert i[2] in r.text
            test_result.append('pass')
        except:
            test_result.append('fail')
    print(test_result)
    # 回写测试结果
    write_test_result(test_result)

仅一个小的demo，还有很多问题需要解决，如接口之间的交互等，可进一步参考httprunner的设计哲学
思路：可尝试建立excel参数与httprunner用例参数之间的映射关系

• 更多特性请参看官方文档，官方文档是最好的学习资料