public static Map<String, List<Map<String, Object>>> readExcelMultiSheet(InputStream in) throws InvalidFormatException, IOException {
Workbook wb = WorkbookFactory.create(in);
Map<String, List<Map<String, Object>>> list = new HashMap<String, List<Map<String, Object>>>();
for (int i = 0; i < wb.getNumberOfSheets(); i++) {//循环所有的sheet页
Sheet cruSheet = wb.getSheetAt(i);//获取每个sheet页
List<Map<String, Object>> models = new ArrayList<Map<String, Object>>();
List<String> thNames = new ArrayList<String>();
cruSheet.getRow(0).forEach(name -> {//获取第一行,遍历,也就是所有的列标题
thNames.add(name.getStringCellValue());
});
cruSheet.rowIterator().forEachRemaining(row -> {//遍历每一行
Map<String, Object> map = new HashMap<String, Object>();
map.put("rowNo", row.getRowNum());
AtomicBoolean isEmpty = new AtomicBoolean(true);
row.forEach(cell -> {//遍历每一行的每一列
Object value = formatCell(cell);//根据每列数据类型获取值
try {
if (cell.getColumnIndex() <= thNames.size()) {
isEmpty.set(isEmpty.get() && (value == null || (value instanceof String &&
Strings.isNullOrEmpty((String) value) && Strings.isNullOrEmpty(((String) value).trim()))));
map.put(thNames.get(cell.getColumnIndex()), value);//以每列的列名标题,存值
}
} catch (Exception e) {
e.printStackTrace();
logger.error("准备取索引为:{}的列,实际列数:{}", cell.getColumnIndex(), thNames.size());
}
});
//不为空读取
if (!isEmpty.get()) {
models.add(map);
}
});
models.remove(0);
list.put(cruSheet.getSheetName(), models);
}
return list;
}
@SuppressWarnings("deprecation")
private static Object formatCell(Cell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return new SimpleDateFormat("yyyyMMdd").format(DateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
long t = Math.round(cell.getNumericCellValue());
double tmp = cell.getNumericCellValue();
if (new BigDecimal(t).compareTo(new BigDecimal(tmp)) == 0) {
return t;
}
return tmp;
}
case Cell.CELL_TYPE_BLANK:
return "";
case Cell.CELL_TYPE_FORMULA:
try {
return cell.getStringCellValue();
} catch (Exception e) {
return cell.getNumericCellValue();
}
default:
return cell.getStringCellValue();
}
}