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 openpyxlclass ExcelHandler:def __init__(self, file_path):self.file_path = file_pathself.workbook = Nonedef open_file(self):"""打开文件"""workbook = openpyxl.load_workbook(self.file_path)self.workbook = workbookreturn workbookdef 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.valuedata.append(row_data)return datadef write(self, sheet_name, row, column, data):sheet = self.get_sheet(sheet_name)sheet.cell(row, column).value = dataself.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 ddtfrom demo_2024.excel_handler import ExcelHandlerdef 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 osfrom 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 openpyxlwork_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.valuedata.append(row_data)
print(data)