JRuby and OpenOffice

UPDATE: This code works for OpenOffice 2.4, but has not been tested with OpenOffice 3+ or with LibreOffice. Your mileage may vary.

I wrote a post not too long ago about how OpenOffice doesn't have much support for basic statistics, and that I would like to create something to fix that.

The OpenOffice API is written largely for Java and seemingly so far to a lesser extent, C++. However, they do try to make it work for most languages, including C#/Mono and Python.

I thought to myself, "JRuby can use Java classes, so can JRuby use the OpenOffice API?" And the answer is, "Yes, it can!"

So I'll tell you how to do it. It's not really that hard, although you'll have to deal with the Java way of doing things a fair bit, so you'll end up writing a fair bit more code than you would if it were a Ruby API. Also, there are a few cases where you have to convert from Ruby basic types to Java basic types, which can catch you if you're not careful.

The first thing you need to do is install the OpenOffice SDK. You can get it here, or if you're on Ubuntu you can install it like this:

sudo apt-get install openoffice.org-dev openoffice.org-dev-doc

The doc package is optional, however I find that documentation is a handy thing to have if you need it.

Once you have that, you're pretty much ready to go! You'll need to check for a few things first, to ensure that the proper JARs are there. Go to your OpenOffice install directory (if you installed using apt-get, this is /usr/lib/openoffice) and check under program/classes for juh.jar and unoil.jar. These are necessary to get the correct classes for this sample.

So let's get to the coding! We want to make something useful, so we'll create something that I complained about in the other post: a random number generator. It'll be a little script that reads in 3 arguments from the command-line: number of values to generate, and the lower and upper bound. We'll ignore some error checking for simplicity, you can feel free to add it in if you like.

First thing we need to do is load in all the Java stuff:

require "java"
require "juh.jar"
require "unoil.jar"

include_class "com.sun.star.uno.UnoRuntime"
include_class "com.sun.star.comp.helper.Bootstrap"
include_class "com.sun.star.beans.PropertyValue"
include_class "com.sun.star.sheet.XSpreadsheetDocument"
include_class "com.sun.star.sheet.XSpreadsheet"
include_class "com.sun.star.sheet.XSpreadsheetView"
include_class "com.sun.star.table.XCell"
include_class "com.sun.star.frame.XModel"
include_class "com.sun.star.frame.XComponentLoader"

def queryUno(klass, obj)
UnoRuntime.queryInterface(klass.java_class, obj)
end

What this does is fairly obvious, it loads in the JAR files, and the classes that we will need for this example. Fortunately, JRuby only needs to load in the classes that we directly use. Ones that are used in the code but not directly used don't need to be included this way - otherwise we'd have to add like 5-10 more include_class lines to this.
I also create a little helper function there to save you some typing. Basically this queryInterface function is used to get an object of a specific type (klass) based on an interface that you pass (obj). It's not really necessary with Ruby, but since we're working with an API that is based around static languages it is something we need to do for now.

Let's set some variables based on the command line. Let's assume only 3 variables were passed, and that they were all integers:

N, A, B = ARGV.map(&:to_i)

Next thing we need to do is actually connect to OpenOffice. We do this by creating a remote context which connects to OpenOffice itself, and a service manager which gives us access to various components of OpenOffice. We want to access the "com.sun.star.frame.Desktop" service which handles the documents that are loaded. After that, we want to load up Calc so that we can start mucking with things.

# bootstrap the environment and load desktop service
remoteContext = Bootstrap.bootstrap
desktop = remoteContext.getServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", remoteContext)

# get us something to load the Calc component
componentLoader = queryUno(XComponentLoader, desktop)

# load the Calc component
calcComponent = componentLoader.loadComponentFromURL("private:factory/scalc", "_blank", 0, [].to_java(PropertyValue))
calcDocument = queryUno(XSpreadsheetDocument, calcComponent)

Wow, all that code. As we can see, we're making heavy use of the Factory pattern. We connect to OpenOffice using the Bootstrap class, which spits out a remote context object that we can use to access the various services of OpenOffice. We then create a desktop object, which lets us access the Desktop service - which we need in order to edit documents.
After that we create a component loader (read: Component Factory) and use that to create us a component for Calc. Note that I use this:

[].to_java(PropertyValue)

Since the loadComponentFromURL method is expecting a Java array, we need to pass it a Java array. The little snippet there is the equivalent to this in Java:

new PropertyValue[]

Finally, we create an object for our Calc document. We can now start editing things! But first, let's see if this actually works for you. Save the file as calc.rb, and run this line to execute it (replace OOHOME with your OpenOffice install directory, /usr/lib/openoffice on Ubuntu):

jruby -IOOHOME/program/classes calc.rb 10 1 10

If you run this, what you should get is a blank OpenOffice Calc window opening up.

Assuming everything is working, let's start entering our data. We want to create a new sheet:

sheets = calcDocument.getSheets
sheets.insertNewByName("Random Numbers", 0)
sheet = queryUno(XSpreadsheet, sheets.getByName("Random Numbers"))

Fairly straight forward, we get the sheets of the document, insert a new one called "Random Numbers" at the beginning, and get the object for it.

Let's add our random numbers:

N.times do |i|
cell = sheet.getCellByPosition(0, i)
cell.setValue( rand(B - A + 1) + A )
end

If we run this code, we now have a new sheet in our open Calc window called "Random numbers, and the first column has 10 random numbers in it. Cool! Now we can do whatever we want with those numbers.

Finally, if we want to automatically switch to the new sheet:

model = queryUno(XModel, calcComponent)
controller = model.getCurrentController
view = queryUno(XSpreadsheetView, controller)
view.setActiveSheet(sheet)

OpenOffice uses an MVC structure for its information. We need to get access to the view, which is done through the controller, which is accessed through the model. Then we tell the view to set our sheet to the active one.

If you have any questions, let me know! I'm no expert by far, but I've been doing a bit of digging and this is what I've got so far.