# python-自动化UI测试之---excel数据驱动

encoding = utf-8

from openpyxl import load_workbook

class ParseExcel(object):
“”“解析excel数据”""

def __init__(self,excelPath,sheetName):
    # 将要读取的excel数据加载到内存（获取一个工作簿对象）
    self.wb = load_workbook(excelPath)
    # 获取一个工作表对象
    self.sheet = self.wb.get_sheet_by_name(sheetName)
    # 获取表中存有数据的最大行号
    self.maxRowNum = self.sheet.max_row

def getDataFromSheet(self):
    """将数据从表中取出，存放在列表中"""
    dataList = []
    # 去掉标题行
    i = 0
    for row in self.sheet.iter_rows():
        i += 1
        if i == 1:
            continue
        tmpList = []
        tmpList.append(row[1].value)
        tmpList.append(row[2].value)
        dataList.append(tmpList)

    return dataList

if name == ‘main’:
excelPath = r’D:\python1\自动化测web\书籍selenium\excel数据驱动\caseData\case.xlsx’
sheetName = ‘searchData’
pe = ParseExcel(excelPath,sheetName)
for i in pe.getDataFromSheet():
print(i)

encoding = utf-8

import unittest,time
from selenium import webdriver
import logging,traceback
import ddt
from 自动化测web.书籍selenium.excel数据驱动.base.excelData import ParseExcel
from selenium.common.exceptions import NoSuchElementException

初始化日志对象

logging.basicConfig(
# 日志级别
level=logging.INFO,
# 日志格式
# 时间、代码所在文件名、代码行号、日志级别名字、日志信息
format=’%(asctime)s % (filename)s[line:%(lineno)d] % (levelname)s % (message)s’,
# 打印日志的时间
datefmt=’%a, %Y- %m- %d %H: %M: %S’,
# 存放日志的目录及文件名
filename=‘D:\python1\自动化测web\书籍selenium\excel数据驱动\log\dataDrive.log’,
# 打开日志文件的方式
filemode=‘w’
)

excelPath = r’D:\python1\自动化测web\书籍selenium\excel数据驱动\caseData\case.xlsx’
sheetName = ‘searchData’

创建ParseExcel类的实例对象

excel = ParseExcel(excelPath,sheetName)

@ddt.ddt()
class TestBaiDuSearch(unittest.TestCase):

def setUp(self) -> None:
    self.dr = webdriver.Chrome()
    print('---------用例准备就绪--------')

@ddt.data(*excel.getDataFromSheet())
def test_baidu_search(self,data):
    testDate, expectData = tuple(data)
    self.dr.get('https://www.baidu.com/')
    self.dr.maximize_window()
    self.dr.implicitly_wait(20)
    print(testDate,expectData)

    try:
        self.dr.find_element_by_id('kw').send_keys(testDate)
        self.dr.find_element_by_id('su').click()
        time.sleep(3)
        # 断言期望结果出现在页面源码中
        self.assertTrue(expectData in self.dr.page_source)

    except NoSuchElementException as e:
        logging.error('查找的页面元素不存在' + str(traceback.format_exc()))

    except AssertionError as e:
        logging.info('失败----搜索：%s ,期望：%s'%(testDate,expectData))

    except Exception as e:
        logging.error('未知错误信息：' + str(traceback.format_exc()))

    else:
        logging.info('成功----搜索：%s ,期望：%s'%(testDate,expectData))

def tearDown(self) -> None:
    self.dr.quit()
    print('--------用例执行完毕----------')

if name == ‘main’:
unittest.main()