Apache POI Excel 解析通用工具類：數值格式化、科學計數法修復、日期轉換最佳實踐

痛點：Excel 單元格類型與 Java 類型的「不匹配」

• 數值型儲存整數卻讀出 1.0、100.000
• 大數字自動變科學計數法 1.23E8
• 日期型既可能是 Date 也可能是數值序列號 (如 44927)
• 公式單元格需求值而非公式文本

核心工具類設計

public final class ExcelCellUtils {
    private static final DecimalFormat DEFAULT_DECIMAL_FMT = new DecimalFormat("0.###");
    private static final DecimalFormat SCIENTIFIC_FMT = new DecimalFormat("0.####################");
    private static final DataFormatter DATA_FORMATTER = new DataFormatter(Locale.CHINA); // 內建格式化器

    private ExcelCellUtils() {}

    /**
     * 獲取單元格「顯示值」的統一入口，自動處理數值、日期、公式、科學計數法
     * @param cell POI Cell 對象
     * @return 字符串值（去除多餘 .0、科學計數法還原、日期格式化）
     */
    public static String getDisplayValue(Cell cell) {
        if (cell == null) return "";
        
        // 公式單元格取緩存值
        if (cell.getCellType() == CellType.FORMULA) {
            try {
                return getDisplayValue(cell.getCachedFormulaResultType(), cell);
            } catch (Exception e) {
                return cell.getCellFormula(); // 降級返回公式文本
            }
        }
        return getDisplayValue(cell.getCellType(), cell);
    }

    private static String getDisplayValue(CellType type, Cell cell) {
        switch (type) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 日期類型：統一格式化為 yyyy-MM-dd HH:mm:ss
                    Date date = cell.getDateCellValue();
                    return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
                }
                // 數值類型：去除尾部 .0，修復科學計數法
                return formatNumeric(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case BLANK:
                return "";
            case ERROR:
                return "[ERROR:" + cell.getErrorCellValue() + "]";
            default:
                return "";
        }
    }

    /**
     * 數值格式化核心邏輯：
     * 1. 整數值 (如 100.0) -> "100"
     * 2. 小數值 (如 3.1400) -> "3.14"
     * 3. 科學計數法 (1.23E8) -> "123000000"
     * 4. 超長整數 (ID/手機號) -> 完整字符串，不丟失精度
     */
    private static String formatNumeric(double value) {
        // 先用科學計數法格式化器判斷是否為科學計數法輸出
        String scientific = SCIENTIFIC_FMT.format(value);
        if (scientific.contains("E") || scientific.contains("e")) {
            // 確為科學計數法，用 BigDecimal 精確還原
            return new BigDecimal(value).toPlainString();
        }
        // 非科學計數法，用默認格式化器去除尾部 0
        String formatted = DEFAULT_DECIMAL_FMT.format(value);
        // 若為整數 (無小數點或小數部分全 0)，去除 .0
        if (formatted.endsWith(".0")) {
            return formatted.substring(0, formatted.length() - 2);
        }
        return formatted;
    }

    /**
     * 獲取數值型單元格的精確數值 (BigDecimal)，用於計算場景
     */
    public static BigDecimal getNumericValue(Cell cell) {
        if (cell == null || cell.getCellType() != CellType.NUMERIC) return null;
        if (DateUtil.isCellDateFormatted(cell)) return null; // 日期不作數值
        return new BigDecimal(cell.getNumericCellValue());
    }

    /**
     * 獲取日期型單元格的 Date 對象
     */
    public static Date getDateValue(Cell cell) {
        if (cell == null) return null;
        if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        }
        // 兼容文本格式日期
        if (cell.getCellType() == CellType.STRING) {
            String str = cell.getStringCellValue().trim();
            for (String pattern : DATE_PATTERNS) {
                try { return new SimpleDateFormat(pattern).parse(str); } 
                catch (ParseException ignored) {}
            }
        }
        return null;
    }

    private static final String[] DATE_PATTERNS = {
        "yyyy-MM-dd", "yyyy/MM/dd", "yyyy.MM.dd",
        "yyyy-MM-dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss"
    };
}

流式讀取大文件 (SXSSF / XSSF Event API)

public class StreamingExcelReader implements AutoCloseable {
    private final OPCPackage pkg;
    private final XSSFReader reader;
    private final SharedStringsTable sst;
    private final StylesTable styles;
    private final DataFormatter formatter = new DataFormatter(Locale.CHINA);

    public StreamingExcelReader(InputStream inputStream) throws IOException, OpenXML4JException {
        this.pkg = OPCPackage.open(inputStream);
        this.reader = new XSSFReader(pkg);
        this.sst = reader.getSharedStringsTable();
        this.styles = reader.getStylesTable();
    }

    public void processSheet(int sheetIndex, SheetRowHandler handler) throws Exception {
        try (InputStream sheetStream = reader.getSheet("rId" + (sheetIndex + 1))) {
            XMLReader parser = XMLReaderFactory.createXMLReader();
            SheetHandler sheetHandler = new SheetHandler(styles, sst, formatter, handler);
            parser.setContentHandler(sheetHandler);
            parser.parse(new InputSource(sheetStream));
        }
    }

    @Override
    public void close() throws IOException {
        pkg.close();
    }

    // 內部 SAX 處理器，逐行回調
    private static class SheetHandler extends DefaultHandler { /* 省略 SAX 事件處理邏輯 */ }
    
    @FunctionalInterface
    public interface SheetRowHandler {
        void handleRow(int rowNum, List<String> cellValues);
    }
}

寫入導出最佳實踐

1. 樣式復用：創建 CellStyle 放入 Map 復用，避免超過 64k 樣式限制導致文件損壞。
2. 大數據導出：SXSSFWorkbook (流式寫入，僅保留滾動窗口在內存)，配合 trackAllColumnsForAutoSizing() 自動列寬。
3. 數值類型顯式設置：cell.setCellValue(double) 會寫為數值型，避免 Excel 警告「數字儲存為文本」。
4. 日期格式：創建 DataFormat 獲取內建格式 ID (如 14 = yyyy-MM-dd)，而非自定義格式字符串。

常見坑點速查表

總結

Excel 解析的核心難點在於「單元格類型判斷」與「格式化還原」。封裝統一的 getDisplayValue() 入口，內部處理數值去尾、科學計數法還原、日期統一格式、公式緩存值讀取，可覆蓋 95% 以上業務場景。大文件場景務必採用流式 API (SXSSF/Event API)，並建立樣式復用機制。願此工具類助你輕鬆應對各類 Excel 業務。