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

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;

    }

}

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);

    }

}

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