Custom Functions | Document Solutions for Excel, Java Edition

Custom Functions

In This Topic

DsExcel Java provides support for adding custom functions, thus enabling users to implement custom arithmetic logic to spreadsheets. These functions run extremely fast, can make web service calls, look similar to the native Excel functions, and can be used across all Excel platforms including major operating systems (Windows, Mac, Mobile OS and Office: both online and offline).

For instance, you can use company’s proprietary functions, apply a nested formula with custom functions, or use a combination of standard built-in functions to handle complex spreadsheet calculations.

To implement custom functions in DsExcel Java, you must create a derived class from the CustomFunction class and declare the custom function in the new class along with the function name, return type, and parameters.

You can also use custom objects in custom functions as demonstrated by the Example 5 of this topic. If one parameter of overloaded Parameter method is set to FunctionValueType.Object and acceptCustomObjects is set to True, custom objects can be used. Similarly, if the return type is FunctionValueType.Object, the formula can return custom objects.

Caching in Custom Functions

Custom functions in the same column store resultant value as cache. Hence, when a custom function in a column is called subsequently with previous parameter, custom function uses the cached value instead of calculating it again. This feature helps in optimizing performance especially in case of repetitive use of the custom function in a single column.

However, to control this caching behavior of custom functions, DsExcel Java provides setIsVolatile method in the class inherited from CustomFunction class. The method lets you choose whether to recalculate a custom function for a column having same parameters every time or use the cached result. The default value of this method is false, which means custom function applied on a single column maintains its own cache and reuses it on a repeated call. For implementation, see Example 6: Create Volatile Cache.

Create Custom Function Using Code

Creating custom function in DsExcel Java involves following three steps.

Step 1: Define a custom function
Step 2: Register the custom function in your worksheet using the AddCustomFunction method
Step 3: Implement the custom function

Shared below are some examples of custom functions that can be created and used to perform complex calculation tasks:

Note: DsExcel Java doesn't allow users to export custom functions i.e. saving custom functions to an excel file is not supported. If a user tries to do so, the #NAME exception will be thrown.

Example 1: Conditional Sum Function

To create and use custom conditional sum function in your spreadsheet, refer to the following example code. This function can sum cell values based on the desired display format or style (like cells with interior color as red).

Java	Copy Code
// 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<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; } } }

Java

Copy Code

// 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<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;
        }
    }
}

Java	Copy Code
// 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);

Java

Copy Code

// 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);

Example 2: Custom Concatenation Function

To create and use custom concatenation function in your spreadsheet, refer to the following example code.

Java	Copy Code
// 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(); } }

Java

Copy Code

// 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();
    }
}

Java	Copy Code
// 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\", \" \", \"Google\", \".\")"); worksheet.getRange("A2").setFormula("=MyConcatenate(A1, \"Documents.\")"); // Value of cell A1 is "I work with Google." Object resultA1 = worksheet.getRange("A1").getValue(); // Value of cell A2 is "I work with Google Documents." Object resultA2 = worksheet.getRange("A2").getValue(); // Display result in cell D1 worksheet.getRange("D1").setValue(resultA2);

Java

Copy Code

// 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\", \" \", \"Google\", \".\")");
worksheet.getRange("A2").setFormula("=MyConcatenate(A1, \"Documents.\")");
// Value of cell A1 is "I work with Google."
Object resultA1 = worksheet.getRange("A1").getValue();
// Value of cell A2 is "I work with Google Documents."
Object resultA2 = worksheet.getRange("A2").getValue();
// Display result in cell D1
worksheet.getRange("D1").setValue(resultA2);

Example 3: Merged Range Function

To create and use custom merged range function in your spreadsheet, refer to the following example code.

Java	Copy Code
// 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<IRange> ranges = ((CalcReference) arguments[0]).getRanges(); for (IRange range : ranges) { return range.getMergeCells(); } } } return false; } }

Java

Copy Code

// 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<IRange> ranges = ((CalcReference) arguments[0]).getRanges();
                for (IRange range : ranges) {
                    return range.getMergeCells();
                }
            }
        }
        return false;
    }
}

Java	Copy Code
// 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);

Java

Copy Code

// 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);

Example 4: Error Detection Function

To create and use custom error detection function in your spreadsheet, refer to the following example code.

Java	Copy Code
// 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; } }

Java

Copy Code

// 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;
          }
}

Java	Copy Code
// 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);

Java

Copy Code

// 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);

Example 5: Greatest Common Division Function using Custom Objects

Refer to the following example code to create and use BigInteger function to calculate greatest common division.

Java	Copy Code
// 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; } } }

Java

Copy Code

// 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;
        }
    }
}

Java	Copy Code
//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");

Java

Copy Code

//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");

Example 6: Create Volatile Custom Function

Following example demonstrates how to create a custom function for generating GUID. To generate a unique GUID every time, custom function should not be using cache. Hence, example code sets the setIsVolatile method to true, so that a new GUID is generated on every call.

Java	Copy Code
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("-",""); } }

Java

Copy Code

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("-","");
    }
}

Java	Copy Code
// 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);

Java

Copy Code

// 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);

Example 7: Create Asynchronous Custom Functions

Asynchronous function is any function that delivers its result asynchronously or concurrently. Asynchronous functions have a non-blocking architecture, so the execution of one task isn't dependent on another. Tasks can run simultaneously. Running asynchronous functions can improve performance by allowing several calculations to run at the same time. DsExcel enables functions to perform asynchronous calculations by deriving them from AsyncCustomFunction class. evaluateAsync method calculates the function asynchronously. DsExcel also provides an enumeration value "Busy" in CalcError enumeration that indicates that a cell is calculating an async formula.

Refer to the following example code to add and use a custom Asynchronous function:

Java	Copy Code
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]; }); } }

Java

Copy Code

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];
        });
    }
    }

Limitations

The AsyncCustomFunction's parameters do not accept any reference because the asynchronous function may run in another thread, and it will cause multi-thread conflicts if you use a reference. Similarly, using objects such as IWorksheet and IWorkbook is not allowed within asynchronous functions.