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

需求背景：
有时候上传文件想要写一个通用的逻辑，就是说，这个excel前面几个字段是基础字段，后面几个字段是定制字段。
那么为了以后上传不同的文件，就需要编写不同的listener去解析每种不同的excel文件，但是由于基础属性的处理过于复杂，担心别人搞坏。因此将excel文件进行拆分，使用两个listener来接收处理一个excel文件。
2. 目标是使用两个监听器，两个实体类来接收excel文件

一，定义基础属性的listener

@Slf4j
@EqualsAndHashCode(callSuper = true)
@RequiredArgsConstructor
public class CustomerProductUploadListener<E> extends AbsExcelListener<Map<Integer, ReadCellData<?>>> {

	// 处理拓展属性的监听器父类
    final AbsExtendListener<E> extListener;
    
    

类似普通的easyExcelListener，需要注意的是泛型

public interface AbsExtendListener<E>  {

    /**
     * @return 针对不同的项目，使用不同的extend clazz 来接收
     */
    Class<E> getExtClz();

    void invoke(E data, ProductUploadBaseDTO base ,  AnalysisContext context);

    void doAfterAllAnalysed(AnalysisContext context);
}

二、定义监听拓展属性的监听器

public class ZTWBExtListener implements AbsExtendListener<ZTWBExtendInfoDTO> {

    @Getter
    // 用于接收拓展属性的实体类
    final Class<ZTWBExtendInfoDTO> extClz;
    final MachineExtendInfoRepository machineExtendInfoRepository = ApplicationContextUtil.getBean(MachineExtendInfoRepository.class);

    public ZTWBExtListener(Class<ZTWBExtendInfoDTO> extClz) {
        this.extClz = extClz;
    }

    @Override
    public void invoke(ZTWBExtendInfoDTO data, ProductUploadBaseDTO machineInfo, AnalysisContext context) {
        // biz
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
// biz
    }

    private MachineExtendInfo buildExtend(String alias, String name, String content, Long machineId, String machineSn) {
      // biz
    }

}

三、基础属性监听器的改造

@SneakyThrows
    @Override
    public void invoke(Map<Integer, ReadCellData<?>> cellDataMap, AnalysisContext context) {

		// 将map中的数据读取到基础属性实体类上
        modelBuildEventListener.invoke(cellDataMap, context);
        ProductUploadBaseDTO model = (ProductUploadBaseDTO) context.readRowHolder().getCurrentRowAnalysisResult();
        // 将map中的属性读取到拓展属性实体类上
        E e1 = readExt(cellDataMap, context);

		// 校验
        boolean validate = validate(model);

        if (!validate) {
        	// 这里为什么要有？ 就是因为我们这里如果报错了是需要把错误文件写回到客户端的，cellData2Map 函数就是这个作用
        	// 不需要的话这段也可以删除了
            val errData = cellData2Map(cellDataMap, model.getErrorMessage(), context);
            errorData0.add(errData);
            return;
        }
		// 基础属性的业务逻辑处理
		// ..... biz 

        // 手动调用 拓展属性监听器，进行他自己的业务逻辑处理，三个参数分别是拓展属性实体类、技术属性实体类、easyExcel的上下文对象
        extListener.invoke(e1, model, context);
 

        // 其余的地方视业务需要添加自己的逻辑
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        try {
        	//call 拓展属性监听器的 函数
            extListener.doAfterAllAnalysed(context);
        } catch (Exception e) {
            throw new ExcelCommonException(e);
        }

		// 如果有验证不通过的属性，将文件写回客户端
        if (!CollectionUtils.isEmpty(errorData0)) {
            errorFile = uploadFile0(errorData0, fileHeadList, ServletUtil.getInstance().currentResponse(), "errFile-");
        }
    }

四、easyExcel 自身提供的函数封装、将map拆开读取、将对象合并

private Map<String, Object> cellData2Map(Map<Integer, ReadCellData<?>> cellDataMap, String s, AnalysisContext context) {
        Map<String, Object> map = new LinkedHashMap<>();
        Map<Integer, Head> baseHeadMap = context.readSheetHolder().excelReadHeadProperty().getHeadMap();
        Map<Integer, Head> extHeadMap = buildHeadMap(extListener.getExtClz(), context);


        List<String> headList = new ArrayList<>();

        for (int i = 0; i < cellDataMap.size(); i++) {
            Head head;
            if (i < baseHeadMap.size()) {
                head = baseHeadMap.get(i);
            } else {
                head = extHeadMap.get(i);
            }
            headList.add(CollectionUtils.firstElement(head.getHeadNameList()));
            ReadCellData<?> 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<Integer, ReadCellData<?>> cellDataMap, AnalysisContext context) throws
            NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException {
        E resultModel = extListener.getExtClz().getConstructor().newInstance();
        ReadSheetHolder readSheetHolder = context.readSheetHolder();

        Map<Integer, Head> headMap = buildHeadMap(extListener.getExtClz(), context);
        com.alibaba.excel.support.cglib.beans.BeanMap dataMap = BeanMapUtils.create(resultModel);
        for (Map.Entry<Integer, Head> entry : headMap.entrySet()) {
            Integer index = entry.getKey();
            Head head = entry.getValue();
            String fieldName = head.getFieldName();
            if (!cellDataMap.containsKey(index)) {
                continue;
            }
            ReadCellData<?> cellData = cellDataMap.get(index);
            Object value = ConverterUtils.convertToJavaObject(cellData, head.getField(),
                    ClassUtils.declaredExcelContentProperty(dataMap, readSheetHolder.excelReadHeadProperty().getHeadClazz(),
                            fieldName, readSheetHolder), readSheetHolder.converterMap(), context,
                    context.readRowHolder().getRowIndex(), index);
            if (value != null) {
                dataMap.put(fieldName, value);
            }
        }
        return resultModel;
    }

五、上传功能的接口

如果没有上传需要，可以不实现这个接口

public interface UploadListener {

    @SneakyThrows
    default String uploadFile0(List<?> 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<Map<String, Object>> errList, List<String> head,  HttpServletResponse response, String filePre) {
        if (CollectionUtils.isEmpty(head)){
            throw new ManagedServiceException("excel head not allowed empty!");
        }
        List<List<Object>> sheetData = new ArrayList<>();
        if (!CollectionUtils.isEmpty(errList)){
            for (Map<String, Object> stringObjectMap : errList) {
                List<Object> dl = new ArrayList<>();
                for (String hk : head) {
                    Object vv = stringObjectMap.get(hk);
                    dl.add(vv);
                }
                sheetData.add(dl);
            }
        }
        List<List<String>> 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<T> extends AnalysisEventListener<T> implements BizListener<BizListener.Ret>, UploadListener {

    protected String errorFile;

    protected List<T> successData = new ArrayList<>();
    protected List<T> errorData = new ArrayList<>();

    private final List<T> all = new ArrayList<>();

    @Setter
    private boolean independentTransactions = false;
    private static final AtomicReference<Validator> 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 <T> boolean validate(final T obj) {
        return validate(new ArrayList<>(), obj);
    }

    public static <T> boolean validate(final List<ErrorData<T>> errors, final T obj) {
        return validate(errors, new AtomicReference<>(), obj);
    }

    public static <T> boolean validate(final AtomicReference<String> msgRef, final T obj) {
        return validate(new ArrayList<>(), msgRef, obj);
    }

    public static <T> boolean validate(final List<ErrorData<T>> errors, AtomicReference<String> msgRef, final T obj) {
        if (Objects.isNull(obj)) {
            log.warn("当前传入的实体对象是Null");
            return false;
        }
        Set<ConstraintViolation<T>> set = requireNonNull(validator()).validate(obj, Default.class);
        if (!CollectionUtils.isEmpty(set)) {
            StringJoiner msg = new StringJoiner(",");
            for (ConstraintViolation<T> 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 <T> String getErrMsg(ConstraintViolation<T> 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<Integer, Head> buildHeadMap(Class<?> headClazz,  AnalysisContext context) {
        val readSheetHolder = context.readSheetHolder();
        val excelReadHeadProperty = readSheetHolder.excelReadHeadProperty();
        val baseHeadMap = excelReadHeadProperty.getHeadMap();
        Map<Integer, Head> headMap = new TreeMap<>();
        initColumnProperties(context.currentReadHolder(), headClazz, headMap, baseHeadMap.size());
        return headMap;
    }

    private void initColumnProperties(ConfigurationHolder configurationHolder, Class<?> headClazz, Map<Integer, Head> headMap, int pos) {
        if (headClazz == null) {
            return;
        }
        val fieldCache = ClassUtils.declaredFields(headClazz, configurationHolder);

        for (Map.Entry<Integer, FieldWrapper> 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<Integer, Head> headMap) {

        List<String> 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> {

    /**
     * 这些数据将会呗返回给前端、
     */
    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 <T> PageRecords<T> fromPage(IPage<T> iPage) {
        PageRecords<T> 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;
    }


}