Creating a Clojure Excel Add-In

Jinx can be used with Clojure as well as other JVM languages, not just Java.

This tutorial shows how Jinx can be used to write an Excel Add-In exposing user defined functions to Excel written in Clojure.

For this tutorial it will help if you are already familiar with Clojure and building Clojure projects into JAR files. You will need to have a working install of Clojure available.

Clojure on Windows

Many Clojure developers prefer to use Linux or MacOS, and historically the Clojure tools have been slightly biased towards those platforms. With the Windows Subsystem for Linux (WSL) it is now much easier to get Clojure working on Windows, and that is what we will use for this tutorial.

If you are already using Linux or MacOS to develop with Clojure, you can continue to do so. The JAR files that you build on these platforms can be copied to your Windows environment and loaded from there if you prefer not to build on Windows.

The rest of this tutorial will assume you are using Clojure on Windows. You can install it using the following instructions:

Install the Windows Subsystem for Linux

Follow the Windows Subsystem for Linux Installation Guide for Windows 10 instructions to install the Windows Subsystem for Linux (WSL).

For this tutorial we will the Ubuntu distribution from the Microsoft Store, but you can use whichever distribution you like.

Install Clojure

Once WSL is installed it will run a bash prompt on your Windows desktop. You can install a different terminal app if you like such as ConEmu, but we will stick with the default for now.

Install the JDK in WSL by running one of the sets of following commands. JDK 8 is reliable for Clojure.

Oracle

 sudo add-apt-repository ppa:webupd8team/java
 sudo apt update
 sudo apt install oracle-java8-installer
 sudo apt install oracle-java8-set-default

Open JDK

 sudo add-apt-repository ppa:openjdk-r/ppa
 sudo apt update -y
 sudo apt install -y openjdk-8-jdk
 sudo apt install ca-certificates-java
 sudo update-ca-certificates -f

Now we can install Clojure following the official instructions.

 curl -O https://download.clojure.org/install/linux-install-1.10.1.478.sh
 chmod +x linux-install-1.10.1.478.sh
 sudo ./linux-install-1.10.1.478.sh

If everything’s worked correctly, you can now run the clojure command:

 $ clojure -h
 Usage: clojure [dep-opt*] [init-opt*] [main-opt] [arg*]
        clj     [dep-opt*] [init-opt*] [main-opt] [arg*]
 ...

Creating the Project

We will create a new project using Leiningen.

If you already have a prefered way of creating Clojure projects, you can ignore this and skip to Writing an Excel Function in Clojure.

Install Leiningen

Install Leiningen following the online instructions. We can use the Ubuntu package manager to do this for us:
```
 sudo apt install leiningen
```
Create the project

Use Leiningen to create a new project, jinx-tutorial:
```
 lein new app jinx-tutorial
```
This will create a new project in a jinx-tutorial folder, including a README, a src folder containing the code, a test folder for tests and a project.cli file which describes the project.
Update the project settings

Our Clojure code needs to be compiled into a JAR file so it can be loaded by the Jinx Excel add-in.

We will use Leiningen to build an uberjar. This is a single standalone JAR file containing the code from our project and any dependencies.

For this to work we need to specify a namespace as out :main in project.clj and ensure it’s also AOT (Ahead Of Time) compiled by adding it to :aot. This should be done for you by default in the project we’ve just created.

The project.clj file is where we put any dependencies, and in order to access the Jinx classes to expose our code to Excel we need to add Jinx to the :dependencies.

Update the project.clj file to look like this:
```
 (defproject jinx-tutorial "0.1.0-SNAPSHOT"
   :description "FIXME: write description"
   :url "http://example.com/FIXME"
   :license {:name "Eclipse Public License"
             :url  "http://www.eclipse.org/legal/epl-v10.html"}
   :dependencies [
                  [org.clojure/clojure "1.8.0"],
                  [com.exceljava/jinx "2.0.0"]
                  ]
   :main ^:skip-aot jinx-tutorial.core
   :target-path "target/%s"
   :profiles {:uberjar {:aot :all}})
```

Test building the project

After updating the project.clj file, test that building the project works by running lein uberjar

 $ lein uberjar
 Retrieving org/clojure/clojure/1.8.0/clojure-1.8.0.pom from central
 Retrieving org/clojure/clojure/1.8.0/clojure-1.8.0.jar from central
 Retrieving com/exceljava/jinx/2.0.0/jinx-2.0.0.jar from central
 Compiling jinx-tutorial.core
 Created /mnt/c/Temp/jinx-tutorial/target/uberjar/jinx-tutorial-0.1.0-SNAPSHOT.jar
 Created /mnt/c/Temp/jinx-tutorial/target/uberjar/jinx-tutorial-0.1.0-SNAPSHOT-standalone.jar

The jinx-tutorial-0.1.0-SNAPSHOT.jar file is the JAR file that later will be loaded by the Jinx Excel add-in.

Writing an Excel Function in Clojure

If you’ve created the project using the instructions above you’ll have a src/jinx-tutorial/core.clj file that looks like the following:

(ns jinx-tutorial.core
  (:gen-class))

(defn -main
  "I don't do a whole lot ... yet."
  [& args]
  (println "Hello, World!"))

In order to expose code to Excel using the Jinx add-in we need to declare a class with methods that will map to Excel functions. We’ll start by adding a class jinx_tutorial.MyAddIn with a single method multiply that just returns the result of multiplying two numbers together.

(ns jinx-tutorial.core
  (:gen-class
   :name jinx_tutorial.MyAddIn
   :methods [
    [multiply [double, double] double]
   ]))

;;; methods

(defn -multiply
  [this x y]
  (* x y))

We can test this out in the Clojure REPL using Leiningen:

$ lein repl
REPL-y 0.3.7, nREPL 0.2.12
Clojure 1.8.0
OpenJDK 64-Bit Server VM 1.8.0_222-8u222-b10-1ubuntu1~18.04.1-b10

jinx-tutorial.core=> (compile 'jinx-tutorial.core)
jinx-tutorial.core=> (. (jinx_tutorial.MyAddIn.) multiply 2 3)
6.0

Now we have a simple method working we can expose that to Excel by applying the @ExcelFunction annotation. You can read more about the @ExcelFunction annotation in the Worksheet Functions section of the documentation.

(ns jinx-tutorial.core
  (:gen-class
    :name jinx_tutorial.MyAddIn
    :methods [
              [^{com.exceljava.jinx.ExcelFunction {:value       "myaddin.multiply"
                                                   :description "Multiply two numbers."}}
               multiply [double double] double]]))

;;; methods

(defn -multiply
  [this x y]
  (* x y))

As before, we can test this out in the REPL, but to test it in Excel we’ll build it as a JAR using the lein uberjar command.

$ lein uberjar
Compiling jinx-tutorial.core
Created /mnt/c/Temp/jinx-tutorial/target/uberjar/jinx-tutorial-0.1.0-SNAPSHOT.jar
Created /mnt/c/Temp/jinx-tutorial/target/uberjar/jinx-tutorial-0.1.0-SNAPSHOT-standalone.jar

The generated file jinx-tutorial-0.1.0-SNAPSHOT-standalone.jar contains everything we need to load this class into the Jinx Excel add-in.

Calling the Clojure Function From Excel

In the previous section we built a JAR file containing a class with a method annotated using the @ExcelFunction annotation. In this section we will load that JAR into Excel using Jinx and call the method as a worksheet function.

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. The classpath can include wildcards and can include multiple paths on multiple lines.

[JINX]
classes =
    jinx_tutorial.MyAddIn

[JAVA]
classpath =
    C:/Temp/jinx-tutorial/target/uberjar/jinx-tutorial-0.1.0-SNAPSHOT-standalone.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 myaddin.multiply function in Excel.

multiply

If you don’t see the myaddin.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.

« Writing your First Java Excel Add-In

Installation »

Introduction

Tutorials

User Guide

Creating a Clojure Excel Add-In

Clojure on Windows

Creating the Project

Writing an Excel Function in Clojure

Calling the Clojure Function From Excel