easyExcel导入功能封装

jojo2311111

已于 2025-01-06 15:27:58 修改

阅读量344

点赞数 10

文章标签： java excel

于 2025-01-06 15:22:58 首次发布

本文链接：https://blog.youkuaiyun.com/qq_34267204/article/details/144960628

版权

这篇文章是总结项目中对easyExcel功能的封装，经过封装后，业务功能可以不用过多关注easyExcel导入细节，减少开发量，提升开发效率和质量

一、数据结构

ImportTaskDO

用于记录导入任务，包括状态，成功失败数量，文件地址，任务id等

CREATE TABLE `import_task_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '任务id',
  `import_file_name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '导入文件名称（系统自动添加时间戳）',
  `import_file_path` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '导入源文件路径',
  `error_file_path` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '导入失败文件路径',
  `import_type` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT '上传类型0追加导入1覆盖导入',
  `import_file_md5` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '文件校验md5值',
  `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '执行状态 0：未导入 1：导入中 2：导入完成 3:导入失败 4:保存导入文件异常',
  `handle_num` int(11) DEFAULT NULL COMMENT '该批次导入的总数',
  `success_num` int(11) DEFAULT NULL COMMENT '成功数',
  `fail_num` int(11) DEFAULT NULL COMMENT '失败数',
  `start_time` datetime DEFAULT NULL COMMENT '开始执行时间',
  `complete_time` datetime DEFAULT NULL COMMENT '执行结束时间',
  `error_msg` text COLLATE utf8_bin COMMENT '导入失败原因',
  `creator_id` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人ID',
  `modifier_id` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '更新人ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_delete` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT 'Y：已删除 N：正常',
  PRIMARY KEY (`id`),
  KEY `IDX` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=210936 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='数据导入任务表';

FileUploadDTO

用于记录存储文件，模板code，表格头，表头行数，导入人等，可以校验文件格式和表头

public class FileUploadDTO implements Serializable {
    /**
     *年份
     */
    private String date;

    /**
     * 导入类型
     * 1覆盖导入0.追加导入
     */
    private String type;

    /**
     * 文件
     */
    private MultipartFile file;

    /**
     * 表头
     */
    private List<String> headList;

    /**
     * 模板code
     */
    private String excelCode;
    /**
     * 用户信息
     */
    private UserInfoDTO userInfo;

    /**
     * 表头所在行
     */
    private int headRowNum;

    /**
     * 第一行数据所在行
     */
    private int firstDataRowNum;

    /**
     * excel名称
     */
    private String excelName;

    private Map requestData;
}

ExcelMessage

用于接收表头和表格数据，可以校验表格数据，获取成功失败数

public class ExcelMessage implements Serializable {
    //总记录数
    private int total;

    //每批大小
    private int batcheCount;

    //要处理的表格页
    private int sheetNo;

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

    //表格数据
    private List data;

    //表格数据校验
    private List<String> checkDataList;

    private String excelCode;

}

二、接口方法

CommonImportExcelManagement

该类为公共导入类，导入方法会进行一些文件校验，并调用easyExcel获取数据，数据获取后会回调dealDataService方法。service层继承该类，并实现dealDataService方法，只需要关注数据处理部分

另外该类也负责数据处理完后更新导入结果

public abstract class CommonImportExcelManagement implements ICommonImportExcelManagement {

    @Autowired
    private ImportTaskManagement importTaskManagement;

    @Autowired
    private Executor asyncServiceExecutor;

    @Value("${import.cache.path}")
    private String importTemplatePath;

    @Autowired
    private IOssManagement ossManagement;

    @Override
    public long importExcel(FileUploadDTO fileUploadDTO, Class<?> excelModeDTOClazz, Class<?> excelErrorModeDTOClazz, Consumer<FileUploadDTO> preCheckConsumer) {
        //填充excel信息
        importTaskManagement.fillExcelInfo(fileUploadDTO);
        //校验文件合法性
        this.checkFile(fileUploadDTO);
        //记录导入任务信息
        ImportTaskBO importTaskBO = importTaskManagement.addImportInfo(fileUploadDTO);
        ExcelMessage excelMessage = new ExcelMessage();
        excelMessage.setSheetNo(0);
        excelMessage.setBatcheCount(5000);
        //读取表头
        EasyExcelUtils.newStartReaderExcelHead(excelMessage, fileUploadDTO, excelModeDTOClazz);
        //校验表头
        importTaskManagement.checkTemplate(fileUploadDTO.getHeadList(), excelMessage.getHeadMap());
        //异步导入
        asyncServiceExecutor.execute(() -> EasyExcelUtils.newStartReader(excelMessage, fileUploadDTO, excelModeDTOClazz, new EasyExcelListener(this, excelMessage, fileUploadDTO, importTaskBO, excelErrorModeDTOClazz)));
        //返回任务id
        return importTaskBO.getId();
    }

    protected void checkFile(FileUploadDTO fileUploadDTO) {
        boolean flag = FileUtils.checkIsExcelFile(fileUploadDTO.getFile());
        if (!flag) {
            throw new BusinessException(BizErrorEnum.EXCEL_LAYOUT_ERROR);
        }
    }

    @Override
    public void generateErrorFile(ImportTaskBO importTaskBO, List errorList, String excelName, Class clazz) {
        SimpleDateFormat df = new SimpleDateFormat(DATETIME_PATTERN_DIGIT);
        String date = df.format(new Date());
        String fileName = excelName + "导入错误记录_" + date + ".xlsx";
        String filePath = importTemplatePath + fileName;
        //失败数据写入文件
        EasyExcelUtils.exportFile(errorList, filePath, clazz);
        importTaskBO.setImportFileName(fileName);
        try {
            OssResultDTO ossResultDTO = ossManagement.upload(fileName, new FileInputStream(filePath));
            importTaskBO.setErrorFilePath(ossResultDTO.getKey());
            org.apache.commons.io.FileUtils.deleteQuietly(new File(filePath));
        } catch (Exception e) {
            log.error("oss上传失败:" + e);
        }
    }

    @Override
    public int updateImportTask(ImportTaskBO importTaskBO) {
        return importTaskManagement.updateImportTask(importTaskBO);
    }

    /**
     * 数据处理
     *
     * @param excelMessage  excel表格数据
     * @param fileUploadDTO 文件信息
     * @param id            任务id
     * @return 成功失败记录
     */
    @Override
    public abstract DataModelDTO dealDataList(ExcelMessage excelMessage, FileUploadDTO fileUploadDTO, Long id);
}

EasyExcelListener

该类用于分批解析excel数据，可以避免内存溢出

另外service层处理完后，调用commonImportExcelManagement方法更新导入任务

@Component
public class EasyExcelListener<T extends ExcelModeDTO> extends AnalysisEventListener<T> {

    /**
     * 通用导入接口
     */
    private ICommonImportExcelManagement commonImportExcelManagement;
    /**
     * excel表格数据
     */
    private ExcelMessage excelMessage;
    /**
     * 文件信息
     */
    private FileUploadDTO fileUploadDTO;
    /**
     * 导入任务
     */
    private ImportTaskBO importTaskBO;
    /**
     * 异常数据实体类
     */
    private Class clazz;

    /**
     * excel所有数据
     */
    List list = new ArrayList<>();

    /**
     * excel正确数据
     */
    List rightList = new ArrayList();
    /**
     * excel错误数据
     */
    List errorList = new ArrayList();

    /**
     * 失败数据量
     */
    private int failNum = 0;

    /**
     * 处理数据量
     */
    private int handleNum = 0;

    private NewEasyExcelListener() {
    }

    public NewEasyExcelListener(ICommonImportExcelManagement commonImportExcelManagement, ExcelMessage excelMessage, FileUploadDTO fileUploadDTO, ImportTaskBO importTaskBO, Class clazz) {
        this.commonImportExcelManagement = commonImportExcelManagement;
        this.excelMessage = excelMessage;
        this.importTaskBO = importTaskBO;
        this.fileUploadDTO = fileUploadDTO;
        this.clazz = clazz;
    }

    @Override
    public void invoke(T excelModeDTO, AnalysisContext analysisContext) {
        if (excelModeDTO.modelIsNull()) {
            errorNum++;
        } else {
            if (errorNum <= 10) {
                errorNum = 0;
                list.add(excelModeDTO);
                if (list.size() >= excelMessage.getBatcheCount()) {
                    // 存储完成清理list
                    excelMessage.setData(list);
                    importTaskBO.setStatus(ImportTaskStatusEnum.IMPORTING.getCode());
                    this.execute();
                    list.clear();
                }
            }
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("数据解析完成");
        if (errorNum <= 10) {
            excelMessage.setData(list);
            this.execute();
            importTaskBO.setStatus(ImportTaskStatusEnum.IMPORT_COMPLETE.getCode());
            //生成错误文件
            commonImportExcelManagement.generateErrorFile(importTaskBO, errorList,fileUploadDTO.getExcelName(),clazz);
        } else {
            importTaskBO.setCompleteTime(new Date());
            importTaskBO.setHandleNum(0);
            importTaskBO.setSuccessNum(0);
            importTaskBO.setFailNum(0);
            importTaskBO.setStatus("3");
            importTaskBO.setErrorMsg("文件空行过多，请修正后导入!");
        }
        list.clear();
        commonImportExcelManagement.updateImportTask(importTaskBO);
    }

    private void execute() {
        DataModelDTO dataModel;
        try {
            dataModel = commonImportExcelManagement.dealDataList(excelMessage, fileUploadDTO, importTaskBO.getId());
        } catch (Exception ex) {
            log.error(fileUploadDTO.getExcelName() + "导入报错" + ex.getMessage(), ex);
            importTaskBO.setStatus(ImportTaskStatusEnum.IMPORT_FAILED.getCode());
            importTaskBO.setErrorMsg(ex.getMessage());
            commonImportExcelManagement.updateImportTask(importTaskBO);
            return;
        }
        if(!ObjectUtils.isEmpty(dataModel.getModeDTOS())){
            rightList.addAll(dataModel.getModeDTOS());
        }
        if (!ObjectUtils.isEmpty(dataModel.getModeErrorDTOS())) {
            errorList.addAll(dataModel.getModeErrorDTOS());
        }
        importTaskBO.setCompleteTime(new Date());
        this.successNum = this.successNum + dataModel.getModeDTOS().size();
        importTaskBO.setSuccessNum(successNum);
        this.failNum = this.failNum + dataModel.getModeErrorDTOS().size();
        importTaskBO.setFailNum(failNum);
        this.handleNum = this.failNum + this.successNum;
        importTaskBO.setHandleNum(handleNum);
        importTaskBO.setModifierId(fileUploadDTO.getLoginId());
        commonImportExcelManagement.updateImportTask(importTaskBO);
    }

    public List getRightList(){
       return rightList;
    }
}

EasyExcelUtil

该类封装了一些EasyExcel的操作

public class EasyExcelUtils {

    /**
     * 读取excel数据
     *
     * @param excelMessage         excel表格数据
     * @param fileUploadDTO        文件信息
     * @param modeDTOClazz         excel实体类
     * @param easyExcelListener    excel数据解析类
     */
    @SneakyThrows
    public static void newStartReader(ExcelMessage excelMessage, FileUploadDTO fileUploadDTO, Class modeDTOClazz, EasyExcelListener easyExcelListener) {
        ExcelReader excelReader =
                EasyExcelFactory.read(fileUploadDTO.getFile().getInputStream(), modeDTOClazz, newEasyExcelListener)
                        .build();
        ReadSheet readSheet = EasyExcelFactory.readSheet(excelMessage.getSheetNo()).headRowNumber(fileUploadDTO.getFirstDataRowNum()).build();
        excelReader.read(readSheet);
        excelReader.finish();
    }

    /**
     * 读取表头
     */
    @SneakyThrows
    public static ExcelMessage newStartReaderExcelHead(ExcelMessage excelMessage, FileUploadDTO fileUploadDTO, Class head) {
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcelFactory.read(fileUploadDTO.getFile().getInputStream(), head, new EasyExcelHeadListener(excelMessage, fileUploadDTO.getHeadRowNum())).build();
            ReadSheet readSheet = EasyExcelFactory.readSheet(excelMessage.getSheetNo()).headRowNumber(fileUploadDTO.getFirstDataRowNum()).build();
            excelReader.read(readSheet);
        } catch (BizException e) {
            if (!Objects.equals(e.getErrorCode(), BizErrorEnum.HEAD_READ_FINISH.getCode())) {
                throw e;
            }
        } finally {
            if (excelReader != null) {
                excelReader.finish();
            }
        }
        return excelMessage;
    }

}

三、Service层使用示例

@Service
public class IncomeImportService extends CommonImportExcelManagement implements ImportService {

    @Override
    public long importExcel(ImportDTO importDTO) {
        String loginId = importDTO.getUserInfo().getLoginId();

        FileUploadDTO fileUploadDTO = new FileUploadDTO();
        fileUploadDTO.setFile(importDTO.getFile());
        fileUploadDTO.setLoginId(loginId);
        fileUploadDTO.setType(importDTO.getType());
        fileUploadDTO.setUserInfo(importDTO.getUserInfo());
        fileUploadDTO.setHeadRowNum(0);
        fileUploadDTO.setFirstDataRowNum(1);
        fileUploadDTO.setExcelCode(ExcelCodeConstants.INCOME_GENERATION);
        return super.importExcel(fileUploadDTO, IncomeExcelModeDTO.class, IncomeExcelModeErrorDTO.class, null);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public DataModelDTO dealDataList(ExcelMessage excelMessage, FileUploadDTO fileUploadDTO, Long id) {
        List<IncomeExcelModeDTO> data = excelMessage.getData();
        // 校验表格数据
        DataModelDTO dataModelDTO = this.checkData(data, fileUploadDTO.getUserInfo());
        List<IncomeExcelModeDTO> modeDTOS = dataModelDTO.getModeDTOS();
        if (CollectionUtils.isEmpty(modeDTOS)) {
            return dataModelDTO;
        }

        ...
        //业务处理
        ...

        return dataModelDTO;
    }

}

@Getter
@Setter
public class IncomeExcelModeDTO {
    @ExcelProperty(index = 0)
    private String busiMonth;
    @ExcelProperty(index = 1)
    private String branchId;
    @ExcelProperty(index = 2)
    private String branchName;
    @ExcelProperty(index = 3)
    private String businessType;
    @ExcelProperty(index = 4)
    private String inInstBusinessIncome;
    @ExcelProperty(index = 5)
    private String incomeType;
    @ExcelProperty(index = 6)
    @NotNullField("客户号)
    private String clientNo;
    @ExcelProperty(index = 7)
    @NotNullField("客户名称")
    private String clientName;
    @ExcelProperty(index = 8)
    @NotNullField("项目号")
    private String projectNo;
    @ExcelProperty(index = 9)
    @NotNullField("项目名称")
    private String projectName;
    @ExcelProperty(index = 10)
    @NumberFormat(value="#.########")
    @NumberNotNullField(value = "金额", scale = "2")
    private String amount;
    @ExcelProperty(index = 11)
    private String remark;
}