Monday, December 5, 2011

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'



25 comments:

  1. undefined local variable or method `xlsx_package' for #

    ReplyDelete
  2. king M give me more to work with please. If there is a problem with the plug in or a problem with the code I am more than happy to do everything in my power to make it work.....

    ReplyDelete
  3. this tutorial you can apply to activeadmin dashboard, the dashboard does not have model. is possible if one could show me the steps?

    tks

    ReplyDelete
  4. Hi, I'm trying to use axlsx in a activeadmin custom page and I get a Template is Missing error:
    Missing template active_admin/page/index with {:locale=>[:en], :formats=>[:xlsx], :handlers=>[:erb, :builder, :arb, :coffee]}. Searched in: * "/projects/myapp/app/views" * "/home/ruby/.rvm/gems/ruby-1.9.3-p194/gems/activeadmin-0.4.4/app/views" * "/home/ruby/.rvm/gems/ruby-1.9.3-p194/gems/kaminari-0.13.0/app/views" * "/home/ruby/.rvm/gems/ruby-1.9.3-p194/gems/devise-2.1.0/app/views" * "/projects/myapp" * "/"

    If I use axlsx in another page that has a model it all works well, I think the problem is similar to King M's.

    Can you give me some help on this? thanks.

    ReplyDelete
    Replies
    1. Hi Joao,

      Can you post your controller code to the repo (https://github.com/randym/acts_as_xlsx) please? I have a feeling that your responder is not set up correctly as that error is rails looking for a view template.

      Delete
  5. page_action :export do

    acts_as_xlsx

    @posts = Post.all

    respond_to do | format |
    format.html
    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
    }
    end
    end

    i used activeadmin custom page and code not work help please!

    ReplyDelete
    Replies
    1. To be honest, I know next to nothing about activeadmin, but looking at what you have there, you are declaring acts_as_xlsx on your controller, but it needs to be called in you model.

      acts_as_xlsx is a mixin for classes that inherit from ActiveRecord::Base, so slapping it into a controller is definitely not going to give you the results you are looking for.

      Do you have a Post.rb model?

      Delete
  6. Incidentally, you don't have to save the file and serve it. You can do the following:

    send_file xls_package.to_stream.string, :filename => 'posts.xlsx", :type => 'application/xlsx'

    ReplyDelete
    Replies
    1. probably better as

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

      Delete
    2. That is vnd.openxmlformats instead of vnd.openxmlformates. Agree?

      Delete
  7. the page they want to present is just a combination of tables and so I have no model, there is some way to declare the variable in the model without being acts_as_xlsx?

    ReplyDelete
    Replies
    1. Ok, So Ive spent some time going over active_admin and I now understand the basics of how it works. As you are looking to export more than one model, you will need to add a custom controller method and generate it directly with the axlsx gem. (acts_as_xlsx is just a wrapper to help you dump your models) Have a look at some of the other blog posts here, and definitely check out the examples/example.rb file in the axlsx repo.

      1. add a custom controller action. Maybe call it 'dashboard_report'
      http://activeadmin.info/docs/8-custom-actions.html

      2. In that controller action, create the report you need directly with axslx
      http://github.com/randym/axlsx

      This will give you full control of the styling and layout so that you can create an excel file that is representative of the data showing on your dashboard.

      If you want to outsource this, please don't hesitate to contact me by email.

      Delete
    2. In the mean time, for basic resources, I just pushed up a pull request that will add an [xlsx] link to each index page for generating a proper xlsx file.

      For you dashboard, you will now have a xlsx_builder that can be used to make a custom package.

      Delete
    3. activeadmin-axlsx has been released.
      Just add it to your Gemfile. No acts_as_xlsx required. I'll try to do a write up when I have a few extra hours.

      Delete
  8. respond_to do | format |
    format.html
    format.xlsx {
    xlsx_package = Post.to_xlsx
    begin
    send_file xlsx_package.to_stream.read, :filename => "posts.xlsx", :type => "application/xlsx"
    ensure
    end
    }

    error string contains null byte

    help pls

    ReplyDelete
    Replies
    1. pretty sure if you are looking to send a stream, that you want to use send_data instead of send_file.

      Delete
    2. This comment has been removed by the author.

      Delete
  9. Under rails 2.3.8 I have just verified that using send_data instead of send_file gets rid of the null byte error.

    Under rails 2.3.14 using send_file also produces a null byte error. But if you use send_data, then I get a "invalid byte sequence in US-ASCII". Any ideas on how to solve this (besides writing to a temp file)?

    Test were made using a fresh rails app, rvm and ruby 1.9.2-p290

    ReplyDelete
    Replies
    1. Ahh.. I just did one more test. Under rails 2.3.14, send_data works fine with ruby 1.8.7. The "invalid byte sequence in US-ASCII" happen under ruby 1.9.2-p290

      Delete
    2. For Ruby 1.9.2 and up, please ensure that the encoding hint is at the top of your source file.

      # encoding: utf-8

      Delete
    3. Yes, I have the encoding declaration in the top, but that does not seem to be enough to solve the problem.

      I have found that the problem is solved by also forcing the encoding like this:

      send_data xlsx_package.to_stream.read.force_encoding("ISO-8859-1"), etc.

      The generated file opens nicely in LibreOffice. But... Is this the right encoding for xlsx files? It doesn't work if I use force_encoding to UTF-8.

      Anyway, it works fine this way for me, even the accents áéíóú.

      Delete
  10. how to limit the data and present oly a customized output ...could you please help ??

    ReplyDelete
    Replies
    1. To limit data, Just use your normal active record scoping to limit the data:

      Something like Post.where(foo: :bar).to_xlsx

      For customizing output you have a few options. You can generate the sheet with to_xlsx and then format the sheet after inserting the data, or roll your own report with the Axlsx gem directly.

      Internally, because we make some pretty complex reports, we have our own custom classes for storing formatting, managing data types and formatting etc. (roll your own)

      Also, axlsx_rails does a good job of moving the report code out into the view layer as well, so you might have a look at that too.

      Delete
  11. Can I use the "if-else" syntax?

    ReplyDelete
  12. Thanks for sharing this. this formula is originally written and it's very useful for me I recommend it The result is really impressive,I get more dynamic dashboards/ Check it.

    ReplyDelete