excel封装和ddt D17

1）excel封装 openpyxl的操作

2）ddt 数据驱动测试

## openpyxl的操作

1.安装：pip install openpyxl

2.导入 openpyxl： import openpyxl

3.workbook对象：工作簿，openpyxl.load_workbook()

4.sheet对象：表单 sheet = workbook["表单名"]

5.cell对象：单元格 cell = sheet.cell()

## ddt 数据驱动测试

1.安装：pip install ddt

2.导入：import ddt

3.测试类加上 @ddt.ddt

4.需要进行数据驱动的方法上加@ddt.data(*测试数据)

5.测试用例方法用参数接收 def test_login(self,case_info)

附带代码：

"""excel操作"""
import openpyxl


class ExcelHandler:
    def __init__(self, file_path):
        self.file_path = file_path
        self.workbook = None

    def open_file(self):
        """打开文件"""
        workbook = openpyxl.load_workbook(self.file_path)
        self.workbook = workbook
        return workbook

    def get_sheet(self, sheet_name):
        """获取sheet表单"""
        workbook = self.open_file()
        return workbook[sheet_name]

    def read_data(self, sheet_name):
        """读取数据，并将表格内容按照列表嵌套字典形式返回"""
        sheet = self.get_sheet(sheet_name)
        rows = list(sheet.rows)
        # 获取标题
        headers = []
        for title in rows[0]:
            headers.append(title.value)

        data = []
        for row in rows[1:]:
            row_data = {}
            for index, cell in enumerate(row):
                row_data[headers[index]] = cell.value
            data.append(row_data)
        return data

    def write(self, sheet_name, row, column, data):
        sheet = self.get_sheet(sheet_name)
        sheet.cell(row, column).value = data
        self.save()
        self.close()

    def save(self):
        """保存文件"""
        self.workbook.save(self.file_path)

    def close(self):
        """关闭文件"""
        self.workbook.close(self.file_path)


if __name__ == '__main__':
    print(ExcelHandler("web_cases.xlsx").read_data("Sheet1"))

"""测试登录功能"""
import unittest
import ddt

from demo_2024.excel_handler import ExcelHandler


def login(username=None, password=None):
    """登录"""
    if username != None and password != None:
        if username == "huahua" and password == "123456":
            return {"msg": "login success"}
        else:
            return {"msg": "username or password is error"}
    else:
        return {"msg": "username or password is empty"}


# 用封装的excel操作读取出测试用例
cases = ExcelHandler("web_cases.xlsx").read_data("Sheet1")

"""数据驱动测试：ddt driver testing"""


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

    @ddt.data(*cases)
    def test_login(self, case):
        data = eval(case["data"])
        username = data["username"]
        password = data["password"]
        expected = case["expected"]
        actual = login(username, password)
        self.assertTrue(expected == actual["msg"])


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

作业：

ddt数据驱动
1， 封装 excel 操作的类（自己实现！！）
2， 基于ddt, 实现 login 函数的单元测试。

上期答案：

# 作业1
import unittest
import os

from lenmon.libs.HTMLTestRunnerNew import HTMLTestRunner

# 初始化一个加载器
loader = unittest.TestLoader()

dir_path = os.path.dirname(os.path.abspath(__file__))
# 使用loader收集所有的测试用例
test_suite = loader.discover(dir_path)

# 执行测试用例，生成测试报告
with open("reports.html", "wb") as f:
    runner = HTMLTestRunner(
        f,
        title="python第一次测试报告",
        description="登录模块的测试报告",
        tester="wuyi"
    )
    runner.run(test_suite)
# 作业2
import openpyxl

work_book = openpyxl.open("web_cases.xlsx")
sheet = work_book["Sheet1"]
rows = list(sheet.rows)

data = []
headers = []
for title in rows[0]:
    headers.append(title.value)

for row in rows[1:]:
    row_data = {}

    for index, cell in enumerate(row):
        row_data[headers[index]] = cell.value
    data.append(row_data)
print(data)