【用ddt思想重构项目】Selenium使用xlrd模块读取excel文件、使用pytest参数化实现ddt

前言

一直想学习自动化测试，但是都没行动，业余时间学习零零碎碎并记录20210421。

8、用ddt思想重构项目

• Selenium读取CSV文件
• Selenium读取XML文件
• Selenium读取json文件
• Selenium 读取excel文件
• Selenium读取ini配置文件
• Selenium读取数据库数据
• Selenium参数化测试
• Selenium ddt
• 使用ddt思想重构项目

Selenium使用xlrd模块读取ecel文件

1、调用xlrd：import xlrd
2、使用xlrd模块调用excel表格
3、结合pytest参数化格式处理方式来实现DDT

实例

1、首先创建个excel表格：test.xlsx

2、安装xlrd模块(MAC)：pip3 install xlrd

或者pycharm里选中xlrd，再选择option+O然后选择安装，授权即可

3、openpyxl包的安装，也是用快捷键装

4、代码test_excel.py

import pytest
import xlrd
from openpyxl.workbook import Workbook

def get_data():
    filename = 'test.xlsx'
    wb = xlrd.open_workbook(filename)
    sheet = wb.sheet_by_index(0)
    rows = sheet.nrows
    cols = sheet.ncols
    lst = []
    for row in range(rows):
        for col in range(cols):
            cell_data = sheet.cell_value(row, col)
            lst.append(cell_data)
            print(lst)
    return lst

@pytest.mark.parametrize('name', get_data())
def test1(name):
    print(name)

if __name__ == '__main__':
    pytest.main(['-sv', 'test_excel.py'])

5、运行结果：

========================================================= ERRORS ==========================================================
_____________________________________________ ERROR collecting test_excel.py ______________________________________________
test_excel.py:19: in <module>
    @pytest.mark.parametrize('name', get_data())
test_excel.py:7: in get_data
    wb = xlrd.open_workbook(filename)
/Library/Python/3.7/site-packages/xlrd/__init__.py:170: in open_workbook
    raise XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]+'; not supported')
E   xlrd.biffh.XLRDError: Excel xlsx file; not supported
================================================= short test summary info =================================================
ERROR test_excel.py - xlrd.biffh.XLRDError: Excel xlsx file; not supported
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Interrupted: 1 error during collection !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
==================================================== 1 error in 0.42s =====================================================
zhengxiaofang@zhengxiangdeMBP ddt % 

报错，需要再安装一个：pip3 install pyexcel-xls

~ % pip3 install pyexcel-xls
Defaulting to user installation because normal site-packages is not writeable
Collecting pyexcel-xls
  Downloading pyexcel_xls-0.6.2-py2.py3-none-any.whl (11 kB)
Collecting pyexcel-io>=0.6.2
  Downloading pyexcel_io-0.6.4-py2.py3-none-any.whl (44 kB)
     |████████████████████████████████| 44 kB 5.9 kB/s
Collecting xlrd<2
  Downloading xlrd-1.2.0-py2.py3-none-any.whl (103 kB)
     |████████████████████████████████| 103 kB 3.7 kB/s
Collecting xlwt
  Downloading xlwt-1.3.0-py2.py3-none-any.whl (99 kB)
     |████████████████████████████████| 99 kB 6.3 kB/s
Collecting lml>=0.0.4
  Downloading lml-0.1.0-py2.py3-none-any.whl (10 kB)
Installing collected packages: lml, xlwt, xlrd, pyexcel-io, pyexcel-xls
Successfully installed lml-0.1.0 pyexcel-io-0.6.4 pyexcel-xls-0.6.2 xlrd-1.2.0 xlwt-1.3.0

再运行看下，正常了

 ddt % pytest -sv test_excel.py
=================================================== test session starts ===================================================
platform darwin -- Python 3.7.3, pytest-6.2.3, py-1.10.0, pluggy-0.13.1 -- /Library/Developer/CommandLineTools/usr/bin/python3
cachedir: .pytest_cache
rootdir: /Users/ff/PycharmProjects_py3/Selenium_project/testcases/ddt
plugins: dependency-0.5.1, allure-pytest-2.8.40
collecting ... ['test1']
['test1', 'test10']
['test1', 'test10', 'test20']
['test1', 'test10', 'test20', 'test2']
['test1', 'test10', 'test20', 'test2', 'test11']
['test1', 'test10', 'test20', 'test2', 'test11', 'test21']
['test1', 'test10', 'test20', 'test2', 'test11', 'test21', 'test3']
['test1', 'test10', 'test20', 'test2', 'test11', 'test21', 'test3', 'test12']
['test1', 'test10', 'test20', 'test2', 'test11', 'test21', 'test3', 'test12', 'test22']
['test1', 'test10', 'test20', 'test2', 'test11', 'test21', 'test3', 'test12', 'test22', 'test4']
['test1', 'test10', 'test20', 'test2', 'test11', 'test21', 'test3', 'test12', 'test22', 'test4', 'test13']
['test1', 'test10', 'test20', 'test2', 'test11', 'test21', 'test3', 'test12', 'test22', 'test4', 'test13', 'test23']
collected 12 items                                                                                                        

test_excel.py::test1[test1] test1
PASSED
test_excel.py::test1[test10] test10
PASSED
test_excel.py::test1[test20] test20
PASSED
test_excel.py::test1[test2] test2
PASSED
test_excel.py::test1[test11] test11
PASSED
test_excel.py::test1[test21] test21
PASSED
test_excel.py::test1[test3] test3
PASSED
test_excel.py::test1[test12] test12
PASSED
test_excel.py::test1[test22] test22
PASSED
test_excel.py::test1[test4] test4
PASSED
test_excel.py::test1[test13] test13
PASSED
test_excel.py::test1[test23] test23
PASSED

=================================================== 12 passed in 0.32s ====================================================
 ddt % 

“永不放弃，总有希望在前面等待！”送给自己，也送给正在阅读文章的博友们～