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.