Document Solutions for Excel, Java Edition | Document Solutions
Features / Custom Functions
In This Topic
    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.

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