OS Independent Excel Reports
by Tyler on Jun.28, 2008, under Programming, Ruby, Ruby on Rails, win32
I ran into this problem awhile ago. I was tasked with making Excel reports using Ruby on Rails. Since I was running on Windows, I just used Win32ole. It worked quite well until I remembered that Win32 is not platform independent. Basically, if the system is hosted on a non-Windows machine the Excel Report will fail. Great. But, Ruby isn’t out for the count yet. I found a pretty cool spreadsheet generator at RubyForge. After installing spreadsheet, it is pretty simple to use. You can either install it by downloading it from the web, click HERE, or use ruby gems by typing this on the command line: “gem install spreadsheet-excel”
#create the workbook
workbook = Spreadsheet::Excel.new(”reports/file.xls”)
#make some formats
headers = Format.new(:color => “black”, :bold => true)
data = Format.new(:color => “black”, :bold => false, :align => “right”)
#spreadsheet uses numbers for rows and columns i.e. A1 = 00
column = 0
row = 0
#write some headers from some header object, assume this is an array
header.each |field|
worksheet.write(column,row,field.chomp,top)
column+=1
end
#now insert data so move down a row
column = 0
row = 1
#the object list is an array
#each entry is a result of the query against the database
object_list.each do |object|
headers.each do |value|
worksheet.write(row,column,object[value],data)
column +=1
end
row +=1
end
#finished so close
workbook.close
Pretty simple. I like the formatting features. It is very easy to switch a format style after defining a particular format object. One of the design decisions that can be argued is how to actually write the data. An alternate approach is to write the headers one at a time, and as each header is written, iterate through the objects that we wish to write. The described method is inefficient because it ignores how Ruby stores its arrays, row-major order.
An alternate approach to calling the write method is to pass an array. Unfortunately, when objects are returned by using the find method, the fields do not return in a specific order so there is no guarantee the headers we wrote at the top of the spreadsheet will be in the same order as the objects that were returned. I deployed a version of this with Ruby Rails.
In my own experience, my new function had surpassed my old Win32 based function in both speed and portability thanks to spreadsheet. I recommend using this method even if your project is slated for a windows box with office installed. Unless you need formulas, then you are out of luck 
The example is almost finished. I encapsulated the above function in a method. You can decide where to implement it, either in one of the helper controllers or in the current controller. In my implementation, a user navigates to the reports page, navigates some Ajax controls and submits, using a button that calls the function below, a desired report. The view for display_printout is left blank.
def display_printout
#I named the excel report create_printout
#I included some error handling and a success returns true
if create_printout
send_file “reports/report.xls”
else
#display some sort of error message
end
end
