Unittest+ddt（操作Excel生成网页级报告）

项目目录如图：

测试数据如图：

各文件代码：
ddt_unittest_by_excel.py

#encoding=utf-8
import ddt
import os
import time
import unittest
from readexcel import ExcelUtil
from writeexcel import Write_excel
from selenium import webdriver
from HTMLTestRunner import HTMLTestRunner
curpath = os.path.dirname(os.path.realpath(__file__))# 获取demo_api.xlsx路径
testxlsx = os.path.join(curpath, "data.xlsx")
testdata =ExcelUtil(testxlsx).dict_data()
wt = Write_excel("data.xlsx")

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

    @classmethod
    def setUpClass(cls):
       cls.driver=webdriver.Chrome()
       cls.driver.get("https://www.baidu.com/")
       cls.driver.maximize_window()

    @ddt.data(*testdata)
    def test_baidu(self, data):
        self.driver.implicitly_wait(3)
        try:
            start = time.time()  # 获取当前的时间戳，用于后面计算查询耗时用
            test_data,expect_data=data["搜索词"],data["期望结果"]
            self.driver.find_element_by_id("kw").clear()
            self.driver.find_element_by_id("kw").send_keys(test_data)
            self.driver.find_element_by_id("su").click()
            time.sleep(3)
            self.assertTrue(expect_data in self.driver.page_source)#断言期望结果是否出现在页面源代码中
            end=time.time()
            time_consuming=int( end - start)
            wt.write(data["rowNum"],4,"pass")
            wt.write(data["rowNum"],5,time_consuming)
        except AssertionError as e:
            end = time.time()
            time_consuming =int( end - start)
            wt.write(data["rowNum"],4,"Failed")
            wt.write(data["rowNum"], 5, time_consuming)
            raise AssertionError
        except Exception as e:
            end = time.time()
            time_consuming =int( end - start)
            wt.write(data["rowNum"],4,"Error")
            wt.write(data["rowNum"],5,time_consuming)

    @classmethod
    def tearDownClass(cls):
        cls.driver.quit()

if __name__ == "__main__":
    suite1= unittest.TestLoader().loadTestsFromTestCase(Test_api)
    suite=unittest.TestSuite([suite1])
    with open('HwTestReport.html', 'wb') as report:
        runner = HTMLTestRunner(stream=report,
                                verbosity=2,
                                title='HTMLTestRunner 测试',
                                description='带饼图，带详情'
                                )

        runner.run(suite)

readexcel.py

import xlrd
class ExcelUtil():
    def __init__(self, excelPath, sheetName="Sheet1"):
        self.data = xlrd.open_workbook(excelPath)
        self.table = self.data.sheet_by_name(sheetName)
        self.keys = self.table.row_values(0)# 获取第一行作为key值，返回列表例如:["序号","搜索词","期望结果","测试时间","测试结果"]
        self.rowNum = self.table.nrows # 获取总行数
        self.colNum = self.table.ncols  # 获取总列数

    def dict_data(self):
        if self.rowNum <= 1:
            print("总行数小于1")
        else:
            r = []
            j = 1
            for i in list(range(self.rowNum-1)):#遍历所有行
                s = {}
                s['rowNum'] = i+2#从第二行取对应values值
                values = self.table.row_values(j)#获取第j行的数据，返回列表
                print(values)
                for x in list(range(self.colNum)):#遍历所有列
                    s[self.keys[x]] = values[x]#存储到字典里
                r.append(s)#每行对应的每列数据存储到列表里
                j += 1
            return r
if __name__ == "__main__":
    filepath = "data.xlsx"
    sheetName = "Sheet1"
    data = ExcelUtil(filepath, sheetName)
    print(data.dict_data())

writeexcel.py

#encoding=utf-8
from openpyxl import load_workbook
class Write_excel(object):
    '''修改excel数据'''
    def __init__(self, filename):
        self.filename = filename
        self.wb = load_workbook(self.filename)
        self.ws = self.wb.active  # 激活sheet

    def write(self, row_n, col_n, value):
        '''写入数据，如(2,3，"hello"),第二行第三列写入数据"hello"'''
        self.ws.cell(row_n, col_n).value = value
        self.wb.save(self.filename)

if __name__ == "__main__":
    wt = Write_excel("data.xlsx")
    wt.write(5, 5, "HELLEOP")
    wt.write(5, 6, "HELLEOP")

执行结果（Excel表格写入+网页报告）：