案例3-数据驱动测试-从excel导入数据

excel：测试数据.xlsx

python获取excel中的数据方法，excelutil.py

#encoding =utf-8
# -*- coding:utf-8 -*-
from openpyxl import load_workbook
class pexcel(object):
    def __init__(self,excelPath,sheetName):
        self.wb=load_workbook(excelPath)
        self.sheet=self.wb.get_sheet_by_name(sheetName)
        self.maxRowNum=self.sheet.max_row
    def getdatas(self):
        datalist=[]
        for line in self.sheet.rows[1:]:
            tmplist=[]
            tmplist.append(line[1].value)
            tmplist.append(line[2].value)
            datalist.append(tmplist)

        return datalist
if __name__=="__main__":
    execelPath=u"C:\\测试数据.xlsx"
    sheetName=u"Sheet1"
    for i in pexcel(execelPath,sheetName).getdatas():
        print i[0],i[1]

从excel导入测试数据.py

#encoding =utf-8
# -*- coding:utf-8 -*-
from selenium import webdriver
import unittest,time
import logging,traceback
import ddt
from reportHtml import htmlTemplate
from selenium.common.exceptions import NoSuchElementException
from excelutil import pexcel
logger = logging.getLogger('mylogger1')
logger.setLevel(logging.DEBUG)
# 创建一个handler，用于写入日志文件
fh = logging.FileHandler('C:/test.log')
fh.setLevel(logging.DEBUG)
# 再创建一个handler，用于输出到控制台
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
# 定义handler的输出格式
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
fh.setFormatter(formatter)
ch.setFormatter(formatter)
# 给logger添加handler
logger.addHandler(fh)
logger.addHandler(ch)

execelPath = u"C:\\测试数据.xlsx"
sheetName = u"Sheet1"
excel=pexcel(execelPath,sheetName)

@ddt.ddt
class Testdemo(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        Testdemo.trStr=""
    def setUp(self):
        self.driver=webdriver.Firefox(executable_path="C:\\webdriver\geckodriver")
        status=None
        flag=0
    @ddt.data(*excel.getdatas())
    def test_dataDriverByFile(self,value):
        testdata,expectdata=tuple(value)
        flagDict={0:'red',1:'#00AC4E'}
        url="http://www.baidu.com"
        self.driver.get(url)
        self.driver.maximize_window()
        # print value.decode('utf-8')
        self.driver.implicitly_wait(10)
        try:
            start=time.time()
            startTime=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
            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,e:
            logger.error(u"找不到页面元素")
            status='fail'
            flag=0
        except AssertionError,e:
            logger.info(u"搜索失败")
            status='fail'
            flag=0
        except Exception,e:
            logger.info(u"未知错误")
            status='fail'
            flag=0
        else:
            logger.info(u"搜索成功")
            status='pass'
            flag=1
        wasteTime=time.time()-start-3
        Testdemo.trStr += u'''
        <tr>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%.2f</td>
            <td style="color:%s">%s</td>
        </tr><br/>'''%(testdata,expectdata,startTime,wasteTime,flagDict[flag],status)
    def tearDown(self):
        self.driver.quit()
    @classmethod
    def tearDownClass(cls):
        htmlTemplate(Testdemo.trStr)

if __name__=='__main__':
    unittest.main()