# -*- coding:utf-8 -*-
'''
@project: ApiAutoTest
@author: Jimmy
@file: read_excel.py
@ide: PyCharm Community Edition
@time: 2018-12-20 10:34
@blog: https://www.cnblogs.com/gotesting/
'''
import openpyxl
import os
from Common.contants import *
class Case:
def __init__(self):
self.case_id = None
self.url = None
self.data = None
self.title = None
self.method = None
self.expected = None
class ReadExcel:
def __init__(self,file_name):
try:
self.filename = os.path.join(data_dir,file_name)
self.wb = openpyxl.load_workbook(self.filename)
except FileNotFoundError as e:
print('{0} not found , please check file path'.format(self.filename))
raise e
def get_cases(self,sheet_name):
sheet = self.wb[sheet_name]
max_row = sheet.max_row
test_cases = []
for r in range(2,max_row+1):
case = Case() # 執行個體化一個data對象，用于存放讀取的測試資料
case.case_id = sheet.cell(row=r,column=1).value
case.title = sheet.cell(row=r,column=2).value
case.method = sheet.cell(row=r,column=3).value
case.url = sheet.cell(row=r,column=4).value
case.data = sheet.cell(row=r,column=5).value
case.expected = sheet.cell(row=r,column=6).value
test_cases.append(case)
return test_cases
# 擷取到workbook裡面所有的sheet名稱的清單
def get_sheet_name(self):
return self.wb.sheetnames
# 根據sheet_name定位到sheet，根據case_id定位到行，寫入result
def write_result(self,sheet_name,case_id,actual,result):
sheet = self.wb[sheet_name]
max_row = sheet.max_row
for r in range(2,max_row+1):
# 擷取第r行第1列的 case_id值
case_id_r = sheet.cell(row=r,column=1).value
# 判斷excel讀取的目前行的case_id_r是否與傳入的case_id相同
if case_id_r == case_id:
sheet.cell(row=r,column=7).value = actual
sheet.cell(row=r,column=8).value = result
self.wb.save(filename=self.filename)
break