文章

高效 Excel 列表和模板导出实践

见字如面,与大家分享实践中的经验与思考。

上一篇文章已经讲完 Excel 的导入,本文将会从 Excel 导出场景讲解常碰到的问题、解决思路以及项目实战,如果嫌弃太长,可以直接跳过,直接看最后的实战环节。

Excel 导出

上来直接一把嗦的做法:

image-20240702172620420

可能出现的问题:

- 查询数据时,不管数据量大小直接 select * from table,数据库执行时间长,而且大量数据加载到内存导致OOM
- 一行行导出到 Excel 中,频繁 IO,效率低
- 非常大数据量时,都写在一个 Sheet 中,效率低,文件打开都会卡很久

设计要点:

- 选择一个高性能、内存管理良好的 Excel 解析框架
- 分批查询数据库数据
- 如果数据量非常的大,数据写到不同的 Sheet 中,比如:一个 Sheet 50W 条数据
- 分批将数据库中查询的数据导入到 Excel 中,而不是一条条插入
- 合理的异常设计

优化后的方案:

image-20240702175059932

需求升级

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

# 导出
- 按照复杂的 Excel 模板导出,比如:业务人员制定复杂的 Excel 模板,既包含详情数据,又包含行数据,并且在不同的 Sheet 页中,需要按照模板进行导出
- Excel 文件中的字段名、表头以及能够支持动态替换,比如:多语言场景下
​
# 通用
- 如果涉及超大数据量的导出,需要采用异步方案,用户可以查看任务的状态以及下载导出的 Excel

EasyExcel 实战

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 导出:导出性能较好,且内存消耗小(✅)

  2. 泛型支持:便于不同业务数据的导出(✅)

  3. 分页查询:通过 LIMITOFFSET 实现分页查询(✅)

  4. 批次处理:循环查询,每次查询 limit 条记录,直到没有更多记录(✅)

  5. 批量数据保存到 Excel:每次查询结果后,批量保存到 Excel(✅)

  6. 基于模板导出(✅)

  7. 可变表头处理:标题国际化等(✅)

  8. 异常状态记录(❌ 待改进)

  9. 巨量数据导出,使用异步方式,并通过数据库表记录状态,Excel 文件上传到文件存储服务,后续用户进行下载(❌ 待改进)

Excel导出核心代码:

@Component
@NoArgsConstructor
public class ExcelExporter<T> {
  private static final Integer EXCEL_MAX_ROW = 1048570;
​
  private final TransmittableThreadLocal<ExcelResult> resultContextHolder = new TransmittableThreadLocal<>();
​
  public ExcelResult batchExport(ExcelExportParam<T> exportParam) {
      initContext(exportParam);
      return exportExcel(exportParam);
  }
​
  private ExcelResult exportExcel(ExcelExportParam<T> exportParam) {
    ExcelResult excelResult = resultContextHolder.get();
    try (ExcelWriter writer = EasyExcel.write(exportParam.getDataOutputStream(), exportParam.getDataClass())
            .head(exportParam.getHeaders()).build()) {
        WriteSheet sheet = EasyExcel.writerSheet(exportParam.getSheetNumber()).build();
        DataLoadParam dataLoadParam = new DataLoadParam(exportParam.getBizParams());
​
        while (true) {
            List<T> dataList = exportParam.getDataLoadFunc().apply(dataLoadParam);
​
            if (CollectionUtils.isEmpty(dataList)) {
                break;
            }
            if ((excelResult.getTotalRowCount() + dataList.size()) > EXCEL_MAX_ROW) {
                break;
            }
​
            writer.write(dataList, sheet);
​
            dataLoadParam.setCurrentPage(dataLoadParam.getCurrentPage() + 1);
            excelResult.calculateTotalRow(dataList.size());
        }
    }
    return excelResult;
  }
​
  private void initContext(ExcelExportParam<T> exportParam) {
      resultContextHolder.set(new ExcelResult());
  }
​
}
​

DB 分批查询核心代码:

直接使用之前公众号文章,通过Excel导入的 10w+ 数据进行导出

// application service
@Override
public ExcelResult batchExport(OutputStream outputStream) {
  int batchSize = 10000;
​
  // 如果没有多语言场景直接使用 Java 对象中的 ExcelProperty 中定义的 value
  List<List<String>> headers = ListUtils.newArrayList();
  headers.add(Arrays.asList("名称-Name"));
  headers.add(Arrays.asList("描述-Description"));
  headers.add(Arrays.asList("年龄-Age"));
  headers.add(Arrays.asList("生日-Birthday"));
​
  ExcelExportParam<ExcelImportDemoDTO> excelExportParam = ExcelExportParam.<ExcelImportDemoDTO>builder()
          .batchSize(batchSize)
          .dataOutputStream(outputStream)
          .headers(headers)
          .dataLoadFunc((dataLoadParam) -> {
              return excelImportDemoQueryExecutor.pageSelect(dataLoadParam, batchSize);
              // 测试 select * 查询全部的表数据
//                    return excelImportDemoQueryExecutor.selectAll();
          })
          .build();
  ExcelResult excelResult = excelExporter.batchExport(excelExportParam);
  log.info("download excel result: {}", excelResult);
  return excelResult;
}
​
// ExcelImportDemoQueryExecutor
@Component
public class ExcelImportDemoQueryExecutor {
​
  private final ExcelImportDemoDOMapper excelImportDemoDOMapper;
​
  public ExcelImportDemoQueryExecutor(ExcelImportDemoDOMapper excelImportDemoDOMapper) {
      this.excelImportDemoDOMapper = excelImportDemoDOMapper;
  }
​
  public List<ExcelImportDemoDTO> pageSelect(DataLoadParam dataLoadParam, int batchSize) {
      ExcelImportDemoDOExample example = new ExcelImportDemoDOExample().page(dataLoadParam.getCurrentPage(), batchSize);
      example.setOrderByClause("id asc");
      // TODO: 通过DataLoadParam.bizParams 来拼接查询字段
      List<ExcelImportDemoDO> excelImportDemoDOS = excelImportDemoDOMapper.selectByExample(example);
​
      return excelImportDemoDOS.stream()
              .map(ExcelImportDemoDTOConverter.CONVERTER::dataObjectToDTO)
              .collect(Collectors.toList());
  }
​
  public List<ExcelImportDemoDTO> selectAll() {
      List<ExcelImportDemoDO> excelImportDemoDOS = excelImportDemoDOMapper.selectByExample(new ExcelImportDemoDOExample());
      return excelImportDemoDOS.stream()
              .map(ExcelImportDemoDTOConverter.CONVERTER::dataObjectToDTO)
              .collect(Collectors.toList());
  }
}

MyBatis XML代码:

<select id="selectByExample" parameterType="top.flyeric.basic.function.infrastructure.excel.dataobject.ExcelImportDemoDOExample" resultMap="BaseResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="Base_Column_List" />
    from excel_import_demo
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
    <if test="rows != null">
      <if test="offset != null">
        limit ${offset}, ${rows}
      </if>
      <if test="offset == null">
        limit ${rows}
      </if>
    </if>
  </select>

导出性能对比

说明:

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

第一次导出, batchSize = 1000,耗时 9.4s:

image-20240705113426081

点击下载 Excel 文件后:

image-20240705113858412

第二次导出,batchSize=5000,耗时 6.28s:

image-20240705114127966

第三次导出,batchSize=10000,耗时 5.8s:

image-20240705114617774

耗时已经非常接近了,不再调整 batchSize。

不使用分页,直接全量查询数据库,耗时 40s:

image-20240705115159428

可以看到耗时翻了将近 10倍,同时本地机器资源占用大,出现 CPU 和温度飙升的情况,10W 的测试数据本地还能抗住,在生产环境数据量更大大概率会出现 OOM 的情况

JXLS 实战

JXLS 是一个用于在 Java 应用程序中导出 Excel 文件的框架。它通过将模板文件与数据源结合使用,提供了一种简洁且灵活的方式来生成复杂的 Excel 报表。Jxls 使用 Apache POI 作为底层库来处理 Excel 文件,并允许使用 Excel 文件本身作为模板。

Jxls 的主要特性

  1. 基于模板:使用 Excel 文件作为模板,可以直观地设计报表格式。

  2. 灵活的数据映射:支持将 Java 对象映射到 Excel 单元格中,支持集合、列表、嵌套对象等复杂数据结构。

  3. 条件处理和表达式:支持在模板中使用条件处理和表达式,能够实现复杂的报表逻辑。

  4. 轻量级:Jxls 是一个轻量级的库,易于集成到现有的 Java 项目中。

核心代码:

// ExcelExporter 类
public void exportByTemplate(InputStream templateInputStream, Map<String, Object> data, OutputStream outputStream) throws IOException {
  // 1、创建临时 Excel 文件
  File tempExportFile = new File("temp.xlsx");
  // 2、使用 JXLS 进行模板替换
  JxlsPoiTemplateFillerBuilder.newInstance().withTemplate(templateInputStream).build().fill(data, new JxlsOutputFile(tempExportFile));
  // 3、将临时文件内容拷贝到 OutputStream 中,并且关闭流
  copyExcelStream(tempExportFile, outputStream);
  // 4、删除临时文件
  tempExportFile.delete();
}
​
private void copyExcelStream(File tempExportFile, OutputStream outputStream) throws IOException {
  try (InputStream ins = Files.newInputStream(tempExportFile.toPath())) {
      byte[] b = new byte[1024];
      int len;
      while ((len = ins.read(b)) > 0) {
          outputStream.write(b, 0, len);
      }
  } catch (IOException ioe) {
      ioe.printStackTrace();
  } finally {
      outputStream.close();
  }
}
​
​
// Application Service 类
@Override
public void exportTemplate(InputStream templateInputStream, OutputStream outputStream) throws IOException {
  // 构建业务单据数据,这里简单构建,实际情况需要从 DB 中获取
  Map<String, Object> details = new HashMap<>();
  details.put("orderNo", "Order-0001");
  details.put("status", "DONE");
  details.put("createTime", LocalDateTime.now());
  details.put("totalAmount", 1200.56);
  details.put("creatorName", "Eric");
​
  List<Map<String, Object>> orderItemsData = new ArrayList<>();
  for(int i = 1 ;i <= 100; i++){
      Map<String, Object> item = new HashMap<>();
      item.put("orderItemNo", "OrderItem-" + i);
      item.put("productName", "商品名称-" + i);
      item.put("productNo", "商品编号-" + i);
      item.put("createTime", LocalDateTime.now());
      orderItemsData.add(item);
  }
  details.put("items", orderItemsData);
​
  excelExporter.exportByTemplate(templateInputStream, details, outputStream);
}
​
// Controller 类
@GetMapping("/template")
public void exportTemplate(HttpServletResponse response,
                         MultipartFile templateFile,
                         @RequestParam(required = false, value = "exportName") String exportName) throws Exception {
  configExportFile(response, exportName);
  excelImportDemoService.exportTemplate(templateFile.getInputStream(), response.getOutputStream());
}
​
private void configExportFile(HttpServletResponse response, String fileName) {
  response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
  response.setCharacterEncoding(StandardCharsets.UTF_8.name());
  String exportFileName = URLEncoder.encode(StringUtils.defaultIfBlank(fileName, "export"), StandardCharsets.UTF_8).replaceAll("\\+", "%20");
  response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + exportFileName + ".xlsx");
}

模板:

最新的 3.0.0 版本,采用 Note 备注的方式,进行标记出Area 区域,方便 JXLS 框架进行识别和替换了。

image-20240705174206119

image-20240705174334351

导出结果:

image-20240705174906580

image-20240705173758082

image-20240705173929710

小结

在高性能、大数据量的情况下,使用 EasyExcel 框架,结合数据库的分页分批查询,来进行高效的导出。当你的系统还需要通过高度定制化的 Excel 模板(比如:详情+明细列表)进行导出时,可以使用 JXLS 框架,这样一个基本的 Excel 导出方案就已经完成了。更多高级的功能还需要结合项目的实际情况进行设计和落地实施。

推荐阅读

高效 Excel 导入:快速解析大文件

打造高效 K8S 本地环境

EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel 官网 (alibaba.com)

Jxls (sourceforge.net)


欢迎关注我的公众号“Eric技术圈”,原创技术文章第一时间推送。

License:  CC BY 4.0