Creating a Kotlin Excel Add-In

Jinx can be used with 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 Kotlin.

For this tutorial it will help if you are already familiar with Kotlin and building Kotlin projects. We’ll be using IntelliJ for this tutorial. If you’re using another IDE or build system you should still be able to follow along, you may just need to change a few steps.

Setting up the Project

  • Create a New Project in IntelliJ and select Kotlin/JVM as the project type.

    kotlin/javm

  • Give your project a name and you should now have a project with the following folder structure:

    folder structure

  • Add the Jinx JAR file included in Jinx download as a dependency by going to File->Project Structure, then selecting Modules on the right and the Dependencies tab in the main section. Click the + button and select JARs or Directories and add the jinx-2.4.4.jar file included in the Jinx download.

    dependencies

  • Configure a JAR artifact by going to File->Project Structure, then selecting Artifacts on the right hand side. Click the + button and select JAR->From module and dependencies…. There’s no need to specify a main class.

    artifacts

Writing an Excel Function in Kotlin

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

With Jinx, a Kotlin function or class method is turned into an Excel UDF using the @ExcelFunction annotation.

  • Add a new Kotlin file ExcelKotlinFunctions to the project by right clicking on the src folder and selecting New -> Kotlin File/Class.

  • We’ll write a simple fibonacci method that given any positive number n returns the nth number in the Fibonacci sequence. It’s not important what this method is, it’s just to illustrate exposing a Kotlin function to Excel as a user defined function.

    Add the following code to your new ExcelKotlinFunctions file:

/*
* Returns the nth number in the Fibonacci sequence.
*/
fun fibonacci(n : Int) : Int {
   if (n <= 1) {
       return n;
   }

   var n1 = 0
   var n2 = 1

   for (i in 2..n) {
       val sum = n1 + n2
       n1 = n2
       n2 = sum
   }

   return n2
}
  • To expose this function to Excel as a user defined function (UDF) all that is needed is to apply the @ExcelFunction annotation. You can read more about the @ExcelFunction annotation in the Worksheet Functions section of the documentation.
import com.exceljava.jinx.ExcelFunction

/*
* Returns the nth number in the Fibonacci sequence.
*/
@ExcelFunction
fun fibonacci(n : Int) : Int {
  • Build the JAR by going to Build -> Build Artifacts -> Build.

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

[JINX]
classes = ExcelKotlinFunctionsKt

[JAVA]
classpath = C:/path-to-project/out/artifacts/{artifact name}/*.jar

The Kotlin function we added gets compiled to a method on a class called ExcelKotlinFunctionsKt. If instead of using a function we’d written a static class method we would add that class (or classes) to the config. If you have called your file something else, you just add Kt to the end of the file name (without the .kt extension) to get the class name.

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 fibonacci function in Excel.

fib

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

© Copyright PyXLL Ltd.