Worksheet Functions

Calling functions written in Java using Jinx in Excel is exactly the same as calling any other Excel function written in VBA or as part of another Excel addin. They are called from formulas in an Excel worksheet in the same way, and appear in Excel’s function wizard.

Methods that are exposed as Excel functions are annotated using the @ExcelFunction annotation. They must be public methods, and can either be static or non-static (instance) methods. Classes with non-static methods must have a public default constructor.

For example, the following code adds a new function hello to Excel:

import com.exceljava.jinx.ExcelFunction;

class HelloFunction {
    @ExcelFunction
    public String hello(String name) {
        return String.format("Hello, %s!", name);
    }
}

Once that code is built the class HelloFunction can be added to the Jinx config file:

> javac -cp <path to jinx jar> HelloFunction.java
> jar -cf HelloFunction.jar HelloFunction.class

[JINX]
classes = HelloFunction

[JAVA]
classpath = ./HelloFunction.jar

Now when Excel is started the function hello exists and we can use it from a formula in a worksheet.

ExcelFunction Options

The @ExcelFunction annotation takes a number of optional parameters. These are used to control how the Java method is exposed to Excel.

It can be useful to use different names and categories for functions to distinguish between your functions and those of other add-ins or Excel’s own functions. For example, the following function has a different name from the method it wraps, and is marked as being thread-safe:

import com.exceljava.jinx.ExcelFunction;

class MyFunction {
    @ExcelFunction(
        value = "HardSums.DoSomething",
        description = "Helpful text for someone who wants to use this",
        category = "Hard Sums",
        isThreadSafe = true
    )
    public static double doSomething(double x, double y) {
        double z = HardSums.someComplexAlgorithm(x, y);
        return z;
    }
}

This method would be called in Excel as =HardSums.DoSomething(x, y).

Argument Types

When calling Java methods from Excel Jinx automatically converts from the Excel cell type to the Java type expected by the method and from the type returned by the method to an Excel type.

Java types map to Excel types as follows:

Methods that take an Object as an argument will receive an Object of the following types:

If a method returns an type other than any of the types mentioned above, the returned instance is stored in a managed object cache and a handle to that instance is returned. If another function expects an argument of that type then it can be passed a handle and the object will be retrieved from the cache before invoking the Java method. See Cached Objects for more details.

Argument Descriptions

As well as being able to add function descriptions to give users help when calling the exposed functions, it is also possible to add descriptions to the arguments.

To add argument descriptions the @ExcelArguments annotation has to be added to the method in addition to the @ExcelFunction annotation. Each argument can be given a name and a description.

Taking the example from ExcelFunction Options and adding argument names looks like:

import com.exceljava.jinx.ExcelFunction;
import com.exceljava.jinx.ExcelArguments;
import com.exceljava.jinx.ExcelArgument;

class MyFunction {
    @ExcelFunction(
        value = "HardSums.DoSomething",
        description = "Helpful text for someone who wants to use this",
        category = "Hard Sums",
        isThreadSafe = true
    )
    @ExcelArguments({
        @ExcelArgument(value = "x", description = "helpful text about x"),
        @ExcelArgument(value = "y", description = "helpful text about y"),
    })
    public static double doSomething(double x, double y) {
        double z = HardSums.someComplexAlgorithm(x, y);
        return z;
    }
}

The order of the @ExcelArgument annotations is the same as the method parameters.

Cached Objects

Often it’s necessary to pass a Java object between functions. You might have one function that creates a complex Java object that is used as an input to various other functions, and it’s not convenient or efficient to keep recreating that object from lots of primitive inputs every time you need it.

Jinx allows you to pass objects around as easily as primitive values via its managed object cache. When a function returns a Java type that can’t be converted to a simple Excel type, Jinx assigns it a unique handle and returns that to Excel. Any function that takes an object of the same type (or a super-type) can be passed that handle and Jinx will fetch the object from the cache and pass it to the Java method.

Jinx takes care of making sure that once objects are no longer referenced they are removed from the cache so they can be garbage collected.

Cached objects are not persisted when Excel closes, and they are not saved with the workbook. This means that every time you open a workbook that depends on cached objects it has to be recalculated so that the object cache is repopulated.

The following is a simple example to demonstrate the prinicple:

import com.exceljava.jinx.ExcelFunction;

class Car {
    private final String color;

    private Car(String color) {
        this.color = color;
    }

    @ExcelFunction
    public static Car newCar(String color) {
        return new Car(color);
    }

    @ExcelFunction
    public static String getCarColor(Car car) {
        return car.color;
    }
}

Custom Argument Converters

Converting from an Excel type to a Java type can result in a lot of repeated code. Sometimes it’s more convenient to be able to write the conversion code once and have Jinx call it automatically. One use case for this is when you have functions that take a enum, and rather than wrapping these functions so that they take a string and then converting the string to the enum it is possible to have Jinx do that for you.

Argument converters are methods annotated with the @ExcelArgumentConverter annotation. When a function is called and an argument can’t be simply converted from the Excel type, Jinx will look for a suitable argument converter to use.

import com.exceljava.jinx.ExcelArgumentConverter;

class MyEnumConverter {
    @ExcelArgumentConverter
    public static MyEnum enumFromString(String value) {
        if (value.equals("X")) {
            return MyEnum.X;
        } else if (value.equals("Y")) {
            return MyEnum.X;
        }
        // etc...
        throw new IllegalArgumentException(String.format("Unexpected value '%s'", value));
    }
}

This class has to be added to the list of classes for Jinx to load, and once it is methods that take MyEnum can be used without any additional code.

import com.exceljava.jinx.ExcelFunction;

class MyEnumFunction {
    @ExcelFunction
    public static String enumToString(MyEnum value) {
        return value.toString();
    }
}

When the enumToString function is called in Excel with a string argument, Jinx will use the registered argument converter to convert it to a MyEnum and call the enumToString method.

Custom Return Value Converters

In the same way as arguments can be automatically converted using @ExcelArgumentConverter, return types can also be converted to native Excel types.

When a return type has no direct mapping to an Excel type, by default it will be added to the object cache and a handle to the cached object is returned (see above). This is not always desirable as for simple types like enums returning an object handle is confusing to the user.

Return value converters are methods annotated with the @ExcelReturnConverter annotation. When a function returns a value whose type has a return converter registered, that return converter is called to convert the value before returning it to Excel.

Return converters are most useful when used in conjunction with argument converters. To complete the example above the return converter for the enum would be written as:

import com.exceljava.jinx.ExcelReturnConverter;

class MyEnumConverter {
    @ExcelReturnConverter
    public static String stringFromEnum(MyEnum value) {
        switch (value) {
            case X:
                return "X";
            case Y:
                return "Y";
            // etc...    
        }
    }
}

This class has to be added to the list of classes for Jinx to load, and once it is methods that return MyEnum will use the conversion method without any additional code.

import com.exceljava.jinx.ExcelFunction;

class MyEnumFunction {
    @ExcelFunction
    public static MyEnum enumX() {
        return MyEnum.X;  // automatically converted to String before returned to Excel
    }
}

Using Arrays

Methods exposed to Excel as UDFs can accepts cell ranges as arguments. Array arguments can be arrays of any type recognized by Jinx, including custom types and cached objects.

Jinx methods may accept 1d or 2d arrays. When passed a range of cells, if a 2d array is used then that array has the same dimensions as the range passed in. 1d arrays are useful for functions that expect a row or column of data.

Functions may also return arrays. In Excel, these functions are termed array formulas and are entered slightly differently from normal formulas:

1. Select the range of cells for the formula (this where the results will appear).
2. Enter the formula (i.e the function you are calling).
3. Press Ctrl+Shift+Enter.

Sometimes the caller of a function won’t know the exact size of the results they are expecting. For this reason, Jinx can automatically resize the output formula based on the dimensions of the array returned. This feature is enabled by passing autoResize=true to @ExcelFunction.

The following is an example of a function that takes an array and returns an array.

import com.exceljava.jinx.ExcelFunction;

class Transpose {
    @ExcelFunction(
        value = "jtranspose",
        description = "Transposes a range of numbers",
        autoResize = true
    )
    public static double[][] transpose(double[][] matrix) {
        int numRows = matrix.length;
        if (numRows == 0) {
            return new double[0][0];
        }
        
        int numCols = matrix[0].length;
        double[][] transpose = new double[numCols][numRows];
        for (int r = 0; r < numRows; ++r) {
            for (int c = 0; c < numCols; ++c) {
                transpose[c][r] = matrix[r][c];
            }
        }
        
        return transpose;
    }
}

Non-Static Methods

Excel functions can be non-static Java methods so long as the class has a public default constructor or a public constructor that takes a single parameter of type ExcelAddIn.

The ExcelAddIn interface can be used to call back into Excel and the Jinx add-in and has, among others, the following methods:

Full documentation for the ExcelAddIn interface can be found in the javadocs.

The convertArgument method is particularly useful for functions that take a variety of different types or an array of Objects.

Below is a contrived example, but it illustrates a non-static method using a public constructor taking an ExcelAddIn.

import com.exceljava.jinx.ExcelFunction;
import com.exceljava.jinx.ExcelAddIn;

class CarUtils {
    private final ExcelAddIn xl;

    public CarUtils(ExcelAddIn xl) {
        this.xl = xl;
    }

    @ExcelFunction
    public String carToString(Object obj) {
        Car car;
        try {
            car = xl.convertArgument(obj, Car.class);
        } catch (Exception e) {
            throw new IllegalArgumentException("Expected a car", e);
        }
        return car.toString();
    }
}

Cell References

As well as passing values to your Excel functions, it’s also possible to pass cell references.

This allows you to get certain bits of metadata from the cell that’s passed to your function as well as the cell value.

To pass a cell reference to your function you simply use the the ExcelReference as one or more of the parameter types. You can also use 1d and 2d arrays of ExcelReference.

The ExcelReference type has methods for getting the cell value, formula, notes, address, sheet name and range. To access some of these, the function needs to be registered with Excel as a macro sheet equivalent function. This gives it access to these properties that normal functions don’t have. To specify a function should be registered as a macro sheet equivalent function set isMacroType = true in the @ExcelFunction annotation.

The following shows how to write a function that returns the formula of the cell passed to it.

import com.exceljava.jinx.ExcelFunction;

class Example {
    @ExcelFunction(
            value = "jinx.getFormula",
            isMacroType = true
    )
    public static String getFormula(ExcelReference cell) {
        return cell.getFormula();
    }
}

To get the ExcelReference of the cell that your function is being called from there is the method ExcelAddIn.getCaller().

The next example returns the address of the calling cell.

import com.exceljava.jinx.ExcelFunction;
import com.exceljava.jinx.ExcelAddIn;

class Example {
    private final ExcelAddIn xl;

    public Example(ExcelAddIn xl) {
        this.xl = xl;
    }

    @ExcelFunction(
            value = "jinx.getCallerAddress",
            isMacroType = true
    )
    public String getCallerAddress() {
        ExcelReference caller = xl.getCaller();
        return caller.getAddress();
    }
}

Calling back into Excel

Sometimes it can be useful to be able to call back into Excel from a worksheet function using Excel’s COM API.

For example, you might have a function that returns a table of data and you want to apply some formatting to that. Or, you might want to update some cells in a location other than where the function was called from².

Excel does not allow you to call all of its API from a user defined function. Anything that would update Excel is usually prevented from being called while the worksheet is calculating.

Jinx provides a helper method ExcelAddIn.schedule(java.lang.Runnable task) to schedule a task to be called after Excel has finished calculating. It ensures that Excel is ready to be called before the task is run and so it is safe to call the Excel COM API from a scheduled task.

To access the Excel object model from Java and Jinx, we recommend that you use the jinx-com4j project, which acts as a bridge between your Jinx add-in and Excel. For details of jinx-com4j see its github page https://exceljava.github.io/jinx-com4j.

An example of using ExcelAddIn.schedule(java.lang.Runnable task) is included in the examples in the jinx-com4j project.