Apache POI 操作 Excel 完整實戰指南（.xls/.xlsx 雙格式）

兩大格式核心差異

💡 統一介面：org.apache.poi.ss.usermodel.* (Workbook/Sheet/Row/Cell) 同時支援兩格式，實際類由 WorkbookFactory 自動判斷

核心 API 速查

實戰範例：建立 .xlsx 報表

public void exportUserReport(List<User> users, OutputStream out) throws IOException {
    try (XSSFWorkbook wb = new XSSFWorkbook()) {
        Sheet sheet = wb.createSheet("用戶清單");
        
        // 標題列樣式
        CellStyle headerStyle = wb.createCellStyle();
        Font headerFont = wb.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        // 建立標題
        Row header = sheet.createRow(0);
        String[] cols = {"ID", "姓名", "Email", "建立時間", "啟用"};
        for (int i = 0; i < cols.length; i++) {
            Cell cell = header.createCell(i);
            cell.setCellValue(cols[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 資料列
        CellStyle dateStyle = wb.createCellStyle();
        dateStyle.setDataFormat(wb.createDataFormat().getFormat("yyyy-mm-dd hh:mm"));
        
        for (int i = 0; i < users.size(); i++) {
            User u = users.get(i);
            Row row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(u.getId());
            row.createCell(1).setCellValue(u.getName());
            row.createCell(2).setCellValue(u.getEmail());
            Cell dateCell = row.createCell(3);
            dateCell.setCellValue(u.getCreatedAt());
            dateCell.setCellStyle(dateStyle);
            row.createCell(4).setCellValue(u.isEnabled());
        }
        
        // 自動欄寬
        for (int i = 0; i < cols.length; i++) sheet.autoSizeColumn(i);
        
        wb.write(out);
    }
}

實戰範例：讀取並修改 Excel

public void updateExcel(File file, String sheetName, int rowIdx, int colIdx, String newVal) throws IOException {
    try (FileInputStream fis = new FileInputStream(file);
         Workbook wb = WorkbookFactory.create(fis)) {
        
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet == null) throw new IllegalArgumentException("Sheet 不存在: " + sheetName);
        
        Row row = sheet.getRow(rowIdx);
        if (row == null) row = sheet.createRow(rowIdx);
        
        Cell cell = row.getCell(colIdx, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        cell.setCellValue(newVal);
        
        try (FileOutputStream fos = new FileOutputStream(file)) {
            wb.write(fos);
        }
    }
}

大數據量場景：SXSSF (Streaming)

// 僅保留最近 100 行在記憶體，其餘刷入磁碟
try (SXSSFWorkbook wb = new SXSSFWorkbook(100)) {
    Sheet sheet = wb.createSheet("大數據");
    for (int i = 0; i < 1_000_000; i++) {
        Row row = sheet.createRow(i);
        row.createCell(0).setCellValue("Row " + i);
    }
    wb.write(new FileOutputStream("big.xlsx"));
}

• SXSSFWorkbook(windowSize)：windowSize 為記憶體保留行數
• 適合「只寫、大量資料、不需隨機讀取」場景
• ⚠️ 不支援讀取、修改現有檔案

常見坑點與避雷指南

❌ getCell(col) 回傳 null → 用 getCell(col, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)

❌ 日期儲存為數字 → 讀取時用 DateUtil.isCellDateFormatted(cell) 判斷再 getDateCellValue()

❌ 公式未計算 → 寫入後呼叫 FormulaEvaluator.evaluateAll(wb) 或開啟 Excel 自動重算

❌ .xls 65535 行限制 → 超過自動切換 .xlsx 或分多 Sheet

❌ 大檔 OOM → 改用 SXSSF 或 EasyExcel (阿里開源，更省記憶體)

依賴坐標 (Maven)

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>5.3.0</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.3.0</version>
</dependency>