jxl读取excel封装成List、Map

一、封装成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;
}