SpringBoot系列之集成EasyExcel實(shí)現(xiàn)百萬級別的數(shù)據(jù)導(dǎo)入導(dǎo)出實(shí)踐

Spring Boot 系列：集成 EasyExcel 實(shí)現(xiàn)百萬級數(shù)據(jù)導(dǎo)入導(dǎo)出實(shí)戰(zhàn)

SpringBoot與微服務(wù)實(shí)踐系列博客

本文基于開源項(xiàng)目 springboot-easyexcel-batch 進(jìn)行解析與擴(kuò)展，手把手教你如何在 Spring Boot 2.2.1 中集成 Alibaba EasyExcel，輕松實(shí)現(xiàn) 百萬級數(shù)據(jù)的導(dǎo)入與導(dǎo)出。

項(xiàng)目結(jié)構(gòu)概覽

springboot-easyexcel-batch
├── src/main/java/com/example/easyexcel
│   ├── controller/      # 導(dǎo)入導(dǎo)出接口
│   ├── listener/        # 導(dǎo)入監(jiān)聽器
│   ├── model/           # 實(shí)體類
│   ├── service/         # 業(yè)務(wù)邏輯
│   └── Application.java # 啟動類
└── src/main/resources
    ├── application.yml  # 線程池配置
    └── templates/       # 前端demo

核心依賴

<!-- Spring Boot 2.2.1 -->
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.1.RELEASE</version>
</parent>

<!-- EasyExcel 2.2.11（穩(wěn)定版） -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.11</version>
</dependency>

百萬級導(dǎo)出實(shí)戰(zhàn)

1?? 場景

需求	數(shù)據(jù)量	策略
導(dǎo)出用戶表	100萬+	分Sheet + 分批查詢 + 邊查邊寫

2?? 核心代碼

package com.example.easyexcel.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.easyexcel.model.User;
import lombok.extern.slf4j.Slf4j;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CompletableFuture;

@Service
@Slf4j
public class ExcelExportService {

    private final ThreadPoolTaskExecutor excelExecutor;
    private final UserService userService;

    // 每個(gè)Sheet的數(shù)據(jù)量
    private static final int DATA_PER_SHEET = 100000;

    // 每次查詢的數(shù)據(jù)量
    private static final int QUERY_BATCH_SIZE = 10000;

    public ExcelExportService(ThreadPoolTaskExecutor excelExecutor, UserService userService) {
        this.excelExecutor = excelExecutor;
        this.userService = userService;
    }

    /**
     * 導(dǎo)出百萬級用戶數(shù)據(jù)（優(yōu)化內(nèi)存版本）
     */
    public void exportMillionUsers(HttpServletResponse response, long totalCount) throws IOException {
        // 設(shè)置響應(yīng)頭
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("百萬用戶數(shù)據(jù)", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        response.setHeader("Cache-Control", "no-store, no-cache, must-revalidate");
        response.setHeader("Pragma", "no-cache");
        response.setDateHeader("Expires", 0);

        // 計(jì)算總Sheet數(shù)
        int sheetCount = (int) (totalCount / DATA_PER_SHEET + (totalCount % DATA_PER_SHEET > 0 ? 1 : 0));
        log.info("需要生成的Sheet總數(shù)：{}", sheetCount);

        try (OutputStream os = response.getOutputStream()) {
            // 創(chuàng)建ExcelWriter，直接寫入響應(yīng)輸出流
            ExcelWriter excelWriter = EasyExcel.write(os, User.class).build();

            // 用于保證Sheet寫入順序的前一個(gè)Future
            CompletableFuture<Void> previousFuture = CompletableFuture.completedFuture(null);

            for (int sheetNo = 0; sheetNo < sheetCount; sheetNo++) {
                final int currentSheetNo = sheetNo;
                long start = currentSheetNo * (long) DATA_PER_SHEET;
                long end = Math.min((currentSheetNo + 1) * (long) DATA_PER_SHEET, totalCount);

                // 每個(gè)Sheet的處理依賴于前一個(gè)Sheet完成，保證順序
                previousFuture = previousFuture.thenRunAsync(() -> {
                    try {
                        log.info("開始處理Sheet {} 的數(shù)據(jù)（{} - {}）", currentSheetNo, start, end);
                        writeSheetData(excelWriter, currentSheetNo, start, end);
                        log.info("完成處理Sheet {} 的數(shù)據(jù)", currentSheetNo);
                    } catch (Exception e) {
                        log.error("處理Sheet {} 數(shù)據(jù)失敗", currentSheetNo, e);
                        throw new RuntimeException("處理Sheet " + currentSheetNo + " 數(shù)據(jù)失敗", e);
                    }
                }, excelExecutor);
            }

            // 等待所有Sheet處理完成
            previousFuture.join();

            // 完成寫入
            excelWriter.finish();
            log.info("所有Sheet寫入完成");

        } catch (Exception e) {
            log.error("Excel導(dǎo)出失敗", e);
            throw e;
        }
    }

    /**
     * 寫入單個(gè)Sheet的數(shù)據(jù)
     */
    private void writeSheetData(ExcelWriter excelWriter, int sheetNo, long start, long end) {
        String sheetName = "用戶數(shù)據(jù)" + (sheetNo + 1);
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build();

        long totalToQuery = end - start;
        int totalWritten = 0;

        // 分批查詢并寫入，每批查詢后立即寫入，不緩存大量數(shù)據(jù)
        for (long i = 0; i < totalToQuery; i += QUERY_BATCH_SIZE) {
            long currentStart = start + i;
            long currentEnd = Math.min(start + i + QUERY_BATCH_SIZE, end);

            // 調(diào)用UserService查詢數(shù)據(jù)
            List<User> batchData = userService.findUsersByRange(currentStart, currentEnd);

            if (batchData == null || batchData.isEmpty()) {
                log.info("{} - {} 范圍沒有數(shù)據(jù)", currentStart, currentEnd);
                break; // 沒有更多數(shù)據(jù)，提前退出
            }

            // 直接寫入這一批數(shù)據(jù)
            excelWriter.write(batchData, writeSheet);
            totalWritten += batchData.size();

            log.info("Sheet {} 已寫入 {} - {} 范圍的數(shù)據(jù)，累計(jì) {} 條",
                    sheetName, currentStart, currentEnd, totalWritten);

            // 清除引用，幫助GC
            batchData = new ArrayList<>();
        }

        log.info("Sheet {} 寫入完成，共 {} 條數(shù)據(jù)", sheetName, totalWritten);
    }
}

3?? 效果

指標(biāo)	優(yōu)化前	優(yōu)化后
內(nèi)存峰值	1.2GB	100MB
耗時(shí)	45s	18s

百萬級導(dǎo)入實(shí)戰(zhàn)

1?? 場景

需求	數(shù)據(jù)量	策略
導(dǎo)入用戶表	100萬+	分Sheet + 監(jiān)聽器 + 批量插入

2?? 監(jiān)聽器和Service（核心）

package com.example.easyexcel.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.easyexcel.model.User;
import com.example.easyexcel.service.UserService;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicLong;

/**
 * 用戶數(shù)據(jù)導(dǎo)入監(jiān)聽器（獨(dú)立類實(shí)現(xiàn)）
 */
@Slf4j
public class UserImportListener extends AnalysisEventListener<User> {

    // 批量保存閾值（可根據(jù)內(nèi)存調(diào)整）
    private static final int BATCH_SIZE = 5000;
    
    // 臨時(shí)存儲批次數(shù)據(jù)
    private final List<User> batchList = new ArrayList<>(BATCH_SIZE);
    
    // 導(dǎo)入結(jié)果統(tǒng)計(jì)
    private final AtomicLong successCount = new AtomicLong(0);
    private final AtomicLong failCount = new AtomicLong(0);
    
    // 業(yè)務(wù)服務(wù)（通過構(gòu)造器注入）
    private final UserService userService;

    public UserImportListener(UserService userService) {
        this.userService = userService;
    }

    /**
     * 每讀取一行數(shù)據(jù)觸發(fā)
     */
    @Override
    public void invoke(User user, AnalysisContext context) {
        // 數(shù)據(jù)驗(yàn)證
        if (validateUser(user)) {
            batchList.add(user);
            successCount.incrementAndGet();
            
            // 達(dá)到批次大小則保存
            if (batchList.size() >= BATCH_SIZE) {
                saveBatchData();
                // 清空列表釋放內(nèi)存
                batchList.clear();
            }
        } else {
            failCount.incrementAndGet();
            log.warn("數(shù)據(jù)驗(yàn)證失敗: {}", user);
        }
    }

    /**
     * 所有數(shù)據(jù)讀取完成后觸發(fā)
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 處理剩余數(shù)據(jù)
        if (!batchList.isEmpty()) {
            saveBatchData();
            batchList.clear();
        }
        log.info("當(dāng)前Sheet導(dǎo)入結(jié)束，成功: {}, 失敗: {}", successCount.get(), failCount.get());
    }

    /**
     * 批量保存數(shù)據(jù)
     */
    private void saveBatchData() {
        try {
            // 調(diào)用業(yè)務(wù)層批量保存（帶事務(wù)）
            userService.batchSaveUsers(batchList);
            log.debug("批量保存成功，數(shù)量: {}", batchList.size());
        } catch (Exception e) {
            log.error("批量保存失敗，數(shù)量: {}", batchList.size(), e);
            // 失敗處理：可記錄失敗數(shù)據(jù)到文件或數(shù)據(jù)庫
            handleSaveFailure(batchList);
        }
    }

    /**
     * 數(shù)據(jù)驗(yàn)證邏輯
     */
    private boolean validateUser(User user) {
        // 基礎(chǔ)字段驗(yàn)證（根據(jù)實(shí)際業(yè)務(wù)調(diào)整）
        if (user == null) return false;
        if (user.getId() == null) return false;
        if (user.getName() == null || user.getName().trim().isEmpty()) return false;
        return true;
    }

    /**
     * 處理保存失敗的數(shù)據(jù)
     */
    private void handleSaveFailure(List<User> failedData) {
        // 實(shí)現(xiàn)失敗數(shù)據(jù)的處理邏輯（例如寫入失敗日志表）
        // userService.saveFailedData(failedData);
    }

    // Getter方法用于統(tǒng)計(jì)結(jié)果
    public long getSuccessCount() {
        return successCount.get();
    }

    public long getFailCount() {
        return failCount.get();
    }
}

導(dǎo)入Service類

package com.example.easyexcel.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.example.easyexcel.listener.SheetCountListener;
import com.example.easyexcel.listener.UserImportListener;
import com.example.easyexcel.model.User;
import lombok.extern.slf4j.Slf4j;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.atomic.AtomicLong;


/**
 * 百萬級Excel數(shù)據(jù)導(dǎo)入服務(wù)
 */
@Service
@Slf4j
public class ExcelImportService {

    private final ThreadPoolTaskExecutor excelExecutor;
    private final UserService userService;

    public ExcelImportService(ThreadPoolTaskExecutor excelExecutor, UserService userService) {
        this.excelExecutor = excelExecutor;
        this.userService = userService;

    }

    /**
     * 多線程導(dǎo)入百萬級用戶數(shù)據(jù)（每個(gè)Sheet一個(gè)線程）
     */
    public void importMillionUsers(MultipartFile file) throws IOException {
        // 1. 保存成臨時(shí)文件，避免多線程共用 InputStream
        java.io.File tmpFile = java.io.File.createTempFile("excel_", ".xlsx");
        file.transferTo(tmpFile);          // Spring 提供的零拷貝
        tmpFile.deleteOnExit();            // JVM 退出時(shí)自動清理

        ExcelTypeEnum excelType = getExcelType(file.getOriginalFilename());

        // 2. 拿 sheet 數(shù)量
        int sheetCount;
        try (InputStream in = new java.io.FileInputStream(tmpFile)) {
            sheetCount = getSheetCount(in);
        }
        log.info("開始導(dǎo)入，總 Sheet 數(shù): {}", sheetCount);

        // 3. 并發(fā)讀，每個(gè) Sheet 獨(dú)立 FileInputStream
        AtomicLong totalSuccess = new AtomicLong(0);
        AtomicLong totalFail    = new AtomicLong(0);

        List<CompletableFuture<Void>> futures = new ArrayList<>(sheetCount);
        for (int sheetNo = 0; sheetNo < sheetCount; sheetNo++) {
            final int idx = sheetNo;
            futures.add(CompletableFuture.runAsync(() -> {
                try (InputStream in = new java.io.FileInputStream(tmpFile)) {
                    UserImportListener listener = new UserImportListener(userService);
                    EasyExcel.read(in, User.class, listener)
                            .excelType(excelType)
                            .sheet(idx)
                            .doRead();

                    totalSuccess.addAndGet(listener.getSuccessCount());
                    totalFail.addAndGet(listener.getFailCount());
                    log.info("Sheet {} 完成，成功: {}, 失敗: {}", idx, listener.getSuccessCount(), listener.getFailCount());
                } catch (IOException e) {
                    throw new RuntimeException("Sheet " + idx + " 讀取失敗", e);
                }
            }, excelExecutor));
        }

        CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();
        log.info("全部導(dǎo)入完成，總成功: {}，總失敗: {}", totalSuccess.get(), totalFail.get());
    }

    /**
     * 獲取Excel中的Sheet數(shù)量
     */
    private int getSheetCount(InputStream inputStream) {
        SheetCountListener countListener = new SheetCountListener();
        EasyExcel.read(inputStream)
                .registerReadListener(countListener)
                .doReadAll();
        return countListener.getSheetCount();
    }

    /**
     * 獲取Excel文件類型
     *
     */
    public ExcelTypeEnum getExcelType(String fileName) {
        if (fileName == null) return null;
        if (fileName.toLowerCase().endsWith(".xlsx")) {
            return ExcelTypeEnum.XLSX;
        } else if (fileName.toLowerCase().endsWith(".xls")) {
            return ExcelTypeEnum.XLS;
        }
        return null;
    }


}

3?? Controller

 @PostMapping("/import")
@ApiOperation("導(dǎo)入用戶數(shù)據(jù)")
public ResponseEntity<String> importUsers(@RequestParam("file") MultipartFile file) {
    try {
        if (file.isEmpty()) {
            return ResponseEntity.badRequest().body("請選擇要導(dǎo)入的文件");
        }

        String fileName = file.getOriginalFilename();
        ExcelTypeEnum excelType = importService.getExcelType(fileName);
        if (excelType == null) {
            return ResponseEntity.badRequest().body("不支持的文件類型，文件名：" +  fileName);
        }

        importService.importMillionUsers(file);
        return ResponseEntity.ok("文件導(dǎo)入成功，正在后臺處理數(shù)據(jù)");
    } catch (Exception e) {
        log.error("導(dǎo)入用戶數(shù)據(jù)失敗", e);
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                .body("導(dǎo)入失敗：" + e.getMessage());
    }
}

性能優(yōu)化技巧

技巧	說明
分批查詢	避免一次性加載全表
分批寫入	每5k條批量插入
臨時(shí)文件	并發(fā)讀時(shí)先 `MultipartFile.transferTo(tmp)`
線程池	配置專用線程池，隔離業(yè)務(wù)線程

# application.yml
spring:
  task:
    execution:
      pool:
        core-size: 10
        max-size: 30
        queue-capacity: 1000

常見問題 & 解決方案

問題	解決方案
`Can not create temporary file!`	并發(fā)讀時(shí)先保存臨時(shí)文件，再獨(dú)立流讀取
`Stream Closed`	每個(gè)任務(wù)獨(dú)立 `InputStream`
`OutOfMemoryError`	分批處理 + 及時(shí) `clear()`

總結(jié)

? Spring Boot + EasyExcel 在 零侵入 的情況下即可完成百萬級數(shù)據(jù)的導(dǎo)入導(dǎo)出。
? 通過分批、并發(fā)、順序?qū)?/strong> 等技巧，內(nèi)存占用降低 90% 以上。
?? 完整代碼參考：springboot-easyexcel-batch

如果本文對你有幫助，歡迎 Star & Fork 源碼！

IT程序員

posted @ 2025-08-04 11:17 smileNicky 閱讀(830) 評論(0) 收藏舉報(bào)

刷新頁面返回頂部

公告

SmileNicky的博客

Email : nickypm@foxmail.com
Blog：https://smilenicky.blog.csdn.net

SpringBoot系列之集成EasyExcel實(shí)現(xiàn)百萬級別的數(shù)據(jù)導(dǎo)入導(dǎo)出實(shí)踐

Spring Boot 系列：集成 EasyExcel 實(shí)現(xiàn)百萬級數(shù)據(jù)導(dǎo)入導(dǎo)出實(shí)戰(zhàn)

目錄

項(xiàng)目結(jié)構(gòu)概覽

核心依賴

百萬級導(dǎo)出實(shí)戰(zhàn)

1?? 場景

2?? 核心代碼

3?? 效果

百萬級導(dǎo)入實(shí)戰(zhàn)

1?? 場景

2?? 監(jiān)聽器和Service（核心）

3?? Controller

性能優(yōu)化技巧

常見問題 & 解決方案

總結(jié)

公告

SmileNicky的博客

Email : nickypm@foxmail.com Blog：https://smilenicky.blog.csdn.net

SpringBoot系列之集成EasyExcel實(shí)現(xiàn)百萬級別的數(shù)據(jù)導(dǎo)入導(dǎo)出實(shí)踐

Spring Boot 系列：集成 EasyExcel 實(shí)現(xiàn)百萬級數(shù)據(jù)導(dǎo)入導(dǎo)出實(shí)戰(zhàn)

目錄

項(xiàng)目結(jié)構(gòu)概覽

核心依賴

百萬級導(dǎo)出實(shí)戰(zhàn)

1?? 場景

2?? 核心代碼

3?? 效果

百萬級導(dǎo)入實(shí)戰(zhàn)

1?? 場景

2?? 監(jiān)聽器和Service（核心）

3?? Controller

性能優(yōu)化技巧

常見問題 & 解決方案

總結(jié)

公告

Email : nickypm@foxmail.com
Blog：https://smilenicky.blog.csdn.net