Thursday, August 2, 2012

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

Wednesday, April 25, 2012

Axlsx 1.1.3 - Release Announcement: Conditional Formatting Excel with Ruby and Ruby on Rails

Contributors Rock

 This will be the first in a series of "Contributors Rock" posts where I hope I will be able to express my gratitude for those that have stepped up and added functionality as well as used the gem.

@scpike of #mom (hashtagmom)  and other things I am not allowed to talk about is one of those people.

A couple of months ago, after releasing the first version of axlsx, I started thinking about what else was really needed on the sharp edge. One of the things that came up was conditional formatting, so I tossed an issue into the repo: Implement Conditional Formatting for worksheet figuring I'd get around to it sometime in June. What came later was totally unexpected. @scpike piped in saying that he was interested in the feature and posted: "I started working on it in my fork (progress so far), but I have a couple of questions. "

I was mind blown. The code was clean, thoroughly documented, well tested and in line with the style of the rest of the gem. Hats off mate - I would have done a worse job of it myself.

 

The Business End

 So what is conditional formatting? In essence, it allows you to change the style for your cells based on the content of the worksheet. Show your rising profitability in smashing green, or your spiking operations costs in a jarring red. Let have a look at a simple example:

Hitting the high notes

The code for this example is available here
require 'axlsx' 
p = Axlsx::Package.new
p.workbook do |wb|
  # define your regular styles
  styles = wb.styles
  title = styles.add_style(:sz => 15, :b => true, :u => true)
  default = styles.add_style(:border => Axlsx::STYLE_THIN_BORDER)
  header = styles.add_style(:bg_color => '00', :fg_color => 'FF', :b => true)
  money = styles.add_style(:format_code => '#,###,##0'
                           :border => Axlsx::STYLE_THIN_BORDER)
  percent = styles.add_style(:num_fmt => Axlsx::NUM_FMT_PERCENT 
                             :border => Axlsx::STYLE_THIN_BORDER)


  # define the style for conditional formatting - its the :dxf bit that counts!
  profitable =  styles.add_style(:fg_color=> 'FF428751',  
                                 :sz => 12, :type => :dxf, :b => true)


  wb.add_worksheet('the high notes') do  |ws|
    ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], 
                :style => title
    ws.add_row
    ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header
    ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'],  
                :style => [default, money, percent]
    ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'],  
                :style => [default, money, percent]
    ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'],  
                :style => [default, money, percent]
    ws.add_row ['Q1-2013(est)', '72000000000', '=B7/SUM(B4:B7)'],  
                :style => [default, money, percent]


    ws.merge_cells 'A1:C1'

    # Apply conditional formatting to range B4:B7 in the worksheet
    ws.add_conditional_formatting('B4:B7', { :type => :cellIs,
                                    :operator => :greaterThan,
                                    :formula => '27000000',
                                    :dxfId => profitable,
                                    :priority => 1 })
  end
end
p.serialize 'the_high_notes.xlsx'




But there is so much more! The previous example used :cellIs as the conditional format type but the specification supports 16 other types of conditional formatting:

colorScale, dataBar, iconSet, top10, uniqueValues, duplicateValues, containsText, notContainsText, beginsWith, endsWith, containsBlanks, notContainsBlanks, containsErrors, notContainsErrors, timePeriod and aboveAverage so get Creative, but keep in mind that users of older versions like 2008 on Mac will receive a 'sorry, you version does not support this!' message and render the worksheet normally for some of the more funky stuff.

Scaled Colors

The code for this example is available here
    color_scale = Axlsx::ColorScale.new

    ws.add_conditional_formatting('B4:B7', { :type => :colorScale,
                                             :operator => :greaterThan,
                                             :formula => 27000000000,
                                             :dxfId => profitable,

                                             :priority => 1,

                                             :color_scale => color_scale })

https://mail-attachment.googleusercontent.com/attachment/u/1/?ui=2&ik=5d9fadb361&view=att&th=136e8e65dab66bae&attid=0.1&disp=inline&realattid=f_h1g79pa32&safe=1&zw&saduie=AG9B_P-Ari2IL5Fz4CmWpkFVr5Vq&sadet=1335348527331&sads=bi72OG22-tJDOjJC_WcJfzA9LCY

Stop and Go

The code for this example is available here
     icon_set = Axlsx::IconSet.new

    ws.add_conditional_formatting('B4:B7', { :type => :iconSet,

                                             :dxfId => profitable,

                                             :priority => 1,

                                             :icon_set => icon_set })




Getting Barred

The code for this example is available here
    data_bar = Axlsx::DataBar.new

    ws.add_conditional_formatting('B4:B7', { :type => :dataBar,

                                             :dxfId => profitable,

                                             :priority => 1,

                                             :icon_set => data_bar })



Triple unicorn rainbow <3 <3 <3 to @scpike for adding this in.