Reading and writing Excel (xls) files with Incanter

I have just added David James Humphreys’ incanter-excel module to the Incanter distribution, providing basic capabilities for reading Microsoft Excel spreadsheets in as Incanter datasets and saving datasets back out as xls files.

I have posted a simple spreadsheet of Australian airline passenger data from the 1950s to the Incanter website for the following example. The read-xls function can read xls files given either a filename or URL, so you won’t need to download the file.

Start by loading the necessary libraries, including incanter.excel.

(use '(incanter core charts excel))

Next, we can use the with-data macro to bind a dataset converted from the above xls file, using the read-xls function, and then view it.

(with-data (read-xls "http://incanter.org/data/aus-airline-passengers.xls")
  (view $data)

The read-xls function takes an optional argument called :sheet that takes either the name or index of the worksheet from the xls file to read (in this case either “dataset” or 0) , it defaults to 0.

[NOTE: A current weakness of read-xls is that cells containing formulae, as opposed to actual data, are not imported (i.e. the cells remain empty).]

Finally, we’ll create a time-series plot of the data. However, the time-series-plot needs time in milliseconds, so we’ll first create a function that converts the date column from Java Date objects to milliseconds, and then view the plot.

  (let [to-millis (fn [dates] (map #(.getTime %) dates))] 
    (view (time-series-plot (to-millis ($ :date)) ($ :passengers)))))

Datasets can also be saved as Excel files using the save-xls function. The following example just reads in one of the sample datasets using incanter.datasets/get-dataset and then saves it as an xls file.

(save-xls (get-dataset :cars) "/tmp/cars.xls")

The incanter-excel module is now included in the Incanter distribution on Github, and is available as a separate dependency from the Clojars repository. The complete code from this example can be found here.

4 responses to “Reading and writing Excel (xls) files with Incanter

  1. Wow, Incanter is getting better and better, very impressive! We are slowly approaching the stage where it would be nice to have a book about it, or an online course ;-)

  2. hi…sorry but I’ve tried run the example and I get this:

    Exception in thread “main” java.lang.IllegalArgumentException: No value supplied for key: true (core.clj:1)

    I think error is in (use ‘(incater core charts excel))
    I’ve tried using (:use incater core charts excel)

    and I get lein run
    Exception in thread “main” java.io.FileNotFoundException: Could not locate incater__init.class or incater.clj on classpath: (core.clj:1)

    thanks!!

  3. Looks like you just left out the second “n” in “incanter”

  4. Very cool. Lack of formula support is understandable.

    I imagine, if you need to import formula cells you could always put together a VB script to get Excel to convert the XLS to a CSV – you’d have to break out of Clojure/JVM though. Could probably be done a little more nicely with OpenOffice, but the formulas compatibility is lacking.

    Incanter is very nice, I won’t be using gnuplot much anymore…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s