EasyExcel导入（含表头验证+空白行读取）

创建表头枚举类：

public enum InvokeHeadEnums {
    /**
     * 播种及产量数据表头
     */
    SOWING_YIELD_HEAD("SOWING_YIELD_HEAD", Arrays.asList("市*", "县（区）*", "乡/镇*", "耕地面积（亩）", "年小麦播种面积（亩）", "小麦总产量（吨）", "单位面积产量（吨/亩）", "时间(YYYY)*"));


    /**
     * 表头所属类型
     */
    private String headType;

    /**
     * 请求头内容
     */
    private List<String> headInfos;

    InvokeHeadEnums(String headType, List<String> headInfos) {
        this.headType = headType;
        this.headInfos = headInfos;
    }

    public String getHeadType() {
        return headType;
    }

    public List<String> getHeadInfos() {
        return headInfos;
    }


    /**
     * 根据表头类型获取字段信息
     *
     * @param headType 表头类型
     * @return 字段信息
     */
    public static List<String> getHeadInfos(String headType) {
        if (StringUtils.isBlank(headType)) {
            Asserts.fail("表头类型不能为空");
        }
        for (InvokeHeadEnums enums : values()) {
            if (headType.equals(enums.getHeadType())) {
                return enums.getHeadInfos();
            }
        }
        return null;
    }

}

继承AnalysisEventListener：

public class EasyExcelListener extends AnalysisEventListener<Object> {

    /**
     * 表头枚举
     */
    private String invokeHead;

    /**
     * 是否跳过空白行 true-跳过 false-不跳过
     */
    private boolean whetherSkipBlankLines = true;


    /**
     * 验证表头+跳过空白行
     *
     * @param invokeHead 表头枚举
     */
    public EasyExcelListener(String invokeHead) {
        this.invokeHead = invokeHead;
    }

    /**
     * 验证表头+是否跳过空白行
     *
     * @param invokeHead            表头枚举
     * @param whetherSkipBlankLines 是否跳过空白行
     */
    public EasyExcelListener(String invokeHead, boolean whetherSkipBlankLines) {
        this.whetherSkipBlankLines = whetherSkipBlankLines;
        this.invokeHead = invokeHead;
    }

    /**
     * 不验证表头
     */
    public EasyExcelListener() {
    }

    /**
     * 不验证表头+是否跳过空白行
     *
     * @param whetherSkipBlankLines 是否跳过空白行
     */
    public EasyExcelListener(boolean whetherSkipBlankLines) {
        this.whetherSkipBlankLines = whetherSkipBlankLines;
    }


    /**
     * 创建list集合封装最终的数据
     */
    List<Object> list = new ArrayList<>();

    /**
     * 读取excle内容
     *
     * @param t
     * @param analysisContext
     */
    @Override
    public void invoke(Object t, AnalysisContext analysisContext) {
        analysisContext.readWorkbookHolder().setIgnoreEmptyRow(false);

        // 验证空数据
        if (whetherSkipBlankLines && BeanUtils.checkObjAllFieldsIsNull(t)) {
            return;
        }
        // 将读取到的数据输出到控制台
        list.add(t);
    }

    /**
     * 读取excel表头信息
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        // 验证表头
        if (StringUtils.isNotBlank(invokeHead)) {
            List<String> headInfos = InvokeHeadEnums.getHeadInfos(invokeHead);
            if (headInfos.isEmpty()) {
                Asserts.fail("未查询到表头对应的字段信息");
            }

            if (headInfos.size() != headMap.size()) {
                Asserts.fail("模板不正确");
            }

            for (Integer index : headMap.keySet()) {
                if (!headInfos.get(index).equals(headMap.get(index))) {
                    Asserts.fail("模板不正确");
                }
            }
        }

    }

    /**
     * 读取完成后执行
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }

    /**
     * 获取表格内容
     *
     * @param obj 需要转化的实体
     * @param <T>
     * @return
     */
    public <T> List<T> getList(Class<T> obj) {
        String jsonObj = JSONUtil.toJsonStr(list);
        return JSONUtil.toList(jsonObj, obj);
    }

    @Override
    public boolean hasNext(AnalysisContext analysisContext) {
        if (RowTypeEnum.EMPTY.equals(analysisContext.readRowHolder().getRowType())) {
            doAfterAllAnalysed(analysisContext);
            return true;
        }
        return super.hasNext(analysisContext);
    }

}

获取导入行数据：

  @PostMapping("/upload")
    @ApiOperation("导入")
    public CommonResult<SowingYieldDataErrorDTO> upload(@ApiParam(name = "file", value = "file", required = true) @RequestParam(value = "file") MultipartFile file,
                                                        @ApiParam(name = "verifyRepeat", value = "是否验证重复 true-验证 false-不验证", required = true) @RequestParam(value = "verifyRepeat") boolean verifyRepeat) throws IOException {
        String originalFilename = file.getOriginalFilename();
        originalFilename = originalFilename.substring(originalFilename.indexOf(CommonSymbolConstant.POINT));
        if (!CommonSymbolConstant.EXCEL_SUFFIX.contains(originalFilename)) {
            Asserts.fail("文件格式错误！");
        }
        EasyExcelListener easyExcelListener = new EasyExcelListener(InvokeHeadEnums.SOWING_YIELD_HEAD.getHeadType());
        EasyExcelFactory.read(file.getInputStream(), SowingYieldDataUploadReq.class, easyExcelListener).sheet(0).doRead();
        List<SowingYieldDataUploadReq> list = easyExcelListener.getList(SowingYieldDataUploadReq.class);
        if (null == list || list.size() == 0) {
            Asserts.fail("不能导入空白数据");
        }
        // 数据验证
         for (SowingYieldDataUploadReq uploadReq : list) {
          
                if (BeanUtils.checkObjAllFieldsIsNull(uploadReq)) {
                    continue;
                }
                ValidatorUtils.validate(uploadReq);
         }     
        SowingYieldDataErrorDTO errorDTO = sowingYieldDataService.dealUploadData(list, verifyRepeat);
        return CommonResult.success(errorDTO);
    }

对象值判空工具类：

public class ObjectUtils {
    public static boolean checkObjAllFieldsIsNull(Object object) {
        if (null == object) {
            return true;
        }
        try {
            for (Field f : object.getClass().getDeclaredFields()) {
                f.setAccessible(true);
                if (f.get(object) != null && StringUtils.isNotBlank(f.get(object).toString())) {
                    return false;
                }
            }
        } catch (Exception e) {
            log.error("判空异常",e)
        }
        return true;
    }
}

ValidatorUtils ：可触发实体上的valida相关注解

@Component
public class ValidatorUtils {

    private static Validator validator;

    @Autowired
    public void setValidator(Validator validator) {
        ValidatorUtils.validator = validator;
    }

    public static <T> void validate(T entity) {
        Set<ConstraintViolation<T>> validate = validator.validate(entity, Default.class);
        if (CollUtil.isNotEmpty(validate)) {
            Asserts.fail(validate.stream().map(ConstraintViolation::getMessage).collect(Collectors.joining(";")));
        }
    }
}

实体举例：

@Data
@ApiModel(value = "播种及产量数据上传实体", description = "播种及产量数据")
public class SowingYieldDataUploadReq {

    @ApiModelProperty(value = "市名称")
    @ExcelProperty(value = "市", index = 0)
    @NotNull(message = "市不能为空")
    private String cityName;

    @ApiModelProperty(value = "县区名称")
    @ExcelProperty(value = "县（区）", index = 1)
    @NotNull(message = "县（区）不能为空")
    private String countiesName;

    @ApiModelProperty(value = "乡镇名称")
    @ExcelProperty(value = "乡/镇", index = 2)
    @NotNull(message = "乡/镇不能为空")
    private String villageTownName;

    @ApiModelProperty(value = "耕地面积（亩）")
    @ExcelProperty(value = "耕地面积（亩）", index = 3)
    @DecimalMin(value = "0", message = "耕地面积（亩）必须大于等于0")
    @DecimalMax(value = "9999999999.9999", message = "耕地面积（亩）必须小于等于9999999999.9999")
    private String agriculturalAcreage;

    @ApiModelProperty(value = "年小麦播种面积（亩）")
    @ExcelProperty(value = "年小麦播种面积（亩）", index = 4)
    @DecimalMin(value = "0", message = "年小麦播种面积（亩）必须大于等于0")
    @DecimalMax(value = "9999999999.9999", message = "年小麦播种面积（亩）必须小于等于9999999999.9999")
    private String annualWheatSownArea;

    @ApiModelProperty(value = "小麦总产量（吨）")
    @ExcelProperty(value = "小麦总产量（吨）", index = 5)
    @DecimalMin(value = "0", message = "小麦总产量（吨）必须大于等于0")
    @DecimalMax(value = "9999999999.9999", message = "小麦总产量（吨）必须小于等于9999999999.9999")
    private String totalWheatProduction;

    @ApiModelProperty(value = "单位面积产量（吨/亩）")
    @ExcelProperty(value = "单位面积产量（吨/亩）", index = 6)
    @DecimalMin(value = "0", message = "单位面积产量（吨/亩）必须大于等于0")
    @DecimalMax(value = "9999999999.9999", message = "单位面积产量（吨/亩）必须小于等于9999999999.9999")
    private String perUnitAreaYield;

    @ApiModelProperty(value = "所属年份")
    @ExcelProperty(value = "时间", index = 7)
    @NotNull(message = "时间不能为空")
    @PositiveOrZero(message = "时间必须大于0")
    @DecimalMax(value = "9999", message = "时间必须小于等于9999")
    private String belongYear;

}