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

Posted 2024-07-13 Updated 2024-07- 13

By Eric

41~53 min read

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

Excel 导出

上来直接一把嗦的做法：

可能出现的问题：

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

设计要点：

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

优化后的方案：

需求升级

到这里我们了解了 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模式，在上层做了模型转换的封装，让使用者更加简单方便

代码实现

代码设计要点（✅ 已完成 ❌ 待改进）：

使用 EasyExcel 导出：导出性能较好，且内存消耗小（✅）
泛型支持：便于不同业务数据的导出（✅）
分页查询：通过 LIMIT 和 OFFSET 实现分页查询（✅）
批次处理：循环查询，每次查询 limit 条记录，直到没有更多记录（✅）
批量数据保存到 Excel：每次查询结果后，批量保存到 Excel（✅）
基于模板导出（✅）
可变表头处理：标题国际化等（✅）
异常状态记录（❌ 待改进）
巨量数据导出，使用异步方式，并通过数据库表记录状态，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：

点击下载 Excel 文件后：

第二次导出，batchSize=5000，耗时 6.28s：

第三次导出，batchSize=10000，耗时 5.8s：

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

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

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

JXLS 实战

JXLS 是一个用于在 Java 应用程序中导出 Excel 文件的框架。它通过将模板文件与数据源结合使用，提供了一种简洁且灵活的方式来生成复杂的 Excel 报表。Jxls 使用 Apache POI 作为底层库来处理 Excel 文件，并允许使用 Excel 文件本身作为模板。
Jxls 的主要特性
基于模板：使用 Excel 文件作为模板，可以直观地设计报表格式。
灵活的数据映射：支持将 Java 对象映射到 Excel 单元格中，支持集合、列表、嵌套对象等复杂数据结构。
条件处理和表达式：支持在模板中使用条件处理和表达式，能够实现复杂的报表逻辑。
轻量级：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 框架进行识别和替换了。

导出结果：

小结

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