🔧 阿川の電商水電行
Shopify 顧問、維護與客製化
💡
小任務 / 單次支援方案
單次處理 Shopify 修正/微調
⭐️
維護方案
每月 Shopify 技術支援 + 小修改 + 諮詢
🚀
專案建置
Shopify 功能導入、培訓 + 分階段交付

Apache POI 萬字總結:滿足你對報表一切幻想

image

背景

國慶期間接了個兼職,處理機構的幾張 Excel 報表。初次溝通,感覺挺簡單,接入 Easyexcel(FastExcel),然後拼 lambda 表達式就跑出來了。不過畢竟工作了這些年,感覺沒這麼簡單。後面找業務方詳細聊了一次,將需求落到紙面上。逐行研究了下 BRD,有點抓狂,跑數加各種樣式,兼容新老版本,老方案是不行了。綜合對比,最終選了老牌的 Apache POI 實現,下面說下為啥選 POI,還有 POI 怎麼用,包含樣式、公式、動態表頭、安全防範、百萬級數據導入導出等功能。

一、技術選型

如果實現該功能,客戶端可以(裝個 app),服務端也可行。考慮到電腦性能和未來大量的擴展升級,首先排除客戶端。服務端有各種語言可以解析 Excel,但是功能參差不齊,下面對比下比較熟悉的幾種(像 C# 不熟悉,直接排除):Apache POI (Java),FastExcel (Java/Kotlin),Python (openpyxl / pandas / xlrd/xlwt),PHP (PhpSpreadsheet / ExcelReader),Rust (calamine / rust_xlsxwriter)(咋還有 rust?因為前面寫了幾篇這塊文章,順手帶上)。

功能項 Apache POI(Java) FastExcel(Java/Kotlin) Python(openpyxl / pandas / xlrd/xlwt) PHP(PhpSpreadsheet / ExcelReader) Rust(calamine / rust_xlsxwriter)
支持格式:xls / xlsx ✅ (HSSF / XSSF / SXSSF) ⚠️ 僅 xlsx ✅ (openpyxl: xlsx / xlrd: xls) ✅ (xls / xlsx / ods) ⚠️ calamine: 讀多種格式;rust_xlsxwriter: 僅寫 xlsx
樣式設置(字體、邊框、對齊、條件格式) ✅ 全面 ⚠️ 基礎樣式有限 ✅ openpyxl 支持全面 ✅ 支持全面 ✅ rust_xlsxwriter 支持全面,calamine 僅讀
多級表頭 / 合併單元格 ✅ 合併支持良好,可構造多級 ⚠️ 支持合併,需手動構造表頭 ✅ openpyxl 支持合併 / 多層 ✅ 合併單元格支持 ✅ merge_range 支持
公式(讀寫 / 計算) ✅ 寫 / 讀 / 評估部分公式 ⚠️ 僅支持寫公式,不評估 ⚠️ 寫入公式支持,計算有限 ⚠️ 寫公式支持,部分計算 ⚠️ 寫公式支持,不計算
下拉選項 / 數據驗證 ✅ DataValidation 支持 ⚠️ 支持不完善或無文檔 ✅ openpyxl 提供 DataValidation ✅ 支持下拉與校驗 ✅ rust_xlsxwriter 支持驗證
圖表生成 ✅ 支持 XSSF 圖表 ❌ 不支持圖表 ✅ openpyxl 支持 BarChart / LineChart 等 ✅ includeCharts 可寫出圖表 ✅ rust_xlsxwriter 支持多類圖表
防注入 / 宏攻擊防護 ⚠️ 提供加密保護但無宏隔離 ❌ 無安全特性 ⚠️ 不解析宏,宏文件不安全 ⚠️ 僅單元格鎖定,無宏隔離 ⚠️ 不解析宏文件
加密 / 密碼保護 ✅ Office 標準加密支持 ❌ 不支持 ⚠️ 加密讀取支持有限 ⚠️ 工作表保護,非文件加密 ⚠️ 加密支持非常有限
大文件讀寫 / 流式寫入 ✅ SXSSF 支持流式寫入 ✅ 高性能流式寫入 ⚠️ read_only / write_only 模式 ⚠️ 受 PHP 記憶體限制 ✅ rust_xlsxwriter / calamine 流式高效
性能 / 記憶體佔用 ⚠️ 需流式模式優化 ✅ 極佳性能 ⚠️ 中等,取決於數據量 ⚠️ 記憶體佔用大 ✅ 高性能,記憶體佔用低
修改已有文件 ✅ 支持讀改寫 ⚠️ 不支持修改已有文件 ⚠️ 可讀寫但慢 ✅ 支持讀寫修改 ⚠️ rust_xlsxwriter 僅創建新文件
生態與文檔 ✅ 成熟 / 官方維護 ⚠️ 較新,文檔有限 ✅ 文檔豐富 ✅ 文檔齊全 ⚠️ Rust 生態新,功能在發展中
  • 從上表可以看出 Apache POI 功能最全面,幾乎涵蓋所有功能,其他各有優劣。
  • 從需求方提供的 Excel 示例文件看,有 xls 和 xlsx 格式的,內容裡面有公式。每個表數據量不大,但是樣式要求高,比如宋體、10 號等。文件來源也需要一些防護,畢竟是外部給的。未來可能需要支持參數校驗等等。不難看出,只有 Apache POI 能勝任。下面整理下 POI 入門文檔,內容參考 POI 官網

二、POI 入門與進階

1、添加 POI 依賴

本文基於 springboot 2.7.18 版本

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

2、基本使用

在 POI 中,Workbook 代表整個 Excel 檔案,sheet 是工作表,可以有多個
Row 和 Cell 是單獨的物件,索引都是從 0 開始。Cell 單元格,可存字符串、數字、布林、日期等
下面程式碼中包含了增刪改查基本操作。

a、新建 Excel 的操作

注意:sheet.removeRow(row1); 不會像你直觀理解的那樣“把行從表格中完全移除並上移下面的行”。它會清空該 Row 物件中的所有單元格,其他行號不變。所以下面調用了封裝的方法移除。

@Test
void testCreateWorkbookAndSheet() throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("員工資訊");

        // 創建表頭
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("編號");
        header.createCell(1).setCellValue("姓名");
        header.createCell(2).setCellValue("薪資");

        // 添加數據
        Row row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue(1001);
        row1.createCell(1).setCellValue("張三");
        row1.createCell(2).setCellValue(12000);

        Row row2 = sheet.createRow(2);
        row2.createCell(0).setCellValue(1001);
        row2.createCell(1).setCellValue("張三");
        row2.createCell(2).setCellValue(12000);

        // 修改單元格
        row1.getCell(2).setCellValue(13000);

        // 刪除一行,清空操作
        sheet.removeRow(row1);
       //如果上移需要使用封裝的方法
       //deleteRow(sheet, 1);
        try (FileOutputStream fos = new FileOutputStream("basic.xlsx")) {
            workbook.write(fos);
        }
        workbook.close();
    }

    /**
     * 刪除指定行,並將下面的行上移 https://stackoverflow.com/questions/21946958/how-to-remove-a-row-using-apache-poi
     *
     * @param sheet    目標 Sheet
     * @param rowIndex 要刪除的行號(0-based)
     */
    public void deleteRow(Sheet sheet, int rowIndex) {
        int lastRowNum = sheet.getLastRowNum();
        if (rowIndex >= 0 && rowIndex < lastRowNum) {
           sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
        }
        if (rowIndex == lastRowNum) {
           Row removingRow = sheet.getRow(rowIndex);
           if (removingRow != null) {
              sheet.removeRow(removingRow);
           }
        }
    }

sheet.removeRow(row1) 效果:
image
deleteRow(sheet, 1) 效果:
image

b、讀取已有文件

注意:WorkbookFactory.create 兼容新舊版本,推薦使用

@Test
void testReadSheet() throws Exception {
        InputStream is = Thread.currentThread()
                .getContextClassLoader()
                .getResourceAsStream("daoru.xls");
        //WorkbookFactory兼容 xls 和 xlsx
        try (Workbook workbook = WorkbookFactory.create(is)) {

            Sheet sheetAt = workbook.getSheetAt(0);
            assertNotNull(sheetAt);

            Row row = sheetAt.getRow(0);
            Cell cell = row.getCell(0);
            System.out.println("第一個單元格內容: " + getCellValue(cell));

            Sheet sheet2 = workbook.getSheet("基本支出決算明細表");
            Row row2 = sheet2.getRow(11);
            Cell cell2 = row2.getCell(7);
            System.out.println("第一個單元格內容: " + getCellValue(cell2));

            // 遍歷每個 Sheet
            for (int i = 0; i < workbook.getNumberOfSheets() && i < 2; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                System.out.println("Sheet[" + i + "] 名稱: " + sheet.getSheetName());

                // 遍歷行
                for (Row r : sheet) {
                    // 遍歷單元格
                    for (Cell c : r) {
                        String value = getCellValue(c); // 使用工具方法獲取顯示值
                        System.out.print(value + "\t");
                    }
                    System.out.println();
                }

                System.out.println("=================================");
            }
        }
    }
c、sheet 的基本操作

可以根據 index,名字等獲取 sheet。也支持修改和排序

@Test
void testUpdateSheet() throws Exception {
        try (Workbook workbook = createWorkbook(XLS)) {
            // 創建新 Sheet
            Sheet newSheet = workbook.createSheet("新建Sheet");

            // 修改已有 Sheet 名稱
            workbook.setSheetName(0, "用戶信息");
            // 4️   調整 Sheet 順序
            workbook.setSheetOrder("用戶信息", 1); // 移動到第 2 個位置
            workbook.setSheetOrder("新建Sheet", 0); // 移動到第 1 個位置

            String outputPath = "target/output" + GOV_XLS.substring(XLS.lastIndexOf('.'));
            // 5️⃣ 導出為新文件
            File outputFile = new File(outputPath);
            try (FileOutputStream os = new FileOutputStream(outputFile)) {
                workbook.write(os);
            }
        }
    }
d、cell 數據的簡單轉換和 double 精度問題
  • cell 目前有四種數據,下面有示例,公式在後續章節。
  • 注意金額的處理:如果涉及到計算,可以把 double 轉到 BigDecimal 處理,double 是二進制浮點數,無法精確表示小數,例如 0.1 + 0.2 ≠ 0.3。如果導出的數據必須是數字類型,可以使用 Bigdecimal 轉下(不超過 15<del>16 位)。數字超過 15<del>16 位有效數字時(無論是整數還是小數),double 就不能精確表示它,只能取“最接近”的二進制數,產生舍入誤差。超過 15 到 16 位的盡量用字符串。
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA: //單元格里存放的是公式,而不是直接的值。
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "UNKNOWN";
        }
    }

    3、樣式設置(字體、邊框、對齊、條件格式)

    以下示例展示了字體加粗等樣式。
    注意

  • style 物件可以重複使用,同一個樣式盡量只創建一次。
  • POI 條件格式公式從單元格左上角開始,即 A1 為相對位置,公式可以使用 $ 絕對引用。
  • 條件格式適合數量中小的單元格,數萬行大表格時條件格式多可能會影響 Excel 打開速度。

    @Test
    void testCreateStyledExcelWithStyleCache() throws Exception {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("樣式示例");
    
            // ========== 樣式緩存 ==========
            Map<String, CellStyle> styleCache = new HashMap<>();
    
            int rows = 5;
            int cols = 5;
    
            for (int i = 0; i < rows; i++) {
                Row row = sheet.createRow(i);
                for (int j = 0; j < cols; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue("R" + (i + 1) + "C" + (j + 1));
    
                    // 樣式 key:行背景 + 列字體顏色 + 是否加粗
                    String key = (i % 2) + "-" + (j % 2) + "-" + (i % 2 == 0);
    
                    // 复用样式
                    int finalI = i;
                    int finalJ = j;
                    CellStyle style = styleCache.computeIfAbsent(key, k -> createCellStyle(workbook, finalI, finalJ));
    
                    cell.setCellStyle(style);
                }
            }
    
            // 自動調整列寬
            for (int j = 0; j < cols; j++) {
                sheet.autoSizeColumn(j);
            }
    
            // 條件格式示例
            SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
            // 條件1:值 > 80 → 綠色
            ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "80");
            rule1.createPatternFormatting().setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
            rule1.getPatternFormatting().setFillPattern(FillPatternType.SOLID_FOREGROUND.getCode());
    
            // 條件2:值 < 50 → 紅色
            ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
            rule2.createPatternFormatting().setFillForegroundColor(IndexedColors.ROSE.getIndex());
            rule2.getPatternFormatting().setFillPattern(FillPatternType.SOLID_FOREGROUND.getCode());
    
            // 應用到區域 B2:B100
            sheetCF.addConditionalFormatting(
                    new CellRangeAddress[]{CellRangeAddress.valueOf("B2:B100")},
                    rule1, rule2
            );
    
            // 導出文件
            try (FileOutputStream fos = new FileOutputStream("target/poi-style-demo-cache.xlsx")) {
                workbook.write(fos);
            }
        }
    }
    
    /**
     * 創建單元格樣式(字體、邊框、對齊、背景)
     */
    private CellStyle createCellStyle(Workbook workbook, int rowIndex, int colIndex) {
        CellStyle style = workbook.createCellStyle();
    
        // 字體
        Font font = workbook.createFont();
        font.setFontName("微軟雅黑");
        font.setFontHeightInPoints((short) 12);
        font.setBold(rowIndex % 2 == 0); // 偶數行加粗
        font.setColor(colIndex % 2 == 0 ? IndexedColors.RED.getIndex() : IndexedColors.BLUE.getIndex());
        style.setFont(font);
    
        // 邊框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
    
        // 對齊
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
    
        // 背景
        style.setFillForegroundColor(rowIndex % 2 == 0 ? IndexedColors.LIGHT_YELLOW.getIndex() : IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    
        return style;
    }

    下面是導出的效果圖:
    image

4、多級表頭與合併單元格

a、多級表頭動態讀取

EasyExcel 讀取多表頭存在問題,必須寫死 index 才有數據,對於需要動態映射的,則沒法處理。使用 POI 可以模仿 EasyExcel 註解,寫個數據解析類。
註解和實體類如下:

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;

@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelProperty {
        //多級表頭,用數組
    String[] value();
}

@Data
public class ExcelBudgetData implements Serializable {

    @ExcelProperty(value = "預算項目")
    private String budgetProject;

    // "完成數"下面的“支付數”
    @ExcelProperty(value = {"完成數", "支付數"})
    private String completedPaymentAmount;

    @ExcelProperty(value = "項目類別")
    private String projectCategory;
}

表頭讀取工具如下,注意跨列的解析方式

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;

public class ExcelHeaderUtil {

    /**
     * 讀取多級表頭,支持跨列合併
     *
     * @param sheet        Excel sheet
     * @param headRowStart 表頭起始行 (0-based)
     * @param headRowEnd   表頭結束行 (0-based)
     * @return 拼接後的多級表頭列表(用 - 連接)
     */
    public static List<String> readMultiLevelHeader(Sheet sheet, int headRowStart, int headRowEnd) {
        List<List<String>> headerRows = new ArrayList<>();
        int maxCol = 0;

        // 讀取每一行表頭內容
        for (int r = headRowStart; r <= headRowEnd; r++) {
            Row row = sheet.getRow(r);
            List<String> rowData = new ArrayList<>();
            if (row != null) {
                int lastCol = row.getLastCellNum();
                maxCol = Math.max(maxCol, lastCol);
                for (int c = 0; c < lastCol; c++) {
                    String value = getMergedCellValue(sheet, r, c);
                    rowData.add(value == null ? "" : value.trim());
                }
            }
            headerRows.add(rowData);
        }

        // 拼接多級表頭
        List<String> finalHeaders = new ArrayList<>();
        for (int c = 0; c < maxCol; c++) {
            StringBuilder sb = new StringBuilder();
            for (List<String> headerRow : headerRows) {
                String val = c < headerRow.size() ? headerRow.get(c) : "";
                if (!val.isEmpty()) {
                    if (sb.length() > 0) {
                        sb.append("-");
                    }
                    sb.append(val);
                }
            }
            finalHeaders.add(sb.toString());
        }

        return finalHeaders;
    }

    private static String getMergedCellValue(Sheet sheet, int rowIndex, int colIndex) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            if (range.isInRange(rowIndex, colIndex)) {
                if (range.getFirstRow() != rowIndex) {
                    return "";
                }
                Row firstRow = sheet.getRow(range.getFirstRow());
                Cell firstCell = firstRow.getCell(range.getFirstColumn());
                return getCellStringValue(firstCell);
            }
        }
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(colIndex);
        return getCellStringValue(cell);
    }
        //公式等沒處理,可以自行添加
    private static String getCellStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return null;
        }
    }
}

使用方式如下:

private List<ExcelBudgetData> parseExcel(Sheet sheet) throws IllegalAccessException {
        // 1️⃣ 讀取多級表頭
        final int headRowStart = 3;
        final int headRowEnd = 4;
        List<String> headers = ExcelHeaderUtil.readMultiLevelHeader(sheet, headRowStart, headRowEnd);
        // 2️⃣ 對應列到實體字段
        Map<Integer, Field> colFieldMap = new HashMap<>();
        Field[] fields = ExcelBudgetData.class.getDeclaredFields();
        for (int i = 0; i < headers.size(); i++) {
            String header = headers.get(i);
            for (Field field : fields) {
                ExcelProperty prop = field.getAnnotation(ExcelProperty.class);
                if (prop != null) {
                    String joined = String.join("-", prop.value()).trim();
                    if (joined.equals(header)) {
                        field.setAccessible(true);
                        colFieldMap.put(i, field);
                        break;
                    }
                }
            }
        }

        List<ExcelBudgetData> result = new ArrayList<>();
        for (int r = headRowEnd + 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            ExcelBudgetData obj = new ExcelBudgetData();

            for (Map.Entry<Integer, Field> entry : colFieldMap.entrySet()) {
                int c = entry.getKey();
                Field field = entry.getValue();
                Cell cell = row.getCell(c);
                Object value = getCellValue(cell, field.getType());
                field.set(obj, value);
            }

            result.add(obj);
        }
        return result;
    }
b、寫入時跨列

下面是一個跨行和跨列的表頭例子
image

@Test
void testMultiHeader() throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("多級表頭");

        // 第一行表頭
        Row row1 = sheet.createRow(0);
        row1.createCell(0).setCellValue("部門");
        row1.createCell(1).setCellValue("銷售額");
        row1.createCell(3).setCellValue("利潤");

        // 第二行子表頭
        Row row2 = sheet.createRow(1);
        row2.createCell(1).setCellValue("Q1");
        row2.createCell(2).setCellValue("Q2");
        row2.createCell(3).setCellValue("Q1");
        row2.createCell(4).setCellValue("Q2");

        // 合併表頭單元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); // 部門
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2)); // 銷售額
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4)); // 利潤

        try (FileOutputStream out = new FileOutputStream("target/multi-header-demo.xlsx")) {
            workbook.write(out);
        }
        workbook.close();
    }

5、公式處理

示例數據如下,C列為公式,對A和B列求和。

A B C
10 20 =A1+B1
5 2 =A2*B2
a、創建公式並讀取求和結果
@Test
void testReadAndEvaluateFormula() throws Exception {
        // Step 1: 創建含公式的 Excel 文件
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Formula");
            Row row1 = sheet.createRow(0);
            //A:10 B:20 C::A1+B1
            row1.createCell(0).setCellValue(10);
            row1.createCell(1).setCellValue(20);
            row1.createCell(2).setCellFormula("A1+B1");

            try (FileOutputStream out = new FileOutputStream("target/demo.xlsx")) {
                workbook.write(out);
            }
        }

        // Step 2: 重新讀取並計算公式
        try (FileInputStream in = new FileInputStream("target/demo.xlsx");
             Workbook workbook = new XSSFWorkbook(in)) {

            Sheet sheet = workbook.getSheetAt(0);
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            Cell formulaCell = sheet.getRow(0).getCell(2);
            //執行 C 列公式
            evaluator.evaluateFormulaCell(formulaCell);

            assertEquals(30.0, formulaCell.getNumericCellValue(), 0.001);
        }
    }

效果如下:
image

b、自定義公式

註冊自定義函數(UDF,User Defined Function),例如計算稅率、平均增長率等。如下自定義公式 MYFUNC(x, y) = x² + y

/**
     * 自定義函數 MYFUNC(x, y) = x^2 + y
     */
    static class MyFunc implements FreeRefFunction {
        @Override
        public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
            try {
                // 先取得單個值(處理引用/區域)
                ValueEval v0 = OperandResolver.getSingleValue(args[0],
                        ec.getRowIndex(), ec.getColumnIndex());
                ValueEval v1 = OperandResolver.getSingleValue(args[1],
                        ec.getRowIndex(), ec.getColumnIndex());

                double x = OperandResolver.coerceValueToDouble(v0);
                double y = OperandResolver.coerceValueToDouble(v1);

                return new NumberEval(x * x + y);
            } catch (EvaluationException | RuntimeException ex) {
                return ErrorEval.VALUE_INVALID;
            }
        }
    }

    @Test
    void testRegisterUdfAndEvaluate() throws Exception {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("UDF");
            Row row = sheet.createRow(0);
            row.createCell(0).setCellValue(3);   // A1
            row.createCell(1).setCellValue(4);   // B1
            Cell formulaCell = row.createCell(2);
            formulaCell.setCellFormula("MYFUNC(A1,B1)"); // C1

            // --- 正確註冊自定義函數的關鍵步驟 ---
            String[] names = {"MYFUNC"};
            FreeRefFunction[] impls = { new MyFunc() };
            UDFFinder udfToolpack = new DefaultUDFFinder(names, impls);

            // 把 UDF 註冊到 Workbook(所有 POI Workbook 實現都支持 addToolPack)
            workbook.addToolPack(udfToolpack);

            // 計算公式並驗證結果
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            CellValue cv = evaluator.evaluate(formulaCell); // 返回 CellValue
            assertEquals(13.0, cv.getNumberValue(), 1e-6);   // 3^2 + 4 = 13
        }
    }

6、下拉選項和數據驗證

下拉和驗證的絕大多數使用場景都是為了生成 Excel 模板,方便用戶填寫數據,而不是在程式裡校驗。簡單的可以約束用戶輸入範圍,複雜的比如類似地域級聯下拉,甚至下拉框引用另一個 sheet。如果是動態的模板,手動配置成本巨高,這時候 POI 可以解決這個問題。
這塊的內容比較多,甚至可以單獨寫一篇,下面舉俩例子。

a、性別和年齡限制
@Test
void testGenerateUserTemplateWithValidations() throws Exception {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("用戶資訊");

        // 標題行
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("姓名");
        header.createCell(1).setCellValue("性別");
        header.createCell(2).setCellValue("年齡");

        DataValidationHelper helper = sheet.getDataValidationHelper();

        // 性別下拉
        DataValidationConstraint genderConstraint =
                helper.createExplicitListConstraint(new String[]{"男", "女"});
        DataValidation genderValidation =
                helper.createValidation(genderConstraint, new CellRangeAddressList(1, 100, 1, 1));

        // ✅ 明確開啟錯誤提示
        genderValidation.setShowErrorBox(true);
        genderValidation.createErrorBox("輸入錯誤", "只能選擇男女");
        sheet.addValidationData(genderValidation);

        // 年齡驗證:0–120
        DataValidationConstraint ageConstraint =
                helper.createNumericConstraint(
                        DataValidationConstraint.ValidationType.INTEGER,
                        DataValidationConstraint.OperatorType.BETWEEN, "0", "120");
        DataValidation ageValidation =
                helper.createValidation(ageConstraint, new CellRangeAddressList(1, 100, 2, 2));

        // ✅ 明確開啟錯誤提示
        ageValidation.setShowErrorBox(true);
        ageValidation.createErrorBox("輸入錯誤", "請輸入 0-120 的整數");
        sheet.addValidationData(ageValidation);

        // 寫入文件
        try (FileOutputStream out = new FileOutputStream("user_template.xlsx")) {
            wb.write(out);
        }
        wb.close();
    }

image

b、引用其他 sheet 作為下拉選項

以下示例將選項放到了 hidden 表,Sheet1 表用於下拉選擇。

@Test
void testCascadeDropdownMultiRow() throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");

        // 1. 創建隱藏 Sheet 存放下拉數據
        Sheet hidden = workbook.createSheet("hidden");
        //(為了看效果,暫時打開)
        workbook.setSheetHidden(workbook.getSheetIndex(hidden), false);

        // 省
        String[] provinces = {"廣東", "江蘇"};
        for (int i = 0; i < provinces.length; i++) {
            hidden.createRow(i).createCell(0).setCellValue(provinces[i]);
        }

        // 市
        String[] guangdongCities = {"廣州", "深圳"};
        String[] jiangsuCities = {"南京", "蘇州"};

        for (int i = 0; i < guangdongCities.length; i++) {
            hidden.getRow(i).createCell(1).setCellValue(guangdongCities[i]);
        }
        for (int i = 0; i < jiangsuCities.length; i++) {
            hidden.getRow(i).createCell(2).setCellValue(jiangsuCities[i]);
        }

        // 2. 定義命名區域
        Name nameProvince = workbook.createName();
        nameProvince.setNameName("province");
        nameProvince.setRefersToFormula("hidden!$A$1:$A$2");

        Name nameGuangdong = workbook.createName();
        nameGuangdong.setNameName("廣東");
        nameGuangdong.setRefersToFormula("hidden!$B$1:$B$2");

        Name nameJiangsu = workbook.createName();
        nameJiangsu.setNameName("江蘇");
        nameJiangsu.setRefersToFormula("hidden!$C$1:$C$2");

        // 3. 設置省下拉(多行)
        DataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        DataValidationConstraint provinceConstraint = helper.createFormulaListConstraint("province");

        // 假設我們需要 100 行
        CellRangeAddressList provinceAddressList = new CellRangeAddressList(0, 99, 0, 0); // A列 0~99行
        DataValidation provinceValidation = helper.createValidation(provinceConstraint, provinceAddressList);
        provinceValidation.setShowErrorBox(true);
        sheet.addValidationData(provinceValidation);

        // 4. 設置市下拉(依賴公式 INDIRECT,多行)
        for (int row = 0; row < 100; row++) {
            String formula = "INDIRECT(A" + (row + 1) + ")"; // A1~A100
            DataValidationConstraint cityConstraint = helper.createFormulaListConstraint(formula);
            CellRangeAddressList cityAddressList = new CellRangeAddressList(row, row, 1, 1); // B列對應行
            DataValidation cityValidation = helper.createValidation(cityConstraint, cityAddressList);
            cityValidation.setShowErrorBox(true);
            sheet.addValidationData(cityValidation);
        }

        // 5. 輸出文件
        try (FileOutputStream fos = new FileOutputStream("cascade_dropdown_multi.xlsx")) {
            workbook.write(fos);
        }
        workbook.close();
    }

級聯效果如下:
image

7、創建圖表(柱狀、折線)

POI 的圖表 API 使用 XDDF。XDDF 是基於 XSSF(xlsx)版本的 API,無法用於 .xls。生成的圖表在 Excel 打開後會自動渲染,不支持純文本查看。示例如下:

@Test
void testCreateBarChart() throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        // 其他代碼...
}

原文出處:https://juejin.cn/post/7558050672819028031


精選技術文章翻譯,幫助開發者持續吸收新知。

共有 0 則留言


精選技術文章翻譯,幫助開發者持續吸收新知。
🏆 本月排行榜
🥇
站長阿川
📝21   💬9   ❤️4
666
🥈
我愛JS
📝4   💬13   ❤️7
257
🥉
御魂
💬1  
3
#4
2
#5
Jack
1
評分標準:發文×10 + 留言×3 + 獲讚×5 + 點讚×1 + 瀏覽數÷10
本數據每小時更新一次
🔧 阿川の電商水電行
Shopify 顧問、維護與客製化
💡
小任務 / 單次支援方案
單次處理 Shopify 修正/微調
⭐️
維護方案
每月 Shopify 技術支援 + 小修改 + 諮詢
🚀
專案建置
Shopify 功能導入、培訓 + 分階段交付