一、封装成List,数据格式为List<Map<String,String>>。将表头作为Key与明细中的值封装到Map,再将每一个Map装入到List中。
@Test
public void readExcel2Map(){
String path = "清单.xls";
try {
InputStream inputStream = new FileInputStream(path);
Workbook workbook = Workbook.getWorkbook(inputStream);
Sheet sheet = workbook.getSheet(0);
List<String> key = new ArrayList<>();
Cell[] row = sheet.getRow(0);
for (int i = 0; i < row.length; i++) {
key.add(row[i].getContents().toString());
}
int columns = sheet.getColumns();
List<Object> datas = new ArrayList<>();
Map<String, String> data;
for (int i = 1; i < columns; i++) {
data = new HashMap<String, String>();
String value = "";
Cell[] column = sheet.getRow(i);
for (int j = 0; j < column.length; j++) {
value = column[j].getContents().toString();
data.put(key.get(j),value);
}
datas.add(data);
}
} catch (Exception e) {
e.printStackTrace();
}
}
二、封装成Map,数据格式为Map<String,Map<String, String>>。将Excel的第一列作为外层Map的Key,Value为Map类型。数据对应如下图Excel。
这里因为在TestNG要参数化需要封装成Object[]或者Object[][]或者Iterator<Object>
public Object[] getCaseData(String moduleName, int caseId){
try {
path = "data/data.xls";
Object[] caseData = new Object[2];
inputStream = new FileInputStream(path);
book = Workbook.getWorkbook(inputStream);
sheet = book.getSheet(moduleName); //如sheet:login
rowNum = sheet.getRows(); //获取行数量,除去表头用例数量为 rows-1
Cell[] cells = sheet.getColumn(0); // 获取第一列的所有数据
List<String> caseKey = new ArrayList<String>();
for (int i = 1; i < rowNum; i++) {
caseKey.add(cells[i].getContents().toString());
}
//获取excel表头作为每行数据的key
List<String> dataKey = new ArrayList<String>();
Cell[] rows = sheet.getRow(0); //获取第一行所有数据
columnNum = sheet.getColumns(); //获取所有列
for (int j = 1; j < columnNum-1; j++) {
dataKey.add(rows[j].getContents().toString());
}
Map<Object, Map<String, String>> cases = new HashMap<Object, Map<String, String>>();
Map<String, String> data = new HashMap<String, String>();
Cell[] row = sheet.getRow(caseId);
String value = "";
for (int c = 1; c < row.length-1; c++) {
value = row[c].getContents().toString();
data.put(dataKey.get(c-1), value);
}
cases.put(caseId, data);
caseData[0] = cases;
caseData[1] = caseId;
return caseData;
/*Map<String, String> data = null;
for (int r = 1; r < rowNum; r++) {
data = new HashMap<String, String>();
Cell[] row = sheet.getRow(r);
String value = "";
for (int c = 1; c < row.length-1; c++) {
value = row[c].getContents().toString();
data.put(dataKey.get(c-1), value);
}
cases.put(caseKey.get(r-1), data);
}*/
} catch (FileNotFoundException e) {
logger.error("没有找到指定的文件:" + "[" + path + "]");
Assert.fail("没有找到指定的文件:" + "[" + path + "]");
} catch (Exception e){
logger.error("不能读取文件: [" + path + "]",e);
Assert.fail("不能读取文件: [" + path + "]");
}
return null;
}