TestNG参数化测试之Excel读取数据

来源：互联网发布：淘宝超时发货赔付规则编辑：程序博客网时间：2024/05/21 05:56

1、新建Excel文档，准备好测试数据

在当前工程的resources目录下，新建文件名为testdata的Excel文档

这里写图片描述

打开Excel，将当前sheet重命名为calculator，构造num1、num2、result三个参数数据
这里写图片描述

2、新建一个ExcelData类用来获取Excel中的数据

ExcelData类需要引入jxl.jar包，jxl.jar是专门处理excel中数据的，代码如下:

package com.mcj.testng;import java.io.File;import java.io.IOException;import java.util.ArrayList;import java.util.HashMap;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;public class ExcelData {    public Workbook workbook;    public Sheet sheet;    public Cell cell;    int rows;    int columns;    public String fileName;    public String caseName;    public ArrayList<String> arrkey = new ArrayList<String>();    String sourceFile;    /**     * @param fileName   excel文件名     * @param caseName   sheet名     */    public ExcelData(String fileName, String caseName) {        super();        this.fileName = fileName;        this.caseName = caseName;    }    /**     * 获得excel表中的数据     */    public Object[][] getExcelData() throws BiffException, IOException {        workbook = Workbook.getWorkbook(new File(getPath()));        sheet = workbook.getSheet(caseName);        rows = sheet.getRows();        columns = sheet.getColumns();        // 为了返回值是Object[][],定义一个多行单列的二维数组        HashMap<String, String>[][] arrmap = new HashMap[rows - 1][1];        // 对数组中所有元素hashmap进行初始化        if (rows > 1) {            for (int i = 0; i < rows - 1; i++) {                arrmap[i][0] = new HashMap<>();            }        } else {            System.out.println("excel中没有数据");        }        // 获得首行的列名，作为hashmap的key值        for (int c = 0; c < columns; c++) {            String cellvalue = sheet.getCell(c, 0).getContents();            arrkey.add(cellvalue);        }        // 遍历所有的单元格的值添加到hashmap中        for (int r = 1; r < rows; r++) {            for (int c = 0; c < columns; c++) {                String cellvalue = sheet.getCell(c, r).getContents();                arrmap[r - 1][0].put(arrkey.get(c), cellvalue);            }        }        return arrmap;    }    /**     * 获得excel文件的路径     * @return     * @throws IOException     */    public String getPath() throws IOException {        File directory = new File(".");        sourceFile = directory.getCanonicalPath() + "\\src\\resources\\"                + fileName + ".xls";        return sourceFile;    }}1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

3、创建TestNg测试类CalculatorExcel

package com.mcj.testng;import java.io.IOException;import java.util.HashMap;import org.testng.Assert;import org.testng.annotations.DataProvider;import org.testng.annotations.Test;import jxl.read.biff.BiffException;public class CalculatorExcel {    Calculator cal=new Calculator();    @DataProvider(name="num")    public Object[][] Numbers() throws BiffException, IOException{        ExcelData e=new ExcelData("testdata", "calculator");        return e.getExcelData();    }    @Test(dataProvider="num")    public void testAdd(HashMap<String, String> data){        System.out.println(data.toString());        float num1=Float.parseFloat(data.get("num1"));        float num2=Float.parseFloat(data.get("num2"));        float expectedResult=Float.parseFloat(data.get("result"));          Float actual=cal.add(num1, num2);        Assert.assertEquals(actual, expectedResult);    }}1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

4、运行结果

{result=10.4, num2=3.9, num1=6.5}
{result=7, num2=2, num1=5}
{result=15, num2=8, num1=7}
{result=8.8, num2=3.3, num1=5.5}
{result=-2, num2=3, num1=-5}
{result=2.6, num2=-2, num1=4.6}
{result=27, num2=23, num1=4}
{result=37, num2=12, num1=25}
PASSED: testAdd({result=10.4, num2=3.9, num1=6.5})
PASSED: testAdd({result=7, num2=2, num1=5})
PASSED: testAdd({result=15, num2=8, num1=7})
PASSED: testAdd({result=8.8, num2=3.3, num1=5.5})
PASSED: testAdd({result=-2, num2=3, num1=-5})
PASSED: testAdd({result=2.6, num2=-2, num1=4.6})
PASSED: testAdd({result=27, num2=23, num1=4})
PASSED: testAdd({result=37, num2=12, num1=25})

===============================================
Default test
Tests run: 8, Failures: 0, Skips: 0

阅读全文

0 0