文章

高效 Excel 导入实践

Posted 2024-07-9 Updated 2024-07- 13
By Eric
36~46 min read

本文会从 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 导入方案就已经完成，更多高级的功能还需要结合项目的实际情况进行设计和落地实施。

后端
Excel 高性能 大数据量
License:  CC BY 4.0