Clojure Programming/Examples/Talking to Excel

Interfacing to other programs via COM can be easily done in Java using the JACOB library. However coping with all the low-level interface when talking to an application is quite tedious. Especially since the interface is designed for C not for Java, not to mention Clojure. So we will try to improve the situation a little bit using the power of Clojure.

Basic Interface

Let's start with a simple example. We start Excel, make the instance visible. Then we shutdown the whole system again.

  (import '(com.jacob.com Dispatch
                          ComThread))

  (def xl (new Dispatch "Excel.Application"))
  (Dispatch/put xl "Visible" true)
  (Dispatch/call xl "Quit")
  (ComThread/Release)

The ugliness of the interface should be immediately visible. We have to call a C interface through Java. So let's wrap the "Visible" call.

  (defn excel-application-visible
    ([xl]
     ; No argument? Return the current value.
     (Dispatch/get xl "Visible"))
    ([xl v]
     ; Argument given? Set it as new value.
     (Dispatch/put xl "Visible" v)))

  (excel-application-visible xl true)

This is much better. We have hidden away the low-level interface behind a simple Clojure function call. Now we have to wrap all the properties and function calls into such functions. But wait! Up to now, this was nothing special. Just a function call. Clojure can save us some work here.

Functions of First Class

Defining a few functions to wrap the interface quickly shows a lot of repetition. Take the previous example: we pass-in the Excel instance and maybe a value and call the underlying interface accordingly. But this is always the same task. So we can put this functionality into a driver function and use partial evaluation to create the wrapper function.

  (defn property-driver
    ([prop xl]
     (Dispatch/get xl prop))
    ([prop xl v]
     (Dispatch/put xl prop v)))

  (def excel-application-visible (partial property-driver "Visible"))

  (excel-application-visible xl true)

So what happens here? We define a new function which is also parametrized by the property name. Then we use the built-in partial to create a specialized function, which is specialized to act on the "Visible" property. So partial has a function as return value.

Macros

Now we have a set of wrappers to hide away the low-level interface. But what about the last instruction in the first example: (ComThread/Release)? Well, this is needed to clean up the COM side and has to be called in each thread, which used COM. This can get especially tedious when faced with exceptions. To get rid of the low-level handling, we use another part of Clojure's power: macros!

  (defn with-excel*
    [thunk]
    (let [worker (fn []
                   (let [xl (excel-application-new)]
                     (try
                       (thunk xl)
                       (finally
                         (excel-quit xl)
                         (ComThread/Release)))))
          t      (new Thread worker)]
      (. t start)
      (. t join)))

  (defmacro with-excel
    [varname & body]
    `(with-excel* (fn [~varname] ~@body)))

First we define again a driver function, which does the heavy lifting. It takes a function, which it calls in newly created thread. The function is handed over the Excel instance. Everything is wrapped into a try to ensure proper cleanup even in case the function throws an exception.

Of course it is not our intention to always define a new function manually, when we want to call this wrapper. Hence we define a macro, which does this for us. It takes a symbol which makes the Excel instance available to the body. The body is wrapped into an anonymous function, which closes the body over its bindings and packages everything up for our wrapper.

Wrap-Up

Rewriting with this modifications our initial example gives us this nice and concise code.

  (with-excel xl
    (excel-application-visible xl true))

Of course this example isn't really useful, but it clearly shows the improvement over the previous pure Java code. Also the power lies in the part we don't see: even if we were throwing an exception in the with-excel everything would be cleaned up.

Further experimenting is left to the user. Things which were left out are e.g. a macro constructing the wrappers, or a function handling Variant types of the COM interface. So experiment and have fun!

See also

There exists docjure - Clojure library which enables read/write of MS Office files (spreadsheets) using Apache POI.

Example:

(use 'dk.ative.docjure.spreadsheet)

; Load a spreadsheet and read the first two columns from the price list sheet:
(->> (load-workbook "spreadsheet.xlsx")
     (select-sheet "Price List")
     (select-columns {:A :name, :B :price}))

> [{:name "Foo Widget", :price 100}, {:name "Bar Widget", :price 200}]