[]
产品首页 在线Demo 产品文档 API 参考 技术社区
立即试用
(Showing Draft Content)

自定义函数

GcExcel Java为添加自定义函数提供了广泛的支持，从而使用户能够将自定义算术逻辑实现到电子表格中。自定义函数运行速度极快，可用于进行web服务调用，工作方式与本机Excel函数类似，并可在所有Excel平台上使用，包括主要操作系统（Windows、Mac、Mobile OS和Office：在线和离线）。

例如，在处理复杂的表单计算时，可以使用公司的专有函数、将嵌套公式应用于自定义函数，或者使用标准内置函数的组合。

为了在GcExcel Java中实现自定义函数，需要从 CustomFunction 类派生一个类，并在新创建的类中声明自定义函数以及函数名、返回类型和参数。

您还可以在自定义函数中使用自定义对象，如本主题的 示例 5 所示。如果将重载 Parameter 方法的一个参数设置为 FunctionValueType.Object 并且 acceptCustomObjects 设置为True时，可以使用自定义对象。类似地，如果返回类型是 FunctionValueType.Object，该公式可以返回自定义对象。

自定义函数中的缓存

同一列中的自定义函数将结果值存储为缓存。因此，当随后使用前一个参数调用列中的自定义函数时，自定义函数将使用缓存的值，而不是再次计算它。此功能有助于优化性能，尤其是在单个列中重复使用自定义函数的情况下。


然而，为了控制自定义函数的缓存行为，GcExcel Java在从 CustomFunction 类的继承类中提供了 setIsVolatile 方法。该方法允许您选择是每次为具有相同参数的列重新计算自定义函数，还是使用缓存结果。此方法的默认值为 false，这意味着应用于单个列的自定义函数维护自己的缓存，并在重复调用时重用它。有关实现，请参见示例6：创建易失性缓存。

创建自定义函数参考下面代码

在GcExcel Java中创建自定义函数涉及以下三个步骤。

  • 步骤 1: 定义自定义函数

  • 步骤 2: 使用 AddCustomFunction 方法在工作表中注册自定义函数

  • 步骤 3: 实现自定义功能

下面是一些可以创建并用于执行复杂计算任务的自定义函数的示例：

**注意：**GcExcel Java不允许用户导出自定义函数，即不支持将自定义函数保存到excel文件。如果用户尝试这样做，将抛出#NAME异常。

示例1：条件和函数

要在表单中创建和使用自定义条件和函数，请参阅以下示例代码。此函数可以根据所需的显示格式或样式（如内部颜色为红色的单元格）对单元格值求和。

// Step 1- Defining custom function: MyConditionalSum
// Creating a new class MyConditionalSumFunctionX by inheriting the CustomFunction class
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 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 toIterable(Object obj) {
        if (obj instanceof Iterable) 
        {
          return (Iterable) obj;
        } 
        else if (obj instanceof Object[][]) 
        {
            List list = new ArrayList();
            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 list = new ArrayList();
            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 list = new ArrayList();
            list.add(obj);
            return list;
        }
    }
}
// Step 2: Register the custom function using the AddCustomFunction method.
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyConditionalSumFunctionX());
IWorksheet worksheet = workbook.getActiveSheet();
        
// Step 3: Implement the custom function
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());
// Sum cells value which display format interior color are red.
worksheet.getRange("C1").setFormula("=MyConditionalSum(A1:A10)");
// Range["C1"]'s value is 40.
Object result = worksheet.getRange("C1").getValue();
// Display result in cell D1
worksheet.getRange("D1").setValue(result);

示例2：自定义连接函数

要在表单中创建和使用自定义连接函数，请参阅以下示例代码。

// Step 1- Defining custom function: MyConcatenate 
// Creating a new class MyConcatenateFunctionX by inheriting the CustomFunction class
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();
    }
}
// Step 2: Register the custom function using the AddCustomFunction method.
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyConcatenateFunctionX());
IWorksheet worksheet = workbook.getActiveSheet();
        
// Step 3: Implement the custom function
worksheet.getRange("A1").setFormula("=MyConcatenate(\"I\", \" \", \"work\", \" \",
\"with\", \" \", \"GcExcel\", \".\")");
worksheet.getRange("A2").setFormula("=MyConcatenate(A1, \"Documents.\")");
// Value of cell A1 is "I work with GcExcel."
Object resultA1 = worksheet.getRange("A1").getValue();
// Value of cell A2 is "I work with GcExcel Documents."
Object resultA2 = worksheet.getRange("A2").getValue();
// Display result in cell D1
worksheet.getRange("D1").setValue(resultA2);

示例3：合并区域函数

要在表单中创建和使用自定义合并区域函数，请参阅以下示例代码。

// Step 1- Defining custom function: MyIsMergedRange 
// Creating a new class MyIsMergedRangeFunctionX by inheriting the CustomFunction class
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 ranges = ((CalcReference) arguments[0]).getRanges();
                for (IRange range : ranges) {
                    return range.getMergeCells();
                }
            }
        }
        return false;
    }
}
// Step 2: Register the custom function using the AddCustomFunction method.
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyIsMergedRangeFunctionX());
IWorksheet worksheet = workbook.getActiveSheet();
        
// Step 3: Implement the custom function
worksheet.getRange("A1:B2").merge();
worksheet.getRange("C1").setFormula("=MyIsMergedRange(A1)");
worksheet.getRange("C2").setFormula("=MyIsMergedRange(H2)");
// A1 is a merged cell, getRange("C1")'s value is true.
Object resultC1 = worksheet.getRange("C1").getValue();
// H2 is not a merged cell, getRange("C2")'s value is false.
Object resultC2 = worksheet.getRange("C2").getValue();
// Display result in cell D1
worksheet.getRange("D1").setValue(resultC2);

示例4：错误检测函数

要在表单中创建和使用自定义错误检测函数，请参阅以下示例代码。

// Step 1- Defining custom function: MyIsError 
// Creating a new class MyIsErrorFunctionX by inheriting the CustomFunction class
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;
          }
}
// Step 2: Register the custom function using the AddCustomFunction method.
Workbook workbook = new Workbook();
Workbook.AddCustomFunction(new MyIsErrorFunctionX());
IWorksheet worksheet = workbook.getActiveSheet();
        
// Step 3: Implement the custom function
worksheet.getRange("A1").setValue(CalcError.Num);
worksheet.getRange("A2").setValue(100);
worksheet.getRange("B1").setFormula("=MyIsError(A1)");
worksheet.getRange("B2").setFormula("=MyIsError(A2)");
// getRange("B1")'s value is true.
Object resultB1 = worksheet.getRange("B1").getValue();
// getRange("B2")'s value is false.
Object resultB2 = worksheet.getRange("B2").getValue();
// Display result in cell D2
worksheet.getRange("D2").setValue(resultB2);

示例 5：使用自定义对象的最大公除法函数

请参考以下示例代码创建和使用 BigInteger 函数计算最大公除法。

// Formula implementation
 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;
        }
    }
}
//create a new workbook
Workbook workbook = new Workbook();
try
{
    Workbook.AddCustomFunction(new BigIntegerPowFunction());
}
catch (RuntimeException ex)
{
    // Function was added
} // End Try
try
{
    Workbook.AddCustomFunction(new BigIntegerMultiplyFunction());
}
catch (RuntimeException ex)
{
    // Function was added
} // End Try
try
{
    Workbook.AddCustomFunction(new GreatestCommonDivisionFunction());
}
catch (RuntimeException ex)
{
// Function was added
} // End Try
        
// Use BigInteger to calculate results
IWorksheet worksheet = workbook.getActiveSheet();
worksheet.getRange("A1").setValue("154382190 ^ 3 = ");
worksheet.getRange("A2").setValue("1643590 * 166935 = ");
worksheet.getRange("A3").setValue("Greatest common division = ");
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)");
        
// Arrange
worksheet.getColumns().get(0).autoFit();
worksheet.getColumns().get(1).setColumnWidth(worksheet.getRange("B1").getText().length() + 1);
 
//save to an pdf file
workbook.save("CustomObjectInCustomFunction.pdf");

示例6：创建易失性自定义函数

下面的示例演示如何创建用于生成 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("-","");
    }
}
// Create a new workbook
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's value has changed.
Object valueA1After = worksheet.getRange("A1").getValue();
System.out.println(valueA1After);

示例 7：创建异步自定义函数

异步函数是以异步或并发方式提供其结果的任何函数。异步函数具有非阻塞体系结构，因此一个任务的执行不依赖于另一个任务。任务可以同时运行。运行异步函数可以通过允许同时运行多个计算来提高性能。GcExcel 通过从 AsyncCustomFunction 类派生函数，使函数能够执行异步计算。 evaluateAsync 方法异步计算函数。GcExcel 还在 CalcError 枚举中提供了一个枚举值“Busy”，该值指示单元格正在计算异步公式。

参考以下示例代码添加和使用自定义异步函数：

public class AsyncFunction {

    public static void main(String[] args) {
        // Register Async custom function.
        Workbook.AddCustomFunction(new MyAddFunction());
        
        // Implement the Async custom Function.
        Workbook workbook = new Workbook();
        IWorksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.getRange("A1").setValue(1);
        worksheet.getRange("B1").setValue(2);
        
        // Add the cell values.
        worksheet.getRange("C1").setFormula("=MyAdd(A1,B1)");
        Object value1 = worksheet.getRange("C1").getValue();
        
        // Display result. The result will be "Busy".
        System.out.println(value1);
        Thread.sleep(2000);
        Object value2 = worksheet.getRange("C1").getValue();
        
        // Display result. The result will be "3".
        System.out.println(value2);
    }
}

// Define Async custom function: MyAddFunction.
class MyAddFunction extends AsyncCustomFunction {
    public MyAddFunction() {
        super("MyAdd", FunctionValueType.Number, new Parameter[] { new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) });
    }

    @Override
    public CompletableFuture<Object> evaluateAsync(Object[] arguments, ICalcContext context) {
        return CompletableFuture.supplyAsync(() -> {
            try {
                Thread.sleep(10);
            } catch (InterruptedException e) {
            }
            return (double)arguments[0] + (double)arguments[1];
        });
    }
}

限制

AsyncCustomFunction 的参数不接受任何单元格引用，因为异步函数可能在另一个线程中运行，如果使用单元格引用，将导致多线程冲突。同样，不允许在异步函数中使用 IWorksheet 和 IWorkbook 等对象。