SpringBoot系列之集成EasyExcel实现百万级别的数据导入导出实践
Spring Boot 系列：集成 EasyExcel 实现百万级数据导入导出实战[*]SpringBoot与微服务实践系列博客
本文基于开源项目 springboot-easyexcel-batch 进行解析与扩展，手把手教你如何在 Spring Boot 2.2.1 中集成 Alibaba EasyExcel，轻松实现 百万级数据的导入与导出。
目录
[*]项目结构概览
[*]核心依赖
[*]百万级导出实战
[*]百万级导入实战
[*]性能优化技巧
[*]常见问题 & 解决方案
[*]总结
项目结构概览
springboot-easyexcel-batch
├── src/main/java/com/example/easyexcel
│ ├── controller/ # 导入导出接口
│ ├── listener/ # 导入监听器
│ ├── model/ # 实体类
│ ├── service/ # 业务逻辑
│ └── Application.java # 启动类
└── src/main/resources
├── application.yml# 线程池配置
└── templates/ # 前端demo核心依赖
<parent>
<groupId>org.springframework.boot</groupId>
spring-boot-starter-parent</artifactId>
<version>2.2.1.RELEASE</version>
</parent>
<dependency>
<groupId>com.alibaba</groupId>
easyexcel</artifactId>
<version>2.2.11</version>
</dependency>百万级导出实战
1️⃣ 场景
需求数据量策略导出用户表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;
// 每个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);
}
}3️⃣ 效果
指标优化前优化后内存峰值1.2GB100MB耗时45s18s百万级导入实战
1️⃣ 场景
需求数据量策略导入用户表100万+分Sheet + 监听器 + 批量插入2️⃣ 监听器和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;
/**
* 用户数据导入监听器（独立类实现）
*/
@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();
}
}导入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数据导入服务
*/
@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)).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;
}
}3️⃣ Controller
@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());
}
}性能优化技巧
技巧说明分批查询避免一次性加载全表分批写入每5k条批量插入临时文件并发读时先 MultipartFile.transferTo(tmp)线程池配置专用线程池，隔离业务线程# application.yml
spring:
task:
execution:
pool:
core-size: 10
max-size: 30
queue-capacity: 1000常见问题 & 解决方案
问题解决方案Can not create temporary file!并发读时先保存临时文件，再独立流读取Stream Closed每个任务独立 InputStreamOutOfMemoryError分批处理 + 及时 clear()总结
✅ Spring Boot + EasyExcel 在 零侵入 的情况下即可完成百万级数据的导入导出。
✅ 通过 分批、并发、顺序写 等技巧，内存占用降低 90% 以上。
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]