Java 大数据量 Excel 导入导出实现方案

<?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>
    <groupId>com.example</groupId>
    <artifactId>excel-demo</artifactId>
    <version>1.0.0</version>
    <packaging>jar</packaging>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.10</version>
    </parent>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!-- Spring Boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- Apache POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
        <!-- EasyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- MyBatis Plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>
        <!-- 验证依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

2. 实体类 (User.java)

package com.example.entity;

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.util.Date;

@Data
@TableName("t_user")
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    
    @TableField("user_name")
    private String userName;
    
    @TableField("email")
    private String email;
    
    @TableField("phone")
    private String phone;
    
    @TableField("age")
    private Integer age;
    
    @TableField("department")
    private String department;
    
    @TableField("salary")
    private Double salary;
    
    @TableField(value = "create_time", fill = FieldFill.INSERT)
    private Date createTime;
    
    @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
}

3. 导入 DTO (ImportUserDTO.java)

package com.example.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import javax.validation.constraints.*;

@Data
public class ImportUserDTO {
    @ExcelProperty("用户名")
    @NotBlank(message = "用户名不能为空")
    @Size(min = 2, max = 20, message = "用户名长度必须在 2-20 之间")
    private String userName;
    
    @ExcelProperty("邮箱")
    @NotBlank(message = "邮箱不能为空")
    @Email(message = "邮箱格式不正确")
    private String email;
    
    @ExcelProperty("手机号")
    @NotBlank(message = "手机号不能为空")
    @Pattern(regexp = "^1[3-9]\\d{9}$", message = "手机号格式不正确")
    private String phone;
    
    @ExcelProperty("年龄")
    @NotNull(message = "年龄不能为空")
    @Min(value = 18, message = "年龄必须大于等于 18 岁")
    @Max(value = 65, message = "年龄必须小于等于 65 岁")
    private Integer age;
    
    @ExcelProperty("部门")
    @NotBlank(message = "部门不能为空")
    private String department;
    
    @ExcelProperty("薪资")
    @NotNull(message = "薪资不能为空")
    @DecimalMin(value = "0.0", message = "薪资必须大于等于 0")
    private Double salary;

    // 行号，用于错误定位
    private Integer rowIndex;
    
    // 导入结果信息
    private String errorMsg;
    
    // 是否验证通过
    private Boolean valid = true;
}

4. 导入结果类 (ExcelImportResult.java)

package com.example.dto;

import lombok.Data;
import java.util.List;

@Data
public class ExcelImportResult<T> {
    // 成功数量
    private int successCount;
    // 失败数量
    private int failCount;
    // 总数量
    private int totalCount;
    // 失败数据列表
    private List<T> failDataList;
    // 错误文件下载路径
    private String errorFileUrl;
    // 导入耗时（毫秒）
    private Long costTime;
    // 错误信息汇总
    private String errorSummary;

    public ExcelImportResult() {
        this.costTime = 0L;
    }

    public void calculate() {
        this.totalCount = successCount + failCount;
    }
}

5. Excel 监听器 (UserExcelListener.java)

package com.example.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.example.dto.ImportUserDTO;
import com.example.service.ExcelService;
import lombok.extern.slf4j.Slf4j;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

@Slf4j
public class UserExcelListener implements ReadListener<ImportUserDTO> {
    // 每隔 1000 条存储数据库，然后清理 list，方便内存回收
    private static final int BATCH_COUNT = 1000;
    // 缓存的数据
    private List<ImportUserDTO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private final ExcelService excelService;
    // 验证器
    private final Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
    // 错误数据列表
    private final List<ImportUserDTO> errorDataList = new ArrayList<>();
    // 成功计数
    private int successCount = 0;

    public UserExcelListener(ExcelService excelService) {
        this.excelService = excelService;
    }

    @Override
    public void invoke(ImportUserDTO data, AnalysisContext context) {
        // 设置行号（从 0 开始，通常第 0 行是标题）
        Integer rowIndex = context.readRowHolder().getRowIndex() + 1;
        data.setRowIndex(rowIndex);
        // 数据验证
        validateData(data);
        if (data.getValid()) {
            cachedDataList.add(data);
            // 达到 BATCH_COUNT，存储一次数据库
            if (cachedDataList.size() >= BATCH_COUNT) {
                saveData();
                cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            }
        } else {
            errorDataList.add(data);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 处理最后一批数据
        if (!cachedDataList.isEmpty()) {
            saveData();
        }
        log.info("Excel 解析完成，成功导入{}条数据，失败{}条数据", successCount, errorDataList.size());
    }

    private void validateData(ImportUserDTO data) {
        Set<ConstraintViolation<ImportUserDTO>> violations = validator.validate(data);
        if (!violations.isEmpty()) {
            data.setValid(false);
            StringBuilder errorMsg = new StringBuilder();
            for (ConstraintViolation<ImportUserDTO> violation : violations) {
                errorMsg.append(violation.getMessage()).append("; ");
            }
            data.setErrorMsg(errorMsg.toString());
        }
    }

    private void saveData() {
        try {
            // 批量保存数据
            int batchSuccessCount = excelService.batchSaveUsers(cachedDataList);
            successCount += batchSuccessCount;
            log.info("批量保存{}条数据成功", batchSuccessCount);
        } catch (Exception e) {
            log.error("批量保存数据失败", e);
            // 记录失败的数据
            for (ImportUserDTO data : cachedDataList) {
                data.setValid(false);
                data.setErrorMsg("系统错误：" + e.getMessage());
                errorDataList.add(data);
            }
        }
    }

    public List<ImportUserDTO> getErrorDataList() {
        return errorDataList;
    }

    public int getSuccessCount() {
        return successCount;
    }
}

6. Service 层 (ExcelService.java & ExcelServiceImpl.java)

package com.example.service;

import com.example.dto.ExcelImportResult;
import com.example.dto.ImportUserDTO;
import com.example.entity.User;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

public interface ExcelService {
    /**
     * 导入用户数据
     */
    ExcelImportResult<ImportUserDTO> importUsers(MultipartFile file);

    /**
     * 批量保存用户
     */
    int batchSaveUsers(List<ImportUserDTO> userList);

    /**
     * 导出用户数据
     */
    void exportUsers(HttpServletResponse response);

    /**
     * 导出错误数据
     */
    void exportErrorData(List<ImportUserDTO> errorDataList, HttpServletResponse response);
}

package com.example.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.example.dao.UserDao;
import com.example.dto.ExcelImportResult;
import com.example.dto.ImportUserDTO;
import com.example.entity.User;
import com.example.listener.UserExcelListener;
import com.example.service.ExcelService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.CompletableFuture;

@Service
@Slf4j
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    private UserDao userDao;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ExcelImportResult<ImportUserDTO> importUsers(MultipartFile file) {
        long startTime = System.currentTimeMillis();
        ExcelImportResult<ImportUserDTO> result = new ExcelImportResult<>();
        try {
            // 创建监听器
            UserExcelListener listener = new UserExcelListener(this);
            // 读取 Excel
            EasyExcel.read(file.getInputStream(), ImportUserDTO.class, listener).sheet().headRowNumber(1)
                    // 跳过标题行
                    .doRead();
            // 设置结果
            result.setSuccessCount(listener.getSuccessCount());
            result.setFailCount(listener.getErrorDataList().size());
            result.setFailDataList(listener.getErrorDataList());
            result.calculate();
            // 如果有错误数据，生成错误报告
            if (!listener.getErrorDataList().isEmpty()) {
                String errorSummary = generateErrorSummary(listener.getErrorDataList());
                result.setErrorSummary(errorSummary);
            }
        } catch (IOException e) {
            log.error("读取 Excel 文件失败", e);
            result.setErrorSummary("文件读取失败：" + e.getMessage());
        }
        long endTime = System.currentTimeMillis();
        result.setCostTime(endTime - startTime);
        return result;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int batchSaveUsers(List<ImportUserDTO> userList) {
        if (userList == null || userList.isEmpty()) {
            return 0;
        }
        List<User> users = new ArrayList<>();
        for (ImportUserDTO dto : userList) {
            User user = new User();
            BeanUtils.copyProperties(dto, user);
            user.setCreateTime(new Date());
            user.setUpdateTime(new Date());
            users.add(user);
        }
        // 批量插入
        int count = userDao.batchInsert(users);
        log.info("批量插入{}条用户数据", count);
        return count;
    }

    @Override
    public void exportUsers(HttpServletResponse response) {
        try {
            // 查询数据
            List<User> userList = userDao.selectList(new QueryWrapper<>());
            // 转换为 DTO
            List<ImportUserDTO> exportData = new ArrayList<>();
            for (User user : userList) {
                ImportUserDTO dto = new ImportUserDTO();
                BeanUtils.copyProperties(user, dto);
                exportData.add(dto);
            }
            // 设置响应头
            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");
            // 写入 Excel
            EasyExcel.write(response.getOutputStream(), ImportUserDTO.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    // 自动列宽
                    .sheet("用户数据").doWrite(exportData);
        } catch (IOException e) {
            log.error("导出 Excel 失败", e);
            throw new RuntimeException("导出失败");
        }
    }

    @Override
    public void exportErrorData(List<ImportUserDTO> errorDataList, HttpServletResponse response) {
        try {
            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");
            EasyExcel.write(response.getOutputStream(), ImportUserDTO.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet("错误数据").doWrite(errorDataList);
        } catch (IOException e) {
            log.error("导出错误数据失败", e);
            throw new RuntimeException("导出失败");
        }
    }

    /**
     * 异步导入方法
     */
    @Async
    public CompletableFuture<ExcelImportResult<ImportUserDTO>> importUsersAsync(MultipartFile file) {
        return CompletableFuture.completedFuture(importUsers(file));
    }

    private String generateErrorSummary(List<ImportUserDTO> errorDataList) {
        StringBuilder summary = new StringBuilder();
        summary.append("共发现").append(errorDataList.size()).append("条错误数据：\n");
        for (ImportUserDTO data : errorDataList) {
            summary.append("第").append(data.getRowIndex()).append("行：").append(data.getErrorMsg()).append("\n");
        }
        return summary.toString();
    }
}

7. DAO 层 (UserDao.java)

package com.example.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;

@Mapper
public interface UserDao extends BaseMapper<User> {
    /**
     * 批量插入用户
     */
    @Insert({"<script>",
            "INSERT INTO t_user(user_name, email, phone, age, department, salary, create_time, update_time) VALUES",
            "<foreach collection='list' item='item' separator=','>",
            "(#{item.userName}, #{item.email}, #{item.phone}, #{item.age},",
            "#{item.department}, #{item.salary}, #{item.createTime}, #{item.updateTime})",
            "</foreach>",
            "</script>"})
    int batchInsert(@Param("list") List<User> userList);
}

8. Controller 层 (ExcelController.java)

package com.example.controller;

import com.example.dto.ExcelImportResult;
import com.example.dto.ImportUserDTO;
import com.example.service.ExcelService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.HashMap;
import java.util.Map;

@RestController
@RequestMapping("/api/excel")
@Api(tags = "Excel 导入导出管理")
@Slf4j
public class ExcelController {
    @Autowired
    private ExcelService excelService;

    @PostMapping("/import/users")
    @ApiOperation("导入用户数据")
    public ResponseEntity<Map<String, Object>> importUsers(@RequestParam("file") MultipartFile file) {
        Map<String, Object> result = new HashMap<>();
        try {
            // 检查文件
            if (file.isEmpty()) {
                result.put("code", 400);
                result.put("message", "文件不能为空");
                return ResponseEntity.badRequest().body(result);
            }
            // 检查文件类型
            String fileName = file.getOriginalFilename();
            if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase().endsWith(".xlsx"))) {
                result.put("code", 400);
                result.put("message", "只支持 Excel 文件");
                return ResponseEntity.badRequest().body(result);
            }
            // 执行导入
            ExcelImportResult<ImportUserDTO> importResult = excelService.importUsers(file);
            result.put("code", 200);
            result.put("message", "导入完成");
            result.put("data", importResult);
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            log.error("导入用户数据失败", e);
            result.put("code", 500);
            result.put("message", "导入失败：" + e.getMessage());
            return ResponseEntity.internalServerError().body(result);
        }
    }

    @GetMapping("/export/users")
    @ApiOperation("导出用户数据")
    public void exportUsers(HttpServletResponse response) {
        try {
            excelService.exportUsers(response);
        } catch (Exception e) {
            log.error("导出用户数据失败", e);
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
        }
    }

    @PostMapping("/export/errors")
    @ApiOperation("导出错误数据")
    public void exportErrorData(@RequestBody ExcelImportResult<ImportUserDTO> importResult, HttpServletResponse response) {
        try {
            excelService.exportErrorData(importResult.getFailDataList(), response);
        } catch (Exception e) {
            log.error("导出错误数据失败", e);
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
        }
    }

    @GetMapping("/template/download")
    @ApiOperation("下载导入模板")
    public void downloadTemplate(HttpServletResponse response) {
        try {
            // 这里可以提供一个空模板或者带说明的模板
            excelService.exportUsers(response); // 暂时用导出方法，实际应创建专门的模板
        } catch (Exception e) {
            log.error("下载模板失败", e);
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
        }
    }
}

9. 启动类 (ExcelApplication.java)

package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableAsync;

@SpringBootApplication
@EnableAsync
public class ExcelApplication {
    public static void main(String[] args) {
        SpringApplication.run(ExcelApplication.class, args);
    }
}

10. 配置文件 (application.properties)

# 服务器配置
server.port=8080
server.servlet.context-path=/excel-demo

# 数据库配置
spring.datasource.url=jdbc:mysql://localhost:3306/excel_demo?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# MyBatis 配置
mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.example.entity
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

# 上传文件大小限制
spring.servlet.multipart.max-file-size=100MB
spring.servlet.multipart.max-request-size=100MB

# 异步线程池配置
spring.task.execution.pool.core-size=10
spring.task.execution.pool.max-size=20
spring.task.execution.pool.queue-capacity=500
spring.task.execution.thread-name-prefix=excel-async-

# 日志配置
logging.level.com.example=DEBUG

11. SQL 建表语句

-- 创建数据库
CREATE DATABASE IF NOT EXISTS excel_demo DEFAULT CHARACTER SET utf8mb4;
USE excel_demo;

-- 用户表
CREATE TABLE IF NOT EXISTS t_user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
    user_name VARCHAR(50) NOT NULL COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    phone VARCHAR(20) NOT NULL COMMENT '手机号',
    age INT COMMENT '年龄',
    department VARCHAR(100) COMMENT '部门',
    salary DECIMAL(10,2) COMMENT '薪资',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_user_name (user_name),
    INDEX idx_email (email),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 导入日志表（可选）
CREATE TABLE IF NOT EXISTS t_import_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
    file_name VARCHAR(255) COMMENT '文件名',
    file_size BIGINT COMMENT '文件大小',
    total_count INT COMMENT '总记录数',
    success_count INT COMMENT '成功数',
    fail_count INT COMMENT '失败数',
    import_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '导入时间',
    operator VARCHAR(50) COMMENT '操作人',
    status TINYINT DEFAULT 1 COMMENT '状态：1-成功，0-失败',
    error_message TEXT COMMENT '错误信息'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='导入日志表';

四、使用示例

1. 启动应用

mvn spring-boot:run

2. 导入 Excel

• 使用 Postman 或前端上传 Excel 文件
• 文件格式要求：包含 userName, email, phone, age, department, salary 列
• 接口地址：POST http://localhost:8080/excel-demo/api/excel/import/users

3. 导出 Excel

• 接口地址：GET http://localhost:8080/excel-demo/api/excel/export/users

五、优化建议

1. 性能优化

• 使用数据库连接池
• 调整批量提交大小
• 使用多线程并行处理
• 添加 Redis 缓存

2. 功能增强

• 支持多种 Excel 格式
• 添加数据清洗功能
• 支持模板自定义
• 添加导入进度查询

3. 安全性考虑

• 文件类型验证
• 文件大小限制
• 病毒扫描
• 访问权限控制

六、注意事项

1. 内存管理：大数据量导入时，注意 JVM 内存设置
2. 事务控制：批量导入要考虑事务边界
3. 异常处理：完善的异常处理和日志记录
4. 数据验证：前端和后端双重验证
5. 用户体验：提供进度反馈和错误报告