【技术派后端篇】技术派整合 FastExcel：高效实现 500 万条数据导出

1 引言

本文将详细介绍技术派整合 FastExcel 实现 500 万条数据导出的过程。FastExcel 前身为在 GitHub 拥有 32.9k star 的 EasyExcel。因原作者从阿里离职，阿里官宣 EasyExcel 停止维护，而 FastExcel 继续为开发者提供 Excel 操作功能。技术派项目缺少 Excel 导出功能，为完善该功能，实现了 FastExcel 的整合，并将包含 Spring Boot 后端和 React 前端的完整代码提交至 GitHub。

2 后端代码实现

1. 引入依赖：在相关 module 中添加如下依赖，以便使用 FastExcel 功能：

   <dependency>
       <groupId>cn.idev.excel</groupId>
       <artifactId>fastexcel</artifactId>
       <version>${fastexcel.version}</version>
   </dependency>
   

2. 添加下载接口：在处理下载请求的com.github.paicoding.forum.web.admin.rest.StatisticsSettingRestController中，添加 pvUvDayDownload2Excel 接口。通过设置 HttpServletResponse 的内容类型、字符编码、文件名编码和响应头，确保能正确向前端返回 Excel 文件。具体代码如下：

   @GetMapping("pvUvDayDownload2Excel")
   public void pvUvDayDownload2Excel(@RequestParam(name = "day", required = false) Integer day,
                                      HttpServletResponse response) throws IOException{
       response.reset();
       response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
       response.setCharacterEncoding("utf-8");
       String fileName = URLEncoder.encode("技术派", "UTF-8").replaceAll("\\+", "%20");
       response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
       // 获取数据
       day = (day == null || day == 0)? DEFAULT_DAY :day;
       statisticsSettingService.download2Excel(day, response);
   }
   

   其中，response.setContentType 设置响应内容类型为 Excel 文件的 MIME 类型；response.setCharacterEncoding 设置响应字符编码为 UTF-8；URLEncoder.encode 对文件名进行 URL 编码并替换特殊字符；response.setHeader 设置响应头，指示浏览器以附件形式下载文件并指定文件名。
3. 调用 FastExcel API 写入文件：在 com.github.paicoding.forum.service.statistics.service.impl.StatisticsSettingServiceImpl#download2Excel 方法中，调用 FastExcel 的 API 实现 Excel 文件写入。首先获取数据列表，进行数据类型转换，然后利用 FastExcel.write 设置输出流和数据类型，sheet 方法设定工作表名称，doWrite 方法完成数据写入。代码如下：

   public void download2Excel(Integer day, HttpServletResponse response) {
       List<StatisticsDayDTO> pvDayList = requestCountService.getPvUvDayList(day);
       List<StatisticsDayExcelDTO> excelDTOList = StatisticsConverter.convertToExcelDTOList(pvDayList);
       try {
           FastExcel.write(response.getOutputStream(), StatisticsDayExcelDTO.class)
                 .sheet(day + "天统计")
                 .doWrite(excelDTOList);
       } catch (IOException e) {
           throw new RuntimeException(e);
       }
   }
   

   数据类型 StatisticsDayExcelDTO 通过 @ExcelProperty 注解指定列名：

   @Data
   public class StatisticsDayExcelDTO {
       @ExcelProperty("日期")
       private String date;
       @ExcelProperty("PV")
       private Long pvCount;
       @ExcelProperty("UV")
       private Long uvCount;
   }
   

3 前端代码实现

1. 封装 Axios 的 get 请求：封装通用的 get 方法，接收请求 URL 和配置对象，返回 Promise 对象，具体实现如下：

   get < T > (url: string, config: AxiosRequestConfig = {}): Promise<ResultData<T>>{
       console.log("开始执行 get 请求", url, config);
       return this.service.get(url, config);
   }
   

   Axios 是基于 promise 的网络请求库，get 方法接受请求地址 url和配置对象 config，返回一个包含 ResultData<T> 类型数据的 Promise。

2. 设置响应类型为 blob：为获取 Excel 文件的请求设置 responseType 为 blob，以正确处理二进制数据，代码如下：

   export const download2ExcelPvUvApi = (day: number) => {
       return http.get(`${PORT1}/statistics/pvUvDayDownload2Excel?day=${day}`, { responseType: "blob" });
   };
   

   该函数导出以便其他模块使用，URL 包含请求参数 day，{ responseType: "blob" } 指定响应类型为 blob 用于处理二进制数据。

3. 添加下载图标：在 echarts 的 toolbar 中添加下载 Excel 的图标，并指定点击事件为 exportToExcel，配置如下：

   feature: {
       myDownloadExcel: {
           show: true,
           title: "下载 Excel",
           icon: "path://M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zm-1 14h-3v-3h-2v3H8v-3H6v3H5v-2h3v-2H5V5h14v9h-2v3z", 
           onclick: exportToExcel, 
       },
   }
   

4. 处理服务端返回数据：exportToExcel 为异步函数，从响应头解析文件名，将响应数据转换为 Blob 对象，创建临时 URL，模拟点击下载链接，并释放 Blob URL 避免内存泄漏。具体实现如下：

   // 导出数据为 Excel 文件
   const exportToExcel = async () => {
       // 通过 dayLimitList 获取对应的天数
       const day = dayLimitList.find(item => item.value === pvUvDay)?.value;
       if (!day) return;
       console.log("导出的天数是", day);
       // 调用下载接口
       const response = await download2ExcelPvUvApi(Number(day));
       const contentDisposition = response.headers["content-disposition"];
       let fileName = "paicoding.xlsx";
       if (contentDisposition) {
           const matches = contentDisposition.match(/filename\*?=utf-8''([^;]+)/i);
           if (matches && matches.length === 2) {
               fileName = decodeURIComponent(matches[1]);
           }
       }
       console.log("文件名是", fileName);
       // 将返回的 Blob 数据转换为可下载文件
       const blob = new Blob([response.data], {
           type: response.headers["content-type"],
       });
       console.log("Blob 数据大小：", blob);
       const url = window.URL.createObjectURL(blob);
       const link = document.createElement("a");
       link.href = url;
       link.download = fileName;
       link.click();
       // 释放 Blob URL，避免内存泄漏
       window.URL.revokeObjectURL(url);
   };
   

4 接口测试

技术派采用前后端分离开发模式，后端开发者完成开发后可利用 Apifox 进行自测。若在 IntelliJ IDEA 中安装 Apifox 插件，可将后端 controller 接口上传至 Apifox，并在 IDE 中直接唤起测试，操作便捷。

5 Bug 处理

1. common-io 包版本问题：当项目中 common-io 包版本低于 2.16.1 时，FastExcel 会出现 java.lang.NoSuchMethodError UnsynchronizedByteArrayOutputStream.builder 错误。解决方法是升级 common-io 包版本，可通过 mvn dependency:tree | grep commons-io 命令或在 IntelliJ IDEA 中查看 external libraries 中 common-io 包版本确认。
2. Axios 请求封装问题：Axios 的 get 请求若封装不正确，responseType: "blob" 参数可能无法传递至后端，需确保请求封装的准确性。
3. MySQL 连接问题：本地连接 MySQL 时，连接字符串需添加 allowPublicKeyRetrieval=true&autoReconnect=true，否则批量处理 500 万数据时可能出现 MySQL 连接断开问题。

6 模拟 500 万条数据导出测试

在本地数据库批量插入 500 万条数据后，编写测试类进行批量导出效率测试。

• 批量插入数据测试类：com.github.paicoding.forum.web.javabetter.mysql1.Insert2TestExcel

  public class Insert2TestExcel {
      public static void main(String[] args) {
          // 连接 MySQL 数据库
          Connection conn = null;
          PreparedStatement pstmt = null;
          Statement stmt = null;
          try {
              conn = DriverManager.getConnection(
                      "jdbc:mysql://localhost:3306/pai_coding?useSSL=false&rewriteBatchedStatements=true",
                      "root",
                      ""
              );
  
              stmt = conn.createStatement();
  //            // 创建表 test_excel,表的字段有 id，day，pu 和 pv
  //            stmt.executeUpdate("CREATE TABLE IF NOT EXISTS test_excel (" +
  //                    "id INT PRIMARY KEY AUTO_INCREMENT, " +
  //                    "day DATE, " +
  //                    "pu INT, " +
  //                    "pv INT)"
  //            );
  
              // 批量插入数据
              conn.setAutoCommit(false); // 关闭自动提交
  
              String insertSQL = "INSERT INTO request_count (host, cnt, date) VALUES (?, ?, ?)";
              pstmt = conn.prepareStatement(insertSQL);
  
              int batchSize = 5000; // 批量大小
              LocalDate baseDate = Date.valueOf("2020-01-01").toLocalDate();
              for (int i = 0; i < 5000000; i++) {
                  pstmt.setString(1, "127.0.0." + (i % 255));
                  pstmt.setInt(2, 100 + i % 10000);
  
                  pstmt.setDate(3, Date.valueOf(baseDate.plusDays(i % 31)));
                  pstmt.addBatch();
  
                  if (i % batchSize == 0) {
                      pstmt.executeBatch();
                  }
              }
              pstmt.executeBatch();
              conn.commit(); // 手动提交
  
              // 查询数据
  //            ResultSet rs = stmt.executeQuery("SELECT id, day, pu, pv FROM test_excel LIMIT 10");
  //            while (rs.next()) {
  //                System.out.printf("ID: %d, Day: %s, PU: %d, PV: %d%n",
  //                        rs.getInt("id"), rs.getString("day"), rs.getInt("pu"), rs.getInt("pv"));
  //            }
  
              // 查多少行
              ResultSet rs2 = stmt.executeQuery("SELECT COUNT(*) AS total FROM request_count");
              if (rs2.next()) {
                  System.out.println("Total rows: " + rs2.getInt("total"));
              }
          } catch (SQLException e) {
              e.printStackTrace();
          } finally {
              // 释放资源
              try {
                  if (pstmt != null) {
                      pstmt.close();
                  }
                  if (stmt != null) {
                      stmt.close();
                  }
                  if (conn != null) {
                      conn.close();
                  }
              } catch (SQLException e) {
                  e.printStackTrace();
              }
          }
      }
  }
  

• 批量导出测试类：com.github.paicoding.forum.web.front.test.rest.TestController#exportBatch

  @Autowired
  private RequestCountService requestCountService;
  
  // 准备使用 FastExcel 批量导出 500万条数据
  @Permission(role = UserRole.ADMIN)
  @GetMapping(path = "exportBatch")
  public void exportBatch(HttpServletResponse response) throws IOException {
      OutputStream outputStream = response.getOutputStream();
  
      // 查出总数量
      long total = requestCountService.count();
  
      // 每页大小
      int pageSize = 100000; // 每页 1 万条数据
  
      // 每个 Sheet 容纳数据条数
      int sheetSize = 1000000; // 每个 Sheet 100 万条数据
      int sheetCount = (int) (total / sheetSize + (total % sheetSize == 0 ? 0 : 1));
  
      // 文件名
      String fileName = URLEncoder.encode("批量导出测试.xlsx", "UTF-8").replaceAll("\\+", "%20");
  
      // 设置响应头
      response.reset();
      response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      response.setCharacterEncoding("utf-8");
      response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
  
      // 开始导出
      try (ExcelWriter excelWriter = FastExcel.write(outputStream, RequestCountExcelDO.class).build()) {
          for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
              // 命名 Sheet
              WriteSheet sheet = FastExcel.writerSheet(sheetIndex, "sheet" + (sheetIndex + 1)).build();
  
              // 查询数据
              for (int pageIndex = 0; pageIndex < sheetSize / pageSize; pageIndex++) {
                  // 第一页是从 0-9999，第二页是从 10000-19999
                  int offset = sheetIndex * sheetSize + pageIndex * pageSize + 1;
                  // TODO 自定义线程池+ CountDownLatch 进行优化
                  List<RequestCountDO> data = requestCountService.listRequestCount(PageParam.newPageInstance(offset, pageSize));
                  List<RequestCountExcelDO> list = StatisticsConverter.convertToRequestCountExcelDOList(data);
                  excelWriter.write(list, sheet);
                  log.info("导出第 {} 页数据，目前是第{} 条数据", pageIndex, offset);
              }
  
          }
      }
  }
  

• 自定义线程池，以及 CountDownLatch 进行优化：com.github.paicoding.forum.web.front.test.rest.TestController#exportBatchPoolCountDownLatch

  // 准备使用 FastExcel 批量导出 500万条数据
  // 自定义线程池，以及 CountDownLatch 进行优化
  @Permission(role = UserRole.ADMIN)
  @GetMapping(path = "exportBatchPoolCountDownLatch")
  public void exportBatchPoolCountDownLatch(HttpServletResponse response) throws IOException {
     OutputStream outputStream = response.getOutputStream();
  
     // 查出总数量
     long total = requestCountService.count();
  
     // 每页大小
     int pageSize = 100000; // 每页 1 万条数据
  
     // 每个 Sheet 容纳数据条数
     int sheetSize = 1000000; // 每个 Sheet 100 万条数据
     int sheetCount = (int) (total / sheetSize + (total % sheetSize == 0 ? 0 : 1));
  
     // 文件名
     String fileName = URLEncoder.encode("批量导出测试.xlsx", "UTF-8").replaceAll("\\+", "%20");
  
     // 设置响应头
     response.reset();
     response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
     response.setCharacterEncoding("utf-8");
     response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
  
     // 开始导出
     int threadPoolSize = Runtime.getRuntime().availableProcessors(); // 根据 CPU 核心数动态分配线程
     ExecutorService threadPool = Executors.newFixedThreadPool(threadPoolSize);
     CountDownLatch latch = new CountDownLatch(sheetCount * (sheetSize / pageSize));
  
     try (ExcelWriter excelWriter = FastExcel.write(outputStream, RequestCountExcelDO.class).build()) {
         for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
             WriteSheet sheet = FastExcel.writerSheet(sheetIndex, "sheet" + (sheetIndex + 1)).build();
             for (int pageIndex = 0; pageIndex < sheetSize / pageSize; pageIndex++) {
                 int finalSheetIndex = sheetIndex;
                 int finalPageIndex = pageIndex;
                 threadPool.submit(() -> {
                     try {
                         // 计算分页偏移量
                         int offset = finalSheetIndex * sheetSize + finalPageIndex * pageSize + 1;
                         List<RequestCountDO> data = requestCountService.listRequestCount(PageParam.newPageInstance(offset, pageSize));
                         List<RequestCountExcelDO> list = StatisticsConverter.convertToRequestCountExcelDOList(data);
  
                         synchronized (excelWriter) {
                             excelWriter.write(list, sheet); // 写入操作需要同步，避免线程冲突
                         }
                         log.info("导出第 {} 页数据，目前是第 {} 条数据", finalPageIndex, offset);
                     } catch (Exception e) {
                         log.error("导出第 {} 页数据时出错", finalPageIndex, e);
                     } finally {
                         latch.countDown(); // 减少计数器
                     }
                 });
             }
         }
         latch.await(); // 等待所有线程完成
     } catch (InterruptedException e) {
         Thread.currentThread().interrupt();
         log.error("线程中断: ", e);
     } finally {
         threadPool.shutdown(); // 关闭线程池
     }
  }
  

初始未优化时，导出耗时约一分钟。通过自定义线程池和 CountDownLatch 优化后，导出时间缩短至 7185 ms，性能提升近 60 倍，充分体现了 FastExcel 的高效性能。

7 参考链接

1. 技术派整合FaseExcel导出500万条数据
2. 项目仓库（GitHub）
3. 项目仓库（码云）