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_xlsxThen in a single elegant line you can create standard spreadsheets from your models:
Posts.where(created_at > Time.now-30.days).to_xlsx
# 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
# 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|
…
endAnd in your controller, you merely need:
format: xlsxOr, 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|
…
endWhat 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']
endWhich 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']
endThere 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
