Apache POI 實戰：Excel (.xls/.xlsx) 讀寫完整指南（含樣式、反射泛型映射）

POI 核心組件

通用寫入：反射泛型導出

public  Workbook export(List data, Class clazz) {
    Workbook wb = new HSSFWorkbook();  // 或 new XSSFWorkbook()
    Sheet sheet = wb.createSheet();
    CellStyle headerStyle = style(wb, Font.BOLDWEIGHT_BOLD, HorizontalAlignment.CENTER);
    CellStyle bodyStyle = style(wb, Font.BOLDWEIGHT_NORMAL, HorizontalAlignment.LEFT);

    // 表頭：取得所有宣告欄位
    Field[] fields = clazz.getDeclaredFields();
    Row header = sheet.createRow(0);
    for (int i = 0; i < fields.length; i++) {
        Cell c = header.createCell(i);
        c.setCellValue(fields[i].getName());
        c.setCellStyle(headerStyle);
    }

    // 資料列
    for (int r = 0; r < data.size(); r++) {
        T obj = data.get(r);
        Row row = sheet.createRow(r + 1);
        for (int c = 0; c < fields.length; c++) {
            fields[c].setAccessible(true);
            Object val = fields[c].get(obj);
            Cell cell = row.createCell(c);
            cell.setCellValue(val != null ? val.toString() : "");
            cell.setCellStyle(bodyStyle);
        }
    }
    // 自動欄寬
    for (int i = 0; i < fields.length; i++) sheet.autoSizeColumn(i);
    return wb;
}

private CellStyle style(Workbook wb, short bold, HorizontalAlignment align) {
    CellStyle s = wb.createCellStyle();
    Font f = wb.createFont(); f.setBold(bold); s.setFont(f);
    s.setAlignment(align); s.setBorderBottom(BorderStyle.THIN);
    return s;
}

通用讀取：反射泛型導入

public  List importExcel(InputStream is, Class clazz) throws Exception {
    List list = new ArrayList<>();
    Workbook wb = WorkbookFactory.create(is);  // 自動偵測 .xls/.xlsx
    Sheet sheet = wb.getSheetAt(0);
    Field[] fields = clazz.getDeclaredFields();

    for (int r = 1; r <= sheet.getLastRowNum(); r++) {  // 跳過表頭
        Row row = sheet.getRow(r);
        if (row == null) continue;
        T obj = clazz.getDeclaredConstructor().newInstance();
        for (int c = 0; c < fields.length; c++) {
            Cell cell = row.getCell(c);
            if (cell == null) continue;
            fields[c].setAccessible(true);
            Class type = fields[c].getType();
            Object val = convert(cell, type);  // 型別轉換邏輯
            fields[c].set(obj, val);
        }
        list.add(obj);
    }
    return list;
}

private Object convert(Cell cell, Class targetType) {
    switch (cell.getCellTypeEnum()) {
        case STRING: return targetType == String.class ? cell.getStringCellValue() : 
                         targetType == Integer.class ? Integer.valueOf(cell.getStringCellValue()) :
                         targetType == Long.class ? Long.valueOf(cell.getStringCellValue()) :
                         targetType == Double.class ? Double.valueOf(cell.getStringCellValue()) :
                         targetType == Boolean.class ? Boolean.valueOf(cell.getStringCellValue()) :
                         cell.getStringCellValue();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) return cell.getDateCellValue();
            double d = cell.getNumericCellValue();
            return targetType == Integer.class ? (int)d : targetType == Long.class ? (long)d : d;
        case BOOLEAN: return cell.getBooleanCellValue();
        default: return null;
    }
}

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

// 僅保留最近 100 行在記憶體，其餘刷入磁碟臨時檔
SXSSFWorkbook wb = new SXSSFWorkbook(100);  
// 後續寫入 API 與 XSSF 完全相同
// 最後
wb.write(out);
wb.dispose();  // 清理臨時檔

常見坑點與解法

Maven 依賴 (POI 5.2.3)

<dependencies>
  <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>
</dependencies>