Creating Excel Spreadsheets with Ruby and JExcelAPI

Everybody loves data, right? And where do we love data? Inside of spreadsheets! Specifically, Excel spreadsheets, which is the spreadsheet format that almost everyone can read and write. At some point or another, you have probably had to create a spreadsheet or report in code, and it can be painful and annoying if you don't have access to a good library. Today we are going to talk about how to get access to one of those decent libraries (JExcelAPI) if you are writing in ruby.

Sadly, while there are a couple of Excel Spreadsheet libraries written in Ruby (notably Spreadsheet), most have pretty limited capabilities. To be able to do more advanced things (like styling or adding images), you have to move to libraries written in other languages. The two main libraries out there are JExcelAPI and Apache POI, both of which are written in Java. We are going to be using JExcelAPI today, but the general technique can be used for Apache POI as well if you prefer that library.

To start off, make sure that you have Java and Ant installed on your system, and download the latest source for JExcelAPI (2.6.11 at the time of this writing). Extract the tar file and go into the "Build" directory on the command line, where you will want to run the command ant. Assuming that you have Java and Ant, this will build you a jar file named "jxl.jar" in the root of the extract source.

Now you are probably wondering how in the world we are going to use a Java jar file in a Ruby app. Well, that is where the very useful RJB ruby gem comes in. RJB stands for "Ruby Java Bridge" and is essentially a way to use Java libraries from within ruby. Installing it is a little bit more complicated than a normal gem - you have to tell the gem where the java vm is located when it is installed. Depending on your operating system, what exactly you need to do to do this depends, but it generally involves setting the $JAVA_HOME environment variable to wherever java is located on your system. For instance, for OS X, you need to do:

Once that is set, you can install the gem using the usual:

Ok, now its time for some Ruby code:

This is a ruby script to make pretty much the simplest spreadsheet possible - a single sheet named "My First Sheet" with no data. So let's walk through and see what is going on. First off, we pull in the RJB library (and since this is Ruby 1.8, we still have to require 'rubygems' before we can require a library from a gem). Once we have RJB, we can load the JExcelAPI jar file - "jxl.jar". This test script is in the same directory as the jar file, which is why the path is "./jxl.jar", but that doesn't have to be the case - it can be any path to a valid jar file. The array of options that is the second argument are the command line arguments to the Java VM - in this case talking about the amount of memory the Java VM will be allowed to use.

Once the jar file is loaded, we need to get some ruby references to the java classes we want to use. We do this using the Rjb::import call, and pass it the full type name string of the class we want. In this way, we can get a handle to the Java File class, and the JExcelAPI Workbook class. What is available on these class objects is exactly the same as on the backing Java classes. This ends up being a little weird, because Java's naming conventions are not the same as Ruby's (camelCaseTown vs. underscore_town), but other than that it is really easy to use.

The new operator is done in Ruby fashion - you can see where we create the Java File object for the spreadsheet, we do:

instead of:

So now that you know all that, it is pretty easy to see that we are opening a file, creating a workbook, adding a sheet to that workbook, and then finally writing and closing the workbook.

Now its time for something a little bit more complicated. Let's add some data and maybe even an image or two.

Wow, a lot more stuff here - but don't worry, a lot of it is repetitive and has to do with formatting. As you can tell, we are importing a lot more Java classes from JExcelAPI this time. This is one of the downsides of using RJB - as you start using more and more Java classes, you have to remember to pull them in.

There are three types of cells that we are creating here - label, number, and image. They are all pretty self-explanatory - labels are for cells with text, numbers are for cells with numbers, and images are for cells with images. Generally, you create the type of cell, with arguments specifying its row and column position, as well as its contents, and then you call addCell to add it to the sheet.

Images are a little bit different, because it can span multiple cells - you actually tell it the row and column positions as well as the number of rows and columns to span, and then call the method addImage to add it to the sheet.

You can give cells a format through the WriteableCellFormat class, and we do so for a couple of the header cells. You can do everything from font styling to cell borders and colors. All of it is really easy to use once you are used to translating the java docs info into the ruby form you actually need to write.

Just in case you were wondering what the spreadsheet the above code creates looks like, here is a picture:

That about wraps it up for this tutorial. If you were wondering why someone would do something like this, think about all the websites in the world that have spreadsheets as some sort of report output - this way, you can still use Ruby/Rails as your web framework, but you can get the benefit of a more advanced library for generating those spreadsheets. You can grab a zip file with the ruby code below, as well as the compiled jar file for JExcelAPI and the example output spreadsheet. If you have any questions, drop a comment below.