Writing your First Java Excel Add-In

This tutorial will take you through creating a brand new project that will expose a method written in Java that can be called from Excel (what Excel calls UDFs or User Defined Functions).

By the end of this tutorial you will have:

• Created a new project from scratch
• Written a User Defined Function to call from Excel
• Configured Jinx

It’s assumed that you’ll be using Maven to build your project. If you’re using another build system you can still follow along but you may need to alter a few steps.

Setting up the Project

We’ll start by creating a new empty project using Maven. Alternatively you could use any IDE you are already comfortable with to do this.

If you are not familiar with Maven please see the Maven Getting Started Guide.

To create the new project we can use Maven’s archetype system with the following command, replacing the name of your company, the project and artifact names with anything you like.

mvn -B archetype:generate \
    -DarchetypeGroupId=org.apache.maven.archetypes \
    -DgroupId=com.mycompany.xladdin \
    -DartifactId=first-jinx-project

A new folder for the project will be created containing a pom.xml file which should look something like this:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
                             http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.mycompany.xladdin</groupId>
  <artifactId>first-jinx-project</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>Maven Quick Start Archetype</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
  </dependencies>
</project>

If you have created the project using IntelliJ the pom.xml may look slightly different.

For a complete reference of what elements are available for use in the POM please see the Maven POM reference.

As well as generating the POM the following directory structure has been created:

first-jinx-project
|-- pom.xml
`-- src
    |-- main
    |   `-- java
    |       `-- com
    |           `-- mycompany
    |               `-- xladdin
    |                   `-- App.java
    `-- test
        `-- java
            `-- com
                `-- mycompany
                    `-- xladdin
                        `-- AppTest.java

This is the standard Maven project directory structure . To learn more about this Maven convention you can read the Maven Introduction to the Standard Directory Layout.

We won’t be using the App or AppTest class, so delete the two files App.java and AppTest.java. If you have used IntelliJ to create your project then these files may not exist.

Writing an Excel Function

An Excel User Defined Function or UDF is a function that can be called in Excel from a worksheet.

With Jinx, a Java method is turned into an Excel UDF using the @ExcelFunction annotation. In order to get that annotation we need to add Jinx to our project dependencies by adding it to the pom.xml file created earlier. Add the following to the dependencies tag (or if there is no dependencies tag in your POM, create an empty one in the project and add the following).

<dependency>
  <groupId>com.exceljava</groupId>
  <artifactId>jinx</artifactId>
  <version>[2.0.0,)</version>
</dependency>

You can change the version in the xml above to match whatever version of Jinx you are using. When the project is built it will automatically download the dependency from Maven Central.

Add a new class ExcelFunctions to the project by creating a file called ExcelFunctions.java in the src/main/java/com/mycompany/xladdin folder. If you’re using an IDE you can add the new class to the project as you would normally.

Create a new public static method multiply to multiply two numbers together. This isn’t a particularly interesting function and since we can already multiply numbers together in Excel it’s not very useful, but we’ll use it to illustrate how to expose a Java method to Excel.

Adding the @ExcelFunction annotation is all that’s needed to expose this static method to Excel as a UDF.

package com.mycompany.xladdin;

import com.exceljava.jinx.ExcelFunction;

/**
 * A collection of simple Excel functions.
 */
public class ExcelFunctions {
    /***
     * Multiply two numbers and return the result.
     */
    @ExcelFunction
    public static double multiply(double x, double y) {
        return x * y;
    }
}

At this point you could go and add some unit tests for this function and use Maven to run them, but we will skip that in this tutorial.

Now we’re ready to build the code into a JAR. This is done using the following Maven package command.

mvn package

Now you should have a file called first-jinx-project.jar in that target folder in your project directory. If using IntelliJ or another IDE you may need to check your settings to find where the artifact is output to. Make a note of the location of the JAR file as we will need it in the next step.

Calling the Function From Excel

If you haven’t already, download Jinx from the download page.

Inside the downloaded zip file you’ll find the Jinx config file, jinx.ini. We need to make some changes to this file so that Jinx knows where to find the JAR file and what classes to load.

Update jinx.ini with the following, replacing the classpath with the actual location of the JAR built earlier.

[JINX]
classes = com.mycompany.xladdin.ExcelFunctions

[JAVA]
classpath = C:/path-to-project/first-jinx-project/target/first-jinx-project.jar

You’ll see in the jinx.ini file there’s also a section at the bottom for logging. That determines where any logs will be written to and if you have any problems always check the log file as often it will tell you what’s gone wrong.

Now we’re ready to start Excel and install the Jinx add-in by doing the following:

• Select the File menu in Excel and go to Options -> Add-Ins -> Manage Excel Addins
• Browse to the folder you unzipped Jinx
• Select jinx.xll or jinx64.xll depending on whether you are using 32 bit or 64 bit Excel

If you get an error saying that jinx.xll is not of the correct format, most likely you’re trying to load the 64 bit version into a 32 bit version of Excel or vice-versa. If this happens just go back and choose the right xll file.

If everything has worked correctly, you should now be able to call the multiply function in Excel.

If you don’t see the multiply function in Excel, check your log file for errors. You can set the log level to finest in your jinx.ini file to see what’s happening in more detail if necessary.

You can read more about writing user defined functions in the user guide, and if you have any questions please contact us.

Reloading

Now you’ve written one user defined function you’ll no doubt want to make changes, add new functions or try writing your own macros and menus. To improve the edit-compile-test cycle you can reload the classes loaded by Jinx without having to restart Excel.

In the Add-ins toolbar¹ you’ll find a Jinx menu with a Reload option². That will reload your classes and re-register any functions, macros and menus so you can keep developing and testing without having to close and restart Excel.

Jinx can be configured to reload automatically whenever changes to the configured JAR or Class files are detetcted. To enable automatic reloading, set auto_reload = 1 in the JINX section of your config file.