JavaWeb 使用 Apache POI 操作 Excel 完整實戰指南

POI 版本選擇與依賴配置

Apache POI 是 Java 操作 Microsoft Office 文檔的標準庫。Excel 操作核心包：

Maven 依賴 (POI 5.2+)

<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>
<!-- 可選：SXSSF 大數據流式寫入 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-lite</artifactId>
    <version>5.2.3</version>
</dependency>

核心場景一：讀取 Excel 文件 (.xlsx)

@Service
public class ExcelReadService {

    public void readExcel(File file) throws IOException {
        try (FileInputStream fis = new FileInputStream(file);
             XSSFWorkbook workbook = new XSSFWorkbook(fis)) { // 或 WorkbookFactory.create(fis) 自動識別格式
            
            int sheetCount = workbook.getNumberOfSheets();
            System.out.println("Sheet 數量: " + sheetCount);

            for (int i = 0; i < sheetCount; i++) {
                XSSFSheet sheet = workbook.getSheetAt(i);
                System.out.println("處理 Sheet: " + sheet.getSheetName());
                
                // 獲取物理行數（跳過空行）
                int rowCount = sheet.getPhysicalNumberOfRows();
                
                for (int r = 0; r < rowCount; r++) {
                    XSSFRow row = sheet.getRow(r);
                    if (row == null) continue; // 跳過空行
                    
                    int cellCount = row.getPhysicalNumberOfCells();
                    
                    for (int c = 0; c < cellCount; c++) {
                        XSSFCell cell = row.getCell(c);
                        if (cell == null) continue;
                        
                        String cellValue = getCellValueAsString(cell);
                        System.out.print(cellValue + "\t");
                    }
                    System.out.println();
                }
            }
        }
    }

    /**
     * 統一獲取單元格字符串值，處理各類型轉換
     */
    private String getCellValueAsString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 日期類型
                    return cell.getDateCellValue().toString();
                }
                // 數值類型：避免科學計數法，按需格式化
                return new BigDecimal(cell.getNumericCellValue()).toPlainString();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                // 公式：嘗試獲取緩存值，或計算
                try {
                    return getCellValueAsString(cell.getCachedFormulaResultType());
                } catch (Exception e) {
                    return "[公式錯誤]";
                }
            case BLANK:
                return "";
            case ERROR:
                return "[錯誤:" + cell.getErrorCellValue() + "]";
            default:
                return "";
        }
    }
}

核心場景二：寫入/導出 Excel 文件

@Service
public class ExcelWriteService {

    public void exportUserList(List<User> users, HttpServletResponse response) throws IOException {
        // 1. 創建工作簿 (SXSSF 適合大數據量，內存友好)
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 內存保留 100 行，其餘寫磁盤
             ServletOutputStream out = response.getOutputStream()) {
            
            // 2. 設置響應頭
            String fileName = URLEncoder.encode("用戶列表_" + LocalDate.now(), "UTF-8");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            
            // 3. 創建 Sheet
            Sheet sheet = workbook.createSheet("用戶數據");
            
            // 4. 創建樣式
            CellStyle headerStyle = createHeaderStyle(workbook);
            CellStyle dataStyle = createDataStyle(workbook);
            
            // 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);
                // 設置列寬
                sheet.setColumnWidth(i, 20 * 256);
            }
            
            // 6. 寫入數據
            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());
                row.createCell(3).setCellValue(user.getPhone());
                
                Cell timeCell = row.createCell(4);
                timeCell.setCellValue(user.getCreateTime());
                timeCell.setCellStyle(dataStyle); // 應用日期格式
                
                row.createCell(5).setCellValue(user.getStatus() ? "啟用" : "禁用");
            }
            
            // 7. 凍結首行
            sheet.createFreezePane(0, 1);
            
            // 8. 自動調整列寬 (慎用大數據)
            // for (int i = 0; i < headers.length; i++) sheet.autoSizeColumn(i);
            
            workbook.write(out);
        }
    }

    private CellStyle createHeaderStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        return style;
    }

    private CellStyle createDataStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        style.setDataFormat(format.getFormat("yyyy-mm-dd hh:mm:ss"));
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }
}

大數據量導出：SXSSF 流式寫入

當導出超過 10 萬行時，XSSFWorkbook 會 OOM。使用 SXSSFWorkbook：

• 內存只保留最近 N 行（窗口大小），其餘刷入臨時文件
• 調用 workbook.dispose() 清理臨時文件
• 不支持讀取、樣式功能受限

常見問題與避坑指南

進階技巧

• 數據驗證：下拉選單 DataValidationHelper
• 合併單元格：sheet.addMergedRegion(new CellRangeAddress(...))
• 圖片插入：Drawing patriarch = sheet.createDrawingPatriarch()
• 註釋/批註：cell.setCellComment(comment)
• 條件格式：SheetConditionalFormatting

總結

Apache POI 是 Java 操作 Excel 的標準選擇。關鍵點：優先使用 ss.usermodel 通用接口、區分 XSSF/SXSSF 場景、統一單元格值獲取邏輯、大數據必用流式 API。掌握核心讀寫模式後，複雜報表導出、模板填充、數據驗證等高級功能皆可舉一反三。