Spring Boot 3 + EasyExcel 文件導入導出實現

SpringBoot集成EasyExcel 3.x：高效實現Excel數據的優雅導入與導出

在現代企業應用中，Excel作為數據交換的重要工具，幾乎無處不在。如何高效且優雅地實現Excel數據的導入與導出，是每個開發者都需要面對的問題。EasyExcel是阿里巴巴開源的一個高性能Excel處理庫，它可以大大簡化Excel操作。在本文中，我將介紹如何在SpringBoot項目中集成EasyExcel 3.x，并實現Excel數據的導入與導出。

1. 項目依賴配置 (pom.xml)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>3.2.0</version><relativePath/></parent><groupId>com.example</groupId><artifactId>excel-demo</artifactId><version>1.0.0</version><properties><java.version>17</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-validation</artifactId></dependency></dependencies>
</project>

2. 實體類定義

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.Pattern;
import jakarta.validation.constraints.Size;
import lombok.Data;/*** @Description: 用于Excel導入導出數據映射* @date 2025/9/15 15:34*/
@Data
public class TerminalImportVO {@ExcelProperty(value = "IMEI", index = 0)@NotBlank(message = "IMEI不能為空")@Size(min = 3, max = 20, message = "IMEI必須為3-15位數字")@Pattern(regexp = "\\d+", message = "IMEI必須為數字")@ColumnWidth(20)private String imei;
}

3. Excel監聽器（用于導入數據處理）

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.cetcnav.entity.TerminalInfo;
import com.cetcnav.exception.ExcelImportException;
import com.cetcnav.service.TerminalInfoService;
import com.cetcnav.vo.TerminalImportVO;
import jakarta.validation.ConstraintViolation;
import jakarta.validation.Validation;
import jakarta.validation.Validator;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;import java.util.ArrayList;
import java.util.List;
import java.util.Set;/*** @Description: Excel導入數據監聽器 處理終端數據的讀取和驗證* @date 2025-09-15 15:37*/
@Slf4j
public class TerminalExcelListener implements ReadListener<TerminalImportVO> {/*** 每隔100條存儲數據庫，然后清理list，方便內存回收*/private static final int BATCH_COUNT = 100;/*** 緩存的數據*/private List<TerminalInfo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);private final TerminalInfoService terminalInfoService;public TerminalExcelListener(TerminalInfoService terminalInfoService) {this.terminalInfoService = terminalInfoService;}/*** 驗證器*/private final Validator validator = Validation.buildDefaultValidatorFactory().getValidator();/*** 錯誤信息收集*/private final List<String> errors = new ArrayList<>();/*** 總行數（包括表頭）*/@Getterprivate int totalRowNum = 0;/*** 成功處理的數據行數* -- GETTER --* 獲取成功處理的數量*/@Getterprivate int successCount = 0;/*** 是否已經拋出異常（防止重復拋出）*/private boolean hasThrownException = false;/*** 每一條數據解析都會來調用*/@Overridepublic void invoke(TerminalImportVO terminalImportVO, AnalysisContext analysisContext) {totalRowNum++;// Excel行號從1開始int currentRowNum = analysisContext.readRowHolder().getRowIndex() + 1;log.info("解析到第{}行數據: {}", currentRowNum, terminalImportVO);try {// 1. 基本數據校驗if (terminalImportVO.getImei() == null) {errors.add(String.format("第%d行%s", currentRowNum, "終端imei不能為空"));return;}// 2. 數據校驗 注解校驗Set<ConstraintViolation<TerminalImportVO>> violations = validator.validate(terminalImportVO);if (!violations.isEmpty()) {for (ConstraintViolation<TerminalImportVO> violation : violations) {String fieldName = violation.getPropertyPath().toString();String errorMsg = violation.getMessage();errors.add(String.format("第%d行%s", currentRowNum, errorMsg));}// 校驗失敗，跳過該行數據return;}// 3. 業務邏輯校驗if (!validateBusinessRules(terminalImportVO, currentRowNum)) {return;}// 4. 數據去重校驗（防止重復導入）if (isDuplicateUser(terminalImportVO, currentRowNum)) {return;}log.info("第{}行數據校驗通過", currentRowNum);// 校驗通過，添加到緩存列表TerminalInfo terminalInfo = new TerminalInfo();BeanUtils.copyProperties(terminalImportVO, terminalInfo);cachedDataList.add(terminalInfo);successCount++;// 達到BATCH_COUNT了，需要去存儲一次數據庫，防止數據幾萬條數據在內存，容易OOMif (cachedDataList.size() >= BATCH_COUNT) {saveData();// 存儲完成清理 listcachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}} catch (Exception e) {errors.add(String.format("第%d行%s", currentRowNum, "數據處理異常"));log.error("第{}行數據處理異常", currentRowNum);}}/*** 所有數據解析完成后會來調用** @param analysisContext 分析上下文*/@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {try {// 這里也要保存數據，確保最后遺留的數據也存儲到數據庫saveData();log.info("Excel解析完成！總行數: {}, 成功: {}, 失敗: {}", totalRowNum - 1, successCount, errors.size());// 如果有錯誤，拋出包含所有錯誤信息的異常if (!errors.isEmpty() && !hasThrownException) {hasThrownException = true;// 減1是排除表頭行throw new ExcelImportException(errors, totalRowNum - 1);}} catch (Exception e) {log.error("數據最終處理異常：{}", e.getMessage());if (!hasThrownException) {hasThrownException = true;throw new RuntimeException("數據處理異常: " + e.getMessage());}}}/*** 保存數據到數據庫*/private void saveData() {if (cachedDataList.isEmpty()) {return;}try {log.info("開始保存{}條數據到數據庫", cachedDataList.size());terminalInfoService.saveBatch(cachedDataList);log.info("存儲數據庫成功！");} catch (Exception e) {log.error("數據保存失敗:{}", e);// 記錄批量保存失敗的錯誤for (TerminalInfo terminalInfo : cachedDataList) {int estimatedRowNum = totalRowNum - cachedDataList.size() + cachedDataList.indexOf(terminalInfo) + 1;errors.add(String.format("第%d行%s", estimatedRowNum, "數據保存失敗"));}// 從成功計數中減去這些失敗的數據successCount -= cachedDataList.size();}}/*** 業務規則校驗** @param terminalImportVO 終端數據* @param rowNum           行號* @return 是否通過校驗*/private boolean validateBusinessRules(TerminalImportVO terminalImportVO, int rowNum) {LambdaQueryWrapper<TerminalInfo> queryWrapper = new LambdaQueryWrapper<>();queryWrapper.eq(TerminalInfo::getImei, terminalImportVO.getImei());queryWrapper.eq(TerminalInfo::getDelFlag, 0);TerminalInfo terminalInfo = terminalInfoService.getOne(queryWrapper);if (ObjectUtil.isNotEmpty(terminalInfo)) {errors.add(String.format("第%d行%s", rowNum,  String.format("終端imei: %s 已存在！", terminalImportVO.getImei())));return false;}return true;}/*** 檢查是否重復終端imei** @param terminalImportVO 終端數據* @param rowNum           行號* @return 是否重復*/private boolean isDuplicateUser(TerminalImportVO terminalImportVO, int rowNum) {for (TerminalInfo terminalInfo : cachedDataList) {if (terminalInfo.getImei().equals(terminalImportVO.getImei())) {errors.add(String.format("第%d行%s", rowNum, "終端imei重復: " + terminalImportVO.getImei()));return true;}}return false;}/*** 獲取錯誤信息列表** @return 錯誤信息列表*/public List<String> getErrors() {return new ArrayList<>(errors);}/*** 獲取失敗數量** @return 失敗數量*/public int getFailedCount() {return errors.size();}
}

4. 控制器層

@GetMapping("/template")@Operation(summary = "下載導入模板")public void downloadTemplate(HttpServletResponse response) throws IOException {try {// 設置響應頭setExcelResponseHeader(response, "終端導入模板");// 創建一個空的用戶列表（只有表頭）List<TerminalImportVO> emptyList = List.of();// 寫入ExcelEasyExcel.write(response.getOutputStream(), TerminalImportVO.class).excelType(ExcelTypeEnum.XLSX).sheet("終端數據").doWrite(emptyList);log.info("模板下載成功");} catch (Exception e) {log.error("模板下載失敗", e);response.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");response.getWriter().println("{\"code\":500,\"message\":\"模板下載失敗:\" + e.getMessage() + \"\"}");}}/*** 導入Excel數據*/@PostMapping("/import")@Operation(summary = "導入Excel數據")public ApiResponse<ExcelImportResponse> importExcel(@RequestParam("file") MultipartFile file) throws IOException {if (file.isEmpty()) {return ApiResponse.error("文件為空！");}// 檢查文件類型String filename = file.getOriginalFilename();if (filename == null || (!filename.endsWith(".xlsx") && !filename.endsWith(".xls"))) {return ApiResponse.error("請上傳Excel文件(.xlsx或.xls)");}TerminalExcelListener listener = new TerminalExcelListener(terminalInfoService);try {// 讀取Excel文件EasyExcel.read(file.getInputStream(), TerminalImportVO.class, listener).sheet().doRead();// 如果沒有錯誤，返回成功響應ExcelImportResponse response = new ExcelImportResponse(listener.getTotalRowNum(), listener.getSuccessCount(), listener.getFailedCount(), listener.getErrors());return ApiResponse.success(response.getSummaryMessage(), response);} catch (ExcelImportException e) {// 捕獲自定義異常，返回詳細的錯誤信息ExcelImportResponse response = new ExcelImportResponse(e.getTotalRows(), e.getSuccessRows(), e.getFailedRows(), e.getErrors());return ApiResponse.error(response.getSummaryMessage(), HttpStatusConstant.ERROR, response);} catch (Exception e) {log.error("導入失敗", e);return ApiResponse.error("導入失敗: " + e.getMessage());}}/*** 導出Excel數據*/@GetMapping("/export")@Operation(summary = "導出Excel數據")public void exportExcel(HttpServletResponse response) throws IOException {try {// 設置響應頭setExcelResponseHeader(response, "終端數據導出");// 獲取數據List<TerminalInfo> terminalInfoList = terminalInfoService.list();// 寫入ExcelEasyExcel.write(response.getOutputStream(), TerminalInfo.class).excelType(ExcelTypeEnum.XLSX).sheet("終端數據").doWrite(terminalInfoList);log.info("導出成功，共{}條數據", terminalInfoList.size());} catch (Exception e) {log.error("導出失敗", e);response.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");response.getWriter().println("{\"code\":500,\"message\":\"導出失敗:\" + e.getMessage() + \"\"}");}}/*** 設置Excel響應頭*/private void setExcelResponseHeader(HttpServletResponse response, String rawFileName) throws IOException {String fileName = URLEncoder.encode(rawFileName, StandardCharsets.UTF_8).replaceAll("\\+", "%20");response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");}

5. 全局異常處理

import com.cetcnav.dto.ApiResponse;
import com.cetcnav.dto.ExcelImportResponse;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
import org.springframework.web.multipart.MaxUploadSizeExceededException;import java.util.List;
import java.util.stream.Collectors;/*** @Description: 全局異常處理器* @date 2025-09-15 14:59*/
@Slf4j
@RestControllerAdvice
public class GlobalExceptionHandler {/*** 處理Excel導入異常*/@ExceptionHandler(ExcelImportException.class)public ApiResponse<Object> handleExcelImportException(ExcelImportException e) {log.warn("Excel導入數據校驗失敗: {}", e.getErrorSummary());return ApiResponse.error(e.getMessage(), 400);}/*** 處理文件大小超過限制異常*/@ExceptionHandler(MaxUploadSizeExceededException.class)public ApiResponse<Object> handleMaxUploadSizeExceededException(MaxUploadSizeExceededException e) {log.warn("文件大小超過限制", e);return ApiResponse.error("文件大小不能超過10MB");}/*** 處理所有其他異常*/@ExceptionHandler(Exception.class)public ApiResponse<Object> handleException(Exception e) {log.error("系統異常", e);return ApiResponse.error("系統異常: " + e.getMessage());}
}

6.Excel導入響應對象

import lombok.Getter;
import java.util.List;/*** @Description: Excel導入響應對象* @date 2025-09-15 16:24*/
@Getter
public class ExcelImportException extends RuntimeException {private final List<String> errors;private final int totalRows;private final int successRows;private final int failedRows;public ExcelImportException(List<String> errors, int totalRows) {super("Excel導入完成，但有部分數據校驗失敗，請查看詳細信息");this.errors = errors;this.totalRows = totalRows;this.successRows = totalRows - errors.size();this.failedRows = errors.size();}/*** 獲取簡化的錯誤摘要信息（用于日志等）*/public String getErrorSummary() {if (errors.isEmpty()) {return "無錯誤";}StringBuilder summary = new StringBuilder();summary.append("共").append(errors.size()).append("處錯誤：");// 按錯誤類型統計errors.forEach(message -> summary.append(message).append("; "));return summary.toString();}
}