【EasyExcel使用两个Java对象来接受一个excel文件】

需求背景：

有时候上传文件想要写一个通用的逻辑，就是说，这个excel前面几个字段是基础字段，后面几个字段是定制字段。

那么为了以后上传不同的文件，就需要编写不同的listener去解析每种不同的excel文件，但是由于基础属性的处理过于复杂，担心别人搞坏。因此将excel文件进行拆分，使用两个listener来接收处理一个excel文件。

2. 目标是使用两个监听器，两个实体类来接收excel文件

一，定义基础属性的listener

@Slf4j
@EqualsAndHashCode(callSuper = true)
@RequiredArgsConstructor
public class CustomerProductUploadListener extends AbsExcelListener readCellData = cellDataMap.get(i);
            Object value = null;
            if (Objects.nonNull(readCellData)) {
                switch (readCellData.getType()) {
                    case STRING:
                        value = readCellData.getStringValue();
                        break;
                    case BOOLEAN:
                        value = readCellData.getBooleanValue();
                        break;
                    case NUMBER:
                        value = readCellData.getNumberValue();
                        break;
                    case DATE:
                        value = readCellData.getDataFormatData().getFormat();
                        break;
                    default:
                        break;
                }
            }
            map.put(CollectionUtils.firstElement(head.getHeadNameList()), value);
        }
        if (StringUtils.isNotBlank(s)) {
            map.put("错误消息", s);
        }
        if (CollectionUtils.isEmpty(fileHeadList)) {
            fileHeadList.addAll(headList);
            fileHeadList.add("错误消息");
        }
        return map;
    }

	// 将map中的拓展属性 读取到 拓展监听器所对应的实体类上，并且返回一个实体类对象
    public E readExt(Map errList,  HttpServletResponse response) {
        return uploadFile0(errList, response, "上传结果");
    }
    @SneakyThrows
    default String uploadFile0(List errList, HttpServletResponse response, String filePre) {
        String randName = filePre + UUID.fastUUID() + ".xlsx";
        String fileName = URLEncoder.encode(randName, "utf-8");
        response.addHeader("Content-Disposition", "filename=" + fileName);
        response.setContentType("application/octet-stream");
        FileItemFactory factory = new DiskFileItemFactory(16, null);
        FileItem fileItem = factory.createItem("textField", "text/plain", true, fileName);
        OutputStream os = fileItem.getOutputStream();
        ExcelWriter excelWriter = EasyExcelFactory.write(os, getGenericClass()).build();
        WriteSheet writeSheet = EasyExcelFactory.writerSheet("Sheet1").build();
        excelWriter.write(errList, writeSheet);
        excelWriter.finish();
        os.close();
        MultipartFile multipartFile = new CommonsMultipartFile(fileItem);
        MinioUtil minioUtil = getBean(MinioUtil.class);
        minioUtil.putObject(multipartFile, minioUtil.getExceptionPath() + multipartFile.getOriginalFilename());
        return multipartFile.getOriginalFilename();
    }
    @SneakyThrows
    default String uploadFile0(List errList, List head,  HttpServletResponse response, String filePre) {
        if (CollectionUtils.isEmpty(head)){
            throw new ManagedServiceException("excel head not allowed empty!");
        }
        List sheetData = new ArrayList();
        if (!CollectionUtils.isEmpty(errList)){
            for (Map stringObjectMap : errList) {
                List dl = new ArrayList();
                for (String hk : head) {
                    Object vv = stringObjectMap.get(hk);
                    dl.add(vv);
                }
                sheetData.add(dl);
            }
        }
        List heads = new ArrayList();
        for (String h : head) {
            heads.add(Lists.newArrayList(h)) ;
        }
        String randName = filePre + UUID.fastUUID() + ".xlsx";
        String fileName = URLEncoder.encode(randName, "utf-8");
        response.addHeader("Content-Disposition", "filename=" + fileName);
        response.setContentType("application/octet-stream");
        FileItemFactory factory = new DiskFileItemFactory(16, null);
        FileItem fileItem = factory.createItem("textField", "text/plain", true, fileName);
        OutputStream os = fileItem.getOutputStream();
        EasyExcelFactory.write(os)
                .head(heads)
                .needHead(true)
                .sheet("Sheet1")
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .doWrite(sheetData);
        os.close();
        MultipartFile multipartFile = new CommonsMultipartFile(fileItem);
        MinioUtil minioUtil = getBean(MinioUtil.class);
        minioUtil.putObject(multipartFile, minioUtil.getExceptionPath() + multipartFile.getOriginalFilename());
        return multipartFile.getOriginalFilename();
    }
    default Class getGenericClass() {
        Type type = getClass().getGenericSuperclass();
        if (type instanceof ParameterizedType) {
            ParameterizedType parameterizedType = (ParameterizedType) type;
            return (Class) parameterizedType.getActualTypeArguments()[0];
        } else {
            throw new IllegalArgumentException("The generic superclass is not a parameterized type.");
        }
    }
}

六、自己封装通用功能的excelListener抽象类

@EqualsAndHashCode(callSuper = true)
@Data @Slf4j
public abstract class AbsExcelListener extends AnalysisEventListener implements BizListener, UploadListener {
    protected String errorFile;
    protected List successData = new ArrayList();
    protected List errorData = new ArrayList();
    private final List all = new ArrayList();
    @Setter
    private boolean independentTransactions = false;
    private static final AtomicReference validatorAtomicReference = new AtomicReference();
    @Override
    public Ret getRet() {
        return new Ret(errorData, successData, getErrorFile());
    }
    private static Validator validator() {
        if (Objects.isNull(validatorAtomicReference.get())) {
            validatorAtomicReference.set(ApplicationContextUtil.getBean(Validator.class));
        }
        return validatorAtomicReference.get();
    }
    public static  boolean validate(final T obj) {
        return validate(new ArrayList(), obj);
    }
    public static  boolean validate(final List errors, final T obj) {
        return validate(errors, new AtomicReference(), obj);
    }
    public static  boolean validate(final AtomicReference msgRef, final T obj) {
        return validate(new ArrayList(), msgRef, obj);
    }
    public static  boolean validate(final List errors, AtomicReference msgRef, final T obj) {
        if (Objects.isNull(obj)) {
            log.warn("当前传入的实体对象是Null");
            return false;
        }
        Set set = requireNonNull(validator()).validate(obj, Default.class);
        if (!CollectionUtils.isEmpty(set)) {
            StringJoiner msg = new StringJoiner(",");
            for (ConstraintViolation cv : set) {
                Field declaredField;
                try {
                    declaredField = obj.getClass().getDeclaredField(cv.getPropertyPath().toString());
                } catch (NoSuchFieldException e) {
                    throw new ExcelAnalysisException(e.getMessage());
                }
                String errMsg = getErrMsg(cv, declaredField);
                msg.add(errMsg);
            }
            String errMsg = msg.toString();
            if (obj instanceof ExcelDtoInstance) {
                ExcelDtoInstance excelDtoInstance = (ExcelDtoInstance) obj;
                excelDtoInstance.addErrorMsg(errMsg);
                errors.add(new ErrorData(errMsg, obj));
            } else {
                Method setErrorMessageMethod = findMethod(obj.getClass(), "setErrorMessage");
                if (Objects.isNull(setErrorMessageMethod)) {
                    log.error("当前实体[{}]没有 setErrorMessage 函数，跳过.", obj.getClass().getSimpleName());
                    msgRef.set(errMsg);
                } else {
                    invokeMethod(setErrorMessageMethod, obj, errMsg);
                }
                errors.add(new ErrorData(errMsg, obj));
            }
            return false;
        } else {
            return true;
        }
    }
    private static  String getErrMsg(ConstraintViolation cv, Field declaredField) {
        String errMsg = "";
        if (declaredField.isAnnotationPresent(ExcelProperty.class)) {
            ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            errMsg = errMsg + annotation.value()[0] + cv.getMessage();
        } else {
            errMsg = cv.getPropertyPath().toString() + cv.getMessage();
        }
        return errMsg;
    }
    Map buildHeadMap(Class headClazz,  AnalysisContext context) {
        val readSheetHolder = context.readSheetHolder();
        val excelReadHeadProperty = readSheetHolder.excelReadHeadProperty();
        val baseHeadMap = excelReadHeadProperty.getHeadMap();
        Map headMap = new TreeMap();
        initColumnProperties(context.currentReadHolder(), headClazz, headMap, baseHeadMap.size());
        return headMap;
    }
    private void initColumnProperties(ConfigurationHolder configurationHolder, Class headClazz, Map headMap, int pos) {
        if (headClazz == null) {
            return;
        }
        val fieldCache = ClassUtils.declaredFields(headClazz, configurationHolder);
        for (Map.Entry entry : fieldCache.getSortedFieldMap().entrySet()) {
            initOneColumnProperty(entry.getKey() + pos, entry.getValue(),
                    fieldCache.getIndexFieldMap().containsKey(entry.getKey()), headMap);
        }
    }
    private void initOneColumnProperty(int index, FieldWrapper field, Boolean forceIndex, Map headMap) {
        List tmpHeadList = new ArrayList();
        boolean notForceName = field.getHeads() == null || field.getHeads().length == 0
                || (field.getHeads().length == 1 && com.alibaba.excel.util.StringUtils.isEmpty(field.getHeads()[0]));
        if (headMap.containsKey(index)) {
            tmpHeadList.addAll(headMap.get(index).getHeadNameList());
        } else {
            if (notForceName) {
                tmpHeadList.add(field.getFieldName());
            } else {
                Collections.addAll(tmpHeadList, field.getHeads());
            }
        }
        Head head = new Head(index, field.getField(), field.getFieldName(), tmpHeadList, forceIndex, !notForceName);
        headMap.put(index, head);
    }
}

七、返回结果封装类

封装通用的返回结果

public interface BizListener {
    /**
     * 这些数据将会呗返回给前端、
     */
    T getRet();
    String getErrorFile();
    @Getter
    class Ret {
        private List errorData;
        private List successData;
        private final int total;
        private final int error;
        private final int success;
        private String errorFile;
        public Ret(List errorData, List successData, String errorFile) {
            Ret.this.errorData = CollectionUtils.isEmpty(errorData) ? Collections.emptyList() : errorData;
            Ret.this.successData = CollectionUtils.isEmpty(successData) ? Collections.emptyList() : successData;
            error = errorData.size();
            success = successData.size();
            total = error + success;
            Ret.this.errorFile = errorFile;
        }
    }
}

八、如何调用？

uploadListener 函数再父类中，请先继承父类，或者将父类的函数copy过来

    @ApiOperation(value = "批量导入")
    @PostMapping(path = {"/import"})
    public Result customerProductImport(@RequestParam("file") MultipartFile file) {
        return uploadListener(file, ProductUploadBaseDTO.class, new CustomerProductUploadListener(new ZTWBExtListener(ZTWBExtendInfoDTO.class)), false);
    }

controller的父类，封装了一些基础操作

@Slf4j
public class BaseAction {
    /**
     * 抽象 写出 excel 的函数
     *
     * @param runnable excel 写出代码块，同步调用
     * @param fileName 导出的文件名字
     */
    @SneakyThrows
    protected void writeExcel(Runnable runnable, String fileName) {
        HttpServletResponse response = ServletUtil.getInstance().currentResponse();
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            fileName = URLEncoder.encode(fileName + DateUtil.format(LocalDateTime.now(), PURE_DATETIME_MS_PATTERN), "UTF-8").replace("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            runnable.run();
        } catch (ManagedServiceException ex) {
         
        }
    }
    protected Result uploadListener(MultipartFile file, Class dtoClz, ReadListener listener) {
        return uploadListener(file, dtoClz, listener, true);
    }
    // BizException
    protected Result uploadListener(MultipartFile file, Class dtoClz, ReadListener listener, boolean useDefaultListener) {
        try {
            EasyExcelFactory.read(file.getInputStream(), dtoClz, listener)
                    .useDefaultListener(useDefaultListener)
                    .sheet().doRead();
            if (listener instanceof BizListener) {
                return data(200, ((BizListener) listener).getRet(), "");
            }
            return Result.success("");
        } catch (ManagedServiceException ex) {
            
        } catch (Exception ex) {
            
        }
    }
    protected Result uploadListener(MultipartFile file, ReadListener listener) {
        return uploadListener(file, listener, Boolean.FALSE);
    }
    protected Result uploadListener(MultipartFile file, ReadListener listener, boolean useDefaultListener) {
        try {
            EasyExcelFactory.read(file.getInputStream(), listener)
                    .useDefaultListener(useDefaultListener)
                    .sheet().doRead();
            if (listener instanceof BizListener) {
                return data(((BizListener) listener).getRet());
            }
            return Result.success(ResultCode.SUCCESS);
        } catch (ManagedServiceException ex) {
             
        } catch (Exception ex) {
            
        }
    }
    public static  PageRecords fromPage(IPage iPage) {
        PageRecords pageRecords = new PageRecords();
        pageRecords.setRows(iPage.getRecords());
        pageRecords.setTotal(iPage.getTotal());
        pageRecords.setTotalPage(iPage.getPages());
        pageRecords.setCode(HttpStatusEnum.CODE_200.getCode());
        pageRecords.setMsg(HttpStatusEnum.CODE_200.getMsg());
        return pageRecords;
    }
}