当前位置：首页 > news >正文

阿里巴巴 EasyExcel 真正的高效与通用导入（亿级数据秒级导入）

news 2025/8/14 2:22:11

需要lombok、hutool、alibaba EasyExcel 相关依赖包

基于Lamba表达式的通用监听器


import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.experimental.UtilityClass;
import lombok.extern.slf4j.Slf4j;import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;@Slf4j
@UtilityClass
public class ExcelListenerUtil {public static String DATASOURCE = null;/*** 创建监听器** @param consumer  消费者* @param threshold 批量写入数* @param <T>* @return*/public <T> AnalysisEventListener<T> getListener(Consumer<List<T>> consumer, int threshold) {return new AnalysisEventListener<>() {private final List<T> dataList = new LinkedList<>();@Overridepublic void invoke(T t, AnalysisContext analysisContext) {//解析行dataList.add(t);if (dataList.size() == threshold) {consumer.accept(dataList);dataList.clear();}}@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {//解析头log.info("表格头信息：【{}】", headMap);//获取表格头数据if (StrUtil.isBlank(DATASOURCE)) {//将表格标题放入缓存，可自行定义其他头数据缓存，缓存数据结构自定义即可DATASOURCE = headMap.get(0);}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {//结束解析if (dataList.size() > 0) {consumer.accept(dataList);}//清空内存的缓存数据ExcelListenerUtil.DATASOURCE = null;}};}
}

导入逻辑

			// 创建临时文件tempFile = FileUtil.writeBytes(file.getBytes(), FileUtil.file(FileUtil.getTmpDir() +FileUtil.FILE_SEPARATOR + "xxxx.xlsx"));// 自定义批次号String batchId = generateBatchId();// 分批导入监听器（提速1）AnalysisEventListener<QhHistoryImportParam> listener = ExcelListenerUtil.getListener(this.batchInsert(batchId), 100);// 读取excelList<QhHistoryImportParam> qhHistoryExcels = EasyExcel.read(tempFile, QhHistoryImportParam.class, listener).sheet().headRowNumber(2) //第3行开始读取数据//异步解析（提速2）.doReadSync();

入库逻辑

private Consumer<List<QhHistoryImportParam>> batchInsert(String batchId) {log.info("消费者");return excelData -> {//自定义的数据库实体List<QhHistory> qhHistories = new ArrayList<>();excelData.forEach(q -> {QhHistory qhHistory = new QhHistory();BeanUtil.copyProperties(q, qhHistory);qhHistory.setContractName(FutureProducts.containsAndGet(q.getContractCode()));qhHistory.setBatchId(batchId);qhHistory.setDatesource(ExcelListenerUtil.DATASOURCE);qhHistories.add(qhHistory);});//异步保存（提速3）CompletableFuture.runAsync(() -> this.saveBatch(qhHistories));};}