如何使用来自Apache POI公式的excel文本函数"NUMBERVALUE"

Question

我正在使用Apache POI来评估具有文本函数"NUMBERVALUE"的公式单元格，然后我得到了一个异常由：org.apache.poi.ss.formula.eval.NotImplementFunctionException：_xlfn。数字值

有趣的是，当我尝试注册此功能时WorkbookEvaluator.registerFunction("NUMBERVALUE", new NumberRValue());它给了我另一个错误：java.lang.IllegalArgumentException：NUMBERVALUE 不是 Excel Analysis Toolpack 中的函数。

我还尝试通过实现 FreeRefFunction 来使用用户定义的函数，它再次回到第一个错误。

由： org.apache.poi.ss.formula.eval.NotImplementFunctionException： _xlfn.数字值

如何在 Apache POI 中实现文本函数？

Answer 1

WorkbookEvaluator.registerFunction仅适用于至少知道每个名称apache poi函数。以下是通过以下方式列出的所有功能：

java.util.Collection<String> unsupportedFuncs = org.apache.poi.ss.formula.WorkbookEvaluator.getNotSupportedFunctionNames();
System.out.println(unsupportedFuncs);

所有列出的函数都可以使用WorkbookEvaluator.registerFunction作为org.apache.poi.hssf.record.formula.functions.Function注册，如果org.apache.poi.hssf.record.formula.atp.AnalysisToolPak，则注册为org.apache.poi.hssf.record.formula.functions.FreeRefFunction如果没有。

但是NUMBERVALUE函数不在此列表中。因此，这只能添加为用户定义的函数。请参阅apache poi的文档的用户定义函数。

该函数必须实现org.apache.poi.ss.formula.functions.FreeRefFunction并且必须在Workbook的 UDF 工具包中注册。

以下完整示例显示了_xlfn.NUMBERVALUE的基本实现。实现是使用 NUMBERVALUE 函数中的描述完成的。到目前为止，它是一个工作草案，可能需要改进以更好地实现与 Excel 自己的结果的兼容性。

import java.io.FileInputStream;
import java.util.Locale;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
import org.apache.poi.ss.formula.udf.DefaultUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.util.LocaleUtil;
public class EvaluateNUMBERVALUE {
public static void main( String[] args ) throws Exception {
Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelWithNUMBERVALUE.xlsx"));    

String[] functionNames = { "_xlfn.NUMBERVALUE" } ;
FreeRefFunction[] functionImpls = { new NumberValue() } ;
UDFFinder udfs = new DefaultUDFFinder( functionNames, functionImpls ) ;
UDFFinder udfToolpack = new AggregatingUDFFinder( udfs ) ;    
workbook.addToolPack(udfToolpack);

LocaleUtil.setUserLocale(Locale.US);
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

DataFormatter dataFormatter = new DataFormatter();

for (Sheet sheet: workbook) {
for (Row row : sheet) {
for (Cell cell : row) {
String cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
System.out.println(cellValue);
}
}   
}
}
}

上面代码中使用的类NumberValue：

import java.lang.NumberFormatException;
import java.util.Locale;
import java.text.DecimalFormatSymbols;
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.eval.NumberEval;
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.functions.FreeRefFunction;
import org.apache.poi.util.LocaleUtil;

public final class NumberValue implements FreeRefFunction  {
@Override
public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {

Locale locale = LocaleUtil.getUserLocale();
DecimalFormatSymbols decimalFormatSymbols = new DecimalFormatSymbols(locale);

String text = null;
//If the Decimal_separator and Group_separator arguments are not specified, separators from the current locale are used.
String decSep = String.valueOf(decimalFormatSymbols.getDecimalSeparator());
String groupSep = String.valueOf(decimalFormatSymbols.getGroupingSeparator());
Double result = Double.NaN;
ValueEval v1 = null;
ValueEval v2 = null;
ValueEval v3 = null;

try {
if (args.length == 1) {  
v1 = OperandResolver.getSingleValue( args[0], ec.getRowIndex(), ec.getColumnIndex());
text = OperandResolver.coerceValueToString(v1);
} else if (args.length == 2) { 
v1 = OperandResolver.getSingleValue( args[0], ec.getRowIndex(), ec.getColumnIndex());
v2 = OperandResolver.getSingleValue( args[1], ec.getRowIndex(), ec.getColumnIndex());
text = OperandResolver.coerceValueToString(v1);
decSep = OperandResolver.coerceValueToString(v2).substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
} else if (args.length == 3) { 
v1 = OperandResolver.getSingleValue( args[0], ec.getRowIndex(), ec.getColumnIndex());
v2 = OperandResolver.getSingleValue( args[1], ec.getRowIndex(), ec.getColumnIndex());
v3 = OperandResolver.getSingleValue( args[2], ec.getRowIndex(), ec.getColumnIndex());
text = OperandResolver.coerceValueToString(v1);
decSep = OperandResolver.coerceValueToString(v2).substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
groupSep = OperandResolver.coerceValueToString(v3).substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
}
} catch (EvaluationException e) {
e.printStackTrace() ;
return e.getErrorEval();
}  

if("".equals(text)) text = "0"; //If an empty string ("") is specified as the Text argument, the result is 0.
text = text.replace(" ", ""); //Empty spaces in the Text argument are ignored, even in the middle of the argument. For example, " 3 000 " is returned as 3000.
String[] parts = text.split("["+decSep+"]");
String sigPart = "";
String decPart = "";
if (parts.length > 2) return ErrorEval.VALUE_INVALID; //If a decimal separator is used more than once in the Text argument, NUMBERVALUE returns the #VALUE! error value.
if (parts.length > 1) {
sigPart = parts[0];
decPart = parts[1];
if (decPart.contains(groupSep)) return ErrorEval.VALUE_INVALID; //If the group separator occurs after the decimal separator in the Text argument, NUMBERVALUE returns the #VALUE! error value.
sigPart = sigPart.replace(groupSep, ""); //If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored.
text = sigPart + "." + decPart;
} else if (parts.length > 0) {
sigPart = parts[0];
sigPart = sigPart.replace(groupSep, ""); //If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored.
text = sigPart;
} 

//If the Text argument ends in one or more percent signs (%), they are used in the calculation of the result. 
//Multiple percent signs are additive if they are used in the Text argument just as they are if they are used in a formula. 
//For example, =NUMBERVALUE("9%%") returns the same result (0.0009) as the formula =9%%.
int countPercent = 0;
while (text.endsWith("%")) {
countPercent++;
text = text.substring(0, text.length()-1);   
}

try {  
result = Double.valueOf(text);
result = result / Math.pow(100, countPercent); //If the Text argument ends in one or more percent signs (%), they are used in the calculation of the result. 
checkValue(result);
} catch (EvaluationException e) {
e.printStackTrace() ;
return e.getErrorEval();
} catch (Exception anyex) {
return ErrorEval.VALUE_INVALID; //If any of the arguments are not valid, NUMBERVALUE returns the #VALUE! error value.
}

return new NumberEval(result);

}

static final void checkValue(double result) throws EvaluationException {
if (Double.isNaN(result) || Double.isInfinite(result)) {
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
}
}

前缀_xlfn.呢？

Excel使用它来标记 Excel 2007 之后引入的功能。前缀存储为函数名称的一部分。如果 Excel 版本知道该函数，因为它高于 Excel 2007，则 GUI 将不会显示该前缀并计算该函数。如果 Excel 版本不知道该功能，则 GUI 将显示前缀以通知用户该不兼容。请参阅问题：_xlfn。前缀显示在公式前面

由于存储了前缀，因此必须注册用户定义的函数，其函数名称包括前缀：String[] functionNames = { "_xlfn.NUMBERVALUE" };。

---

此代码是公开的。它可以在任何类型的项目中自由重用。当然，我这边没有任何保证。