本例子中使用java的jxl来实现对excel的读取,读取到map中。
本例子在其他人例子的基础上,稍微改动了一下。
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
import org.testng.Assert;
import jxl.*;
/**
* Excel放在Data文件夹下</p>
* Excel命名方式:测试类名.xls</p>
* Excel的sheet命名方式:测试方法名</p>
* Excel第一行为Map键值</p>
* 代码参考郑鸿志的Blog
* @ClassName: ExcelDataProvider
* @Description: TODO(读取Excel数据)
*/
public class ExcelDataProvider {
private Workbook book = null;
private Sheet sheet = null;
private int rowNum = 0;
private int currentRowNo = 0;
private int columnNum = 0;
private String[] columnnName;
public ExcelDataProvider(String classname, String methodname) {
try {
int dotNum = classname.indexOf(".");
if (dotNum > 0) {
classname = classname.substring(classname.lastIndexOf(".") + 1,
classname.length());
}
//从/data文件夹下读取以类名命名的excel文件
String path = "data/" + classname + ".xls";
InputStream inputStream = new FileInputStream(path);
book = Workbook.getWorkbook(inputStream);
//取sheet
sheet = book.getSheet(methodname);
rowNum = sheet.getRows();
System.out.println("RowNUm is "+rowNum);
Cell[] cell = sheet.getRow(0);
columnNum = cell.length;
System.out.println("columnNum is "+columnNum);
columnnName = new String[cell.length];
for (int i = 0; i < cell.length; i++) {
columnnName[i] = cell[i].getContents().toString();
}
this.currentRowNo++;
} catch (Exception e) {
e.printStackTrace();
Assert.fail("unable to read Excel data");
}
}
//判断是否读取结束
public boolean hasNext() {
if (this.rowNum == 0 || this.currentRowNo >= this.rowNum) {
try {
book.close();
} catch (Exception e) {
e.printStackTrace();
}
return false;
} else {
// sheet下一行内容为空判定结束
if ((sheet.getRow(currentRowNo))[0].getContents().equals(""))
return false;
return true;
}
}
//每一行的数据都读入到map中,以列名为key,每一行的值为value
public Map<String, String> next() {
Cell[] c = sheet.getRow(this.currentRowNo);
Map<String, String> data = new HashMap<String, String>();
for (int i = 0; i < this.columnNum; i++) {
String temp = "";
try {
temp = c[i].getContents().toString();
} catch (ArrayIndexOutOfBoundsException ex) {
temp = "";
}
data.put(this.columnnName[i], temp);
}
this.currentRowNo++;
return data;
}
public void remove() {
throw new UnsupportedOperationException("remove unsupported.");
}
}