Operators Functions API Pricelist Company Blog Contact Us

User Defined Functions (UDF)

The Adaptive Calculation Engine allows users to create custom functions that can be used the same way as SUM() or other built-in functions. Although the ACE4J library implements the latest and most popular Excel functions, users will benefit from the ability to create their own functions and use them for calculations.

Introduction

Suppose you need to create two custom functions, FTOC() and CTOF(), to convert the temperature in degrees Fahrenheit to degrees Celsius (FTOC()) and vice versa (CTOF()). The below code creates these functions:

public interface UserFunctionsInterface {
    double fToC(double fahrenheitTemperature);
    double cToF(double celsiusTemperature);
}

public class UserFunctionsImpl implements UserFunctionsInterface {
    public double fToC(double fahrenheitTemperature)
    {
        return (fahrenheitTemperature - 32) * 5/9;
    }

    public double cToF(double celsiusTemperature)
    {
        return celsiusTemperature * 9/5 + 32;
    }
}

The below example shows how to introduce the UserFunctionsImpl class to the ACE4J UDFLocator class, so it can register user function methods fToC() and cToF() to inside-the-engine calculations.
The formulas in this example,

fahrenheit_temp=ROUND(CTOF(38),0)

and

celsius_temp=ROUND(FTOC(5),0)

use the CTOF() and FTOC() functions as the first argument to the built-in function ROUND() to round the calculated result to the nearest integer:

import com.crystalprism.ce.exception.UDFException;
import com.crystalprism.ce.formula.HybridFormulaEvaluator;
import com.crystalprism.ce.formula.UDFLocator;
import com.crystalprism.ce.usermodel.*;

public class UserFunctionsExample {
    public static void main(String[] args)
    {
        try {
            // Register user defined functions in the Adaptive Calculation Engine
            String myUDFClassName = UDFLocator.add(UserFunctionsImpl.class, UserFunctionsInterface.class);
            String myFormulaText1 = "ROUND(CTOF(38),0)";
            String myFormulaText2 = "ROUND(FTOC(5),0)";
            CalculationContext calculationContext = new CalculationContext("UDFApp");

            calculationContext.put("fahrenheit_temp", new FormulaField(ComputeFieldType.NUMERIC,
                    myFormulaText1));
            calculationContext.put("celsius_temp", new FormulaField(ComputeFieldType.NUMERIC,
                    myFormulaText2));
            calculationContext.setUDFClassName(myUDFClassName);
            if (HybridFormulaEvaluator.evaluateAllFormulas(calculationContext)) {
                calculationContext.getComputeFields().forEach((computeFieldName, computeField) ->
                        System.out.println(computeFieldName +
                                " is: " + computeField.getDisplayValue()));
            }
        }
        catch (UDFException e) {
            System.out.println(e.getMessage());
        }
    }
}

fahrenheit_temp is: 100
celsius_temp is: -15

Overview

The Adaptive Calculation Engine uses the following static methods of the abstract class UDFLocator to register user defined functions for the inside-the-engine calculations:

String add(Class UDFImpl, Class UDFInterface)

UDFImpl - a non-abstract class that implements the UDFInterface;

UDFInterface - an interface that groups user function methods.

String add(Class UDFClass, ListudfNames)

UDFClassl - a non-abstract class that contains user function methods;

udfNames - a list of user function non-static method names.

Both methods return the fully qualified name of the first parameter class.

Both methods throw the UDFException when one of the following takes place:

  • - The user function class cannot be instantiated;
  • - Illegal access to the user function class was detected;
  • - A not supported parameter type or return type of a user function method was encountered;
  • - A user function method does not have parameters.

The Adaptive Calculation Engine allows double (Double), boolean (Boolean), and String data types for UDF method parameters and return values.