1、用到的包管理
2、第一个类,其中本类的方法没有用到另一个累的内容,调用的解析excel方法是
package com.pingan.point;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import com.pingan.core.dataprovider.ExcelDataProvider;
public class TestDataByExcelFile {
@DataProvider(name="searchData")
public static Iterator<Object[]> data() throws IOException
{
//return getSearchData("D:\\","2857.xlsx","Sheet0");//获取Excel文件的测试数据
return new ExcelDataProvider("D:\\2857.xlsx","Sheet0","");
}
@Test(dataProvider="searchData")
public void testSearch1(String searchdata1,String searchdata2,String searchdata3,Boolean searchResult) {
System.out.println(searchdata1);
System.out.println(searchdata2);
System.out.println(searchdata3);
System.out.println(searchResult);
}
@Test(dataProvider="searchData")
public void testSearch2(Map<String, String> caseData) {
System.out.println(caseData.get("mobile"));
}
//从Excel文件获取测试数据的静态方法
public static Object[][] getSearchData(String filePath,String FileName,String sheetName) throws IOException{
//根据参数传入的数据文件路径和文件名称,组合出Excel数据文件的绝对路径,声明一个File文件对象
File file = new File(filePath + "\\" + FileName);
//创建FileInputStream对象用于读取Excel文件
FileInputStream inputStream = new FileInputStream(file);
Workbook Workbook = null;
//获取文件名参数的扩展名,判断是.xlsx文件还是.xls文件
String fileExtensionName = FileName.substring(FileName
.indexOf("."));
if (fileExtensionName.equals(".xlsx")) {
Workbook = new XSSFWorkbook(inputStream);
} else if (fileExtensionName.equals(".xls")) {
Workbook = new HSSFWorkbook(inputStream);
}
//通过sheetName参数,声称Sheet对象
Sheet Sheet = Workbook.getSheet(sheetName);
//获取Excel数据文件Sheet1中数据的行数,getLastRowNum()方法获取数据的最后一行行号
//getFirstRowNum()方法获取数据的第一行行号,相减之后得出数据的行数,Excel文件的行号和列号都是从0开始
int rowCount = Sheet.getLastRowNum() - Sheet.getFirstRowNum();
//创建list对象存储从Excel数据文件读取的数据
List<Object[]> records = new ArrayList<Object[]>();
//循环遍历Excel数据文件的所有数据,除了第一行,第一行是数据列名称
for (int i = 1; i < rowCount + 1; i++) {
//使用getShow方法获取行对象
Row row = Sheet.getRow(i);
//声明一个数组,存储Excel数据文件每行中的3个数据,数组的大小用getLastCellNum()方法进行动态声明,实现测试数据个数和数组大小一致
String fields[] = new String[row.getLastCellNum()];
for (int j = 0; j < row.getLastCellNum(); j++) {
//使用getCell()和getStringCellValue()方法获取Excel文件中的单元格数据
if(row.getCell(j)!=null){
row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
fields[j] =row.getCell(j).getStringCellValue();
}
}
//将fields的数据对象存入records的list中
records.add(fields);
}
// 将存储测试数据的List转换为一个Object的二维数组
Object[][] results = new Object[records.size()][];
// 设置二位数组每行的值,每行是一个Object对象
for (int i = 0; i < records.size(); i++) {
results[i] = records.get(i);
}
System.out.println(results.toString());
return results;
}
}
3、第二个类
package com.pingan.core.dataprovider;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExcelDataProvider implements Iterator<Object[]> {
private static final Logger logger = LoggerFactory
.getLogger(ExcelDataProvider.class);
private Workbook book = null;
private Sheet sheet = null;
private int rowCount = 0;
private int curRowNo = 0;
private int columnNum = 0;
private String[] columnnName;
private List<Object> datalist = new ArrayList<Object>();
private Object[] data = null;
public ExcelDataProvider(String excelFile, String sheetName,
String groupName) {
try {
logger.info("File Path:" + excelFile);
logger.info("sheetname:" + sheetName + " groupName:" + groupName);
//
File excelDir = new File(excelFile);
FileInputStream is = new FileInputStream(excelDir);
this.book = WorkbookFactory.create(is);
this.sheet = book.getSheet(sheetName);
if (this.sheet != null) {
Row c = sheet.getRow(0);
this.columnNum = c.getPhysicalNumberOfCells();
columnnName = new String[c.getPhysicalNumberOfCells()];
for (int i = 0; i < c.getPhysicalNumberOfCells(); i++) {
columnnName[i] = c.getCell(i).getStringCellValue();
}
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
//
Row tmpRow = sheet.getRow(i);
Map<String, String> rowData = new HashMap<String, String>();
rowData = GetRowData(tmpRow);
if (groupName.trim().length() > 0) {
if (rowData.containsKey("GroupName")
&& rowData.get("GroupName").equals(groupName)) {
datalist.add(rowData);
this.rowCount++;
}
} else {
datalist.add(rowData);
this.rowCount++;
}
}
data = datalist.toArray();
} else {
logger.info("sheetName:\"" + sheetName
+ "\" 涓嶅瓨鍦�!璇锋鏌DataFactory.dataName鎴栬�呮柟娉曞悕鏄惁涓巗heetName鐩哥瓑");
}
} catch (Exception e) {
logger.error("璇诲彇Excel鏂囦欢澶辫触", e);
}
}
private Map<String, String> GetRowData(Row tmpRow) {
Map<String, String> rowData = new HashMap<String, String>();
for (int j = 0; j < this.columnNum; j++) {
String temp = "";
try {
Cell cell = tmpRow.getCell(j,Row.RETURN_BLANK_AS_NULL);
if(cell !=null){
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
temp = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
temp = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
temp = Boolean.toString(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
temp = "";
break;
default:
temp = "Type is not found";
break;
}
}
else{
temp="null";
}
} catch (ArrayIndexOutOfBoundsException ex) {
temp = "";
}
rowData.put(this.columnnName[j], temp);
// logger.info(rowData.toString());
}
return rowData;
}
public boolean hasNext() {
// TODO Auto-generated method stub
if (this.rowCount == 0 || this.curRowNo >= this.rowCount) {
return false;
} else
return true;
}
public Object[] next() {
logger.debug("curRowNo:{};rowCount:{}", curRowNo, rowCount);
Object r[] = new Object[1];
r[0] = data[curRowNo];
this.curRowNo++;
return r;
}
public void remove() {
// TODO Auto-generated method stub
throw new UnsupportedOperationException("remove unsupported.");
}
@Override
public void forEachRemaining(Consumer<? super Object[]> action) {
// TODO Auto-generated method stub
}
}