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")
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.