Excel导入导出完整方案：让数据流转如丝般顺滑！📊

标题： Excel还在用POI手写？EasyExcel来救场！
副标题： 从百万数据导出到模板校验，Excel处理全攻略

---

🎬 开篇：一次Excel导出的内存溢出

某电商平台订单导出功能：

运营：导出本月所有订单（100万条）
系统：开始导出... 💀
系统：内存占用飙升... 💀
系统：OutOfMemoryError！💥

原因分析：
- 使用POI一次性加载所有数据到内存
- 100万行 × 50列 = 海量对象
- JVM堆内存不足
- 系统崩溃

改用EasyExcel后：
运营：导出100万订单
系统：
  - 流式写入 ✅
  - 内存占用稳定在50MB ✅
  - 3分钟导出完成 ✅
  
老板：这才对嘛！ 😊

教训：大数据量Excel必须用流式处理！

🤔 为什么选择EasyExcel？

传统POI的问题：

• 内存占用大： 全量加载数据
• 处理速度慢： 大文件卡顿
• 代码复杂： 需要大量样板代码

EasyExcel的优势：

• 低内存： 100万数据只需几十MB
• 高性能： 流式读写
• 简单易用： 注解式开发

---

📚 知识地图

Excel导入导出方案
├── 📤 导出功能
│   ├── 简单导出
│   ├── 模板导出
│   ├── 动态列导出
│   ├── 多Sheet导出
│   ├── 大数据量导出
│   └── 异步导出
├── 📥 导入功能
│   ├── 简单导入
│   ├── 模板校验
│   ├── 数据校验
│   ├── 批量导入
│   ├── 异常处理
│   └── 导入进度
├── ⚡ 技术选型
│   ├── EasyExcel（推荐）⭐⭐⭐⭐⭐
│   ├── Apache POI ⭐⭐⭐
│   └── JExcelAPI ⭐⭐
└── 🎯 高级功能
    ├── 自定义转换器
    ├── 样式设置
    ├── 合并单元格
    ├── 下拉选项
    └── 公式计算

---

📦 依赖引入

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

---

📤 导出功能实现

1. 简单导出

/**
 * 订单导出实体
 */
@Data
public class OrderExportVO {
    
    @ExcelProperty(value = "订单编号", index = 0)
    private String orderNo;
    
    @ExcelProperty(value = "用户姓名", index = 1)
    private String userName;
    
    @ExcelProperty(value = "手机号", index = 2)
    private String phone;
    
    @ExcelProperty(value = "订单金额", index = 3)
    @NumberFormat("#.##")
    private BigDecimal totalAmount;
    
    @ExcelProperty(value = "订单状态", index = 4)
    private String statusName;
    
    @ExcelProperty(value = "创建时间", index = 5)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    private Date createTime;
}

/**
 * 导出服务
 */
@Service
@Slf4j
public class ExcelExportService {
    
    /**
     * ⚡ 简单导出（内存模式）
     * 适用场景：数据量 < 1万
     */
    public void simpleExport(List<OrderExportVO> data, HttpServletResponse response) 
        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");
        
        // ⚡ 写入Excel
        EasyExcel.write(response.getOutputStream(), OrderExportVO.class)
            .sheet("订单列表")
            .doWrite(data);
    }
    
    /**
     * ⚡ 流式导出（大数据量）
     * 适用场景：数据量 > 1万
     */
    public void streamExport(OrderQueryDTO query, HttpServletResponse response) 
        throws IOException {
        
        // 设置响应头
        setExcelResponseHeader(response, "订单列表");
        
        // ⚡ 使用WriteHandler流式写入
        EasyExcel.write(response.getOutputStream(), OrderExportVO.class)
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())  // 自适应列宽
            .sheet("订单列表")
            .doWrite(new PageReadListener(query));  // 分页查询
    }
    
    /**
     * ⚡ 分页查询监听器
     */
    private class PageReadListener implements Iterable<OrderExportVO> {
        
        private OrderQueryDTO query;
        private int pageNo = 1;
        private int pageSize = 1000;
        
        public PageReadListener(OrderQueryDTO query) {
            this.query = query;
        }
        
        @Override
        public Iterator<OrderExportVO> iterator() {
            return new Iterator<OrderExportVO>() {
                
                private List<OrderExportVO> currentPage = new ArrayList<>();
                private int index = 0;
                private boolean hasMore = true;
                
                @Override
                public boolean hasNext() {
                    // 当前页数据已读完，加载下一页
                    if (index >= currentPage.size() && hasMore) {
                        loadNextPage();
                    }
                    
                    return index < currentPage.size();
                }
                
                @Override
                public OrderExportVO next() {
                    return currentPage.get(index++);
                }
                
                /**
                 * ⚡ 加载下一页数据
                 */
                private void loadNextPage() {
                    log.info("加载第{}页数据", pageNo);
                    
                    // 分页查询数据库
                    PageResult<Order> page = orderService.queryPage(query, pageNo, pageSize);
                    
                    // 转换为导出VO
                    currentPage = page.getList().stream()
                        .map(this::convertToExportVO)
                        .collect(Collectors.toList());
                    
                    index = 0;
                    pageNo++;
                    hasMore = page.getTotal() > (pageNo - 1) * pageSize;
                }
                
                private OrderExportVO convertToExportVO(Order order) {
                    OrderExportVO vo = new OrderExportVO();
                    BeanUtils.copyProperties(order, vo);
                    vo.setStatusName(getStatusName(order.getStatus()));
                    return vo;
                }
            };
        }
    }
}

2. 多Sheet导出

/**
 * 多Sheet导出
 */
public void multiSheetExport(HttpServletResponse response) throws IOException {
    setExcelResponseHeader(response, "数据报表");
    
    // ⚡ 创建ExcelWriter
    try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {
        
        // Sheet1：订单数据
        WriteSheet orderSheet = EasyExcel.writerSheet(0, "订单列表")
            .head(OrderExportVO.class)
            .build();
        List<OrderExportVO> orders = queryOrders();
        excelWriter.write(orders, orderSheet);
        
        // Sheet2：用户数据
        WriteSheet userSheet = EasyExcel.writerSheet(1, "用户列表")
            .head(UserExportVO.class)
            .build();
        List<UserExportVO> users = queryUsers();
        excelWriter.write(users, userSheet);
        
        // Sheet3：商品数据
        WriteSheet productSheet = EasyExcel.writerSheet(2, "商品列表")
            .head(ProductExportVO.class)
            .build();
        List<ProductExportVO> products = queryProducts();
        excelWriter.write(products, productSheet);
    }
}

3. 动态列导出

/**
 * 动态列导出（表头不固定）
 */
public void dynamicExport(List<Map<String, Object>> data, 
                         List<String> headers,
                         HttpServletResponse response) throws IOException {
    
    setExcelResponseHeader(response, "动态报表");
    
    // ⚡ 动态构建表头
    List<List<String>> headList = headers.stream()
        .map(Collections::singletonList)
        .collect(Collectors.toList());
    
    // ⚡ 动态构建数据
    List<List<Object>> dataList = data.stream()
        .map(row -> headers.stream()
            .map(row::get)
            .collect(Collectors.toList()))
        .collect(Collectors.toList());
    
    // 写入Excel
    EasyExcel.write(response.getOutputStream())
        .head(headList)
        .sheet("数据")
        .doWrite(dataList);
}

4. 异步导出

/**
 * 异步导出服务
 */
@Service
@Slf4j
public class AsyncExportService {
    
    @Autowired
    private OrderService orderService;
    
    @Autowired
    private AsyncTaskExecutor asyncExecutor;
    
    @Autowired
    private FileStorageService fileStorageService;
    
    /**
     * ⚡ 异步导出（适用于超大数据量）
     */
    @Async
    public CompletableFuture<String> asyncExport(OrderQueryDTO query, Long userId) {
        return CompletableFuture.supplyAsync(() -> {
            try {
                // 1. 创建临时文件
                String fileName = "订单列表_" + System.currentTimeMillis() + ".xlsx";
                File tempFile = File.createTempFile("export_", ".xlsx");
                
                // 2. ⚡ 写入Excel
                try (ExcelWriter excelWriter = EasyExcel.write(tempFile, OrderExportVO.class).build()) {
                    WriteSheet writeSheet = EasyExcel.writerSheet("订单列表").build();
                    
                    // 分页查询并写入
                    int pageNo = 1;
                    int pageSize = 1000;
                    
                    while (true) {
                        PageResult<Order> page = orderService.queryPage(query, pageNo, pageSize);
                        
                        if (page.getList().isEmpty()) {
                            break;
                        }
                        
                        List<OrderExportVO> data = page.getList().stream()
                            .map(this::convertToExportVO)
                            .collect(Collectors.toList());
                        
                        excelWriter.write(data, writeSheet);
                        
                        // 更新进度
                        updateProgress(userId, pageNo * pageSize, page.getTotal());
                        
                        pageNo++;
                        
                        if (pageNo * pageSize >= page.getTotal()) {
                            break;
                        }
                    }
                }
                
                // 3. 上传到文件服务器
                String fileUrl = fileStorageService.upload(tempFile, fileName);
                
                // 4. 删除临时文件
                tempFile.delete();
                
                // 5. 发送通知
                sendExportNotification(userId, fileUrl);
                
                log.info("异步导出完成：userId={}, fileUrl={}", userId, fileUrl);
                
                return fileUrl;
                
            } catch (Exception e) {
                log.error("异步导出失败：userId={}", userId, e);
                throw new RuntimeException("导出失败", e);
            }
            
        }, asyncExecutor);
    }
    
    /**
     * 更新导出进度
     */
    private void updateProgress(Long userId, long current, long total) {
        int progress = (int) (current * 100 / total);
        
        // 存储到Redis
        String key = "export:progress:" + userId;
        redisTemplate.opsForValue().set(key, String.valueOf(progress), 1, TimeUnit.HOURS);
        
        // 推送进度更新（WebSocket）
        webSocketPusher.pushProgress(userId, progress);
    }
    
    /**
     * 发送导出完成通知
     */
    private void sendExportNotification(Long userId, String fileUrl) {
        // 发送站内信
        messageService.sendMessage(userId, "导出完成", 
            "您的订单数据已导出完成，点击下载：" + fileUrl);
    }
    
    private OrderExportVO convertToExportVO(Order order) {
        OrderExportVO vo = new OrderExportVO();
        BeanUtils.copyProperties(order, vo);
        return vo;
    }
}

---

📥 导入功能实现

1. 简单导入

/**
 * 订单导入实体
 */
@Data
public class OrderImportDTO {
    
    @ExcelProperty(value = "订单编号", index = 0)
    @NotBlank(message = "订单编号不能为空")
    private String orderNo;
    
    @ExcelProperty(value = "用户姓名", index = 1)
    @NotBlank(message = "用户姓名不能为空")
    private String userName;
    
    @ExcelProperty(value = "手机号", index = 2)
    @Pattern(regexp = "1[3-9]\d{9}", message = "手机号格式错误")
    private String phone;
    
    @ExcelProperty(value = "订单金额", index = 3)
    @NotNull(message = "订单金额不能为空")
    private BigDecimal totalAmount;
    
    @ExcelProperty(value = "订单状态", index = 4)
    private String statusName;
    
    /**
     * 行号（用于错误提示）
     */
    @ExcelIgnore
    private Integer rowNum;
}

/**
 * 导入监听器
 */
public class OrderImportListener implements ReadListener<OrderImportDTO> {
    
    /**
     * 批量处理数量
     */
    private static final int BATCH_SIZE = 500;
    
    /**
     * 缓存数据
     */
    private List<OrderImportDTO> cachedData = new ArrayList<>();
    
    /**
     * 错误信息
     */
    private List<String> errors = new ArrayList<>();
    
    private OrderService orderService;
    private Validator validator;
    
    public OrderImportListener(OrderService orderService) {
        this.orderService = orderService;
        this.validator = Validation.buildDefaultValidatorFactory().getValidator();
    }
    
    /**
     * ⚡ 每读取一行数据都会调用
     */
    @Override
    public void invoke(OrderImportDTO data, AnalysisContext context) {
        // 设置行号
        data.setRowNum(context.readRowHolder().getRowIndex() + 1);
        
        // ⚡ 数据校验
        Set<ConstraintViolation<OrderImportDTO>> violations = validator.validate(data);
        
        if (!violations.isEmpty()) {
            String error = String.format("第%d行数据校验失败：%s",
                data.getRowNum(),
                violations.stream()
                    .map(ConstraintViolation::getMessage)
                    .collect(Collectors.joining(", ")));
            
            errors.add(error);
            return;
        }
        
        // ⚡ 业务校验
        String businessError = validateBusiness(data);
        if (businessError != null) {
            errors.add(String.format("第%d行：%s", data.getRowNum(), businessError));
            return;
        }
        
        // 添加到缓存
        cachedData.add(data);
        
        // ⚡ 批量保存
        if (cachedData.size() >= BATCH_SIZE) {
            saveData();
            cachedData.clear();
        }
    }
    
    /**
     * 所有数据读取完成后调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 保存剩余数据
        if (!cachedData.isEmpty()) {
            saveData();
            cachedData.clear();
        }
        
        log.info("数据导入完成，共{}行", context.readRowHolder().getRowIndex());
    }
    
    /**
     * 业务校验
     */
    private String validateBusiness(OrderImportDTO data) {
        // 示例：校验订单编号是否重复
        if (orderService.existsByOrderNo(data.getOrderNo())) {
            return "订单编号已存在";
        }
        
        // 校验金额范围
        if (data.getTotalAmount().compareTo(BigDecimal.ZERO) <= 0) {
            return "订单金额必须大于0";
        }
        
        if (data.getTotalAmount().compareTo(new BigDecimal("1000000")) > 0) {
            return "订单金额不能超过100万";
        }
        
        return null;
    }
    
    /**
     * ⚡ 批量保存数据
     */
    private void saveData() {
        try {
            List<Order> orders = cachedData.stream()
                .map(this::convertToOrder)
                .collect(Collectors.toList());
            
            orderService.saveBatch(orders);
            
            log.info("批量保存成功：count={}", orders.size());
            
        } catch (Exception e) {
            log.error("批量保存失败", e);
            errors.add("批量保存失败：" + e.getMessage());
        }
    }
    
    /**
     * 转换为订单实体
     */
    private Order convertToOrder(OrderImportDTO dto) {
        Order order = new Order();
        BeanUtils.copyProperties(dto, order);
        // 设置默认值
        order.setStatus(1);
        order.setCreateTime(new Date());
        return order;
    }
    
    /**
     * 获取错误信息
     */
    public List<String> getErrors() {
        return errors;
    }
    
    /**
     * 是否有错误
     */
    public boolean hasErrors() {
        return !errors.isEmpty();
    }
}

/**
 * 导入服务
 */
@Service
@Slf4j
public class ExcelImportService {
    
    @Autowired
    private OrderService orderService;
    
    /**
     * ⚡ 导入Excel
     */
    public ImportResult importOrders(MultipartFile file) throws IOException {
        // 创建监听器
        OrderImportListener listener = new OrderImportListener(orderService);
        
        // ⚡ 读取Excel
        EasyExcel.read(file.getInputStream(), OrderImportDTO.class, listener)
            .sheet()
            .doRead();
        
        // 构建返回结果
        ImportResult result = new ImportResult();
        result.setSuccess(!listener.hasErrors());
        result.setErrors(listener.getErrors());
        
        return result;
    }
    
    /**
     * ⚡ 下载导入模板
     */
    public void downloadTemplate(HttpServletResponse response) throws IOException {
        setExcelResponseHeader(response, "订单导入模板");
        
        // 构建示例数据
        List<OrderImportDTO> data = Arrays.asList(
            createSampleData("ORD20250101001", "张三", "13812345678", new BigDecimal("100.00"), "待支付"),
            createSampleData("ORD20250101002", "李四", "13987654321", new BigDecimal("200.00"), "已支付")
        );
        
        // ⚡ 写入模板
        EasyExcel.write(response.getOutputStream(), OrderImportDTO.class)
            .sheet("订单数据")
            .doWrite(data);
    }
    
    private OrderImportDTO createSampleData(String orderNo, String userName, 
                                           String phone, BigDecimal amount, 
                                           String statusName) {
        OrderImportDTO dto = new OrderImportDTO();
        dto.setOrderNo(orderNo);
        dto.setUserName(userName);
        dto.setPhone(phone);
        dto.setTotalAmount(amount);
        dto.setStatusName(statusName);
        return dto;
    }
}

/**
 * 导入结果VO
 */
@Data
public class ImportResult {
    
    /**
     * 是否成功
     */
    private Boolean success;
    
    /**
     * 错误信息列表
     */
    private List<String> errors;
    
    /**
     * 成功数量
     */
    private Integer successCount;
    
    /**
     * 失败数量
     */
    private Integer failCount;
}

---

⚡ 高级功能

1. 自定义转换器

/**
 * 订单状态转换器
 */
public class OrderStatusConverter implements Converter<Integer> {
    
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }
    
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    
    /**
     * ⚡ 读取时：String -> Integer
     */
    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) {
        String value = context.getReadCellData().getStringValue();
        
        switch (value) {
            case "待支付": return 1;
            case "已支付": return 2;
            case "已发货": return 3;
            case "已完成": return 4;
            case "已取消": return 5;
            default: return 0;
        }
    }
    
    /**
     * ⚡ 写入时：Integer -> String
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) {
        Integer value = context.getValue();
        
        String statusName;
        switch (value) {
            case 1: statusName = "待支付"; break;
            case 2: statusName = "已支付"; break;
            case 3: statusName = "已发货"; break;
            case 4: statusName = "已完成"; break;
            case 5: statusName = "已取消"; break;
            default: statusName = "未知";
        }
        
        return new WriteCellData<>(statusName);
    }
}

/**
 * 使用自定义转换器
 */
@Data
public class OrderExportVO {
    
    @ExcelProperty(value = "订单状态", index = 4, converter = OrderStatusConverter.class)
    private Integer status;
}

2. 自定义样式

/**
 * 自定义样式处理器
 */
public class CustomCellStyleHandler implements CellWriteHandler {
    
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Cell cell = context.getCell();
        Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
        
        // ⚡ 设置样式
        CellStyle cellStyle = workbook.createCellStyle();
        
        // 设置边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        
        // 设置字体
        Font font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 11);
        cellStyle.setFont(font);
        
        // 设置居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        
        // ⚡ 表头加粗、背景色
        if (context.getHead()) {
            font.setBold(true);
            cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        
        cell.setCellStyle(cellStyle);
    }
}

/**
 * 使用自定义样式
 */
EasyExcel.write(response.getOutputStream(), OrderExportVO.class)
    .registerWriteHandler(new CustomCellStyleHandler())
    .sheet("订单列表")
    .doWrite(data);

---

✅ 最佳实践

Excel导入导出最佳实践：

1️⃣ 技术选型：
   □ 小数据量（< 1万）：POI
   □ 大数据量（> 1万）：EasyExcel ⭐⭐⭐⭐⭐
   □ 超大数据量（> 100万）：异步导出
   
2️⃣ 导出优化：
   □ 流式写入（节省内存）
   □ 分页查询（避免一次性加载）
   □ 异步导出（大数据量）
   □ 压缩下载（减少传输时间）
   
3️⃣ 导入优化：
   □ 模板校验
   □ 数据校验（注解+业务）
   □ 批量保存（500-1000条/批）
   □ 错误提示（行号+错误信息）
   
4️⃣ 安全防护：
   □ 文件大小限制
   □ 文件类型校验
   □ 数据量限制
   □ 权限校验
   
5️⃣ 用户体验：
   □ 模板下载
   □ 导入进度显示
   □ 错误信息详细
   □ 导出完成通知

---

🎉 总结

Excel导入导出核心：

1️⃣ EasyExcel：低内存、高性能
2️⃣ 流式处理：百万数据不在话下
3️⃣ 数据校验：注解+业务双重保障
4️⃣ 异步导出：大数据量不阻塞
5️⃣ 用户体验：进度显示+错误提示

---

记住：大数据量Excel必须用流式处理！ 📊

---

文档编写时间：2025年10月24日
作者：热爱数据处理的Excel工程师
版本：v1.0
愿每个Excel都高效流畅！ ✨