poi_pond¶ ↑

This is a gem to encapsulate the Apache Java POI library for use in a native (non jruby) environment.

My motivation for creating this gem was the need to create an excel spreadsheet in Ruby that offers some advanced features that currently do not exist in other Ruby Excel generator gems. POI has a gem that is supposed to compile POI as a native Ruby extension. I tried it and had a lot of problems with it and finally gave up on that approach. I really didn’t feel like converting my project to jruby just to use POI, and using an approach that required running a JVM based process in the background or doing system calls just seemed wrong. So I created POI pond which uses RJB to access the the jvm, load POI and encapsulate the poi functions in a form that is not as doing direct RJB/POI calls.

You will need to have a jvm installed and in your path to work correctly. At the time of this writing this gem has only been tested on MacOS version 10.6.6. Your mileage may vary.

This is a light wrapper around POI which is a Java library. If a method is added by the gem it follows standard Ruby conventions ie: my_method_name. If a a method is coming directly from the POI java library it follows the Java camel case convention myMethodName. My thought is that it would make it easier to know where to look for documentation etc. following that approach. If this really bothers you send me a message or just fork the project and add some wrappers to make things more ruby like. There is also an opportunity to make things a bit more user friendly do dry up the syntax a bit more. It did enought for my needs to get my product out the door, but I may revisit this again at a later date. If you want to fix bugs or improve things I will be happy to merge them in as long as your code has good unit tests around it.

POIPond is currently set up to be a mixin for use in a class.

require ‘poi_pond’ require POIPond

The primary intended use of this gem at this point is to create a big spreadsheet at one time. Below is an example of how you would do that. when your app starts up you will also need to call initialize_poi to get the java classes loaded.

sheet = create_spreadsheet([:sheet => {:name => 'sheet1', :row => [{:row_index => 1, :cell => [{:cell_index => 1, 
                          			:style => 'title'}]}]}],

{‘title’ => {:font_height => 24}})

create_spreadsheet takes a array of sheets and a hash of styles.

each sheet has a hash with a name and an array of rows. each row contains a row_index which matches up to the excel row number and an array of cells. Each cell contains an index which matches the excel column number, a style which matches one of our styles in the style hash and a value which contains the cell’s contents.

Here is an example of a style hash.

{‘title’ => {:font_height => 24, :font_color => ‘DARK_BLUE’, :font_name => ‘Tahoma’, :horizontal_alignment => ‘ALIGN_LEFT’, :vertical_alignment => ‘VERTICAL_TOP’}}

This one has one style called ‘title’. The font names come from the available system fonts for the client and the horizontal and vertical alignment values come directly from POI, see the POI documentation to find the supported values.

now that we have created our spreadsheet lets output it.

sheet.write poi_output_file(‘my_awesome_poi_spreadsheet.xsl’)

poi_output_file take a filename string that you want to write your spreadsheet to.

if you want to send your excel file as a file in a rails app you would do something like this.

excel_byte_array = poi_byte_array_output_stream sheet.write excel_byte_array

excel_byte_array is an Java object loaded through rjb so to use it you will need to use the Java call to return a byte array that can be converted to a ruby object like this.

excel_byte_array.toByteArray

if you want to include a photo in your spreadsheet you would do this.

file = File.new(‘allie2.JPG’).bytes.to_a sheet = create_spreadsheet([:sheet => {:name => ‘sheet1’, :photos => [{:row => 10, :column => 1, :photo => file}], :row => [{:row_index => 1, :cell => [{:cell_index => 1, :style => ‘title’}]}]}], {‘title’ => {:font_height => 24}})

this reads in a file and turns it into a byte array. POIPond reads the byte array and embeds it in the spreadsheet in the row and column you tell it to place it in.

• Check out the latest master to make sure the feature hasn’t been implemented or the bug hasn’t been fixed yet

• Check out the issue tracker to make sure someone already hasn’t requested it and/or contributed it

• Fork the project

• Start a feature/bugfix branch

• Commit and push until you are happy with your contribution

• Make sure to add tests for it. This is important so I don’t break it in a future version unintentionally.

• Please try not to mess with the Rakefile, version, or history. If you want to have your own version, or is otherwise necessary, that is fine, but please isolate to its own commit so I can cherry-pick around it.

Copyright © 2011 Lance Gleason. See LICENSE.txt for further details.