[rails-oceania] Exporting content to Excel

[Matthew Delves]

Greetings all,
I've been looking at the spreadsheet (http://
spreadsheet.rubyforge.org/) gem to get data exported from my database
to an excel document, though am running into a couple of hitches with
it.

It doesn't want to write the file correctly. The code that I currently
have is:

--
def excel
# Create a new spreadsheet
book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
sheet.name = 'All Member Details'

# Add the header row
sheet.row(0).push Member.column_names
puts Member.column_names

# Add the content
Member.find(:all).each_with_index do |member, index|
sheet.row(index + 1).push member.attributes.values
puts member.attributes.values
end

# close the workbook
# book.close

# Offer up the file for download
downloadpath = 'downloads/members.xls'
book.write downloadpath
send_file downloadpath, :x_sendfile => true

end
--

Any help in getting this going would be greatly appreciated.

Thanks,
Matt Delves

[Torm3nt]

Hi Matt,

Could you be a little more specific? What do you mean by "write the file correctly"?

1. Doesn't write it at all?

2. Writes the file but screws up the formatting?

3. Writes it to the wrong location?

.etc.etc.

Kirk

[Matthew Delves]

Hi Kirk,

The file gets written to the right location though it ends up only being about 8 bytes in length. I would guess from this that it doesn't write it correctly.

Thanks,

Matt Delves

[James Sadler]

Hi Matthew,

I can't help specifically with the Spreadsheet gem (I recall trying it and having similar issues and crashes), but there is a robust alternative.

I've had to deal with Excel files from a Rails app recently, and I found the Ruby Excel libraries to be very under-featured or fragile.  In the end I used JRuby and the Apache POI Java library which in my experience has been very robust.

So you end up using Java but the pain is somewhat abated by using JRuby.  It was an acceptable compromise for me :)

Regards,

James.

2009/3/9 Matthew Delves <matthew...@gmail.com>

--
James

[Marc]

I might be way off the mark here. But I did find if you don't require
automating the export to excel and if you are using rails, you could
just use a respond_to with a format.xls. You just have to specify the
mime_type (Mime::Type.register "text/html", :xls), and in the
respond_to block, do something like:

respond_to do |format|
format.xls {
headers['Content-Type'] = "application/vnd.ms-excel"
headers['Content-Disposition'] = "attachment; filename='#
{filename}-#{Time.now.to_s}.xls'"
headers['Cache-Control'] = ''
render :action => action_name, :layout => false
}
end

Forgive the fugly code =). In the views, just use a table with the
necessary thead and tbody... no other formatting required.

It's easier. I did find issues with the other solutions as well... and
most of users didn't mind a click to get the reports instead.

Cheers
Marc