1.由于是顺序解析class类的各字段所以需要 excel列的顺序和实体类中字段的顺序一致和类型对应
实体类代码省略,解析excel代码如下:
public static <T> List<T> parseExcel(InputStream fileInputStream, Class<T> type) throws IOException, InstantiationException, IllegalAccessException, InvalidFormatException { Workbook workBook = WorkbookFactory.create(fileInputStream); List<T> result = new ArrayList<>(); Sheet sheet = workBook.getSheetAt(0); for(int ri = sheet.getFirstRowNum() + 1 ; ri <= sheet.getLastRowNum(); ri++) { Row row = sheet.getRow(ri); if (row == null) { break; } T object = type.newInstance(); Field[] fields = object.getClass().getDeclaredFields(); Field.setAccessible(fields, true); boolean hasData = false; for(int ci = row.getFirstCellNum(); ci < row.getLastCellNum(); ci++) { Cell cell = row.getCell(ci); Object value = null; try { value = getCellValue(cell, fields[ci]); } catch (Exception e) { throw new RuntimeException("第" + (ri + 1) + "行第" + (ci + 1) + "列单元格数据解析异常"); } fields[ci].set(object, value); if (value != null) { hasData = true; } } if (hasData) { ValidatorFactory validatorFactory=Validation.buildDefaultValidatorFactory(); // 对对象属性进行校验 如果有问题 则直接抛出异常 Set<ConstraintViolation<T>> errorMsg = validatorFactory.getValidator().validate(object); if (!CollectionUtils.isEmpty(errorMsg)) { String failMsg = "第" + (ri + 1) + "行" + errorMsg.stream().map(ConstraintViolation::getMessage).collect(Collectors.joining(",")); throw new RuntimeException(failMsg); } result.add(object); } Field.setAccessible(fields, false); } return result; } /** * 读取单元格数据 可以根据需要补充其他类型与java类型的映射 * @param cell * @param field * @return */ private static Object getCellValue(Cell cell, Field field) { if(cell==null){ return null; } CellType type = cell.getCellTypeEnum(); if (CellType.BLANK == type) { return null; } else if (CellType.BOOLEAN == type && field.getType().equals(Boolean.class)) { return cell.getBooleanCellValue(); } else if (CellType.ERROR == type) { return null; } else if (CellType.NUMERIC == type || CellType.FORMULA == type) { if (field.getType().equals(Integer.class)) { return (int) cell.getNumericCellValue(); } else if (field.getType().equals(Long.class)) { return (long) cell.getNumericCellValue(); } else if (field.getType().equals(Double.class)) { return cell.getNumericCellValue(); } } else if (CellType.STRING == type && field.getType().equals(String.class)) { return cell.getStringCellValue(); } return null; }
调用时需要传excel对应的iNputstream和解析的实体类Class即可拿到对应的实体List;
如有侵权请联系删除!!!!!!!