Excel on Rails Like a Pro with axlsx_rails

It just keeps getting better...

A few months ago I knocked out the first of what should be about 10 or 12 posts highlighting what contributors have added in to axlsx. - While I have a draft of the next one ready some really cool stuff has recently come online by Noel Peden that I am going bring up to the top of the stack.

It is not a contribution to axlsx, but a contribution to the entire rails community build on top of axlsx to provide cleanly coded, professional reporting with Excel via an axlsx specific renderer and template handler.

Hats off to you Noel - nice rails-fu.

Digging in

Noel was kind enough to provide a write up using the classic Posts/Comments app. I've added a few modifications, but I think you will quickly see why this is by far the best approach to generating xlsx reports off a rails application.

Axlsx_Rails 

You’re up and running with Axlsx. Fantastic! But what about your rails app? Acts_as_xlsx takes care of the model side of MVC. First you configure the model:

# models/post.rb
class Post < ActiveRecord::Base
    acts_as_xlsx

Then in a single elegant line you can create standard spreadsheets from your models:

Posts.where(created_at > Time.now-30.days).to_xlsx

This is short enough that sending this back with your controller isn’t too bad:

# app/controllers/post_controller.rb
def index
    @posts = Post.all

    respond_to do |format|
        …
        format.xlsx {
            send_data Post.to_xlsx.to_stream.read, :filename => 'posts.xlsx', :type => "application/vnd.openxmlformates-officedocument.spreadsheetml.sheet"
        }
    end

But what about formatting? This can quickly produce lots of code:

# app/controllers/post_controller.rb
…
format.xlsx {
    chart_color =  %w(88F700, 279CAC, B2A200, FD66A3, F20062, C8BA2B, 67E6F8, DFFDB9, FFE800, B6F0F8)
    header_style = { :bg_color => "00", :fg_color => "FF", :alignment => { :horizontal => :center }, :bold => true }
    package = Post.to_xlsx(
    header_xf = package.workbook.styles.add_style header_style
    package.workbook.worksheets.first.tap do |sheet|
        sheet.row_style 0, header_xf
        sheet.add_chart(Axlsx::Pie3DChart, :title => "コメント獲得") do |chart|
            chart.add_series :data => sheet.cols[3][(1..-1)], :labels => sheet.cols[0][(1..-1)], :colors => chart_color
            chart.start_at 2, sheet.rows.size
            chart.end_at 3, sheet.rows.size + 20
        end
    end

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

In short order your controllers will be very full. You can make a separate controller method, but you still have view code in the controller.

Enter axlsx_rails. Axlsx_rails provides a renderer and template handler for xlsx. Place your code in templates with the .xlsx.axlsx extension, and use the provided Axlsx package instance (aptly named xlsx_package):

# app/views/posts/index.xlsx.axlsx
…
Post.to_xlsx package: xlsx_package
xlsx_package.workbook.worksheets.first.tap do |sheet|
    …
end

And in your controller, you merely need:

format: xlsx

Or, if you need to specify the filename:

render :xlsx => "index", :filename => "all_posts.xlsx"

If you don’t use acts_as_xlsx, you pass in your data and use the xlsx_package to create a spreadsheet:

# app/views/posts/custom.xlsx.axlsx
wb = xlsx_package.workbook
wb.add_worksheet(name: 'Foobar') do |sheet|
    @data.each do |datum|
    …
end

What about partials? They work too. Simply pass in the package, workbook, or the variable desired. Using this you can create a common coversheet, or common formatting, or header:

# app/views/shared/_cover_sheet.xlsx.axlsx
wb.add_worksheet(name: "Cover Sheet") do |sheet|
    sheet.add_row ['Cover', 'Sheet']
end

Which would be used like:

# app/views/posts/custom.xlsx.axlsx
wb = xlsx_package.workbook
render :partial => 'shared/cover_sheet', :locals => {:wb => wb}
wb.add_worksheet(name: "Content") do |sheet|
    sheet.add_row ['Content']
end

There isn’t much more to it. For the few remaining details, see the github page.

I envision that this is going to let you do CRAZY cool stuff like define all of your styles for the workbook in one partial and generate separate reports depending on which view and partials you load.

Give it a go! If you get stuck, you can always find me (and Noel sometimes too!) on freenode #axlsx