Apache POI运行时添加FORMULATEXT函数的实现方法

问题原因 #

Apache POI默认未内置FORMULATEXT函数的实现，计算公式时会抛出NotImplementedFunctionException，错误提示中的_xlfn.FORMULATEXT是POI对Excel新增函数的标识前缀。

解决步骤 #

1. 自定义FORMULATEXT函数实现 #

创建类实现POI的FreeRefFunction接口，模拟Excel中FORMULATEXT的行为：

import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;

public class FormulaTextFunction implements FreeRefFunction {

    public static final FreeRefFunction INSTANCE = new FormulaTextFunction();

    @Override
    public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) throws EvaluationException {
        // 校验参数个数，FORMULATEXT仅接受1个参数
        if (args.length != 1) {
            return ErrorEval.VALUE_INVALID;
        }

        // 解析参数为单元格引用
        ValueEval arg = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex());
        if (!(arg instanceof org.apache.poi.ss.formula.eval.RefEval)) {
            return ErrorEval.REF_INVALID;
        }

        org.apache.poi.ss.formula.eval.RefEval refEval = (org.apache.poi.ss.formula.eval.RefEval) arg;
        Cell targetCell = refEval.getInnerValueEval(refEval.getFirstSheetIndex()).getCell(refEval.getRow(), refEval.getColumn());

        // 单元格为空或无公式时，返回Excel原生的#N/A错误
        if (targetCell == null || targetCell.getCellType() != CellType.FORMULA) {
            return ErrorEval.NA_INVALID;
        }

        // 返回目标单元格的公式文本
        return new StringEval(targetCell.getCellFormula());
    }
}

2. 注册自定义函数到POI评估器 #

在创建公式评估器后，将自定义函数注册到工作簿的评估器中：

// 假设workbook是你的XSSFWorkbook或HSSFWorkbook实例
WorkbookEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator().getWorkbookEvaluator();
// 注册函数，名称需匹配错误提示中的_xlfn.FORMULATEXT
evaluator.registerFunction("_xlfn.FORMULATEXT", FormulaTextFunction.INSTANCE);

3. 原有公式无需修改 #

你之前的公式代码可以保持不变，POI会自动将FORMULATEXT映射到注册的_xlfn.FORMULATEXT函数：

cell.setCellFormula("IF(P"+(row+1)+"<>\"\",MID(FORMULATEXT(P"+(row+1)+"),3,LEN(FORMULATEXT(P"+(row+1)+"))),\"-\")");

注意事项 #

• 确保使用POI 3.17及以上版本，该版本开始支持完善的自定义函数注册机制。
• 可根据需求调整自定义函数的返回逻辑，比如无公式时返回空字符串，但建议尽量对齐Excel原生行为。

内容的提问来源于stack exchange，提问作者Agnese Tedeschi