数据驱动部分，是自动化测试常用部分，也是参数化设计的重要环节，前面分享了，mysql、yaml做数据源，那么再来分享下excel做数据驱动
思路：
先用POI读取excel。解析读取数据，返回list,返回Object[][]即可
工具类文件：
读取excel，返回map对象list集合
ReadExcelUtil.java
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map; /**
* 读取excel，返回map对象list集合
*
* @author longrong.lang
*/
public class ReadExcelUtil { /**
* 读取excel操作
*
* @param filePath
* @return:读取excel，返回map对象集合
*/
public static List<Map<String, String>> getExcuteList(String filePath) {
Workbook wb = null;
Sheet sheet = null;
Row row = null;
List<Map<String, String>> list = null;
String cellData = null;
String columns[] = {"name", "method", "value","备注"};
wb = readExcel(filePath);
if (wb != null) {
//用来存放表中数据
list = new ArrayList<Map<String, String>>();
//获取第一个sheet
sheet = wb.getSheetAt();
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
//获取第一行
row = sheet.getRow();
//获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = ; i < rownum; i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
for (int j = ; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
map.put(columns[j], cellData);
}
} else {
break;
}
list.add(map);
}
}
return list;
} /**
* 判断excel文件的类型
*
* @param filePath
* @return
*/
public static Workbook readExcel(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return wb = new XSSFWorkbook(is);
} else {
return wb = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
} public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
//判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
//判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
} }
然后把解析出来的list转换成Object[][]类型的数据，且结合在@DataProvider中。
import org.testng.annotations.DataProvider; import java.util.List;
import java.util.Map; public class ExcelDataHeleper { @DataProvider
public Object[][] dataMethod(){
List<Map<String, String>> result = ReadExcelUtil.getExcuteList("D:\\data.xls");
Object[][] files = new Object[result.size()][];
for(int i=; i<result.size(); i++){
files[i] = new Object[]{result.get(i)};
}
return files;
} }
再通过测试文件来测试一下：
import org.testng.annotations.Test;
import java.util.Map;
public class TestDataUtil extends ExcelDataHeleper {
@Test(dataProvider="dataMethod")
public void testmethod1(Map<?, ?> param){
System.out.println(param.get("name")+"\t"+param.get("method")+"\t"+param.get("value"));
}
}
运行结果：
[TestNG] Running:
C:\Users\Administrator\.IntelliJIdea2018.2\system\temp-testng-customsuite.xml
输入框 id kw
百度一下 id su
退出 name tj_logout
2018年11月15日14点41分 2018/11/15 14:42:31 脚本
退出 name tj_logout ===============================================
Default Suite
Total tests run: 5, Failures: 0, Skips: 0
=============================================== Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF-8 Process finished with exit code 0