Excel导入导出详解（POI、EasyExcel、大数据量优化、异步导出、模板填充）

---

前言

Excel导入导出是后台系统的常见功能。为什么POI导出100万数据会OOM？如何优化Excel导入性能？EasyExcel和POI有什么区别？如何实现复杂的Excel模板？ 理解POI的内存模型、EasyExcel的事件驱动、分批读取的内存优化、模板填充的灵活性、异步导出的用户体验，才能掌握Excel处理的本质。

摘要

从"导出100万数据导致服务OOM"的故障出发，剖析Excel大数据量处理的核心方案。通过POI的全量加载问题、EasyExcel的流式读写、分批处理的内存优化、模板导出的复杂报表、异步导出的MQ解耦，揭秘从简单导出到企业级Excel服务的完整演进。配合详细代码实现与性能对比，给出Excel处理的最佳实践。

---

一、从导出OOM说起

周四下午，哈吉米的导出功能挂了：

场景1：导出100万数据OOM

用户操作：
  点击"导出全部用户数据"
  
服务器：
  查询100万用户
  写入Excel
  
结果：
  java.lang.OutOfMemoryError: Java heap space
  服务器OOM
  导出失败

哈吉米：“为什么会OOM？”

查看代码：

@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
    // 1. 查询所有数据（100万条）
    List<User> users = userMapper.selectAll();
    
    // 2. 创建Excel
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("用户数据");
    
    // 3. 写入数据
    int rowNum = 0;
    for (User user : users) {
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(user.getId());
        row.createCell(1).setCellValue(user.getUsername());
        row.createCell(2).setCellValue(user.getEmail());
        // ... 10个字段
    }
    
    // 4. 输出到响应流
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=users.xlsx");
    workbook.write(response.getOutputStream());
    workbook.close();
}

南北绿豆赶来：“POI全量加载到内存，100万数据撑爆了。”

哈吉米：“那怎么办？”

阿西噶阿西：“用EasyExcel，流式写入，内存占用小。来，我给你讲讲3种Excel处理方案。”

---

场景2：导出慢，用户等待30分钟

用户点击导出：
  14:30:00 点击
  15:00:00 下载完成
  
等待：30分钟

用户："我是要导出，不是要我等半小时啊！"

哈吉米：“导出太慢了，用户体验差。”

南北绿豆：“大数据量导出应该异步处理，用户不用等待。”

---

二、Excel处理的3种技术方案

2.1 方案1：POI（传统方案）

POI的内存模型：

POI的两种模式：

1. HSSF（.xls）
   - Excel 97-2003格式
   - 全量加载到内存
   - 最大65536行
   
2. XSSF（.xlsx）
   - Excel 2007+格式
   - 全量加载到内存
   - 无行数限制
   
问题：
  100万行 × 10列 × 100字节 ≈ 1GB内存
  → OOM

POI导出代码：

public void exportWithPOI(List<User> users, OutputStream outputStream) {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("用户");
    
    // 表头
    Row headerRow = sheet.createRow(0);
    headerRow.createCell(0).setCellValue("ID");
    headerRow.createCell(1).setCellValue("用户名");
    headerRow.createCell(2).setCellValue("邮箱");
    
    // 数据
    int rowNum = 1;
    for (User user : users) {
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(user.getId());
        row.createCell(1).setCellValue(user.getUsername());
        row.createCell(2).setCellValue(user.getEmail());
    }
    
    // 输出
    workbook.write(outputStream);
    workbook.close();
}

POI的优缺点：

优点：
  ✓ 功能强大（单元格样式、公式、图表）
  ✓ 支持复杂格式
  
缺点：
  ✗ 全量加载到内存（大数据OOM）
  ✗ 性能差（100万数据需要5分钟）
  ✗ API复杂

哈吉米：“POI适合小数据量（几千行），大数据量不行。”

---

2.2 方案2：EasyExcel（推荐）

EasyExcel的优势：

阿里开源的Excel处理工具

特点：
  - 流式写入（不全量加载）
  - 内存占用小（几十MB）
  - 性能高（100万数据1分钟）
  - API简单

引入依赖：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

EasyExcel导出：

@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
    // 1. 设置响应头
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("用户数据", "UTF-8");
    response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
    
    // 2. EasyExcel写入（流式，内存占用小）
    EasyExcel.write(response.getOutputStream(), User.class)
        .sheet("用户")
        .doWrite(this::queryUserList);  // 分批查询
}

/**
 * 分批查询数据（每次1000条）
 */
private List<User> queryUserList() {
    // 数据量大时，分批查询
    // 这里简化，实际应该用分页
    return userMapper.selectAll();
}

定义导出实体：

@Data
public class UserExportVO {
    
    @ExcelProperty("用户ID")
    private Long id;
    
    @ExcelProperty("用户名")
    private String username;
    
    @ExcelProperty("邮箱")
    private String email;
    
    @ExcelProperty(value = "注册时间", converter = DateConverter.class)
    private Date createTime;
}

EasyExcel流式写入原理：

传统POI：
  100万行数据 → 全部加载到内存 → 一次性写入文件
  内存占用：1GB
  
EasyExcel：
  查询1000行 → 写入文件 → 释放内存
  查询1000行 → 写入文件 → 释放内存
  ...
  内存占用：几十MB（固定）

EasyExcel分批写入示意图：

性能对比：

方案	100万数据	内存占用	耗时
POI	❌ OOM	1GB+	-
POI + SXSSFWorkbook	✅	200MB	5分钟
EasyExcel	✅	50MB	1分钟

南北绿豆：“EasyExcel性能好，内存占用小，是大数据量导出的首选。”

---

2.3 方案3：异步导出（用户体验最好）

原理：导出任务放到MQ，异步处理，完成后通知用户下载。

用户点击导出：

@PostMapping("/export/async")
public Result exportAsync() {
    Long userId = getCurrentUserId();
    
    // 1. 创建导出任务
    ExportTask task = new ExportTask();
    task.setUserId(userId);
    task.setStatus(ExportStatus.PENDING);
    task.setTaskNo(UUID.randomUUID().toString());
    exportTaskMapper.insert(task);
    
    // 2. 发送MQ消息
    ExportMessage msg = new ExportMessage();
    msg.setTaskNo(task.getTaskNo());
    msg.setUserId(userId);
    rabbitTemplate.convertAndSend("export.queue", msg);
    
    // 3. 立即返回（不等待）
    return Result.success("导出任务已提交，完成后将通知您");
}

后台异步处理：

@RabbitListener(queues = "export.queue")
public void handleExport(ExportMessage msg) {
    String taskNo = msg.getTaskNo();
    
    try {
        // 1. 更新任务状态：处理中
        exportTaskMapper.updateStatus(taskNo, ExportStatus.PROCESSING);
        
        // 2. 查询数据并导出（分批）
        String filePath = "/opt/export/" + taskNo + ".xlsx";
        
        EasyExcel.write(filePath, UserExportVO.class)
            .registerWriteHandler(new CustomCellWriteHandler())
            .sheet("用户")
            .doWrite(() -> {
                // 分批查询（每批10000条）
                return queryBatch();
            });
        
        // 3. 上传到OSS
        String ossUrl = ossService.upload(filePath);
        
        // 4. 更新任务状态：完成
        exportTaskMapper.updateStatus(taskNo, ExportStatus.COMPLETED);
        exportTaskMapper.updateUrl(taskNo, ossUrl);
        
        // 5. 通知用户（站内信、短信、邮件）
        notifyUser(msg.getUserId(), "导出完成，点击下载：" + ossUrl);
        
    } catch (Exception e) {
        // 6. 导出失败
        log.error("导出失败：taskNo={}", taskNo, e);
        exportTaskMapper.updateStatus(taskNo, ExportStatus.FAILED);
    }
}

前端查询导出进度：

// 轮询查询导出状态
async function checkExportStatus(taskNo) {
    const response = await axios.get('/api/export/status', {
        params: { taskNo }
    });
    
    const task = response.data;
    
    if (task.status === 'COMPLETED') {
        // 导出完成，显示下载链接
        showDownloadLink(task.url);
    } else if (task.status === 'FAILED') {
        // 导出失败
        alert('导出失败，请重试');
    } else {
        // 处理中，继续等待
        setTimeout(() => checkExportStatus(taskNo), 3000);  // 3秒后再查
    }
}

异步导出流程图：

异步导出的优势：

同步导出：
  用户等待：30分钟
  期间无法操作
  超时可能失败
  
异步导出：
  用户等待：1秒（提交任务）
  可以继续操作
  后台慢慢处理
  完成后通知下载
  
用户体验提升：100倍

哈吉米：“异步导出用户体验好，不用等待。”

---

三、EasyExcel核心功能

3.1 简单导出

最简单的导出：

@GetMapping("/export/simple")
public void simpleExport(HttpServletResponse response) throws IOException {
    // 1. 查询数据
    List<User> users = userMapper.selectList(null);
    
    // 2. 一行代码导出
    EasyExcel.write(response.getOutputStream(), User.class)
        .sheet("用户")
        .doWrite(users);
}

自定义表头：

@Data
public class UserExportVO {
    
    @ExcelProperty(value = "用户编号", index = 0)
    private Long id;
    
    @ExcelProperty(value = "用户姓名", index = 1)
    private String username;
    
    @ExcelProperty(value = {"基本信息", "邮箱地址"}, index = 2)  // 多级表头
    private String email;
    
    @ExcelProperty(value = {"基本信息", "手机号码"}, index = 3)
    private String phone;
}

// 表头效果：
// |  用户编号  |  用户姓名  |    基本信息     |
// |          |          | 邮箱 | 手机号 |

---

3.2 分批导出（大数据量）

原理：分批查询，流式写入。

@GetMapping("/export/batch")
public void batchExport(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-Disposition", "attachment; filename=users.xlsx");
    
    // 使用PageReadHandler分批读取
    EasyExcel.write(response.getOutputStream(), UserExportVO.class)
        .registerWriteHandler(new CustomCellWriteHandler())
        .sheet("用户")
        .doWrite(new PageReadHandler());
}

/**
 * 分批查询（每批10000条）
 */
class PageReadHandler implements ReadListener<UserExportVO> {
    
    private static final int BATCH_SIZE = 10000;
    private int currentPage = 1;
    
    @Override
    public List<UserExportVO> getData() {
        // 分页查询
        Page<User> page = userMapper.selectPage(
            new Page<>(currentPage++, BATCH_SIZE),
            null
        );
        
        if (page.getRecords().isEmpty()) {
            return Collections.emptyList();  // 无数据，结束
        }
        
        // 转换为导出VO
        return page.getRecords().stream()
            .map(this::convert)
            .collect(Collectors.toList());
    }
}

分批导出流程：

第1批：
  查询：SELECT * FROM user LIMIT 0, 10000
  写入：10000行
  内存：50MB
  释放内存
  
第2批：
  查询：SELECT * FROM user LIMIT 10000, 10000
  写入：10000行
  内存：50MB
  释放内存
  
...

第100批：
  查询：SELECT * FROM user LIMIT 990000, 10000
  写入：10000行
  
完成：100万行导出，内存占用始终50MB

南北绿豆：“分批查询 + 流式写入，内存占用固定，不会OOM。”

---

3.3 自定义样式

自定义单元格样式：

public class CustomCellWriteHandler implements CellWriteHandler {
    
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Cell cell = context.getCell();
        Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
        
        // 创建样式
        CellStyle style = workbook.createCellStyle();
        
        // 设置字体
        Font font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 12);
        
        // 表头加粗
        if (context.getRowIndex() == 0) {
            font.setBold(true);
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        
        // 数字右对齐
        if (cell.getCellType() == CellType.NUMERIC) {
            style.setAlignment(HorizontalAlignment.RIGHT);
        }
        
        style.setFont(font);
        cell.setCellStyle(style);
    }
}

// 使用
EasyExcel.write(outputStream, UserExportVO.class)
    .registerWriteHandler(new CustomCellWriteHandler())
    .sheet("用户")
    .doWrite(users);

---

3.4 模板导出

原理：提前制作Excel模板，填充数据。

模板文件（template.xlsx）：

|  用户报表  |        |        |
|  统计日期：{date}  |        |
|  总用户数：{total} |        |
|------|--------|--------|
|  ID  | 用户名  | 邮箱   |
|------|--------|--------|
|  {.id}  | {.username} | {.email} |  ← 数据占位符

填充模板：

public void exportWithTemplate(HttpServletResponse response) throws IOException {
    // 1. 模板文件路径
    String templatePath = "/templates/user_template.xlsx";
    
    // 2. 准备数据
    Map<String, Object> header = new HashMap<>();
    header.put("date", LocalDate.now().toString());
    header.put("total", userMapper.count());
    
    List<User> users = userMapper.selectList(null);
    
    // 3. 填充模板
    EasyExcel.write(response.getOutputStream())
        .withTemplate(templatePath)
        .sheet()
        .doFill(new FillWrapper("header", Collections.singletonList(header)))
        .doFill(new FillWrapper("data", users));
}

复杂报表示例：

财务报表模板：
  - 表头：公司logo、报表名称、统计日期
  - 汇总：总收入、总支出、净利润
  - 明细：订单列表
  - 图表：收入趋势图
  
填充：
  header：{companyName, reportDate, totalIncome, totalExpense}
  data：订单列表

阿西噶阿西：“模板导出适合复杂报表，保持格式一致。”

---

四、Excel导入

4.1 简单导入

EasyExcel导入：

@PostMapping("/import")
public Result importUsers(@RequestParam("file") MultipartFile file) throws IOException {
    // 监听器
    UserImportListener listener = new UserImportListener(userService);
    
    // 读取Excel
    EasyExcel.read(file.getInputStream(), UserImportVO.class, listener)
        .sheet()
        .doRead();
    
    return Result.success("导入成功：" + listener.getSuccessCount() + "条");
}

导入监听器：

public class UserImportListener implements ReadListener<UserImportVO> {
    
    private static final int BATCH_SIZE = 1000;
    
    private List<User> batch = new ArrayList<>(BATCH_SIZE);
    private UserService userService;
    private int successCount = 0;
    
    public UserImportListener(UserService userService) {
        this.userService = userService;
    }
    
    @Override
    public void invoke(UserImportVO data, AnalysisContext context) {
        // 1. 转换为User对象
        User user = convert(data);
        batch.add(user);
        
        // 2. 达到批次大小，批量保存
        if (batch.size() >= BATCH_SIZE) {
            saveBatch();
        }
    }
    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 3. 处理剩余数据
        saveBatch();
        log.info("导入完成，总计：{}条", successCount);
    }
    
    private void saveBatch() {
        if (batch.isEmpty()) {
            return;
        }
        
        // 批量保存
        userService.saveBatch(batch);
        successCount += batch.size();
        
        // 清空批次
        batch.clear();
    }
    
    public int getSuccessCount() {
        return successCount;
    }
}

导入流程图：

---

4.2 导入校验

数据校验：

@Data
public class UserImportVO {
    
    @ExcelProperty("用户名")
    @NotBlank(message = "用户名不能为空")
    @Length(min = 2, max = 20, message = "用户名长度2-20")
    private String username;
    
    @ExcelProperty("邮箱")
    @Email(message = "邮箱格式错误")
    private String email;
    
    @ExcelProperty("手机号")
    @Pattern(regexp = "^1[3-9]\\d{9}$", message = "手机号格式错误")
    private String phone;
}

监听器中校验：

@Override
public void invoke(UserImportVO data, AnalysisContext context) {
    // 1. JSR303校验
    Set<ConstraintViolation<UserImportVO>> violations = validator.validate(data);
    
    if (!violations.isEmpty()) {
        // 校验失败，记录错误
        int rowNum = context.readRowHolder().getRowIndex() + 1;
        String error = violations.stream()
            .map(ConstraintViolation::getMessage)
            .collect(Collectors.joining(", "));
        
        errors.add("第" + rowNum + "行：" + error);
        return;  // 跳过这行
    }
    
    // 2. 业务校验（用户名是否重复）
    if (userService.existsByUsername(data.getUsername())) {
        errors.add("第" + rowNum + "行：用户名已存在");
        return;
    }
    
    // 3. 校验通过，加入批次
    batch.add(convert(data));
}

导入结果返回：

@Data
public class ImportResult {
    private int totalCount;      // 总行数
    private int successCount;    // 成功导入
    private int failCount;       // 失败数量
    private List<String> errors; // 错误详情
}

// 返回
return Result.success(new ImportResult(
    totalCount, 
    successCount, 
    failCount, 
    errors
));

南北绿豆：“导入必须校验，避免脏数据入库。”

---

五、Excel处理总结

5.1 核心要点

南北绿豆总结：

1. POI：功能强大但内存占用大，适合小数据量
2. EasyExcel：流式处理，内存占用小，适合大数据量
3. 分批处理：分批查询 + 流式写入，避免OOM
4. 异步导出：MQ异步处理，用户无需等待
5. 模板导出：复杂报表，格式统一
6. 导入校验：JSR303 + 业务校验，保证数据质量

5.2 面试高频问题

阿西噶阿西：

问题1：如何导出100万数据不OOM？

方案：EasyExcel分批导出
  
实现：
  
1. 分批查询
   每次查询10000条（LIMIT）
   
2. 流式写入
   EasyExcel边查边写
   写完一批，释放内存
   
3. 内存占用
   固定50MB（不随数据量增长）
   
代码：
  EasyExcel.write(outputStream, User.class)
      .sheet("用户")
      .doWrite(() -> queryBatch());  // 分批查询
      
原理：
  不是一次性加载100万数据到内存
  而是分100次，每次1万条
  内存占用固定

问题2：POI和EasyExcel有什么区别？

POI：
  - Apache开源
  - 功能强大（样式、公式、图表）
  - 全量加载到内存（大数据OOM）
  - 性能差（100万数据5分钟）
  - API复杂
  - 适用：小数据量（<1万行）、复杂格式
  
EasyExcel：
  - 阿里开源
  - 功能够用（基本样式）
  - 流式处理（内存占用小）
  - 性能好（100万数据1分钟）
  - API简单
  - 适用：大数据量（>1万行）、简单格式
  
选择：
  - 数据量小，格式复杂 → POI
  - 数据量大 → EasyExcel

问题3：如何实现异步导出？

实现步骤：
  
1. 用户点击导出
   - 创建导出任务（状态：待处理）
   - 返回taskNo
   
2. 发送MQ消息
   - 消息内容：taskNo、userId、查询条件
   
3. Worker消费消息
   - 查询数据
   - 生成Excel
   - 上传到OSS
   - 更新任务状态（完成）
   
4. 通知用户
   - 站内信、短信、邮件
   - 提供下载链接
   
5. 用户下载
   - 点击链接，从OSS下载
   
优势：
  - 用户无需等待（秒级返回）
  - 后台慢慢处理（分钟级）
  - 用户体验好

问题4：Excel导入如何做数据校验？

3层校验：
  
1. 格式校验（EasyExcel自动）
   - 数字类型：只能是数字
   - 日期类型：只能是日期
   
2. 注解校验（JSR303）
   @NotBlank、@Email、@Pattern
   
3. 业务校验
   - 用户名是否重复
   - 关联数据是否存在
   - 业务规则（如余额不能为负）
   
处理：
  - 校验失败，记录错误信息
  - 跳过错误行，继续处理
  - 返回成功数和失败数

问题5：如何提升Excel导入性能？

优化方案：
  
1. 批量插入
   - 每1000行批量插入
   - 减少数据库IO
   
2. 异步处理
   - 大文件导入放MQ
   - 用户无需等待
   
3. 禁用索引
   - 导入前：ALTER TABLE user DISABLE KEYS
   - 导入后：ALTER TABLE user ENABLE KEYS
   
4. 事务批次
   - 每1万行提交一次
   - 避免大事务
   
5. 多线程处理
   - 文件切分
   - 多线程并行导入
   
性能提升：
  单线程：10万条/分钟
  优化后：100万条/分钟（提升10倍）

哈吉米：“掌握了Excel处理，数据导入导出就不是问题了。”

---