Thursday, December 8, 2011

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.






6 comments:

  1. Randym can u please give an example of how to generate reports from multiple tables and I must be able to customize the report generated with certain conditions. I have followed your blog for generating excel reports and im able to generate but im not sure of how to combine multiple data's from multiple table and put it in a single excel sheet. please can you help me out ,I'm a beginner in rails.

    ReplyDelete
    Replies
    1. Hi Gandhimathy

      I forgot to mention that I am in JST, so I might not respond immediately on IRC.

      Delete
  2. Hi Gandhimathy

    There are a number of approaches that can be used to achieve what I think you are trying to do.
    I think the easiest would be to create a reporting class that accepts any search parameters from your controller, loads up the appropriate records with your standard models and then generates the excel data you want to create.

    But let's start slow, yeah?

    I have a simple rails app on github that would be perfect for this.
    (The UI is all in Japanese, but we can ignore that and just focus on the code.)

    Can you fork/clone the following two repositories please?
    https://github.com/randym/minamirb
    https://github.com/randym/axlsx

    We will use the first one to actually create examples of the kind multi model reporting you want to do, and the second is the core repo for generating styles. Having a copy of this you can refer/hack on along the way will help quite a bit and it is always easier (and safer!) to hack in a clone of the repo than your installed gem.

    When you are set up, grab me on irc.freenode.net in the #axlsx channel.
    I am assuming you are familiar with forking and cloning repo's, but if you are not, jump into that IRC channel and I will walk you thru it.

    ReplyDelete
  3. Couple issues: your first two instances of "title" on this page should have a colon in front of them ( as in acts_as_xlsx :columns => [:id, title, :content, :created_at] versus acts_as_xlsx :columns => [:id, :title, :content, :created_at] )

    Also, when I attempt the i18n step, I get the error "Your worksheet name 'translation missing: en.activerecord.attributes.posts' is too long. Worksheet names must be 31 characters (bytes) or less"

    I tried changing a few things and finally added a line "posts: Posts" right above the "post:" line and that seemed to fix it.

    Otherwise, thanks for this tutorial!

    ReplyDelete
  4. I dunno if the guy behind this gem is still mantaining but I'd like to know if this gem supports AJAX calls. I'm using it for a while and it's a great tool but I can't make it through AJAX.

    ReplyDelete