springboot将excel导入到数据库并校验excel中的字段

用户接收excel的值

@Data
public class IntelligentRoadProtectionVO  {
    public static Integer index = 1;

    private Integer excelIndex;
    public IntelligentRoadProtectionVO() {
        excelIndex = index++;
    }
    @Excel(name = "发生日期")
    private Date occurrenceDate;
    /**
     * 校验并初始化导入数据
     * @param excels
     * @param exception
     */
    public static void checkAndInitExcelData(List<IntelligentRoadProtectionVO> excels, List<String> exception) {
        if (CollectionUtils.isEmpty(excels)) {
            return;
        }
        // 必填项校验
        requiredCheck(excels, exception);
    }
    /**
     * 必填项数据校验
     * @param excels
     * @param exception
     */
    private static void requiredCheck(List<IntelligentRoadProtectionVO> excels, List<String> exception) {
        excels.forEach(excel -> {
            if (StringUtils.isEmpty(excel.getOccurrenceDate())) {
                exception.add(MessageFormat.format("第[{0}]行发生时间为空或者时间格式不正确,正确的时间格式为(yyyy-MM-dd HH:mm:ss);", excel.getExcelIndex()));
            }
            if (StringUtils.isEmpty(excel.getParkingTime())) {
                exception.add(MessageFormat.format("第[{0}]行停车时间为空;", excel.getExcelIndex()));
            }
       
        });
    }
}

service 业务类

@SneakyThrows
    @Override
    public List<String> uploadExcel(MultipartFile file) {
        List<String> exception = new ArrayList<>();

        // 解析导入信息
        ImportParams params = new ImportParams();
        params.setKeyIndex(null);
        //从第一列开始
        params.setTitleRows(0);
        //从第三行开始
        params.setHeadRows(2);
        IntelligentRoadProtectionVO.index = 1;
        List<IntelligentRoadProtectionVO> excels = ExcelImportUtil.importExcel(file.getInputStream(), IntelligentRoadProtectionVO.class, params);
        if (CollectionUtils.isEmpty(excels)) {
            exception.add("请填写数据");
        }

        // 数据校验
        IntelligentRoadProtectionVO.checkAndInitExcelData(excels, exception);
        if (!CollectionUtils.isEmpty(exception)) {
            return exception;
        }
        IntelligentRoadProtection intelligentRoadProtection=new IntelligentRoadProtection();
        for(IntelligentRoadProtectionVO excelBO:excels){
            BeanUtils.copyProperties(excelBO, intelligentRoadProtection);
            String userName = UserUtil.getUser().getUserName();
            Long userId = UserUtil.getUser().getUserId();
            String roleName = intelligentRoadProtectionMapper.findRoleName(userId);
            intelligentRoadProtection.setCode(UUID.randomUUID().toString().replace("-",""));
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date occurrenceDate = excelBO.getOccurrenceDate();
            String format = formatter.format(occurrenceDate);
            intelligentRoadProtection.setOccurrenceDate(format);
            intelligentRoadProtectionMapper.insert(intelligentRoadProtection);
        }
        return exception;
    }

controller

public ResponseResult<List<String>> uploadExcel(@RequestParam("file") MultipartFile file) throws Exception {
        if (!org.springframework.util.StringUtils.isEmpty(file.getOriginalFilename()) && !file.getOriginalFilename().endsWith(".xls")) {
            throw new BaseBusinessException("请使用正确的模版");
        }
        return ResponseResult.ok(intelligentRoadProtectionService.uploadExcel(file));
    }