Using acts_as_xlsx to generate excel data in your rails application

Getting Started With Acts as Xlsx Part 1

This tutorial gives a basic overview of how to integrate acts_as_xlsx into your Rails 3 application to provide excel downloads of your models. The second part of the series covers style, chart generation and multiple sheets.

After reading this you should be familiar with:

1. Installing acts_as_xlsx in your rails project
2. The basics creating excel  reports from active record objects
3. The basics of streaming excel reports
4. Implementing web-service support with responds_to

This guide is follows on top of the Getting Started with Rails guide for Rails 3.1 and uses the models and controllers created in that tutorial. The plugin supports Ruby versions 1.8.7, 1.9.2 adn 1.9.3 as well as Rails 2.3 and up however the plugin management, routes and web-service syntax is slightly different in the two rails versions.

1 Guide Assumptions

 This guide is designed for intermediate users and assumes a basic knowledge of Ruby on Rails programming, using bundler to manage your gems and a very rudimentary understanding of spreadsheets in general. In addition to the rails setup described in the Getting Started with Rails guide, you also need to add the following to your application Gemfile

gem 'acts_as_xlsx'

And install it from the command line as follows:

$ bundle install

2 What is Acts as Xlsx

Ever have a client ask if they could get that data in Excel? That's what Acts as xlsx does. It is an Active Record plugin that lets you convert any Active Record based model or finder method result into a fully valid xlsx, also knows as Office Open XML spreadsheet. It wraps the axslx gem and integrates it into Active Record.

In fact, using the plugin is ridiculously easy so in this guide we are also going to show you how to stream the files, and set up web-service support so that when a request says it wants xlsx - it gets xlsx.

3 Updating the Models

Adding acts_as_xlsx to your ActiveRecord::Base inheriting models will add a class method to the model called to_xlsx.

Open up the app/models/post.rb file and add acts_as_xslx just below the class declaration.

class Post < ActiveRecord::Base

acts_as_xlsx

Open up the app/models/comment.rb file and add acts_as_xslx just below the class declaration.

class Comment < ActiveRecord::Base

acts_as_xlsx

4 Update the Posts Controller

Now lets add the xlsx format to respond_to for web-service support.

def index

@posts = Post.all

respond_to do | format | 
  format.html # index.html.erb
  format.json { render :json => @posts }
  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/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    ensure
      temp.close
      temp.unlink
    end

 }  

  end

end

There is quite a bit going on in that code, and we will be adding more as we move forward. The important things to understand in terms of acts_as_xlsx are that you will need to serialize the package to a file resource, and then return it with send_file.

EDIT: This has come up a few times so I will add it to the original post here.

Use the following to stream the file without writing it to disk on the server:

send_data xlsx_package.to_stream.read, :filename => 'posts.xlsx', :type=> "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

5 Update the View

While you can directly access localhost:3000/posts.xlsx, lets add in a url_for based link for requesting the download. Add the following to the bottom of app/views/posts/index.html.erb

<%= link_to 'Download', url_for(:format=>"xlsx") %>

Click that link at http://0.0.0.0:3000/posts and here is what you get:

In the next post, we will take a look at localization of field names, using finder methods to restrict the data and customizing the columns reported.

For now, here are a couple of hints:

acts_as_xlsx :columns => [:created_at, :name, :title, :content, :'comments.last.content']
Post.to_xlsx :columns => [:created_at, :name, :title], :i18n => 'activerecord.attributes'