Arrays and Array Types

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

Lists and Other Collection Types

While Jinx will handle array types automatically, it doesn’t handle Lists or other collection types by default.

If you want to be able to write functions that, for example, take a List instead of an array you can do so by adding a generic type converter.