数据文件

序号	检索词	期望结果
1	北京	北京
2	上海	上海
3	广州	广州

获取测试数据方法

安装openpyxl

C:\Users\Administrator>pip install openpyxl
Collecting openpyxl
  Downloading openpyxl-3.0.3.tar.gz (172 kB)
     |████████████████████████████████| 172 kB 384 kB/s
Collecting jdcal
  Using cached jdcal-1.4.1-py2.py3-none-any.whl (9.5 kB)
Collecting et_xmlfile
  Using cached et_xmlfile-1.0.1.tar.gz (8.4 kB)
Installing collected packages: jdcal, et-xmlfile, openpyxl
    Running setup.py install for et-xmlfile ... done
    Running setup.py install for openpyxl ... done
Successfully installed et-xmlfile-1.0.1 jdcal-1.4.1 openpyxl-3.0.3

方法封装

# encoding = utf-8
from openpyxl import load_workbook
 
 
class ParseExcel(object):
 
    def __init__(self, excelPath, sheetName):
        self.wb = load_workbook(excelPath)
        #  self.sheet = self.lwb.get_sheet_by_name(sheetName)
        self.sheet = self.wb[sheetName]
        self.maxRowNum = self.sheet.max_row
 
    def getDatasFromSheet(self):
        dataList = []
        for line in list(self.sheet.rows)[1:]:
            tmpList = []
            tmpList.append(line[1].value)
            tmpList.append(line[2].value)
            dataList.append(tmpList)
        return dataList
 
 
if __name__ == '__main__':
    excelPath = u'D:\\Programs\\Python\\PythonUnittest\\TestData\\测试数据.xlsx'
    sheetName = u'搜索数据表'
    pe = ParseExcel(excelPath, sheetName)
    for i in pe.getDatasFromSheet():
        print(i[0], i[1])

封装了getDatasFromSheet方法，该方法将解析Excel，并将数据存到List中去，后续的测试代码调用的实际上是从List里边获取数据

测试代码

# encoding = utf-8
from selenium import webdriver
import unittest
import time
import traceback
import ddt
import logging
from Util.ParseExcelUtil 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, %d %b %Y %H:%M:%S',
    # 日志文件存放的目录及日志文件名
    filename='D:\\Programs\\Python\\PythonUnittest\\Reports\\TestResults.TestResults',
    # 打开日志的方式
    filemode='w'
)
 
excelPath = u"D:\\Programs\\Python\\PythonUnittest\\TestData\\测试数据.xlsx"
sheetName = u"搜索数据表"
excel = ParseExcel(excelPath, sheetName)
 
 
@ddt.ddt
class TestDataDrivenByExcel(unittest.TestCase):
    def setUp(self):
        self.driver = webdriver.Chrome()
 
    @ddt.data( * excel.getDatasFromSheet())
    def test_dataDrivenByExcel(self, data):
        testData, expectData = tuple(data)
        url = "http://www.baidu.com"
        self.driver.get(url)
        self.driver.maximize_window()
        self.driver.implicitly_wait(10)
 
        try:
            self.driver.find_element_by_id("kw").send_keys(testData)
            self.driver.find_element_by_id("su").click()
            time.sleep(3)
            self.assertTrue(expectData in self.driver.page_source)
        except NoSuchElementException as e:
            logging.error(u"查找的页面元素不存在，异常堆栈信息为：" + str(traceback.format_exc()))
        except AssertionError as e:
            logging.info(u"搜索 ‘%s’,期望 ‘%s’ ,失败" % (testData, expectData))
        except Exception as e:
            logging.error(u"未知错误，错误信息：" + str(traceback.format_exc()))
        else:
            logging.info(u"搜索 ‘%s’,期望 ‘%s’ ,通过" % (testData, expectData))
 
    def tearDown(self):
        self.driver.quit()
 
 
if __name__ == "__main__":
    unittest.main()