Add a picture to an excel file using YAJB and railsxls

February 13th 2008

Ever find yourself wanting to generate an excel spreadsheet that includes logos or images?

If you want to generate xslx spreadsheets it’s not too bad, because you can embed the image binary in the markup – but for older excel formats it can get a little trickier.

Enter railsxls – an excel plugin for Rails!

railsxls is basically a wrapper around the funky POI library, so anything you can do with POI you can do with railsxls.

“But Chris, how can a Rails plugin use a Java library?!?”

Well I’m glad you asked – by using Yet Another Java Branch (or YAJB for short) This exciting little gem let’s you calls out to Java libraries using Ruby code.

Niiiice.

“Ok, so I can use the POI library commands to insert pictures onto an excel spreadsheet?”

Well…kinda…here’s how!

First install railsxls and yajb (details here)

The POI code that handles addPictures uses byte arrays, which YAJB has a hard time transporting between Java and Ruby. (Well, it did for me anyway)

So instead you have to use a jlambda, which let’s you run chunks of Java using ruby…like this:

# Here's the java code I want to run
java_code = "System.out.println(message);" 
# And here's the jlambda that sets it up
# Note the first param "String message" which let's you determine what param to pass in
lambda_to_run = jlambda("String message", java_code)
# Now we're actually CALLING the code and passing in a string
lambda_to_run.call "bananas!"

So, that’s a jlambda!

Now, with this new found knowledge, let’s call the appropriate POI code to create a picture. (I put the java code in the here_doc)

# First off, let's use YAJB calls to import some java libraries
jimport "java.io.*" 
jimport "org.apache.poi.hssf.usermodel.*" 
# Now create the new HSSFWorkbook option
workbook = jnew :HSSFWorkbook
java_code =&lt;&lt;JAVA
java.io.File file = new java.io.File("#{File.join(RAILS_ROOT, 'tmp',"rails.png")}" );
java.io.FileInputStream fis = new java.io.FileInputStream(file);
byte[] bytes = new byte[(int) file.length()];
fis.read(bytes);
return new Integer(workbook.addPicture(bytes, org.apache.poi.hssf.usermodel.HSSFPicture.PICTURE_TYPE_PNG));
JAVA
# Create a worksheet in the workbook - an important step!
sheet = workbook.createSheet("new sheet") 
row = sheet.createRow(0)
# This patriarch business is used for writing images and autoshapes
patriarch = sheet.createDrawingPatriarch
# Anchors are used for positioning
anchor = jnew :HSSFClientAnchor, 0,0,0,255,2,2,4,7;
anchor.setAnchorType( 2 );
# Work your magic jlambda!  (Pass in the workbook as a parameter)
p2 = jlambda("org.apache.poi.hssf.usermodel.HSSFWorkbook workbook", java_code)
image_index = p2.call workbook
# An image index is returned and this is used by the createPicture function
patriarch.createPicture anchor, image_index

The code doesn’t cover saving the workbook, but you could do something like this:

temp = Tempfile.new('railsworksheet-', File.join(RAILS_ROOT, 'tmp') )
out = jnew :FileOutputStream, temp.path
workbook.write(out)
out.close
File.open(temp.path, 'rb') { |file| file.read }

Now go erm…insert images!