Java Web 導出 Excel 完整方案：Apache POI 流式寫入、大數據量優化與 Spring MVC 整合

數據導出是後台管理系統的標配功能。本文基於 Apache POI 實現 Excel 導出完整方案：HSSFWorkbook (.xls) 與 SXSSFWorkbook (.xlsx 大數據) 雙模式、Spring MVC 流式響應、模板樣式複用、多 Sheet 支援、導出進度異步化，並解決中文文件名編碼、大數據內存溢出、日期/數字格式化等工程化難題。

依賴配置

<dependencies>
    <!-- POI 核心 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <!-- .xlsx 支援 (OOXML) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
    <!-- 流式大數據導出 (關鍵) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>5.2.3</version>
    </dependency>
</dependencies>

Spring MVC 控制器：流式響應

@RestController
@RequestMapping("/api/export")
public class ExportController {

    @Autowired private ExportService exportService;

    @GetMapping("/users")
    public void exportUsers(HttpServletResponse response,
                            @RequestParam(required = false) String keyword,
                            @RequestParam(defaultValue = "xlsx") String format) throws IOException {
        
        // 1. 設置響應頭
        String fileName = "用戶數據_" + LocalDate.now() + "." + format;
        String encodedName = URLEncoder.encode(fileName, StandardCharsets.UTF_8).replace("+", "%20");
        
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedName + "\"; filename*=UTF-8''" + encodedName);
        response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Expires", "0");
        
        // 2. 關鍵：關閉輸出流緩衝，直接寫入 ServletOutputStream
        response.setBufferSize(0); // 禁用 Tomcat 輸出緩衝
        
        // 3. 委託 Service 寫入流
        try (ServletOutputStream out = response.getOutputStream()) {
            exportService.exportUsers(out, keyword, format);
        }
    }
}

Service 層：雙模式導出實現

@Service
public class ExportService {

    // 閾值：超過 5000 行自動切換流式模式
    private static final int STREAMING_THRESHOLD = 5000;
    private static final int ROW_ACCESS_WINDOW = 100; // SXSSF 內存保留行數

    public void exportUsers(OutputStream out, String keyword, String format) throws IOException {
        // 1. 查詢總數 (決定導出模式)
        long total = userMapper.countByKeyword(keyword);
        
        if (total <= STREAMING_THRESHOLD && "xls".equals(format)) {
            // 小數據量 + .xls → HSSFWorkbook (全內存)
            exportWithHSSF(out, keyword);
        } else {
            // 大數據量 或 .xlsx → SXSSFWorkbook (流式，臨時文件)
            exportWithSXSSF(out, keyword, ROW_ACCESS_WINDOW);
        }
    }

    // 傳統模式：全內存，支援 .xls (65535 行限制)
    private void exportWithHSSF(OutputStream out, String keyword) throws IOException {
        try (HSSFWorkbook wb = new HSSFWorkbook()) {
            buildSheet(wb, "用戶列表", keyword, wb::createRow);
            wb.write(out);
        }
    }

    // 流式模式：僅保留最近 ROW_ACCESS_WINDOW 行在內存，其餘刷入臨時文件
    private void exportWithSXSSF(OutputStream out, String keyword, int windowSize) throws IOException {
        try (SXSSFWorkbook wb = new SXSSFWorkbook(windowSize)) {
            // 關鍵：設置臨時文件目錄 (避免系統 tmp 空間不足)
            wb.setTempFileDirectory(new File("/data/tmp/poi"));
            buildSheet(wb, "用戶列表", keyword, wb::createRow);
            wb.write(out);
            wb.dispose(); // 手動清理臨時文件 (try-with-resources 不會自動調用)
        }
    }

    // 統一構建 Sheet 邏輯 (函數式傳遞 Row 創建方式)
    private void buildSheet(Workbook wb, String sheetName, String keyword, 
                            Function<Integer, Row> rowCreator) {
        
        Sheet sheet = wb.createSheet(sheetName);
        
        // 1. 樣式定義
        CellStyle headerStyle = createHeaderStyle(wb);
        CellStyle dataStyle = createDataStyle(wb);
        CellStyle dateStyle = createDateStyle(wb);
        CellStyle numberStyle = createNumberStyle(wb);
        
        // 2. 表頭
        String[] headers = {"ID", "用戶名", "郵箱", "手機", "狀態", "註冊時間", "最後登錄", "餘額"};
        Row headerRow = rowCreator.apply(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
            sheet.setColumnWidth(i, 20 * 256); // 20 字符寬度
        }
        
        // 3. 數據行 (分頁查詢，避免一次性載入所有數據)
        int pageSize = 500;
        long offset = 0;
        int rowNum = 1;
        
        while (true) {
            List<UserDTO> users = userMapper.selectPage(keyword, offset, pageSize);
            if (users.isEmpty()) break;
            
            for (UserDTO user : users) {
                Row row = rowCreator.apply(rowNum++);
                fillRow(row, user, dataStyle, dateStyle, numberStyle);
            }
            
            offset += pageSize;
            if (users.size() < pageSize) break;
        }
        
        // 4. 自動列寬 (僅適用小數據，大數據建議固定寬度)
        // for (int i = 0; i < headers.length; i++) sheet.autoSizeColumn(i);
    }

    private void fillRow(Row row, UserDTO user, CellStyle dataStyle, 
                         CellStyle dateStyle, CellStyle numberStyle) {
        int col = 0;
        setCell(row, col++, user.getId(), dataStyle);
        setCell(row, col++, user.getUsername(), dataStyle);
        setCell(row, col++, user.getEmail(), dataStyle);
        setCell(row, col++, user.getPhone(), dataStyle);
        setCell(row, col++, user.getStatusText(), dataStyle);
        setCell(row, col++, user.getCreatedAt(), dateStyle);
        setCell(row, col++, user.getLastLoginAt(), dateStyle);
        setCell(row, col++, user.getBalance(), numberStyle);
    }

    private void setCell(Row row, int col, Object value, CellStyle style) {
        Cell cell = row.createCell(col);
        cell.setCellStyle(style);
        if (value == null) return;
        if (value instanceof Number) cell.setCellValue(((Number) value).doubleValue());
        else if (value instanceof LocalDateTime) cell.setCellValue((LocalDateTime) value);
        else if (value instanceof LocalDate) cell.setCellValue((LocalDate) value);
        else if (value instanceof Boolean) cell.setCellValue((Boolean) value);
        else cell.setCellValue(value.toString());
    }

    // 樣式工廠方法省略...
}

大數據量優化關鍵點總結

異步導出進度方案 (超大數據量)

@PostMapping("/async/users")
public R<String> asyncExport(@RequestBody ExportQuery query) {
    String taskId = UUID.randomUUID().toString();
    // 1. 保存任務記錄：PENDING
    exportTaskMapper.insert(taskId, query, "PENDING");
    
    // 2. 異步執行
    CompletableFuture.runAsync(() -> {
        try {
            exportTaskMapper.updateStatus(taskId, "RUNNING", 0);
            String filePath = "/data/exports/" + taskId + ".xlsx";
            try (FileOutputStream out = new FileOutputStream(filePath);
                 SXSSFWorkbook wb = new SXSSFWorkbook(100)) {
                
                wb.setTempFileDirectory(new File("/data/tmp/poi"));
                int total = exportService.exportUsersAsync(wb, query, progress -> {
                    exportTaskMapper.updateProgress(taskId, progress);
                });
                wb.write(out);
            }
            exportTaskMapper.updateStatus(taskId, "COMPLETED", 100, filePath);
        } catch (Exception e) {
            exportTaskMapper.updateStatus(taskId, "FAILED", 0, error: e.getMessage());
        }
    }, exportExecutor); // 專用線程池
    
    return R.ok(taskId); // 前端輪詢 /api/export/progress/{taskId}
}

@GetMapping("/progress/{taskId}")
public R<ExportProgress> progress(@PathVariable String taskId) {
    return R.ok(exportTaskMapper.selectProgress(taskId));
}

@GetMapping("/download/{taskId}")
public void download(@PathVariable String taskId, HttpServletResponse response) throws IOException {
    ExportTask task = exportTaskMapper.selectById(taskId);
    if (task == null || !"COMPLETED".equals(task.getStatus())) {
        response.sendError(HttpServletResponse.SC_NOT_FOUND);
        return;
    }
    // 流式下載文件...
}

模板導出進階：預製樣式 + 數據填充

// 1. 準備 template.xlsx (含表頭樣式、凍結窗格、數據驗證、條件格式)
try (InputStream tmpl = getClass().getResourceAsStream("/templates/user_export.xlsx");
     XSSFWorkbook wb = new XSSFWorkbook(tmpl);
     ServletOutputStream out = response.getOutputStream()) {
    
    Sheet sheet = wb.getSheetAt(0);
    // 2. 獲取模板行樣式 (第 1 行為表頭，第 2 行为數據行樣式參考)
    Row styleRow = sheet.getRow(1);
    
    // 3. 填充數據
    int rowNum = 1;
    for (UserDTO user : users) {
        Row row = sheet.createRow(rowNum++);
        // 複製樣式
        for (int i = 0; i < headers.length; i++) {
            Cell cell = row.createCell(i);
            if (styleRow != null && styleRow.getCell(i) != null) {
                cell.setCellStyle(styleRow.getCell(i).getCellStyle());
            }
            setCellValue(cell, user, i);
        }
    }
    
    // 4. 移除樣式參考行
    sheet.shiftRows(1, 1, -1); // 刪除第 2 行
    
    wb.write(out);
}

常見問題排查

總結

Java Web Excel 導出核心鏈路：Controller 設置響應頭 → Service 選型 Workbook (HSSF/SXSSF) → 分頁查詢數據 → 復用樣式填充 Row/Cell → 直接寫入 ServletOutputStream → 客戶端下載。關鍵架構決策：數據量 > 5000 行必用 SXSSFWorkbook + 分頁查詢 + 臨時文件目錄隔離。現代系統建議採用異步任務 + 進度輪詢 + 文件下載模式，將導出耗時操作從 HTTP 請求中剝離，提升用戶體驗與系統穩定性。模板導出模式可最大程度復用 Excel 原生樣式功能，減少代碼量。