Excel高性能導(dǎo)出方案！

2025-05-06 01:00:00

很多小伙伴門在開發(fā)數(shù)據(jù)導(dǎo)出功能時(shí)，習(xí)慣性使用Apache POI的HSSF/XSSF組件。這類方案在數(shù)據(jù)量超過5萬行時(shí)，會(huì)出現(xiàn)明顯的性能斷崖式下跌。根本原因在于內(nèi)存對象模型的設(shè)計(jì)缺陷：每個(gè)Cell對象占用約1KB內(nèi)存，百萬級(jí)數(shù)據(jù)直接導(dǎo)致JVM堆內(nèi)存爆炸。

前言

在我們的日常工作中，經(jīng)常會(huì)有Excel數(shù)據(jù)導(dǎo)出的需求。

但可能會(huì)遇到性能和內(nèi)存的問題。

今天這篇文章跟大家一起聊聊Excel高性能導(dǎo)出的方案，希望對你會(huì)有所幫助。

1.傳統(tǒng)方案的問題

很多小伙伴門在開發(fā)數(shù)據(jù)導(dǎo)出功能時(shí)，習(xí)慣性使用Apache POI的HSSF/XSSF組件。

這類方案在數(shù)據(jù)量超過5萬行時(shí)，會(huì)出現(xiàn)明顯的性能斷崖式下跌。

根本原因在于內(nèi)存對象模型的設(shè)計(jì)缺陷：每個(gè)Cell對象占用約1KB內(nèi)存，百萬級(jí)數(shù)據(jù)直接導(dǎo)致JVM堆內(nèi)存爆炸。

示例代碼（反面教材）：

// 典型內(nèi)存殺手寫法
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int i = 0; i < 1000000; i++) {
    Row row = sheet.createRow(i); // 每行產(chǎn)生Row對象
    row.createCell(0).setCellValue("數(shù)據(jù)"+i); // 每個(gè)Cell獨(dú)立存儲(chǔ)
}

這種寫法會(huì)產(chǎn)生約100萬個(gè)Row對象和1000萬個(gè)Cell對象（假設(shè)每行10列），直接導(dǎo)致內(nèi)存占用突破1GB。

更致命的是頻繁Full GC會(huì)導(dǎo)致系統(tǒng)卡頓甚至OOM崩潰。

2.流式處理架構(gòu)設(shè)計(jì)

高性能導(dǎo)出的核心在于內(nèi)存與磁盤的平衡。

這里給出兩種經(jīng)過生產(chǎn)驗(yàn)證的方案：

方案一：SXSSFWorkbook

使用SXSSFWorkbook類，它是Apache POI的增強(qiáng)版。

具體示例如下：

// 內(nèi)存中只保留1000行窗口
SXSSFWorkbook workbook = new SXSSFWorkbook(1000); 
Sheet sheet = workbook.createSheet();
for (int i = 0; i < 1000000; i++) {
    Row row = sheet.createRow(i);
    // 寫入后立即刷新到臨時(shí)文件
    if(i % 1000 == 0) {
        ((SXSSFSheet)sheet).flushRows(1000); 
    }
}

通過設(shè)置滑動(dòng)窗口機(jī)制，將已處理數(shù)據(jù)寫入磁盤臨時(shí)文件，內(nèi)存中僅保留當(dāng)前處理批次。實(shí)測百萬數(shù)據(jù)內(nèi)存占用穩(wěn)定在200MB以內(nèi)。

方案二：EasyExcel

EasyExcel是阿里巴巴開源的Excel高性能處理框架，目前在業(yè)界使用比較多。

最近EasyExcel的作者又推出了FastExcel，它是EasyExcel的升級(jí)版。

// 極簡流式API示例
String fileName = "data.xlsx";
EasyExcel.write(fileName, DataModel.class)
    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
    .sheet("Sheet1")
    .doWrite(data -> {
        // 分頁查詢數(shù)據(jù)
        int page = 0;
        while (true) {
            List<DataModel> list = queryByPage(page, 5000);
            if (CollectionUtils.isEmpty(list)) break;
            data.write(list);
            page++;
        }
    });

該方案通過事件驅(qū)動(dòng)模型和對象復(fù)用池技術(shù)，百萬數(shù)據(jù)導(dǎo)出內(nèi)存占用可控制在50MB以下。

其核心優(yōu)勢在于：

自動(dòng)分批加載數(shù)據(jù)（默認(rèn)每批次5000條）
通過反射緩存消除重復(fù)對象創(chuàng)建
內(nèi)置樣式優(yōu)化策略避免內(nèi)存碎片

3.數(shù)據(jù)庫查詢的黃金法則

即便導(dǎo)出工具優(yōu)化到位，若數(shù)據(jù)查詢環(huán)節(jié)存在瓶頸，整體性能仍會(huì)大打折扣。這里給出三個(gè)關(guān)鍵優(yōu)化點(diǎn)：

3.1 解決深度分頁問題

傳統(tǒng)分頁查詢在百萬級(jí)數(shù)據(jù)時(shí)會(huì)出現(xiàn)性能雪崩：

SELECT * FROM table LIMIT 900000, 1000 -- 越往后越慢！

正確姿勢應(yīng)使用游標(biāo)方式：

// 基于自增ID的遞進(jìn)查詢
Long lastId = 0L;
int pageSize = 5000;
do {
    List<Data> list = jdbcTemplate.query(
        "SELECT * FROM table WHERE id > ? ORDER BY id LIMIT ?",
        new BeanPropertyRowMapper<>(Data.class),
        lastId, pageSize);
    if(list.isEmpty()) break;
    lastId = list.get(list.size()-1).getId();
    // 處理數(shù)據(jù)...
} while (true);

該方案利用索引的有序性，將時(shí)間復(fù)雜度從O(N2)降為O(N)。

3.2 減少字段數(shù)量

-- 錯(cuò)誤寫法：全字段查詢
SELECT * FROM big_table 

-- 正確姿勢：僅取必要字段
SELECT id,name,create_time FROM big_table

實(shí)測顯示，當(dāng)單行數(shù)據(jù)從20個(gè)字段縮減到5個(gè)字段時(shí)，查詢耗時(shí)降低40%，網(wǎng)絡(luò)傳輸量減少70%。

3.3 連接池參數(shù)調(diào)優(yōu)

# SpringBoot配置示例
spring:
  datasource:
    hikari:
      maximum-pool-size: 20 # 根據(jù)CPU核數(shù)調(diào)整
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

導(dǎo)出場景建議使用獨(dú)立連接池，避免影響主業(yè)務(wù)。

連接數(shù)計(jì)算公式：線程數(shù) = CPU核心數(shù) * 2 + 磁盤數(shù)。

4.生產(chǎn)級(jí)進(jìn)階技巧

4.1 異步分片導(dǎo)出

想要提升Excel數(shù)據(jù)導(dǎo)出的性能，我們必須使用多線程異步導(dǎo)出的方案。

具體示例如下：

@Async("exportExecutor")
public CompletableFuture<String> asyncExport(ExportParam param) {
    // 1. 計(jì)算分片數(shù)量
    int total = dataService.count(param);
    int shardSize = total / 100000; 

    // 2. 并行處理分片
    List<CompletableFuture<Void>> futures = new ArrayList<>();
    for (int i = 0; i < shardSize; i++) {
        int finalI = i;
        futures.add(CompletableFuture.runAsync(() -> {
            exportShard(param, finalI * 100000, 100000);
        }, forkJoinPool.commonPool()));
    }

    // 3. 合并文件
    CompletableFuture.allOf(futures.toArray(new CompletableFuture)
        .thenApply(v -> mergeFiles(shardSize));
    return CompletableFuture.completedFuture(taskId);
}

通過分治策略將任務(wù)拆解為多個(gè)子任務(wù)并行執(zhí)行，結(jié)合線程池管理實(shí)現(xiàn)資源可控。

4.2 配置JVM參數(shù)

我們需要配置JVM參數(shù)，并且需要對這些參數(shù)進(jìn)行調(diào)優(yōu)：

// JVM啟動(dòng)參數(shù)示例
-Xmx4g -Xms4g 
-XX:+UseG1GC 
-XX:MaxGCPauseMillis=200
-XX:ParallelGCThreads=4
-XX:ConcGCThreads=2
-XX:InitiatingHeapOccupancyPercent=35

這樣可以有效的提升性能。

導(dǎo)出場景需特別注意：