One of the great advantages of using JRuby is the ability to connect with Java libraries (JARs) and call Java code from your Ruby scripts. This post shows how to use Apache POI (the Java API for Microsoft Documents) to generate a simple XLSX file.

Apache POI is implemented in Java and can be called from other Java code if it is set up correctly. JRuby takes care of a lot of the plumbing that is required to make this work from your Ruby scripts. In this post, we go through the basics of making the code work.

Framing our Approach

At a very high level, there are two ways to approach the creation of the XLSX file:

• Direct mode: in this mode, the data for the full Excel file is kept in memory and then finalised and flushed to disk all at once. The memory consumption in this case can be significant, especially if the file is very large.
• Streaming mode: in this mode, the data is continuously written to a temporary file and this results in a much tighter memory profile since a specified number of records are kept in memory. The rest are written to the disk.

Picking one over the other is a matter of trade-offs and is outside the scope of this post. You should read the benefits and limitations of using the SXSSF Streaming User API on the Apache POI site. Also, see the table at the end of this page that shows which features are not available in which format. Finally, if you try something else, the main how-to page is very good reading.

In this post, our aim is also to be able to use the sample code on the how-to page for the SXSSF Streaming User API from JRuby and to see how it is approached and done.

If you want to jump ahead and just get the source code, take a look at my rb-xlsx-converter-comparison repository on GitHub and read the README and specifically look at the code in use_poi_stream.rb for what works.

Background to Apache POI

Here are a few things that we need to know about Apache POI and that will help us find the way to proceed when integrating from JRuby.

1. As we said earlier, it’s Java – so, it’s distributed as compiled JARs. We will need to grab the latest stable distribution
2. Many JAVA libraries are very well documented and Apache POI is no exception. We will need to refer to the API Documents for Apache POI frequently.
3. Naturally, it helps to have some code that we use to get started. We will use the sample code on the how-to page for the SXSSF Streaming User API. If you try to do something more complicated and want to look up how to achieve it, the Busy Developers’ Guide to HSSF and XSSF Features is an excellent quick reference. Keep it handy.
4. Of course, for this work, we are using JRuby. So, you need Java, and you also need to install JRuby on your system

Our Scope

Simply: use JRuby + Apache POI to create an Excel file that has 1000 rows with each row having 10 cells filled with some random data.

So, if we are to put in pseudocode, we just need to do this:

initialize the xlsx file
for 1000 rows
  for 10 columns
    generate and add a cell
  end
end
finalize the xlsx file

Going back to JRuby

By now, you probably know how to use jruby to run a ruby script. If you need a quick refresher take a look at this post on running scripts from JRuby on this site. In short, we expect to basically just do jruby script.rb on the command line.

In preparation, you might also want to read the page on Calling Java from JRuby on the JRuby wiki.

Putting this post together basically needed:

• Download stuff
• Read the wiki on how it works
• Find the sample code
• Try to convert it to JRuby
• Stare at the failure
• Read the wiki again
• Check the sample again
• Check the API documentation (ah!)
• Fix the code and repeat

So, yes, anyone can do it!

Let’s get started – setting up

The first thing we need to do is set up the project and then we will get into the rest of the details.

For this post, we will follow a simple approach to get this working. The main Ruby script will go into the root of the project folder for now and we’ll include folders for the Java JARs.

Now, let’s download what we need.

• Grab the latest distribution of Apache POI and unzip these into the project folder.
• Also, Apache POI uses log4j for logging (by default) – so, you need a couple of JARs from there too. Grab the latest distribution and unzip the files into the project folder.

The directory structure should like below:

use_poi.rb
 ├───log4j
 └───poi-bin-5.1.0
     ├───auxiliary
     ├───lib
     └───ooxml-lib

Good! We are ready to get started. If you have a different directory structure or layout, don’t worry – you’ll only need to make some minor changes in the require_relative statements.

I’ve used JRuby 9.2.18.0 but any recent version should work.

Quick Overview of Connecting up from JRuby

Here are some things to keep in mind when using a Java JAR from JRuby. There’s a lot more on the Calling Java from JRuby page which you should definitely read.

1. You need to require 'java' so that you are able to bridge through to the Java world
2. You need to require all the JAR files that are needed by the script (and by the JARs you require)
3. Once that is done, you’ll be able to refer to Java classes via their full paths
4. You will then be able to call the Java methods defined on any of these classes

I found that the most difficult part was #3 – finding what is a class and is available for you to call from your JRuby code. Sometimes, you get errors that do not point out obviously what might have failed, and this can take a bit of time to work out. It naturally gets easier as you do more of it (or if you’re also familiar with Java).

Constructing our script

So, we are ready to start now that we have the directories set up as required and the JAR files available.

Require Java & the JAR files

The first bits of code we need are:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
require 'java'

require_relative 'log4j/log4j-core-2.16.0.jar'
require_relative 'poi-bin-5.1.0/poi-5.1.0.jar'
require_relative 'poi-bin-5.1.0/lib/commons-codec-1.15.jar'
require_relative 'poi-bin-5.1.0/lib/commons-collections4-4.4.jar'
require_relative 'poi-bin-5.1.0/lib/commons-io-2.11.0.jar'
require_relative 'poi-bin-5.1.0/lib/commons-math3-3.6.1.jar'
require_relative 'poi-bin-5.1.0/lib/log4j-api-2.16.0.jar'
require_relative 'poi-bin-5.1.0/lib/SparseBitSet-1.2.jar'
require_relative 'poi-bin-5.1.0/poi-ooxml-5.1.0.jar'
require_relative 'poi-bin-5.1.0/poi-ooxml-full-5.1.0.jar'
require_relative 'poi-bin-5.1.0/ooxml-lib/commons-compress-1.21.jar'
require_relative 'poi-bin-5.1.0/ooxml-lib/commons-logging-1.2.jar'
require_relative 'poi-bin-5.1.0/ooxml-lib/xmlbeans-5.0.2.jar'
require_relative 'poi-bin-5.1.0/ooxml-lib/curvesapi-1.06.jar'

1
2
3
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk

1
2
3
4
5
# Point to the Java class for the streaming file
StWb = org.apache.poi.xssf.streaming.SXSSFWorkbook

# Create new file keeping only the latest 100 records in memory
wb = StWb.new(100)

1
2
3
4
5
6
7
8
9
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 1000; rownum++){
  Row row = sh.createRow(rownum);
  for(int cellnum = 0; cellnum < 10; cellnum++){
      Cell cell = row.createCell(cellnum);
      String address = new CellReference(cell).formatAsString();
      cell.setCellValue(address);
  }
}

1
2
3
4
5
6
7
8
9
10
# Add a sheet to the new workbook (must provide sheet name)
sheet = wb.createSheet('Sheet 1')

# Add 1000 rows to the sheet
(0...1000).each {|rownum|
  row = sheet.createRow(rownum)
  (0...10).each {|cellnum|  # Add 10 colums to the sheet
    row.createCell(cellnum).cell_value = (rand * 1000).to_i # store a 4 digit random value
  }
}

1
2
3
4
5
6
7
# Finalise the file - needs a Java IO FileOutputStream to write it out
java_import java.io.FileOutputStream
fos = FileOutputStream.new('poi_1k-rows.xlsx')

wb.write(fos) # Combines data from the temporary file into the final file
fos.close # Closes the file
wb.dispose # Removes the temporary file(s) that were created along the way