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

本文主要是介绍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*/@Overridepublic void invoke(Object t, AnalysisContext analysisContext) {analysisContext.readWorkbookHolder().setIgnoreEmptyRow(false);// 验证空数据if (whetherSkipBlankLines && BeanUtils.checkObjAllFieldsIsNull(t)) {return;}// 将读取到的数据输出到控制台list.add(t);}/*** 读取excel表头信息** @param headMap* @param context*/@Overridepublic 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*/@Overridepublic 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);}@Overridepublic 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;@Autowiredpublic 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;}

这篇关于EasyExcel导入（含表头验证+空白行读取）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---