Axlsx Making Excel Reports with Ruby on Rails Part 2

Getting Started with Acts as Xlsx

In my last post I showed how to quickly and easily generate an excel file from your rails models. But to be honest, most of the time we really dont' want to show every field in the table to our clients. They don't need to know, or frankly really care about every field in our database and what about labels?

In this guide I'll show you how to do that along with a few pieces of Axlsx styling that take your report from data to asset.

After reading this you should be able to

1. specify what columns you want included in your report
2. Use the I18n library to specify labels for your columns
3. Specify styling and formatting for your worksheet
4. Add a chart to your worksheet.
5. Combine multiple models into the same workbook 

1. Guide Assumptions
This guide serves to introduce a few of the features that are available in axlsx - the most complete Office Open XML library for spreadsheet generation to date. It also assumes that you are familiar with generating the yaml files used for I18n in rails.

2. Specifying Columns
One of the key options of the acts_as_xlsx mixin is the :columns option. It lets you specify an array or symbols that are evaluated against the active record object when generating each row. The columns attribute can be specified both when including acts_as_xlsx to your model as well as when you are generating data with to_xlsx. Any columns you specify in to_xlsx will override the definition on the model. Lets try model level specifications first.


Open up the app/models/post.rb file and add :columns to your acts_as_xslx call just below the class declaration.

class Post < ActiveRecord::Base

acts_as_xlsx :columns => [:id, title, :content, :created_at]

make sure your local server is running and access http://0.0.0.0:3000/posts.xlsx

Notice how the column headers are capitalized and the under-bars have been removed? acts_as_xlsx uses the humanize method for column titles when you are not specifying I18n. Before we update our column titles to a more meaningful set of names, lets add a chained method column.

class Post < ActiveRecord::Base

acts_as_xlsx :columns => [:id, title, :content, :created_at, :'comments.size']

 Any method on your model, and any method or attribute of any relation can be specified in this manner. open up http://0.0.0.0:3000/posts.xlsx and have a look. You can see the number of comments for each post added to the sheet.

2. Using I18n

Open up the app/models/post.rb file and add :i18n to your acts_as_xslx call just below the class declaration.

class Post < ActiveRecord::Base

acts_as_xlsx :columns => [:id, :title, :content, :created_at, :'comments.size'], :i18n => true

Open up config/locales/en.yml and edit it to show the following

en:
  hello: "Hello world"
  activerecord:
    attributes:
      post:
        id: Post Number
        title: Post Title
        content: Post Content
        created_at: Posted At
        comments:
          size: Num of Comments

Regenerate you report by accessing http://0.0.0.0:3000/posts.xlsx and you the column titles are now rendered based on the info in your locale file. If you are using a different language than English, be sure to update that locale file. When i18n is true, acts_as_xlsx will lookup entries under activerecord.attributes by default. You can specify any yaml path you like and acts_as_xlsx will look for entries in that path. For example, you could use 'post_report.attributes' to separate labels for your reports from the labels used in your application.

3. Styling Your Worksheet

The library behind acts_as_xlsx give you tremendous control over how your data is rendered, styled, as well as charting and image management. Let's use some of that power to make this 'data dump' into something you client will be a little bit more enthusiastic about.

You should remember this code from the last post:

  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.openxmlformates-officedocument.spreadsheetml.sheet"
    ensure
      temp.close
      temp.unlink
    end

 }  

Thanks to pmorton  for pointing out the correct MIME type! 

Let's set the style for the header row, and add borders to all the other cells in the table and change the posted date to show the time as well as the date in an international format.

  format.xlsx {
    xlsx_package = Post.to_xlsx :name => "Posts", :header_style => {:bg_color => "00", 
                                                                                :fg_color => "FF",  
                                                                                :sz => 16, 
                                                                                :alignment => { :horizontal => :center }},
                                                                                :style => {:border => Axlsx::STYLE_THIN_BORDER}

        timestamp = xlsx_package.workbook.styles.add_style :format_code => "YYYY-MM-DD HH:MM:SS", :border => Axlsx::STYLE_THIN_BORDER
        
        xlsx_package.workbook.worksheets.first.col_style 3, timestamp, :row_offset => 1
    
    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

 }  

Ok, it is starting to look a bit better now! You can specify :header_style and :style in the call to to_xlsx. Internally it is simply calling add_style to the package workbook's stylesheet, so all of the options available in axlsx for styling an excel in ruby are available to you here. We also call that method to specify the date format for the posted dates in our workbook. For more information about styling with axlsx, take a look at the Styles:#add_style method here

4. Adding a Chart

Lets face it, there are people  who just *love* charts. Any kind of chart - That is why axlsx supports adding charts to your worksheets. At the time of this writing, axlsx is the only ruby gem for writing Office Open XML spreadsheets (xlsx) that supports charts, so lets add on in:

  format.xlsx {
    xlsx_package = Post.to_xlsx :name => "Posts", :header_style => {:bg_color => "00", 
                                                                                :fg_color => "FF",  
                                                                                :sz => 16, 
                                                                                :alignment => { :horizontal => :center }},
                                                                                :style => {:border => Axlsx::STYLE_THIN_BORDER}
 
    sheet = xlsx_package.workbook.worksheets.first 

    timestamp = sheet.workbook.styles.add_style :format_code => "YYYY-MM-DD HH:MM:SS", :border => Axlsx::STYLE_THIN_BORDER
     
    sheet.col_style 3, timestamp, :row_offset => 1

    sheet.add_chart(Axlsx::Pie3DChart, :title => "Comments Per Post") do | chart |
      chart.start_at 0, sheet.rows.size + 1
      chart.end_at 5, sheet.rows.size + 15
      chart.add_series :data =>sheet.cols[4][(1..-1)], :labels => sheet.cols[0][1..-1]
    end
    .... serialization block ....
}  

5. Using Multiple Worksheets
Ok, great we have an overview of the posts in our blog, but what about the actual comments? All those meaty details can be added in with one line.

  format.xlsx {
    xlsx_package = Post.to_xlsx :name => "Posts", :header_style => {:bg_color => "00", 
                                                                                :fg_color => "FF",  
                                                                                :sz => 16, 
                                                                                :alignment => { :horizontal => :center }},
                                                                                :style => {:border => Axlsx::STYLE_THIN_BORDER}
 
    sheet = xlsx_package.workbook.worksheets.first 

    timestamp = sheet.workbook.styles.add_style :format_code => "YYYY-MM-DD HH:MM:SS", :border => Axlsx::STYLE_THIN_BORDER
     
    sheet.col_style 3, timestamp, :row_offset => 1

    sheet.add_chart(Axlsx::Pie3DChart, :title => "Comments Per Post") do | chart |
      chart.start_at 0, sheet.rows.size + 1
      chart.end_at 5, sheet.rows.size + 15
      chart.add_series :data =>sheet.cols[4][(1..-1)], :labels => sheet.cols[0][1..-1]
    end

    # Adding the comments to another sheet
    Comment.to_xlsx :package => xlsx_package


    .... serialization block ....
}  

 The acts_as_xlsx gem also lets you specify an existing package when serializing the models data. Just specify the :package option when you call to_xlsx.


You should end up with something like this:

In my next post, I'll be introducing even more features of axlsx that we can use in ruby to generate excel spreadsheets with more charting, more styles, cell merging and functions. In a fun attempt to recreate one of Microsoft's skydrive template.

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'

Excel With Ruby and Ruby on Rails

It happened, didn't it? Some client asked you if they could get all that great data you are generating in an excel spreadsheet because they need to share it with their colleagues, or perform further analysis.
You probably went the easy route first - csv - and they complained. The dates are wrong, the leading '0's are missing, the percentage data is a string. You should not be too surprised.   Comma-separated values are old technology and pre-date personal computers by more than a decade.

There are already few gems out there for generating excel data that let you modify an excel workbook but they all seem to missing a few key components. We need multiple sheets, formatting, styling, automated column widths, charts in excel, validation, and support for non-ascii text.

And now we have it.

 axlsx and its rails plugin acts_as_xlsx

For those of you who actually look at the gems you are installing, start with the README's
http://github.com/randym/axlsx
http://github.com/randym/acts_as_xlsx

Every method covered in specs, every method with docs and always green on travis.ci.
well, usually green....