文章

高效 Excel 导入方案总结

本文会从 Excel 导入场景进行讲解我们常碰到的问题、解决思路以及项目实战,如果嫌弃太长,可以直接跳过,直接看最后的实战环节。

Excel 导入

上来直接一把嗦的做法:

image-20240702165924468

可能碰到的问题:

 - 读取 Excel 数据量非常大时,可能出现内存溢出
 - 一条条插入数据库,效率低,导致执行时间很长
 - 检验数据效率低,比如:判断数据是否数据库中存在,如果存在则跳过或者报错等

设计要点:

 - 选择一个高性能、内存管理良好的 Excel 解析框架
 - 异步多线程分批读取数据(optional)
 - 批量插入到数据库
 - 合理的异常设计

优化后的方案:

image-20240702174955254

如果选用的框架读性能好,也可以不采用异步分批读取,比如:EasyExcel 框架的 SAX(Simple API for XML)解析模式,虽然SAX解析是一种顺序读取数据的方式,但它的内存效率和性能表现都非常好,特别适合处理大数据量的Excel文件。如果需要异步处理,可以在监听器中结合多线程或异步框架进行数据处理。

EasyExcel 读取 Excel 数据的原理:

  1. 基于SAX解析

    • EasyExcel使用SAX模式读取Excel文件,这种模式是逐行解析和处理数据,而不是一次性将整个文件加载到内存中。这样可以显著降低内存使用,避免内存溢出的问题。

    • SAX模式的读取方式适用于处理大文件,因为它不会将整个文件加载到内存,而是逐行解析处理,每行数据处理完后立即释放内存。

  2. 事件驱动

    • SAX解析基于事件驱动,当解析器遇到特定的元素(如行、单元格)时,会触发相应的事件(如startElement、endElement),然后调用相应的处理方法。

    • 在EasyExcel中,这些事件驱动的回调由用户实现的监听器(Listener)处理,通过回调方法来处理解析到的数据。

需求升级

到这里我们了解了 Excel 导入的基本诉求和解决方案,但是我们经常还会碰到如下需求:

 # 导入
 - 动态表头支持,比如:多语言场景下
 - 支持异常错误状态记录,方便断点续传或者重新导入失败的批次(结合实际情况选择方案:1)中断导入  2)继续导入)
 ​
 # 通用
 - 如果涉及超大数据量的导入,可采用异步方案,用户可以查看任务的状态以及错误信息

最佳实践

使用 EasyExcel 进行 Excel 的导入,以下是官方的说明:

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。 easyexcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用easyexcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便

代码设计要点(✅ 已完成 ❌ 待改进):

  1. 选用 EasyExcel 解析 Excel 数据,读取性能较好,且内存消耗小(✅)

  2. 泛型支持不同的业务数据导入,Excel Cell 的内容和 Java 对象自动转换(✅)

  3. 批量插入数据库(✅)

  4. 动态表头支持,通过 ExcelProperty Index 设置来避免动态表头带来的无法解析问题(✅)

  5. 异常错误记录,后续失败批次重新导入或者断点续传(❌ 目前只是内容中统计错误,没有进行数据库或者文件的存储,后续有空再进行完善)

代码实现

导入核心代码:

 /**
 * 调用入口 
 */
 @Override
 public ExcelResult batchImport(InputStream excelFileStream) {
     ExcelImportParam<ExcelImportDemoDTO> excelImportParam = ExcelImportParam.<ExcelImportDemoDTO>builder()
             .dataInputStream(excelFileStream)
             .dataClass(ExcelImportDemoDTO.class)
             .batchSize(1000)
             .dataImportConsumer(dataList -> {
                 excelImportDemoCmdExecutor.batchImport(dataList);
 //                    throw new RuntimeException("test throw exception when process data");
             })
             .build();
     ExcelResult excelResult = excelImporter.readXlsx(excelImportParam);
     log.info("upload excel result: {}", excelResult);
     return excelResult;
 }
 ​
 /**
 * 读取 Excel 中数据
 **/
 public ExcelResult readXlsx(ExcelImportParam<T> excelImportParam) {
         BatchReadListener<T> listener = new BatchReadListener<>(excelImportParam.getBatchSize(), excelImportParam.getConsumer());
 ​
         EasyExcel.read(excelImportParam.getDataInputStream(), excelImportParam.getDataClass(), listener)
                 .excelType(ExcelTypeEnum.XLSX)
                 .ignoreEmptyRow(true)
                 .sheet(excelImportParam.getSheetNumber())
                 .headRowNumber(excelImportParam.getHeadRowNumber())
                 .doRead();
         return listener.getExcelResult();
     }
 @Getter
 @Slf4j
 public class BatchReadListener<T extends BaseExcelRowData> implements ReadListener<T> {
 ​
     private int batchSize;
     private List<T> cachedDataList;
     private Consumer<List<T>> dataImportConsumer;
     private ExcelResult excelResult = new ExcelResult(); // 记录 excel 导入结果
 ​
     public BatchReadListener(int batchSize, Consumer<List<T>> dataImportConsumer) {
         this.batchSize = batchSize;
         this.cachedDataList = ListUtils.newArrayListWithExpectedSize(batchSize);
         this.dataImportConsumer = dataImportConsumer;
     }
 ​
     @Override
     public void onException(Exception exception, AnalysisContext context) {
         super.onException(exception, context);
     }
 ​
     @Override
     public void invoke(T data, AnalysisContext context) {
         this.cachedDataList.add(data);
         // 达到 BATCH_COUNT 了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
         if (this.cachedDataList.size() >= this.batchSize) {
             this.processData();
         }
     }
 ​
     @Override
     public void doAfterAllAnalysed(AnalysisContext context) {
         if (CollectionUtils.isNotEmpty(this.cachedDataList)) {
             this.processData();
         }
     }
 ​
     private void processData() {
         try {
             this.dataImportConsumer.accept(this.cachedDataList);
         } catch (Exception exception) {
             this.excelResult.recordErrorMessage(exception.getMessage());
             log.info("[BatchReadListener] processData error:{}", exception.getMessage());
         } finally {
             // 统计总数
             this.excelResult.calculateTotalRow(this.cachedDataList.size());
             // 清空 cachedDataList
             this.cachedDataList = ListUtils.newArrayListWithExpectedSize(this.batchSize);
         }
     }
 }

数据库批量保存核心代码:

 @Component
 public class ExcelImportDemoCmdExecutor {
 ​
     private final ExcelImportDemoDOMapper excelImportDemoDOMapper;
 ​
     public ExcelImportDemoCmdExecutor(ExcelImportDemoDOMapper excelImportDemoDOMapper) {
         this.excelImportDemoDOMapper = excelImportDemoDOMapper;
     }
 ​
     public void batchImport(List<ExcelImportDemoDTO> excelImportDemoDTOS) {
         if (CollectionUtils.isNotEmpty(excelImportDemoDTOS)) {
             List<ExcelImportDemoDO> excelImportDemoDOS = excelImportDemoDTOS.stream()
                     .map(ExcelImportDemoDTOConverter.CONVERTER::toDataObject).collect(Collectors.toList());
             // 批量插入
             excelImportDemoDOMapper.batchInsert(excelImportDemoDOS);
 ​
             // 模拟一条条插入
 //            excelImportDemoDOS.forEach(excelImportDemoDOMapper::insert);
         }
     }
 }
 <!-- 批量插入脚本 -->
 <insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
   insert into excel_import_demo
   (id, description, name, age, birthday, deleted, create_by, create_time, update_by, 
     update_time, delete_time)
   values
   <foreach collection="list" item="item" separator=",">
     (#{item.id,jdbcType=BIGINT}, #{item.description,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, 
       #{item.age,jdbcType=INTEGER}, #{item.birthday,jdbcType=DATE}, #{item.deleted,jdbcType=BIT}, 
       #{item.createBy,jdbcType=VARCHAR}, #{item.createTime,jdbcType=TIMESTAMP}, #{item.updateBy,jdbcType=VARCHAR}, 
       #{item.updateTime,jdbcType=TIMESTAMP}, #{item.deleteTime,jdbcType=TIMESTAMP})
   </foreach>
 </insert>
 ​
 <!-- 单条插入脚本 -->
 <insert id="insert" parameterType="top.flyeric.basic.function.infrastructure.excel.dataobject.ExcelImportDemoDO" useGeneratedKeys="true" keyProperty="id">
   insert into excel_import_demo (id, description, name, 
     age, birthday, deleted, create_by, 
     create_time, update_by, update_time, 
     delete_time)
   values (#{id,jdbcType=BIGINT}, #{description,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, 
     #{age,jdbcType=INTEGER}, #{birthday,jdbcType=DATE}, #{deleted,jdbcType=BIT}, #{createBy,jdbcType=VARCHAR}, 
     #{createTime,jdbcType=TIMESTAMP}, #{updateBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP}, 
     #{deleteTime,jdbcType=TIMESTAMP})
 </insert>

快速生成 10w 条数据 Excel 文件,便于做导入测试:

 @Getter
 @Setter
 public class ExcelData {
     private String name;
     private String description;
     private Integer age;
     private LocalDate birthday;
 }
 ​
 public class ExcelGenerator {
 ​
     private static final int TOTAL_COUNT = 100000;
 ​
     public static void main(String[] args) {
         // 生成数据
         List<ExcelData> dataList = generateData(TOTAL_COUNT);
 ​
         // 写入到Excel文件
         String fileName = "test_large_dataset.xlsx";
         EasyExcel.write(fileName, ExcelData.class).sheet("Sheet1").doWrite(dataList);
 ​
         System.out.println(TOTAL_COUNT + " rows of data have been written to " + fileName);
     }
 ​
     private static List<ExcelData> generateData(int totalCount) {
         List<ExcelData> dataList = new ArrayList<>();
         for (int i = 1; i <= totalCount; i++) {
             ExcelData data = new ExcelData();
             data.setName("Name_" + i);
             data.setDescription("这是名称-" + i);
             data.setAge((int) (Math.random() * (65 - 18 + 1)) + 18); // 随机生成18到65之间的年龄
             data.setBirthday(randomBirthday()); // 随机生成生日
             dataList.add(data);
         }
         return dataList;
     }
 ​
     private static LocalDate randomBirthday() {
         long minDay = LocalDate.of(1958, 1, 1).toEpochDay();
         long maxDay = LocalDate.of(2005, 12, 31).toEpochDay();
         long randomDay = ThreadLocalRandom.current().nextLong(minDay, maxDay);
         return LocalDate.ofEpochDay(randomDay);
     }
 }

导入性能对比

说明:

本人限制了容器环境的资源,最多 2 核 CPU 和 4 GB 内存,同时安装了多个中间件,本次测试所使用 MySQL 性能较差。

批量插入性能:

 ## 第一次导入-每批次 100(28s):
 [http-nio-8081-exec-1] t.flyeric.common.webmvc.HttpLogAspect    用户: [null-null], 结束请求,请求地址: POST, 请求方法: /basic-function/tech/upload, 方法名: upload, costs: 28232 ms
 ​
 ## 第二次导入 - 每批次 500 (21s):
 [http-nio-8081-exec-1] t.flyeric.common.webmvc.HttpLogAspect    用户: [null-null], 结束请求,请求地址: POST, 请求方法: /basic-function/tech/upload, 方法名: upload, costs: 21311 ms
 ​
 ## 第三次导入 - 每批次 1000 (20s):
 [http-nio-8081-exec-1] t.flyeric.common.webmvc.HttpLogAspect    用户: [null-null], 结束请求,请求地址: POST, 请求方法: /basic-function/tech/upload, 方法名: upload, costs: 19953 ms

image-20240703190634667

image-20240703195058896

image-20240703195351873

单条一个个插入:

 ## 第一次导入(1130s ≈ 19分钟):
 [http-nio-8081-exec-1] t.flyeric.common.webmvc.HttpLogAspect    用户: [null-null], 结束请求,请求地址: POST, 请求方法: /basic-function/tech/upload, 方法名: upload, costs: 1130533 ms

image-20240703193929149

image-20240703194231246

到这里可以看到两种方案性能的直观对比,相差几十倍!!!

总结

选择一个合适的 Excel 框架,支持高性能和低内存消耗,同时批量将 Excel 数据保存到数据中,这样一个基本的 Excel 导入方案就已经完成,更多高级的功能还需要结合项目的实际情况进行设计和落地实施。

License:  CC BY 4.0