您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

JavaWeb 導出 Excel 並彈出下載框：Apache POI 流式輸出完整實現

編輯：Java 相關知識

依賴引入

<!-- POI 4.x+ 支持 .xls/.xlsx -->
<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>

核心導出邏輯

@GetMapping("/export")
public void exportExcel(HttpServletResponse response) throws IOException {
    // 1. 獲取數據 (示例)
    List list = userService.getExportData();
    
    // 2. 創建 Workbook (選擇 .xlsx 格式)
    try (Workbook wb = new XSSFWorkbook(); // HSSFWorkbook 為 .xls
         ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
        
        // 3. 創建 Sheet
        Sheet sheet = wb.createSheet("用戶數據");
        
        // 4. 樣式定義
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        Font headerFont = wb.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        
        CellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.LEFT);
        
        // 5. 寫入表頭
        Row headerRow = sheet.createRow(0);
        String[] headers = {"ID", "用戶名", "郵箱", "手機", "狀態", "創建時間"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 6. 寫入數據行
        for (int i = 0; i < list.size(); i++) {
            UserExportVO vo = list.get(i);
            Row row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(vo.getId());
            row.createCell(1).setCellValue(vo.getUsername());
            row.createCell(2).setCellValue(vo.getEmail());
            row.createCell(3).setCellValue(vo.getPhone());
            row.createCell(4).setCellValue(vo.getStatusDesc());
            row.createCell(5).setCellValue(vo.getCreateTime());
            
            // 應用樣式
            for (int j = 0; j < headers.length; j++) {
                row.getCell(j).setCellStyle(dataStyle);
            }
        }
        
        // 7. 自動調整列寬
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        
        // 8. 寫入 ByteArrayOutputStream
        wb.write(baos);
        
        // 9. 設置響應頭並輸出
        String fileName = "用戶數據_" + LocalDate.now() + ".xlsx";
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", 
            "attachment; filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()));
        response.setContentLengthLong(baos.size());
        
        try (OutputStream os = response.getOutputStream()) {
            baos.writeTo(os);
            os.flush();
        }
    }
}

大數據量優化：SXSSFWorkbook (流式寫入)

**問題**：`XSSFWorkbook` 將所有數據載入記憶體，百萬行會 OOM。

**解決**：`SXSSFWorkbook` 基於臨時文件，僅保留滑動窗口在記憶體。

// 替換創建方式，參數為滑動窗口行數 (默認 100)
try (Workbook wb = new SXSSFWorkbook(100); // 或 new SXSSFWorkbook(new XSSFWorkbook(), 100)
     ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
    
    // 後續寫入代碼完全相同
    
    // 關鍵：SXSSFWorkbook 需顯式 dispose 釋放臨時文件
    wb.write(baos);
    wb.dispose(); // 必須調用！
}

進階功能

1. 合併儲存格

// 合併第 0 行第 0-2 列 (標題跨列)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
Row titleRow = sheet.createRow(0);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("用戶數據導出報表");
titleCell.setCellStyle(titleStyle);

2. 數據驗證 (下拉選單)

DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"啟用", "禁用", "鎖定"});
CellRangeAddressList regions = new CellRangeAddressList(1, 1000, 4, 4); // 第 5 列, 第 2-1001 行
DataValidation validation = helper.createValidation(constraint, regions);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);

3. 條件格式 (數值顏色)

SheetConditionalFormatting cf = sheet.getSheetConditionalFormatting();
ConditionalFormattingRule rule = cf.createConditionalFormattingRule(
    ComparisonOperator.GT, "1000");
FontFormatting fontFmt = rule.createFontFormatting();
fontFmt.setFontColorIndex(IndexedColors.RED.getIndex());
cf.addConditionalFormatting(new CellRangeAddress[]{new CellRangeAddress(1, 1000, 5, 5)}, rule);

4. 圖片插入

try (InputStream is = getClass().getResourceAsStream("/static/logo.png")) {
    byte[] bytes = IOUtils.toByteArray(is);
    int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
    CreationHelper helper = wb.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setCol1(0); anchor.setRow1(0);
    Picture pict = drawing.createPicture(anchor, pictureIdx);
    pict.resize(); // 按原始大小
}

Spring MVC 統一封裝工具類

public class ExcelExportUtil {
    
    public static  void export(HttpServletResponse response, 
                                  String fileName, String sheetName,
                                  String[] headers, List data,
                                  Function rowMapper) {
        
        try (Workbook wb = new SXSSFWorkbook(100);
             ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            
            Sheet sheet = wb.createSheet(sheetName);
            CellStyle headerStyle = createHeaderStyle(wb);
            CellStyle dataStyle = createDataStyle(wb);
            
            // 表頭
            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 < data.size(); i++) {
                Row row = sheet.createRow(i + 1);
                Object[] values = rowMapper.apply(data.get(i));
                for (int j = 0; j < values.length; j++) {
                    Cell cell = row.createCell(j);
                    setCellValue(cell, values[j]);
                    cell.setCellStyle(dataStyle);
                }
            }
            
            // 列寬
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }
            
            wb.write(baos);
            wb.dispose();
            
            // 響應
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", 
                "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setContentLengthLong(baos.size());
            baos.writeTo(response.getOutputStream());
            
        } catch (IOException e) {
            throw new RuntimeException("Excel 導出失敗", e);
        }
    }
    
    private static void setCellValue(Cell cell, Object value) {
        if (value == null) return;
        if (value instanceof Number) cell.setCellValue(((Number) value).doubleValue());
        else if (value instanceof Boolean) cell.setCellValue((Boolean) value);
        else if (value instanceof Date) cell.setCellValue((Date) value);
        else cell.setCellValue(value.toString());
    }
}

Controller 調用示例

@GetMapping("/export")
public void export(HttpServletResponse response) {
    List users = userService.listAll();
    ExcelExportUtil.export(response, 
        "用戶數據_" + LocalDate.now() + ".xlsx",
        "用戶列表",
        new String[]{"ID", "用戶名", "郵箱", "狀態"},
        users,
        u -> new Object[]{u.getId(), u.getUsername(), u.getEmail(), u.getStatusDesc()}
    );
}

常見問題排查

問題	原因	解決
導出文件打開損壞	響應未 reset / 輸出流被其他 Filter 污染	`response.reset()`、確保無其他內容輸出、返回 `void` 而非 String
中文文件名亂碼	URLEncoder 編碼缺失	`URLEncoder.encode(fileName, "UTF-8")`
大數據導出 OOM	使用 XSSFWorkbook 全量載入	改用 `SXSSFWorkbook(100)` + `wb.dispose()`
數字顯示為科學計數法	Excel 自動格式化	設置單元格格式 `@` (文本) 或 `'1234567890123456` 前綴單引號
日期顯示為數字	未設置單元格日期格式	`cellStyle.setDataFormat(wb.createDataFormat().getFormat("yyyy-mm-dd"))`
SXSSF 臨時文件殘留	未調用 dispose()	try-with-resources 中確保 `wb.dispose()`

Excel 導入解析 (補充)

public List importExcel(MultipartFile file) throws IOException {
    try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
        Sheet sheet = wb.getSheetAt(0);
        List list = new ArrayList<>();
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            User u = new User();
            u.setUsername(getCellString(row.getCell(0)));
            u.setEmail(getCellString(row.getCell(1)));
            // ...
            list.add(u);
        }
        return list;
    }
}

private String getCellString(Cell cell) {
    if (cell == null) return "";
    return switch (cell.getCellType()) {
        case STRING -> cell.getStringCellValue();
        case NUMERIC -> DateUtil.isCellDateFormatted(cell) ? 
            cell.getDateCellValue().toString() : String.valueOf((long) cell.getNumericCellValue());
        case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
        case FORMULA -> cell.getCellFormula();
        default -> "";
    };
}