SpringBoot集成EasyExcel实现百万级别的数据导入导出实践指南

<!-- 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（稳定版） -->
<dependency>
    <groupid>com.alibaba</groupid>
    <artifactid>easyexcel</artifactid>
    <version>2.2.11</version>
</dependency>

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;

    // 每个sheet的数据量
    private static final int data_per_sheet = 100000;

    // 每次查询的数据量
    private static final int query_batch_size = 10000;

    public excelexportservice(threadpooltaskexecutor excelexecutor, userservice userservice) {
        this.excelexecutor = excelexecutor;
        this.userservice = userservice;
    }

    /**
     * 导出百万级用户数据（优化内存版本）
     */
    public void exportmillionusers(httpservletresponse response, long totalcount) throws ioexception {
        // 设置响应头
        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");
        response.setheader("cache-control", "no-store, no-cache, must-revalidate");
        response.setheader("pragma", "no-cache");
        response.setdateheader("expires", 0);

        // 计算总sheet数
        int sheetcount = (int) (totalcount / data_per_sheet + (totalcount % data_per_sheet > 0 ? 1 : 0));
        log.info("需要生成的sheet总数：{}", sheetcount);

        try (outputstream os = response.getoutputstream()) {
            // 创建excelwriter，直接写入响应输出流
            excelwriter excelwriter = easyexcel.write(os, user.class).build();

            // 用于保证sheet写入顺序的前一个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);

                // 每个sheet的处理依赖于前一个sheet完成，保证顺序
                previousfuture = previousfuture.thenrunasync(() -> {
                    try {
                        log.info("开始处理sheet {} 的数据（{} - {}）", currentsheetno, start, end);
                        writesheetdata(excelwriter, currentsheetno, start, end);
                        log.info("完成处理sheet {} 的数据", currentsheetno);
                    } catch (exception e) {
                        log.error("处理sheet {} 数据失败", currentsheetno, e);
                        throw new runtimeexception("处理sheet " + currentsheetno + " 数据失败", e);
                    }
                }, excelexecutor);
            }

            // 等待所有sheet处理完成
            previousfuture.join();

            // 完成写入
            excelwriter.finish();
            log.info("所有sheet写入完成");

        } catch (exception e) {
            log.error("excel导出失败", e);
            throw e;
        }
    }

    /**
     * 写入单个sheet的数据
     */
    private void writesheetdata(excelwriter excelwriter, int sheetno, long start, long end) {
        string sheetname = "用户数据" + (sheetno + 1);
        writesheet writesheet = easyexcel.writersheet(sheetno, sheetname).build();

        long totaltoquery = end - start;
        int totalwritten = 0;

        // 分批查询并写入，每批查询后立即写入，不缓存大量数据
        for (long i = 0; i < totaltoquery; i += query_batch_size) {
            long currentstart = start + i;
            long currentend = math.min(start + i + query_batch_size, end);

            // 调用userservice查询数据
            list<user> batchdata = userservice.findusersbyrange(currentstart, currentend);

            if (batchdata == null || batchdata.isempty()) {
                log.info("{} - {} 范围没有数据", currentstart, currentend);
                break; // 没有更多数据，提前退出
            }

            // 直接写入这一批数据
            excelwriter.write(batchdata, writesheet);
            totalwritten += batchdata.size();

            log.info("sheet {} 已写入 {} - {} 范围的数据，累计 {} 条",
                    sheetname, currentstart, currentend, totalwritten);

            // 清除引用，帮助gc
            batchdata = new arraylist<>();
        }

        log.info("sheet {} 写入完成，共 {} 条数据", sheetname, totalwritten);
    }
}

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;

/**
 * 用户数据导入监听器（独立类实现）
 */
@slf4j
public class userimportlistener extends analysiseventlistener<user> {

    // 批量保存阈值（可根据内存调整）
    private static final int batch_size = 5000;
    
    // 临时存储批次数据
    private final list<user> batchlist = new arraylist<>(batch_size);
    
    // 导入结果统计
    private final atomiclong successcount = new atomiclong(0);
    private final atomiclong failcount = new atomiclong(0);
    
    // 业务服务（通过构造器注入）
    private final userservice userservice;

    public userimportlistener(userservice userservice) {
        this.userservice = userservice;
    }

    /**
     * 每读取一行数据触发
     */
    @override
    public void invoke(user user, analysiscontext context) {
        // 数据验证
        if (validateuser(user)) {
            batchlist.add(user);
            successcount.incrementandget();
            
            // 达到批次大小则保存
            if (batchlist.size() >= batch_size) {
                savebatchdata();
                // 清空列表释放内存
                batchlist.clear();
            }
        } else {
            failcount.incrementandget();
            log.warn("数据验证失败: {}", user);
        }
    }

    /**
     * 所有数据读取完成后触发
     */
    @override
    public void doafterallanalysed(analysiscontext context) {
        // 处理剩余数据
        if (!batchlist.isempty()) {
            savebatchdata();
            batchlist.clear();
        }
        log.info("当前sheet导入结束，成功: {}, 失败: {}", successcount.get(), failcount.get());
    }

    /**
     * 批量保存数据
     */
    private void savebatchdata() {
        try {
            // 调用业务层批量保存（带事务）
            userservice.batchsaveusers(batchlist);
            log.debug("批量保存成功，数量: {}", batchlist.size());
        } catch (exception e) {
            log.error("批量保存失败，数量: {}", batchlist.size(), e);
            // 失败处理：可记录失败数据到文件或数据库
            handlesavefailure(batchlist);
        }
    }

    /**
     * 数据验证逻辑
     */
    private boolean validateuser(user user) {
        // 基础字段验证（根据实际业务调整）
        if (user == null) return false;
        if (user.getid() == null) return false;
        if (user.getname() == null || user.getname().trim().isempty()) return false;
        return true;
    }

    /**
     * 处理保存失败的数据
     */
    private void handlesavefailure(list<user> faileddata) {
        // 实现失败数据的处理逻辑（例如写入失败日志表）
        // userservice.savefaileddata(faileddata);
    }

    // getter方法用于统计结果
    public long getsuccesscount() {
        return successcount.get();
    }

    public long getfailcount() {
        return failcount.get();
    }
}

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数据导入服务
 */
@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;

    }

    /**
     * 多线程导入百万级用户数据（每个sheet一个线程）
     */
    public void importmillionusers(multipartfile file) throws ioexception {
        // 1. 保存成临时文件，避免多线程共用 inputstream
        java.io.file tmpfile = java.io.file.createtempfile("excel_", ".xlsx");
        file.transferto(tmpfile);          // spring 提供的零拷贝
        tmpfile.deleteonexit();            // jvm 退出时自动清理

        exceltypeenum exceltype = getexceltype(file.getoriginalfilename());

        // 2. 拿 sheet 数量
        int sheetcount;
        try (inputstream in = new java.io.fileinputstream(tmpfile)) {
            sheetcount = getsheetcount(in);
        }
        log.info("开始导入，总 sheet 数: {}", sheetcount);

        // 3. 并发读，每个 sheet 独立 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("全部导入完成，总成功: {}，总失败: {}", totalsuccess.get(), totalfail.get());
    }

    /**
     * 获取excel中的sheet数量
     */
    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;
    }


}

 @postmapping("/import")
@apioperation("导入用户数据")
public responseentity<string> importusers(@requestparam("file") multipartfile file) {
    try {
        if (file.isempty()) {
            return responseentity.badrequest().body("请选择要导入的文件");
        }

        string filename = file.getoriginalfilename();
        exceltypeenum exceltype = importservice.getexceltype(filename);
        if (exceltype == null) {
            return responseentity.badrequest().body("不支持的文件类型，文件名：" +  filename);
        }

        importservice.importmillionusers(file);
        return responseentity.ok("文件导入成功，正在后台处理数据");
    } catch (exception e) {
        log.error("导入用户数据失败", e);
        return responseentity.status(httpstatus.internal_server_error)
                .body("导入失败：" + e.getmessage());
    }
}

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