How to export data from model to excel file on rubyonrails

Question

I'm trying to export data from my models to an excel spreadsheet. I have seen 3 ways

1. Using the spreadsheet gem which I didn't understand how to use it, the examples I saw was writing to a local file but I'm looking to generate a file every time user clicks on a link.
2. Creating a method called export, and running the query there, then making a export.xls file in my view, and that file creating the table I want to be exported to the excel file, but this approach don't allow me to create multiple sheets.
3. Followed this tutorial, http://oldwiki.rubyonrails.org/rails/pages/HowToExportToExcel, but here doesn't show how to put the link in the view, looks to me that I'm missing something in the routes, I can give github so you can take a look at my code if needed.

Answer 1

My choice is to just manualy generate CSV file. Like:

File.new("data.csv", "w+") do |f|
  @my_data.each do |data|
    f << [data.title, data.body, ...].join(", ") + "\n"
  end
end

CSV file can be opened with excel or any other spreadsheet soft.

Answer 2

Plugging my own gem here, but you might have a look at https://github.com/randym/acts_as_xlsx

It gives you a bit more than writeexcel or spreadsheet in terms of localization, graphs, tables and formatting from the axlsx gem.

It also integrated with active record scoping and method chains.

Blogpost with detailed usage examples: http://axlsx.blogspot.com/

http://axlsx.blogspot.jp/2011/12/using-actsasxlsx-to-generate-excel-data.html

http://axlsx.blogspot.jp/2011/12/axlsx-making-excel-reports-with-ruby-on.html

On Github: https://github.com/randym/axlsx

On Rubygems: https://rubygems.org/gems/axlsx

On Rubytookbox: https://www.ruby-toolbox.com/projects/axlsx

Basically it involves setting up a responder in your controller

    format.xlsx {
      xlsx_package = Post.to_xlsx
      begin
        temp = Tempfile.new("posts.xlsx")
        xlsx_package.serialize temp.path
        send_file temp.path, :filename => "posts.xlsx", :type => "application/xlsx"
      ensure
        temp.close
        temp.unlink
      end
    }  

and the following on your model

     class Post < ActiveRecord::Base
       acts_as_xlsx

The two blog posts above give a fairly clear walk-through.

Answer 3

I'm using writeexcel in my most recent Rails project. A fast and simple to use way to export excel files directly - no CSV!

To use it directly in your views you have to register writeexcel as a template handler - this is excalty what my gist does. Then create a new template like export.xls.writeexcel, insert your code and you're good to go.