The Chris O Show

If you’re anything like me, and you keep forgetting whereabouts your gems are installed, use the following command:

gem environment

This will pop up a handy list like this:

RubyGems Environment:
  - VERSION: 0.9.4 (0.9.4)
  - INSTALLATION DIRECTORY: /usr/local/lib/ruby/gems/1.8
  - GEM PATH:
     - /usr/local/lib/ruby/gems/1.8
  - REMOTE SOURCES:
     - http://gems.rubyforge.org

Your gems are nicely tucked away within your GEM PATH directory

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 =<<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!