Java 操作 Excel 完整實戰：Apache POI 讀寫、樣式、大數據流式處理

原文標題雖為 JavaMail，但內容實為 VBScript 設定 IP，嚴重錯位。本文重寫為 **Java 操作 Excel 實戰指南**，採用 Apache POI (HSSF/XSSF) 為主，涵蓋讀寫、樣式、公式、圖片、SXSSF 大數據流式導出，替代已停維護的 JXL。

技術選型對比

Maven 依賴

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

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.3</version>
</dependency>

核心場景代碼範例 (POI)

1. 讀取 .xlsx (XSSFWorkbook)

public List<Map<String, String>> readExcel(Path file) throws IOException {
    try (XSSFWorkbook wb = new XSSFWorkbook(file.toFile())) {
        XSSFSheet sheet = wb.getSheetAt(0);
        Row headerRow = sheet.getRow(0);
        int colCount = headerRow.getLastCellNum();
        List<String> headers = new ArrayList<>();
        for (int i = 0; i < colCount; i++) headers.add(headerRow.getCell(i).getStringCellValue());

        List<Map<String, String>> data = new ArrayList<>();
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) continue;
            Map<String, String> map = new LinkedHashMap<>();
            for (int c = 0; c < colCount; c++) {
                Cell cell = row.getCell(c);
                map.put(headers.get(c), getCellValueAsString(cell));
            }
            data.add(map);
        }
        return data;
    }
}

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

2. 寫入帶樣式 .xlsx

public void writeStyledExcel(Path file, List<Map<String, Object>> data) throws IOException {
    try (XSSFWorkbook wb = new XSSFWorkbook()) {
        Sheet sheet = wb.createSheet("Report");
        // 標題樣式
        CellStyle headerStyle = wb.createCellStyle();
        Font headerFont = wb.createFont(); headerFont.setBold(true); headerFont.setColor(IndexedColors.WHITE.getIndex());
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);

        // 數據樣式
        CellStyle dataStyle = wb.createCellStyle();
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setAlignment(HorizontalAlignment.LEFT);

        // 寫標題
        Row headerRow = sheet.createRow(0);
        int col = 0;
        for (String key : data.get(0).keySet()) {
            Cell cell = headerRow.createCell(col++);
            cell.setCellValue(key);
            cell.setCellStyle(headerStyle);
        }

        // 寫數據
        int rowIdx = 1;
        for (Map<String, Object> rowMap : data) {
            Row row = sheet.createRow(rowIdx++);
            col = 0;
            for (Object val : rowMap.values()) {
                Cell cell = row.createCell(col++);
                setCellValue(cell, val);
                cell.setCellStyle(dataStyle);
            }
        }
        // 自動欄寬
        for (int i = 0; i < col; i++) sheet.autoSizeColumn(i);
        try (FileOutputStream fos = new FileOutputStream(file.toFile())) {
            wb.write(fos);
        }
    }
}

private void setCellValue(Cell cell, Object val) {
    if (val == null) return;
    if (val instanceof Number) cell.setCellValue(((Number) val).doubleValue());
    else if (val instanceof Boolean) cell.setCellValue((Boolean) val);
    else if (val instanceof java.util.Date) cell.setCellValue((java.util.Date) val);
    else cell.setCellValue(val.toString());
}

3. 大數據導出：SXSSF (流式，低記憶體)

public void exportBigData(Path file, int totalRows) throws IOException {
    // keep 100 rows in memory, spill to disk
    try (SXSSFWorkbook wb = new SXSSFWorkbook(100)) {
        Sheet sheet = wb.createSheet("BigData");
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("ID");
        header.createCell(1).setCellValue("Value");

        for (int i = 1; i <= totalRows; i++) {
            Row row = sheet.createRow(i);
            row.createCell(0).setCellValue(i);
            row.createCell(1).setCellValue("Data-" + i);
            if (i % 10000 == 0) System.out.println("Written " + i + " rows");
        }
        try (FileOutputStream fos = new FileOutputStream(file.toFile())) {
            wb.write(fos);
        }
        wb.dispose(); // 刪除臨時檔
    }
}

4. 插入圖片

public void insertImage(Path file, Path imgPath, int row, int col) throws IOException {
    try (XSSFWorkbook wb = new XSSFWorkbook(file.toFile())) {
        Sheet sheet = wb.getSheetAt(0);
        byte[] pictureData = Files.readAllBytes(imgPath);
        int pictureIdx = wb.addPicture(pictureData, Workbook.PICTURE_TYPE_PNG);
        CreationHelper helper = wb.getCreationHelper();
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(col); anchor.setRow1(row);
        anchor.setCol2(col + 1); anchor.setRow2(row + 10); // 佔 10 行高
        Picture picture = drawing.createPicture(anchor, pictureIdx);
        picture.resize(); // 按原圖比例
        try (FileOutputStream fos = new FileOutputStream(file.toFile())) wb.write(fos);
    }
}

EasyExcel 簡化版導出 (推薦新專案)

@Data
public class ExportDto {
    @ExcelProperty("用戶ID") private Long id;
    @ExcelProperty("姓名") private String name;
    @ExcelProperty("註冊時間") @DateTimeFormat("yyyy-MM-dd") private LocalDateTime regTime;
}

public void easyExport(Path file, List<ExportDto> data) {
    EasyExcel.write(file.toFile(), ExportDto.class).sheet("用戶列表").doWrite(data);
}

生產環境避坑指南

• 日期/數字格式： 顯式建立 CellStyle.setDataFormat(fmt)，避免科學計數法或日期顯示為數字。
• 合併儲存格： `sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol))`，合併後僅左上儲存格有值。
• 公式計算： 寫入公式後呼叫 `wb.getCreationHelper().createFormulaEvaluator().evaluateAll()` 強制重算。
• 記憶體洩漏： SXSSF 必須呼叫 `dispose()` 清理臨時檔；大量寫入時定期 `wb.flush()`。
• 並發寫入： Workbook 非線程安全，每請求獨立實例，或用鎖/ThreadLocal 隔離。

選擇 POI 掌握全功能控制，或 EasyExcel 享受極簡 API 與自動流式優勢，讓 Java Excel 開發事半功倍。