Integrating Excel and Clojure

Clojure combines the interactive development convenience of a scripting language with an efficient and robust infrastructure for multithreaded programming. It runs on the JVM and interfaces with Java code seamlessly, making it a pragmatic choice for those who want the the succinctness, flexibility and productivity of a dynamic language with the performance, security and stability of the JVM.

Developers may love what Clojure can offer, but more often than not code is written by developers to use used by end users who are not themselves developers. Creating rich user interfaces or web applications for end users takes time and effort, and can be brittle as changes to the underlying application often require changes to the user interface. There is one user interface tool that you might have overlooked that virtually everyone is already familiar with — Microsoft Excel.

In recent years Excel has featured in headlines for all the wrong reasons, and businesses are starting to recognise that storing data and business logic in Excel workbooks can be a huge operational risk. This doesn’t mean that Excel shouldn’t be used at all, just that we need to find other ways to use it that don’t introduce such risks.

By using Excel as a user interface tool, with data store securely in databases a business logic written and stored outside of Excel, the operational risk is eliminated. We can keep the productivity and flexibility of Excel, but power it with tools written in keeping with modern development practices.

How does this relate to Clojure? Excel add-ins don’t need to be written in VBA! Writing an Excel add-in in Clojure is simpler than you might think, and doesn’t require any VBA or C code — if you already know Clojure, exposing your Clojure code to Excel is straightforward!

Introducting Jinx — The Excel Java Add-In. Jinx embeds the JVM into Excel and provides simple annotations that turn Java methods into Excel functions. A Clojure also uses the JVM, we can use Jinx to expose Clojure code to Excel. The rest of this article describes how to do just that, with no Java code required.

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:

1. Install the Windows Subsystem for Linux

Follow the Windows Subsystem for Linux Installation Guide for Windows 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.

2. 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.

1. Install Leiningen

Install Leiningen following the online instructions. We can use the Ubuntu package manager to do this for us:

 sudo apt install leiningen

2. 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.

3. 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 "1.6.0"]
     ]
  :main ^:skip-aot jinx-tutorial.core
  :target-path "target/%s"
  :profiles {:uberjar {:aot :all}})

4. 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/1.5.0/jinx-1.6.0.jar from central
 Compiling jinx-tutorial.core
 Created target/uberjar/jinx-tutorial-0.1.0-SNAPSHOT.jar
 Created 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 Jinx 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 target/uberjar/jinx-tutorial-0.1.0-SNAPSHOT.jar
Created 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. There is a free version which includes everything you need for this tutorial.

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. Relative paths may be used, and are relative to the location of the jinx.ini file.

[JINX]
classes =
   jinx_tutorial.MyAddIn
[JAVA]
classpath =
   ./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.

Calling a Clojure method from Excel

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 Jinx user guide, and if you have any questions please contact me.