SpringBoot 2.x 使用 Apache POI 实现数据导入到 Excel

一、环境准备

1.1 添加依赖

在 pom.xml 中添加 POI 相关依赖：

<!-- Apache POI 核心库 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>
<!-- POI-OOXML 支持 xlsx 格式 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

1.2 基础配置类

创建 Excel 工具类基础配置：

@Configuration
public class ExcelConfig {
    
    @Bean
    public ExcelExportService excelExportService() {
        return new ExcelExportService();
    }
    
    @Bean
    public ExcelImportService excelImportService() {
        return new ExcelImportService();
    }
}

二、基础导出功能实现

2.1 创建 Excel 导出服务

@Service
public class ExcelExportService {
    
    private static final Logger logger = LoggerFactory.getLogger(ExcelExportService.class);
    
    /**
     * 导出数据到 Excel 文件
     * @param dataList 数据列表
     * @param headers 表头数组
     * @param fileName 文件名
     * @return 文件字节数组
     */
    public byte[] exportToExcel(List<Map<String, Object>> dataList, String[] headers, String fileName) {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");
            
            // 创建表头行
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }
            
            // 填充数据行
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(i + 1);
                Map<String, Object> rowData = dataList.get(i);
                int cellIndex = 0;
                for (String key : rowData.keySet()) {
                    Object value = rowData.get(key);
                    if (value != null) {
                        row.createCell(cellIndex).setCellValue(value.toString());
                    } else {
                        row.createCell(cellIndex).setCellValue("");
                    }
                    cellIndex++;
                }
            }
            
            // 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }
            
            // 转换为字节数组
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            return outputStream.toByteArray();
        } catch (Exception e) {
            logger.error("导出Excel失败", e);
            throw new RuntimeException("导出Excel失败");
        }
    }
}

2.2 控制器实现

@RestController
@RequestMapping("/api/excel")
public class ExcelController {
    
    @Autowired
    private ExcelExportService excelExportService;
    
    @GetMapping("/export")
    public ResponseEntity<byte[]> exportExcel() {
        // 模拟数据
        List<Map<String, Object>> dataList = new ArrayList<>();
        Map<String, Object> data1 = new HashMap<>();
        data1.put("name", "张三");
        data1.put("age", 25);
        data1.put("email", "zhangsan@example.com");
        dataList.add(data1);
        
        Map<String, Object> data2 = new HashMap<>();
        data2.put("name", "李四");
        data2.put("age", 30);
        data2.put("email", "lisi@example.com");
        dataList.add(data2);
        
        String[] headers = {"姓名", "年龄", "邮箱"};
        String fileName = "用户数据.xlsx";
        
        byte[] excelBytes = excelExportService.exportToExcel(dataList, headers, fileName);
        
        HttpHeaders header = new HttpHeaders();
        header.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        header.setContentDispositionFormData("attachment", fileName);
        
        return new ResponseEntity<>(excelBytes, header, HttpStatus.OK);
    }
}

三、高级导出功能

3.1 样式定制

public class ExcelStyleUtil {
    
    /**
     * 创建表头样式
     */
    public static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }
    
    /**
     * 创建数据行样式
     */
    public static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.LEFT);
        return style;
    }
}

3.2 使用样式的导出方法

public byte[] exportWithStyle(List<Map<String, Object>> dataList, String[] headers, String fileName) {
    try (Workbook workbook = new XSSFWorkbook()) {
        Sheet sheet = workbook.createSheet("Sheet1");
        
        // 创建样式
        CellStyle headerStyle = ExcelStyleUtil.createHeaderStyle(workbook);
        CellStyle dataStyle = ExcelStyleUtil.createDataStyle(workbook);
        
        // 创建表头行
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 填充数据行
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Map<String, Object> rowData = dataList.get(i);
            int cellIndex = 0;
            for (String key : rowData.keySet()) {
                Cell cell = row.createCell(cellIndex);
                Object value = rowData.get(key);
                if (value != null) {
                    cell.setCellValue(value.toString());
                } else {
                    cell.setCellValue("");
                }
                cell.setCellStyle(dataStyle);
                cellIndex++;
            }
        }
        
        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        return outputStream.toByteArray();
    } catch (Exception e) {
        logger.error("导出Excel失败", e);
        throw new RuntimeException("导出Excel失败");
    }
}

四、大数据量导出优化

4.1 SXSSFWorkbook 处理大数据

public byte[] exportLargeData(List<Map<String, Object>> dataList, String[] headers, String fileName) {
    // 使用SXSSFWorkbook处理大数据量，设置内存中保留100行，其余写入临时文件
    try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) {
        Sheet sheet = workbook.createSheet("Sheet1");
        
        // 创建表头行
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        
        // 分批处理数据
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Map<String, Object> rowData = dataList.get(i);
            int cellIndex = 0;
            for (String key : rowData.keySet()) {
                Object value = rowData.get(key);
                if (value != null) {
                    row.createCell(cellIndex).setCellValue(value.toString());
                } else {
                    row.createCell(cellIndex).setCellValue("");
                }
                cellIndex++;
            }
            
            // 每处理1000行手动flush一次
            if (i % 1000 == 0) {
                ((SXSSFSheet)sheet).flushRows(100);
            }
        }
        
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.dispose(); // 删除临时文件
        return outputStream.toByteArray();
    } catch (Exception e) {
        logger.error("导出大数据量Excel失败", e);
        throw new RuntimeException("导出Excel失败");
    }
}

4.2 异步导出实现

@RestController
@RequestMapping("/api/excel")
public class ExcelAsyncController {
    
    @Autowired
    private ExcelExportService excelExportService;
    
    @Autowired
    private TaskExecutor taskExecutor;
    
    @GetMapping("/async-export")
    public ResponseEntity<String> asyncExportExcel() {
        String taskId = UUID.randomUUID().toString();
        
        taskExecutor.execute(() -> {
            try {
                // 模拟大数据量
                List<Map<String, Object>> dataList = generateLargeData(100000);
                String[] headers = {"ID", "名称", "描述", "创建时间"};
                
                byte[] excelBytes = excelExportService.exportLargeData(dataList, headers, "大数据导出.xlsx");
                
                // 将文件保存到服务器或云存储
                String filePath = "/tmp/excel-exports/" + taskId + ".xlsx";
                Files.write(Paths.get(filePath), excelBytes);
                
                // 可以在这里发送邮件通知用户下载
            } catch (Exception e) {
                logger.error("异步导出Excel失败", e);
            }
        });
        
        return ResponseEntity.ok("{\"taskId\":\"" + taskId + "\",\"status\":\"processing\"}");
    }
    
    @GetMapping("/download/{taskId}")
    public ResponseEntity<byte[]> downloadExcel(@PathVariable String taskId) {
        String filePath = "/tmp/excel-exports/" + taskId + ".xlsx";
        try {
            byte[] excelBytes = Files.readAllBytes(Paths.get(filePath));
            
            HttpHeaders header = new HttpHeaders();
            header.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            header.setContentDispositionFormData("attachment", "大数据导出.xlsx");
            
            // 删除临时文件
            Files.deleteIfExists(Paths.get(filePath));
            
            return new ResponseEntity<>(excelBytes, header, HttpStatus.OK);
        } catch (Exception e) {
            throw new RuntimeException("文件不存在或已过期");
        }
    }
    
    private List<Map<String, Object>> generateLargeData(int count) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            Map<String, Object> data = new HashMap<>();
            data.put("id", i + 1);
            data.put("name", "项目" + (i + 1));
            data.put("description", "这是第" + (i + 1) + "个项目的描述");
            data.put("createTime", LocalDateTime.now().minusDays(i).toString());
            dataList.add(data);
        }
        return dataList;
    }
}

五、Excel 模板导出

5.1 使用模板文件

public byte[] exportWithTemplate(List<Map<String, Object>> dataList, String templatePath) {
    try (InputStream is = new FileInputStream(templatePath);
         Workbook workbook = new XSSFWorkbook(is)) {
        
        Sheet sheet = workbook.getSheetAt(0);
        
        // 获取模板中的样式
        Row templateRow = sheet.getRow(1);
        CellStyle dataStyle = templateRow.getCell(0).getCellStyle();
        
        // 从第2行开始填充数据（假设第1行是表头）
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Map<String, Object> rowData = dataList.get(i);
            
            Cell cell0 = row.createCell(0);
            cell0.setCellValue(rowData.get("name").toString());
            cell0.setCellStyle(dataStyle);
            
            Cell cell1 = row.createCell(1);
            cell1.setCellValue(Integer.parseInt(rowData.get("age").toString()));
            cell1.setCellStyle(dataStyle);
            
            Cell cell2 = row.createCell(2);
            cell2.setCellValue(rowData.get("email").toString());
            cell2.setCellStyle(dataStyle);
        }
        
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        return outputStream.toByteArray();
    } catch (Exception e) {
        logger.error("使用模板导出Excel失败", e);
        throw new RuntimeException("导出Excel失败");
    }
}

5.2 动态生成多Sheet

public byte[] exportMultiSheet(Map<String, List<Map<String, Object>>> sheetDataMap, String fileName) {
    try (Workbook workbook = new XSSFWorkbook()) {
        for (Map.Entry<String, List<Map<String, Object>>> entry : sheetDataMap.entrySet()) {
            String sheetName = entry.getKey();
            List<Map<String, Object>> dataList = entry.getValue();
            
            Sheet sheet = workbook.createSheet(sheetName);
            
            // 创建表头（假设所有Sheet使用相同的表头结构）
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("姓名");
            headerRow.createCell(1).setCellValue("年龄");
            headerRow.createCell(2).setCellValue("邮箱");
            
            // 填充数据
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(i + 1);
                Map<String, Object> rowData = dataList.get(i);
                row.createCell(0).setCellValue(rowData.get("name").toString());
                row.createCell(1).setCellValue(Integer.parseInt(rowData.get("age").toString()));
                row.createCell(2).setCellValue(rowData.get("email").toString());
            }
        }
        
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        return outputStream.toByteArray();
    } catch (Exception e) {
        logger.error("导出多Sheet Excel失败", e);
        throw new RuntimeException("导出Excel失败");
    }
}

六、导出性能优化建议

使用 SXSSFWorkbook 处理大数据：
- 设置合理的窗口大小（如100-1000行）
- 及时清理临时文件（调用 dispose() 方法）
样式优化：
- 复用 CellStyle 对象，避免为每个单元格创建新样式
- 将样式创建放在循环外部
内存管理：
- 对于超大文件，考虑分批次导出
- 使用 try-with-resources 确保资源释放
异步处理：
- 使用 Spring 的异步任务处理长时间导出
- 提供进度查询接口
文件存储：
- 将生成的文件暂存到服务器或云存储
- 设置文件过期时间，定期清理

七、常见问题解决方案

7.1 中文乱码问题

// 在响应头中设置正确的编码
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.set("Content-Disposition", "attachment;filename=" + 
           URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20"));

7.2 日期格式处理

// 创建日期样式
CellStyle dateStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));

// 应用样式
Cell dateCell = row.createCell(3);
dateCell.setCellValue(new Date());
dateCell.setCellStyle(dateStyle);

7.3 导出超时处理

@GetMapping("/export-with-timeout")
public ResponseEntity<byte[]> exportWithTimeout() {
    Future<byte[]> future = taskExecutor.submit(() -> {
        // 长时间导出任务
        return excelExportService.exportLargeData(generateLargeData(100000), 
                new String[]{"ID", "名称", "描述"}, "大数据.xlsx");
    });
    
    try {
        byte[] result = future.get(30, TimeUnit.SECONDS); // 设置30秒超时
        return ResponseEntity.ok()
                .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"大数据.xlsx\"")
                .body(result);
    } catch (TimeoutException e) {
        future.cancel(true);
        throw new RuntimeException("导出超时，请尝试减少数据量或使用异步导出");
    } catch (Exception e) {
        throw new RuntimeException("导出失败");
    }
}

通过以上实现，您可以在 SpringBoot 2.x 项目中轻松集成 Apache POI 实现 Excel 导出功能，并根据需求进行各种定制化开发。

文中内容均来源于公开资料，受限于信息的时效性和复杂性，可能存在误差或遗漏。我们已尽力确保内容的准确性，但对于因信息变更或错误导致的任何后果，本站不承担任何责任。如需引用本文内容，请注明出处并尊重原作者的版权。

THE END