java通用excel读取

• 只需要传入excel和对应的实体类即可实现自动解析

只需要传入excel和对应的实体类即可实现自动解析

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；

如有侵权请联系删除！！！！！！！