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