前言：目前我們進行測試時用於儲存測試資料的軟體幾乎都是excel，excel方便儲存和管理資料，讀取資料時也比較清晰，測試時我們需要從excel從讀取測試資料，結束後還需把測試結果寫入到excel中，讀取這一動作以往都是通過人工來操作，學會使用openpyxl後，就可以實現自動化地讀取資料，解放雙手。
openpyxl簡單介紹
openpyxl是一個開源專案，它是一個用於讀取/寫入Excel 2010文件（如xlsx 、xlsm 、xltx 、xltm檔案 ）的Python庫，如果要處理更早格式的Excel文件（xls），需要用到其它庫（如：xlrd、xlwt等），這是openpyxl比較其他模組的不足之處。openpyxl是一款比較綜合的工具，不僅能夠同時讀取和修改Excel文件，而且可以對Excel檔案內單元格進行詳細設定，包括單元格樣式等內容，甚至還支援圖表插入、列印設定等內容。
python中與excel操作相關的模組：
- ? xlrd庫：從excel中讀取資料，支援xls、xlsx格式
- ? xlwt庫：對excel進行修改操作，但不支援對xlsx格式的修改
- ? xlutils庫：在xlwt和xlrd中，對一個已存在檔案進行修改
- ? openpyxl庫：主要針對xlsx格式的excel進行讀取和編輯
openpyxl的安裝
pip install openpyxl
openpyxl的三個基本概念
- WorkBook：工作簿物件
- Sheet：表單物件
- Cell：表格物件
也就是我們使用excel時可以看到的三個物件，我們操作excel的步驟是開啟工作簿，選中表單，對單元格進行操作，而openpyxl的工作原理也是如此。
openpyxl對excel進行操作
新建excel
執行以下程式碼後，就會發現專案中多了一個excel檔案。
import openpyxl
# 建立一個工作簿
workbook = openpyxl.Workbook()
# 建立一個表單
sheet = workbook.create_sheet('表單1')
# 寫入一個資料
sheet.cell(row=1, column=1, value="python")
# 儲存
workbook.save('test.xlsx')
? 注意點：如果儲存的檔名已存在，將會覆蓋原來的檔案
開啟已存在的檔案
- 開啟已存在的檔案：
load_workbook() - 獲取表單：
workbook['表單名'] - 讀取指定表格：
cell(row, column).value
import openpyxl
# 開啟工作簿
workbook = openpyxl.load_workbook('test.xlsx')
# 獲取表單
sheet = workbook['表單1']
# 讀取指定的單元格資料
cell = sheet.cell(row=1, column=1).value
print(cell)
執行結果：
C:\software\python\python.exe D:/learn/python24/python_base/test.py
python
Process finished with exit code 0
讀取單個單元格
# 方式一：讀取A6單元格的值
cell1 = sheet['A6'].value
# 方式二：讀取第3行,第4列單元格的值
cell2 = sheet.cell(row=3, column=4).value
讀取多個單元格
# 讀取A1-B4的單元格，共8個單元格
cell3 = sheet['A1':'B4']
# 讀取A1-B4的單元格，共8個單元格
cell4 = sheet['A1:B4']
# 讀取第2行的單元格
cell5 = sheet[2]
# 讀取第1-2行的單元格
cell5 = sheet[1:2]
讀取多個單元格時，都是以元組的形式返回
其他相關操作
| 關鍵程式碼 | 相關描述 |
|---|---|
cell(row, column, value) |
寫入資料，三個引數分別是行，列，值 |
cell(row, column).value |
獲取指定單元格的值，兩個引數分別是行，列 |
workbook.remove('表單名') |
刪除表單 |
del workbook['表單名'] |
刪除表單 |
workbook.save('檔名') |
儲存工作簿 |
sheet.max_row |
獲取表單資料的總行數 |
sheet.max_column |
獲取表單資料的總列數 |
sheet.rows |
獲取按行所有的資料 |
sheet.columns |
獲取按列所有的資料 |
? 注意點：
- openpyxl中列和行的起始標識都是1，不是從0開始
- 從excel中讀取出來的資料只有兩種型別，即數值型別和字串型別
- 不要隨便在表格中敲空格，會影響判斷最大行數和最大列數
- 執行操作excel的程式碼時，要先關閉在作業系統中開啟的相關excel表，否則可能會無法讀取/寫入資料
- 寫入資料時要執行儲存
import openpyxl
# 開啟工作簿
wb = openpyxl.load_workbook('test.xlsx')
# 獲取表單
sh = wb['表單1']
# 讀取指定的單元格資料
res1 = sh.cell(row=1, column=1).value
print(res1)
# 獲取最大行數
print(sh.max_row)
# 獲取最大列數
print(sh.max_column)
# 按列讀取所有資料，每一列的單元格放入一個元組中
print(sh.columns) # 直接列印，列印結果是一個可迭代物件，我們可以轉換成列表來檢視
# 按行讀取所有資料，每一行的單元格放入一個元組中
rows = sh.rows
# print(list(rows)) # 轉換成列表之後列印結果為具體的單元格，如下
# [(<Cell '表單1'.A1>, <Cell '表單1'.B1>, <Cell '表單1'.C1>, <Cell '表單1'.D1>, <Cell '表單1'.E1>, <Cell '表單1'.F1>, <Cell '表單1'.G1>),
# (<Cell '表單1'.A2>, <Cell '表單1'.B2>, <Cell '表單1'.C2>, <Cell '表單1'.D2>, <Cell '表單1'.E2>, <Cell '表單1'.F2>, <Cell '表單1'.G2>),
# (<Cell '表單1'.A3>, <Cell '表單1'.B3>, <Cell '表單1'.C3>, <Cell '表單1'.D3>, <Cell '表單1'.E3>, <Cell '表單1'.F3>, <Cell '表單1'.G3>)]
# 我們可以通過for迴圈以及value來檢視單元格的值
for row in list(rows): # 遍歷每行資料
case = [] # 用於存放一行資料
for c in row: # 把每行的每個單元格的值取出來，存放到case裡
case.append(c.value)
print(case)
執行結果：
C:\software\python\python.exe D:/learn/python24/python_base/test.py
case_id
3
7
<generator object Worksheet._cells_by_col at 0x0000012FEF90D8B8>
['case_id', 'interface', 'title', 'method', 'url', 'data', 'expected']
[1, 'login', '登入成功', 'post', '/member/login', '{"mobile_phone":"15692004245","pwd":"miki12345"}', "{'code': 0, 'msg': 'OK'}"]
[2, 'login', '登入失敗-手機號為空', 'post', '/member/login', '{"mobile_phone":"","pwd":"miki12345"}', "{'code': 2, 'msg': '手機號碼為空'}"]
excel表中的原資料如下圖：
openpyxl封裝
在測試過程中，我們會經常需要訪問excel讀取測試資料，如果毎訪問一次我們就呼叫一次openpyxl，進行開啟excel，選取表單，讀取資料等操作，將會增加我們的工作量和程式碼量。凡是需要重複使用的，我們都可以對它進行二次封裝，寫成我們自己的版本，還可以在封裝過程中加入我們想要的內容，使用的時候直接呼叫即可。
在封裝openpyxl時，讀取excel時我寫了兩種實現方法，第一種是讀取資料後，把每條測試資料的鍵值儲存到一個字典中，再把字典存到一個列表，這種方式比較容易理解，但是呼叫的時候需要多敲幾個括號或者引號；第二種是把每條測試資料的鍵值存到一個例項物件的屬性中，再把每個例項存到一個列表。實際上他們的作用是一樣，只是第二種方式呼叫時更簡單。
# read_excel.py
import openpyxl
class CasesData:
"""用於儲存測試用例資料"""
pass
class ReadExcel:
"""用於讀寫excel資料"""
def __init__(self, file_name, sheet_name):
"""
:param file_name: excle檔名
:param sheet_name: 表單名
"""
self.file_name = file_name
self.sheet_name = sheet_name
def open(self):
"""開啟工作簿，選中表單"""
self.wb = openpyxl.load_workbook(self.file_name)
self.sh = self.wb[self.sheet_name]
def close(self):
"""關閉工作簿物件的方法"""
self.wb.close()
def read_data(self):
"""按行讀取資料，最後返回一個儲存字典的列表"""
self.open() # 開啟工作簿
rows = list(self.sh.rows) # 按行讀取所有資料
titles = [] # 用於存放用例的標題
for t in rows[0]:
title = t.value
titles.append(title)
cases = [] # 用於存放所有的測試資料
for row in rows[1:]:
case = [] # 用於存放單條測試用例資料
for r in row:
case.append(r.value)
cases.append(dict(zip(titles, case))) # 通過zip聚合打包用例的標題和資料
self.close()
return cases
def read_data_obj(self):
"""按行讀取資料，最後返回的是一個儲存例項物件的列表"""
self.open() # 開啟工作簿，選中表單
rows = list(self.sh.rows) # 按行讀取所有資料
titles = [] # 用於存放用例的標題
for t in rows[0]:
title = t.value
titles.append(title)
cases = [] # 用於存放所有的測試資料
for row in rows[1:]:
case_obj = CasesData() # 建立一個物件用來儲存該行用例資料
case = [] # 用於儲存單條測試用例資料
for r in row:
case.append(r.value)
case_info = dict(zip(titles, case)) # 通過zip聚合打包用例的標題和資料
for k, v in case_info.items(): # 把該行測試用例標題和資料遍歷出來，設成物件的屬性
setattr(case_obj, k, v)
cases.append(case_obj)
self.close()
return cases
def write_data(self, row, column, msg):
"""寫入資料的方法"""
self.open()
self.sh.cell(row=row, column=column, value=msg)
self.wb.save(self.filename)
self.close()
if __name__ == '__main__':
# 直接執行本檔案時執行，下面是一個應用例項
# 需要讀取excel時直接呼叫ReadExcel類
test = ReadExcel('cases.xlsx', 'login')
res = test.read_data_obj() # 最後返回的是一個儲存例項物件的列表
print(res[0].case_id) # 列印第1個用例的case_id
print(res[3].title) # 列印第4個用例的title
test2 = ReadExcel('cases.xlsx', 'login')
res2 = test2.read_data() # 最後返回一個儲存字典的列表
print(res2[0]['case_id']) # 列印第1個用例的case_id
print(res2[3]['title']) # 列印第4個用例的title
上面封裝的openpyxl中，應用例項的excel表結構如下：
