需要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<>();
@Override
public void invoke(T t, AnalysisContext analysisContext) {
//解析行
dataList.add(t);
if (dataList.size() == threshold) {
consumer.accept(dataList);
dataList.clear();
}
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
//解析头
log.info("表格头信息:【{}】", headMap);
//获取表格头数据
if (StrUtil.isBlank(DATASOURCE)) {
//将表格标题放入缓存,可自行定义其他头数据缓存,缓存数据结构自定义即可
DATASOURCE = headMap.get(0);
}
}
@Override
public 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);
// 读取excel
List<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));
};
}