注意点:目前testNG 只支持2003左右的excel格式文件,但是文件名后缀要求必须是.xlsx,所以要把.xls的后缀名改为.xlsx,才能正常读取,真是个坑!
1、pom.xml文件配置如下:
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>org.testng</groupId>
<artifactId>testng</artifactId>
<version>6.9.13.8</version>
</dependency>
2、新建Excel文档,准备好测试数据
在当前工程的resources目录下,新建文件名为testdata的Excel文档
打开Excel,将当前sheet重命名为calculator,构造num1、num2、result三个参数数据
3、新建一个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();
HashMap<String, String>[][] arrmap = new HashMap[rows - 1][1];
if (rows > 1) {
for (int i = 0; i < rows - 1; i++) {
arrmap[i][0] = new HashMap<>();
}
} else {
System.out.println("excel中没有数据");
}
for (int c = 0; c < columns; c++) {
String cellvalue = sheet.getCell(c, 0).getContents();
arrkey.add(cellvalue);
}
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
4、创建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
5、运行结果
{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