[]
GcExcel Java 支持添加自定义函数，这使用户能够在电子表格中实现自定义的算术逻辑。这些函数运行速度极快，可调用 Web 服务，与原生 Excel 函数外观相似，且能跨所有 Excel 平台使用，包括主流操作系统（Windows、Mac、移动操作系统）以及 Office（在线和离线版本）。
例如，您可以使用公司的专有函数、将自定义函数嵌套在公式中，或结合标准内置函数来处理复杂的电子表格计算。
要在 GcExcel Java 中实现自定义函数，必须创建一个继承自 CustomFunction 类的派生类，并在新类中声明自定义函数，包括函数名称、返回类型和参数。
您还可以在自定义函数中使用自定义对象，如本主题的 示例 5 所示。如果重载的 Parameter 方法中有一个参数被设置为 FunctionValueType.Object，且 acceptCustomObjects 被设为 True，则可以使用自定义对象。同样，如果返回类型为 FunctionValueType.Object，公式可以返回自定义对象。
创建自定义函数请遵循以下步骤：
步骤 1：定义自定义函数：创建一个继承自 CustomFunction 的新类，并实现所需逻辑。
步骤 2：注册自定义函数：使用 AddCustomFunction() 方法将自定义函数注册到工作表。
步骤 3：实现自定义函数：编写自定义函数的具体实现代码。
当将自定义函数导出到 .xlsx 文件时，Excel 无法识别这些函数，因此包含自定义函数的单元格将显示 #NAME 错误。
同一列中的自定义函数会将计算结果存储在缓存中。因此，当某一列中的自定义函数后续使用相同参数被调用时，会直接使用缓存值，而不再重新计算。此功能有助于优化性能，尤其是在同一列中重复使用自定义函数的场景。
不过，为了控制自定义函数的缓存行为，GcExcel Java 在继承自 CustomFunction 类的类中提供了 setIsVolatile 方法。该方法允许您选择：对于参数相同的列，是每次重新计算自定义函数，还是使用缓存结果。此方法的默认值为 false，即应用于单列的自定义函数会维护自己的缓存，并在重复调用时复用缓存。
以下示例演示如何创建一个生成 GUID 的自定义函数。为了每次生成唯一的 GUID，自定义函数不应使用缓存。因此，示例代码将 setIsVolatile 方法设置为 true，以便每次调用时生成新的 GUID。
public class GeneralID extends CustomFunction {
public GeneralID() {
super("GeneralID", FunctionValueType.Object);
this.setIsVolatile(true);
}
@Override
public Object evaluate(Object[] objects, ICalcContext iCalcContext) {
return UUID.randomUUID().toString().replaceAll("-","");
}
}// 创建一个新工作簿
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new GeneralID());
IWorksheet worksheet = workbook.getActiveSheet();
worksheet.getRange("A1").setFormula("=GeneralID()");
Object valueA1Before = worksheet.getRange("A1").getValue();
worksheet.getRange("A2").setFormula("=GeneralID()");
// A1 的值已更改
Object valueA1After = worksheet.getRange("A1").getValue();
System.out.println(valueA1After);使用自定义函数时，传入函数的输入参数可能包含错误值，如 #DIV/0!、#N/A 或 #VALUE! 等。与 MS Excel 类似，默认情况下，如果任何参数包含错误，函数将不会被调用，且该错误会直接作为公式单元格的结果返回。
为了帮助开发人员处理函数参数中的错误值，GcExcel Java 在 CustomFunction 类中提供了 setAcceptErrors 方法。如果 setAcceptErrors 被设为 True，即使输入中存在错误，自定义函数也会被调用。默认情况下，setAcceptErrors 为 false，因此如果任何参数是错误值，函数将不会执行，且错误会直接返回。
有关实现细节，请参见以下示例代码。
public class MyFunctionWithAcceptErrors extends CustomFunction {
private boolean acceptErrors = false;
public MyFunctionWithAcceptErrors(String funcName, boolean acceptError) {
super(funcName, FunctionValueType.Text);
this.acceptErrors = acceptError;
}
@Override
public boolean getAcceptErrors() {
return acceptErrors;
}
@Override
public void setAcceptErrors(boolean value) {
acceptErrors = value;
}
@Override
public Object evaluate(Object[] arguments, ICalcContext context) {
if (arguments == null || arguments.length == 0) {
return null;
}
StringBuilder result = new StringBuilder();
for (Object argument : arguments) {
if (argument instanceof CalcError) {
CalcError calcError = (CalcError) argument;
if (result.length() > 0) {
result.append(", #").append(calcError);
} else {
result.append("Exist errors: #").append(calcError);
}
}
}
return result.length() > 0 ? result.toString() : "No error";
}
}// 创建一个新工作簿
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncCanAcceptErrors", true));
Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncNotAcceptErrors", false));
IWorksheet worksheet = workbook.getActiveSheet();
worksheet.getRange("A1:B1").merge();
worksheet.getRange("A7:B7").merge();
worksheet.getRange("A1:B1").setValue("接受错误的自定义函数");
worksheet.getRange("A7:B7").setValue("不接受错误的自定义函数");
worksheet.getRange("A2, A8").setValue("公式");
worksheet.getRange("B2, B8").setValue("结果");
worksheet.getRange("A1:B1, A7:B7, A2, A8, B2, B8").getFont().setBold(true);
worksheet.getRange("A2, A8, B2, B8").setHorizontalAlignment(HorizontalAlignment.Center);
// 单元格 B3 的值为 "Exist errors: #Div0"
worksheet.getRange("A3").setValue("=MyFuncCanAcceptErrors(1, 2, 1/0)");
worksheet.getRange("B3").setFormula("=MyFuncCanAcceptErrors(1, 2, 1/0)");
// 单元格 B4 的值为 "Exist errors: #Value"
worksheet.getRange("A4").setValue("=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)");
worksheet.getRange("B4").setFormula("=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)");
// 单元格 B5 的值为 "Exist errors: #Name, #Num"
worksheet.getRange("A5").setValue("=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)");
worksheet.getRange("B5").setFormula("=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)");
// 单元格 B9 的值为 #DIV/0! 错误
worksheet.getRange("A9").setValue("=MyFuncNotAcceptErrors(1, 2, 1/0)");
worksheet.getRange("B9").setFormula("=MyFuncNotAcceptErrors(1, 2, 1/0)");
// 单元格 B10 的值为 #VALUE! 错误
worksheet.getRange("A10").setValue("=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)");
worksheet.getRange("B10").setFormula("=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)");
// 单元格 B11 的值为 #NAME? 错误
worksheet.getRange("A11").setValue("=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)");
worksheet.getRange("B11").setFormula("=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)");
worksheet.getRange("A:B").autoFit();
worksheet.getPageSetup().setPrintHeadings(true);
worksheet.getPageSetup().setPrintGridlines(true);
worksheet.getPageSetup().setOrientation(PageOrientation.Landscape);
worksheet.getPageSetup().setPrintArea("$A$1:$G$20");
// 保存为 PDF 文件
workbook.save("AcceptErrorsInCustomFunction.pdf");输出结果如下图所示：
要在电子表格中创建和使用自定义条件求和函数，请参考以下示例代码。该函数可以根据所需的显示格式或样式（如内部颜色为红色的单元格）对单元格值求和。
// 步骤 1 - 定义自定义函数：MyConditionalSum
// 创建一个继承自 CustomFunction 类的新类 MyConditionalSumFunctionX
class MyConditionalSumFunctionX extends CustomFunction
{
public MyConditionalSumFunctionX()
{
super("MyConditionalSum", FunctionValueType.Number, CreateParameters());
}
private static Parameter[] CreateParameters()
{
Parameter[] parameters = new Parameter[254];
for (int i = 0; i < 254; i++)
{
parameters[i] = new Parameter(FunctionValueType.Object, true);
}
return parameters;
}
@Override
public Object evaluate(Object[] arguments, ICalcContext context)
{
double sum = 0d;
for (Object argument : arguments)
{
Iterable<Object> iterator = toIterable(argument);
for (Object item : iterator)
{
if (item instanceof CalcError)
{
return item;
}
else if (item instanceof Double)
{
sum += (double) item;
}
}
}
return sum;
}
private static Iterable<Object> toIterable(Object obj) {
if (obj instanceof Iterable)
{
return (Iterable) obj;
}
else if (obj instanceof Object[][])
{
List<Object> list = new ArrayList<Object>();
Object[][] array = (Object[][]) obj;
for (int i = 0; i < array.length; i++)
{
for (int j = 0; j < array[i].length; j++)
{
list.add(array[i][j]);
}
}
return list;
}
else if (obj instanceof CalcReference)
{
List<Object> list = new ArrayList<Object>();
CalcReference reference = (CalcReference) obj;
for (IRange range : reference.getRanges())
{
int rowCount = range.getRows().getCount();
int colCount = range.getColumns().getCount();
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
if (range.getCells().get(i, j).getDisplayFormat().getInterior().getColor().equals(Color.getRed()))
{
list.add(range.getCells().get(i, j).getValue());
}
}
}
}
return list;
}
else
{
List<Object> list = new ArrayList<Object>();
list.add(obj);
return list;
}
}
}// 步骤 2：使用 AddCustomFunction 方法注册自定义函数
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyConditionalSumFunctionX());
IWorksheet worksheet = workbook.getActiveSheet();
// 步骤 3：实现自定义函数
worksheet.getRange("A1:A10").setValue(new Object[][]
{
{ 1 }, { 2 }, { 3 }, { 4 }, { 5 },
{ 6 }, { 7 }, { 8 }, { 9 }, { 10 }
});
IFormatCondition cellValueRule = (IFormatCondition) worksheet.getRange("A1:A10").getFormatConditions()
.add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 5, null);
cellValueRule.getInterior().setColor(Color.getRed());
// 对显示格式中内部颜色为红色的单元格值求和
worksheet.getRange("C1").setFormula("=MyConditionalSum(A1:A10)");
// 单元格 C1 的值为 40
Object result = worksheet.getRange("C1").getValue();
// 在单元格 D1 中显示结果
worksheet.getRange("D1").setValue(result);要在电子表格中创建和使用自定义拼接函数，请参考以下示例代码。
// 步骤 1 - 定义自定义函数：MyConcatenate
// 创建一个继承自 CustomFunction 类的新类 MyConcatenateFunctionX
class MyConcatenateFunctionX extends CustomFunction
{
public MyConcatenateFunctionX() {
super("MyConcatenate", FunctionValueType.Text, CreateParameters());
}
static Parameter[] CreateParameters()
{
Parameter[] parameters = new Parameter[254];
for (int i = 0; i < 254; i++)
{
parameters[i] = new Parameter(FunctionValueType.Variant);
}
return parameters;
}
@Override
public Object evaluate(Object[] arguments, ICalcContext context)
{
StringBuilder sb = new StringBuilder();
for (Object argument : arguments)
{
if (argument instanceof CalcError)
{
return argument;
}
if (argument instanceof String || argument instanceof Double) {
sb.append(argument);
}
}
return sb.toString();
}
}// 步骤 2：使用 AddCustomFunction 方法注册自定义函数
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyConcatenateFunctionX());
IWorksheet worksheet = workbook.getActiveSheet();
// 步骤 3：实现自定义函数
worksheet.getRange("A1").setFormula("=MyConcatenate(\"I\", \" \", \"work\", \" \",\"with\", \" \", \"Google\", \".\")");
worksheet.getRange("A2").setFormula("=MyConcatenate(A1, \"Documents.\")");
// 单元格 A1 的值为 "I work with Google."
Object resultA1 = worksheet.getRange("A1").getValue();
// 单元格 A2 的值为 "I work with Google Documents."
Object resultA2 = worksheet.getRange("A2").getValue();
// 在单元格 D1 中显示结果
worksheet.getRange("D1").setValue(resultA2);要在电子表格中创建和使用自定义合并区域函数，请参考以下示例代码。
// 步骤 1 - 定义自定义函数：MyIsMergedRange
// 创建一个继承自 CustomFunction 类的新类 MyIsMergedRangeFunctionX
class MyIsMergedRangeFunctionX extends CustomFunction
{
public MyIsMergedRangeFunctionX()
{
super("MyIsMergedRange", FunctionValueType.Boolean,
new Parameter[] { new Parameter(FunctionValueType.Object, true) });
}
@Override
public Object evaluate(Object[] arguments, ICalcContext context)
{
if (arguments[0] instanceof CalcReference) {
if (arguments[0] instanceof CalcReference) {
List<IRange> ranges = ((CalcReference) arguments[0]).getRanges();
for (IRange range : ranges) {
return range.getMergeCells();
}
}
}
return false;
}
}// 步骤 2：使用 AddCustomFunction 方法注册自定义函数
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyIsMergedRangeFunctionX());
IWorksheet worksheet = workbook.getActiveSheet();
// 步骤 3：实现自定义函数
worksheet.getRange("A1:B2").merge();
worksheet.getRange("C1").setFormula("=MyIsMergedRange(A1)");
worksheet.getRange("C2").setFormula("=MyIsMergedRange(H2)");
// A1 是合并单元格，单元格 C1 的值为 true
Object resultC1 = worksheet.getRange("C1").getValue();
// H2 不是合并单元格，单元格 C2 的值为 false
Object resultC2 = worksheet.getRange("C2").getValue();
// 在单元格 D1 中显示结果
worksheet.getRange("D1").setValue(resultC2);要在电子表格中创建和使用自定义错误检测函数，请参考以下示例代码。
// 步骤 1 - 定义自定义函数：MyIsError
// 创建一个继承自 CustomFunction 类的新类 MyIsErrorFunctionX
class MyIsErrorFunctionX extends CustomFunction
{
public MyIsErrorFunctionX()
{
super("MyIsError", FunctionValueType.Boolean, new Parameter[]{new Parameter(FunctionValueType.Variant)});
}
@Override
public Object evaluate(Object[] arguments, ICalcContext context)
{
if (arguments[0] instanceof CalcError)
{
if ((CalcError) arguments[0] != CalcError.None && (CalcError) arguments[0] != CalcError.GettingData)
{
return true;
} else
{
return false;
}
}
return false;
}
}// 步骤 2：使用 AddCustomFunction 方法注册自定义函数
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyIsErrorFunctionX());
IWorksheet worksheet = workbook.getActiveSheet();
// 步骤 3：实现自定义函数
worksheet.getRange("A1").setValue(CalcError.Num);
worksheet.getRange("A2").setValue(100);
worksheet.getRange("B1").setFormula("=MyIsError(A1)");
worksheet.getRange("B2").setFormula("=MyIsError(A2)");
// 单元格 B1 的值为 true
Object resultB1 = worksheet.getRange("B1").getValue();
// 单元格 B2 的值为 false
Object resultB2 = worksheet.getRange("B2").getValue();
// 在单元格 D2 中显示结果
worksheet.getRange("D2").setValue(resultB2);参考以下示例代码，创建并使用 BigInteger 函数来计算最大公约数。
// 公式实现
public static class BigIntegerMultiplyFunction extends CustomFunction
{
public BigIntegerMultiplyFunction()
{
super("BIG.INTEGER.MULT", FunctionValueType.Object, new Parameter[]
{
new Parameter(FunctionValueType.Text),
new Parameter(FunctionValueType.Text)
});
}
@Override
public Object evaluate(Object[] arguments, ICalcContext context)
{
if (!(arguments[0] instanceof String) || !(arguments[1] instanceof String))
{
return CalcError.Value;
}
String leftNumber = (String)arguments[0];
String rightNumber = (String)arguments[1];
try
{
return new BigInteger(leftNumber).multiply(new BigInteger(rightNumber));
}
catch (NumberFormatException e)
{
return CalcError.Value;
}
catch (ArithmeticException e2)
{
return CalcError.Value;
}
}
}
public static class BigIntegerPowFunction extends CustomFunction
{
public BigIntegerPowFunction()
{
super("BIG.INTEGER.POW", FunctionValueType.Object, new Parameter[]
{
new Parameter(FunctionValueType.Text),
new Parameter(FunctionValueType.Number)
});
}
@Override
public Object evaluate(Object[] arguments, ICalcContext context)
{
if (!(arguments[0] instanceof String) || !(arguments[1] instanceof Double))
{
return CalcError.Value;
}
String number = (String)arguments[0];
double exp = (Double)arguments[1];
if (exp > Integer.MAX_VALUE || exp < Integer.MIN_VALUE)
{
return CalcError.Value;
}
int iExp = CInt(exp);
try
{
return new BigInteger(number).pow(iExp);
}
catch (NumberFormatException e)
{
return CalcError.Value;
}
catch (ArithmeticException e2)
{
return CalcError.Value;
}
}
public static int CInt(double source)
{
int floor = (int)Math.floor(source);
if (Math.abs(source - floor) == 0.5)
{
if (floor % 2 == 0)
return floor;
else
return (int)Math.ceil(source);
}
else if (Math.abs(source - floor) < 0.5)
return floor;
else
return (int)Math.ceil(source);
}
}
public static class GreatestCommonDivisionFunction extends CustomFunction
{
public GreatestCommonDivisionFunction()
{
super("BIG.INTEGER.GCD", FunctionValueType.Object, new Parameter[]
{
new Parameter(FunctionValueType.Object, false, true),
new Parameter(FunctionValueType.Object, false, true)
});
}
@Override
public Object evaluate(Object[] arguments, ICalcContext context)
{
if (!(arguments[0] instanceof BigInteger) || !(arguments[1] instanceof BigInteger))
{
return CalcError.Value;
}
BigInteger leftNumber = (BigInteger)arguments[0];
BigInteger rightNumber = (BigInteger)arguments[1];
try
{
return leftNumber.gcd(rightNumber);
}
catch (ArithmeticException e)
{
return CalcError.Value;
}
}
}// 创建一个新工作簿
Workbook workbook = new Workbook();
try
{
Workbook.AddCustomFunction(new BigIntegerPowFunction());
}
catch (RuntimeException ex)
{
// 函数已添加
} // End Try
try
{
Workbook.AddCustomFunction(new BigIntegerMultiplyFunction());
}
catch (RuntimeException ex)
{
// 函数已添加
} // End Try
try
{
Workbook.AddCustomFunction(new GreatestCommonDivisionFunction());
}
catch (RuntimeException ex)
{
// 函数已添加
} // End Try
// 使用 BigInteger 计算结果
IWorksheet worksheet = workbook.getActiveSheet();
worksheet.getRange("A1").setValue("154382190 ^ 3 = ");
worksheet.getRange("A2").setValue("1643590 * 166935 = ");
worksheet.getRange("A3").setValue("最大公约数 = ");
worksheet.getRange("B1").setFormula("=BIG.INTEGER.POW(\"154382190\", 3)");
worksheet.getRange("B2").setFormula("=BIG.INTEGER.MULT(\"1643590\", \"166935\")");
worksheet.getRange("B3").setFormula("=BIG.INTEGER.GCD(B1,B2)");
// 调整格式
worksheet.getColumns().get(0).autoFit();
worksheet.getColumns().get(1).setColumnWidth(worksheet.getRange("B1").getText().length() + 1);
// 保存为 PDF 文件
workbook.save("CustomObjectInCustomFunction.pdf");