Easy Excel

EasyExcel导入文件

正常的数据直接导入，非正常数据错误原因返回给前端

EasyExcel官网

https://easyexcel.opensource.alibaba.com/docs/current/

代码

依赖

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
</dependency>

controller

@PostMapping("/import")
public AjaxResult importData(MultipartFile file) throws Exception {
    List list = entityService.importData(file);
    return AjaxResult.success(list);
}

service

@Override
public List importData(MultipartFile file) throws IOException {
    EntityImportListener EntityImportListener = new EntityImportListener();
    EasyExcel.read(file.getInputStream(), EntityImportVo.class,EntityImportListener).sheet().doRead();
    return EntityImportListener.getErrorList();
}

listener

@Slf4j
public class EntityImportListener extends AnalysisEventListener<EntityImportVo> implements ExcelListener<EntityImportVo> {
	// spring管理的容器需要在类初始化时注入，不能直接使用注解导入
    private EntityMapper EntityMapper;
    /**
     * 保存实体对象list
     */
    private Set<Entity> set = new HashSet<>();

    /**
     * 保存失败数据集合信息，返回给前端
     */
    private List<String> errorList = new ArrayList<>();

    /**
     * 每100条保存一次
     */
    private static final Integer BATCH_COUNT = 100;

    /**
     * excel 表头数据
     */
    private Map<Integer, String> headMap;

    /**
     * 判断是否有校验问题
     */
    private Boolean flag = false;
    
    public P2pOssProdImportListener(){
        //通过spring工具类管理bean
        this.EntityMapper = SpringUtil.getBean(EntityMapper.class);
    }
    @Override
    public ExcelResult<EntityImportVo> getExcelResult() {
        return null;
    }
    //解析数据的方法
     @Override
    public void invoke(EntityImportVo EntityImportVo, AnalysisContext analysisContext) {
        //校验对象必填字段是否为空
        try {
            validate(EntityImportVo,analysisContext);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
        //没错误保存数据
        if(!flag){
            set.add(EntityImportVo);
            if(set.size() == BATCH_COUNT){
                saveData();
                set.clear();
            }
        }
    }
    private void validate(EntityImportVo EntityImportVo, AnalysisContext analysisContext) throws IllegalAccessException{
        Field[] fields = EntityImportVo.getClass().getDeclaredFields();
        Integer rowIndex = analysisContext.readRowHolder().getRowIndex();
        for(Field field : fields){
            field.setAccessible(true);
            boolean present = field.isAnnotationPresent(NotBlank.class);
            if(present){
                Object o = field.get(EntityImportVo);
                //字段值为空，添加信息到list
                if(ObjectUtils.isEmpty(o)){
                    //读取字段列
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    int index = annotation.index();
                    String msg = "第"+(rowIndex+1)+"行第"+(index+1)+"列数据不为空";
                    flag = true;
                    errorList.add(msg);
                }
            }
        }
        
        //非必填，判断转换
        if(StringUtils.isNotEmpty(EntityImportVo.getModeway())){
            String value1 = ModewayEnum.getValue(EntityImportVo.getModeway());
            if(StringUtils.isEmpty(value1)){
                String msg = "第"+(rowIndex+1)+"行接入方式不正确";
                flag = true;
                errorList.add(msg);
            }
        }
    }
    
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("全部解析完成");
        //最后一批数据入库
        saveData();
    }
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        this.headMap = headMap;
        log.info("解析到一条表头数据: {}", JSONUtil.toJsonStr(headMap));
    }

    private void saveData() {
        if(CollectionUtils.isNotEmpty(set)){
            entityMapper.insertByList(new ArrayList<>(set));
        }
    }
    
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        String errMsg = null;
        if (exception instanceof ExcelDataConvertException) {
            // 如果是某一个单元格的转换异常 能获取到具体行号
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            Integer rowIndex = excelDataConvertException.getRowIndex();
            Integer columnIndex = excelDataConvertException.getColumnIndex();
            errMsg = StrUtil.format("第{}行-第{}列-表头{}: 解析异常<br/>",
                    rowIndex + 1, columnIndex + 1, headMap.get(columnIndex));
            if (log.isDebugEnabled()) {
                log.error(errMsg);
            }
        }
        if (exception instanceof ConstraintViolationException) {
            ConstraintViolationException constraintViolationException = (ConstraintViolationException) exception;
            Set<ConstraintViolation<?>> constraintViolations = constraintViolationException.getConstraintViolations();
            String constraintViolationsMsg = constraintViolations.stream()
                    .map(ConstraintViolation::getMessage)
                    .collect(Collectors.joining(", "));
            errMsg = StrUtil.format("第{}行数据校验异常: {}", context.readRowHolder().getRowIndex() + 1, constraintViolationsMsg);
            if (log.isDebugEnabled()) {
                log.error(errMsg);
            }
        }
        if(StringUtils.isNotEmpty(errMsg)){
            errorList.add(errMsg);
        }
    }

    public  List getErrorList(){
        return errorList;
    }
}

实体

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class EntityImportVo {
    /**
     * value和index建议使用一种
     */
    @ExcelProperty(index = 0)
    @NotBlank
    private String customerName;

    @ExcelProperty(index = 1)
    private String projectName;

    @ExcelProperty(index = 2)
    @NotBlank
    private String accessNumber;
}