Axlsx

Excel on Rails Like a Pro with axlsx_rails

2012-08-02T19:14:00.001+09:00

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

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

2012-04-25T20:50:00.000+09:00

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 })

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.

A Deeper Look: Excel reporting in Ruby with Axlsx

2012-04-13T09:07:00.000+09:00

It has taken me far longer to get around to it than I wanted, but here it is - a more realistic example of how to take advantage of some of the features in the axlsx library. The example we are going to look at in this post is an attempt to recreate the skydrive template shown below.

That's the goal, now lets see how we close we can come.

Step one: Create the basic data

If you have followed any of the other posts on this blog, you already know this bit. We need to make a package, and begin a worksheet block for adding rows to the worksheet. The key at this point is to leave styling, formatting and layout aside and get the basic sheet up and working first. Unlike data driven sheets, we are working against a static content set. However the core concepts are the same.

require 'axlsx'

Axlsx::Package.new do | p |
p.workbook do | wb |
    wb.add_worksheet do | sheet |
sheet.add_row
sheet.add_row [nil, "College Budget"]
sheet.add_row
sheet.add_row [nil, "What's coming in this month.", nil, nil, "How am I doing"]
sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"]
sheet.add_row [nil, "Estimated monthly net income", 500, nil, "Monthly income", "=C9"]
sheet.add_row [nil, "Financial aid", 100, nil, "Monthly expenses", "=C27"]
sheet.add_row [nil, "Allowance from mom & dad", 20000, nil, "Semester expenses", "=F19"]
sheet.add_row [nil, "Total", "=SUM(C6:C8)", nil, "Difference", "=F6 - SUM(F7:F8)"]
sheet.add_row

sheet.add_row [nil, "What's going out this month.", nil, nil, "Semester Costs"]
sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"]
sheet.add_row [nil, "Rent", 650, nil, "Tuition", 200]
sheet.add_row [nil, "Utilities", 120, nil, "Lab fees", 50]
sheet.add_row [nil, "Cell phone", 100, nil, "Other fees", 10]
sheet.add_row [nil, "Groceries", 75, nil, "Books", 150]
sheet.add_row [nil, "Auto expenses", 0, nil, "Deposits", 0]
sheet.add_row [nil, "Student loans", 0, nil, "Transportation", 30]
sheet.add_row [nil, "Other loans", 350, nil, "Total", "=SUM(F13:F18)"]
sheet.add_row [nil, "Credit cards", 450]
sheet.add_row [nil, "Insurance", 0]
sheet.add_row [nil, "Laundry", 10]
sheet.add_row [nil, "Haircuts", 0]
sheet.add_row [nil, "Medical expenses", 0]
sheet.add_row [nil, "Entertainment", 500]
sheet.add_row [nil, "Miscellaneous", 0]
sheet.add_row [nil, "Total", "=SUM(C13:C26)"]

      sheet.add_chart(Axlsx::Pie3DChart) do | chart |
chart.title = sheet["B11"]
      chart.add_series :data => sheet["C13:C26"], :labels => sheet["B13:B26"]
      chart.start_at 7, 2
    chart.end_at 12, 15
end
sheet.add_chart(Axlsx::Bar3DChart, :barDir => :col) do | chart |
chart.title = sheet["E11"]
chart.add_series :labels => sheet["E13:E18"], :data => sheet["F13:F18"]
chart.start_at 7, 16
chart.end_at 12, 31
end
    end
end
p.serialize 'axlsx.xlsx'
end

Some things worth noting in that code are:
1. Many of the object creation methods in axlsx will yield themselves up if you pass a block. Take advantage of that to help keep your code clean and readable.

2. If you need some data calculated based on the values in your sheet it is better to add a string formula to excel than to do your calculations in Ruby. for example "=SUM(C13:C26)" as it makes the worksheet self updating, and saves you the trouble of maintaining the calculation.

3. When you are dealing with a fixed layout, like this report - use Excel style column/row references like "A1" and "C13:C26" - Axlsx will work out what actual cells that refers to.

4. Charts are good. Charts make people happy. Charts are what make your sales people, and your clients like you.

Run that code and we get something like this:

Not a bad start - but we need to get some style in there and fix the empty column widths to something a bit slimmer.

Step two: Style and Formatting

Excel styling can be a bit difficult to handle at first as it is a bit different from anything you may have done with CSS. The first thing to understand is that each cell stores a reference to one, and only one style definition in the master style records. This means that you cannot apply two styles and expect them to merge/inherit happily. Assigning a new style to an existing cell will override any previous settings so thinking about all the possible style combinations before writing the code is productive.

For this exercise, we add in styles in the sheet block as follows:

 styles = wb.styles

header = styles.add_style :bg_color => "DD", :sz => 16, :b => true, :alignment => {:horizontal => :center}
tbl_header = styles.add_style :b => true, :alignment => {:horizontal => :center}
ind_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => {:indent => 1}
col_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => {:horizontal => :center}
label = styles.add_style :alignment => { :indent => 1 }
money = styles.add_style :num_fmt => 5
t_label = styles.add_style :b => true, :bg_color => "FFDFDEDF"
t_money = styles.add_style :b => true, :num_fmt => 5, :bg_color => "FFDFDEDF"

That code defines every style we will use in our workbook. I think most of it is self explanatory with the exception of 'num_fmt => 5'. Excel defines a number of formats that can be directly accessed by index, so you don't have to write out the format code manually. As always, a reference is listed in the docs

But we are not done there, we still need to apply these styles to our rows. 'add_row' accepts an options hash that can contain style and type information. For this exercise let's focus on :style.

When specifying the style option, it can be either an integer, or a an array of integer or nil values.

sheet.add_row [1,2,3], :style = 1

Would apply the same style to each of the three cells

sheet.add_row [1,2,3], :style = [1]

Would apply the style only to the first cell.

sheet.add_row [1,2,3], :style = [nil, 1]

Would apply the style only to the second cell in the row.

Have a look at the gist to see how each individual row is styled.

The last thing we need to do is merge some of the titles and column headers and make the empty columns a bit more narrow.

sheet.merged_cells.concat ["B4:C4","E4:F4","B11:C11","E11:F11","B2:F4"]

sheet.column_widths 2, nil, nil, 2, nil, nil, 2

Axlsx does it's best to automatically determine the proper width for you columns, but often you will want to make adjustments with the column_widths method. Each parameter is applied in order to the columns in the worksheet. Nil values are ignored (and use the default auto_width behavior).

Our end result looks like this:

Not bad for around 60 lines of code.

If you are looking for something to hack with, the source code for this exercise is available here.
If you are looking to get involved and implement part of the ECMA-376 spec give me shout on github (randym) or grab me on #axlsx on freenode.

Happy reporting.

Excel file generation with Ruby, JRuby and Rubinius, multiple charts, styles and print options.

2012-04-05T23:34:00.001+09:00

Over the last three or four months, amazing things have happened and I have simply been scrambling to keep up. The axlsx and acts_as_xlsx gems have gone far beyond my personal expectations and I need to express my gratitude to the OSS community that has really come to rally around new features, better performance and helping this gem set to stand up and knock every other excel generating gem off the charts.

here is the proof:
https://www.ruby-toolbox.com/categories/reporting

We are going to surpass the download count it took odf-reports to do in more than two years in about five months. I am simply awed. On top of that, we just released support for JRuby and Rubinius.

What I should be doing here is giving everyone lots of examples of how to take advantage of all the amazing features that have been build into axlsx over the last few months like support for named tables, simplified border styling, splatter charts, page margin and print fitting as well as a 7x+ increase in performance.

Unfortunately this also happens to be the beginning of the peak time in my day job as Ops manager at reallyenglish. That means that on a good day I get one or two hours to keep up with github, the great guys at travis-ci and feel guilty about not actually giving examples of the better ways to use axlsx!

So I am going to give some hints!

Serialization:

The gem no longer requires you to write to a temp file thanks to the great guys writing rubyzip.
Use to_stream when you want to serialize your package! But always remember that the download will block! In a poor hardware/high volume situation you always want to move those files off to a separate server!

Stylin'

It's all about workbook.add_style
If you are looking to make a professional report, give it some thought in advance. What styles do you need? It is a lot like CSS. You don't want to be hard coding the style for every element in your web page, and the same applies for axlsx. my_style_for_headers = wb.add_style [insert style hash here] run once will serve you for all worksheets in your workbook.

Charts
Use them, and your clients will love you! dump csv - and your clients will :meh
joekain stepped up out of the blue and brought us splatter plots! I promise you, as a guy who deals directly with many of the top companies in Japan, your clients will love you for it. We eat our own dog food and have had huge amounts of praise for this.

But I think the most important thing of all, even though it is hard to give credit enough, is to express that the list of contributors you will find on github and in the README (which you really should read!) is the first and foremost reason why this gem is rapidly becoming the BEST way to generate excel based reports. It simply could not be done without these guys. To them, I tip my hat and bow at the waist.

so.. THANK YOU <3 <3 <3

Axlsx Making Excel Reports with Ruby on Rails Part 2

2011-12-08T14:05:00.001+09:00

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

specify what columns you want included in your report
Use the I18n library to specify labels for your columns
Specify styling and formatting for your worksheet
Add a chart to your worksheet.
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.

Using acts_as_xlsx to generate excel data in your rails application

2011-12-05T23:24:00.000+09:00

Getting Started With Acts as Xlsx Part 1

This tutorial gives a basic overview of how to integrate acts_as_xlsx into your Rails 3 application to provide excel downloads of your models. The second part of the series covers style, chart generation and multiple sheets.

After reading this you should be familiar with:

Installing acts_as_xlsx in your rails project
The basics creating excel reports from active record objects
The basics of streaming excel reports
Implementing web-service support with responds_to

This guide is follows on top of the Getting Started with Rails guide for Rails 3.1 and uses the models and controllers created in that tutorial. The plugin supports Ruby versions 1.8.7, 1.9.2 adn 1.9.3 as well as Rails 2.3 and up however the plugin management, routes and web-service syntax is slightly different in the two rails versions.

1 Guide Assumptions

This guide is designed for intermediate users and assumes a basic knowledge of Ruby on Rails programming, using bundler to manage your gems and a very rudimentary understanding of spreadsheets in general. In addition to the rails setup described in the Getting Started with Rails guide, you also need to add the following to your application Gemfile

gem 'acts_as_xlsx'

And install it from the command line as follows:

$ bundle install

2 What is Acts as Xlsx

Ever have a client ask if they could get that data in Excel? That's what Acts as xlsx does. It is an Active Record plugin that lets you convert any Active Record based model or finder method result into a fully valid xlsx, also knows as Office Open XML spreadsheet. It wraps the axslx gem and integrates it into Active Record.

In fact, using the plugin is ridiculously easy so in this guide we are also going to show you how to stream the files, and set up web-service support so that when a request says it wants xlsx - it gets xlsx.

3 Updating the Models

Adding acts_as_xlsx to your ActiveRecord::Base inheriting models will add a class method to the model called to_xlsx.

Open up the app/models/post.rb file and add acts_as_xslx just below the class declaration.

class Post < ActiveRecord::Base

acts_as_xlsx

Open up the app/models/comment.rb file and add acts_as_xslx just below the class declaration.

class Comment < ActiveRecord::Base

acts_as_xlsx

4 Update the Posts Controller

Now lets add the xlsx format to respond_to for web-service support.

def index

@posts = Post.all

respond_to do | format |
format.html # index.html.erb
format.json { render :json => @posts }
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.openxmlformats-officedocument.spreadsheetml.sheet"
    ensure
temp.close
      temp.unlink
    end

}

end

There is quite a bit going on in that code, and we will be adding more as we move forward. The important things to understand in terms of acts_as_xlsx are that you will need to serialize the package to a file resource, and then return it with send_file.

EDIT: This has come up a few times so I will add it to the original post here.

Use the following to stream the file without writing it to disk on the server:

send_data xlsx_package.to_stream.read, :filename => 'posts.xlsx', :type=> "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

5 Update the View

While you can directly access localhost:3000/posts.xlsx, lets add in a url_for based link for requesting the download. Add the following to the bottom of app/views/posts/index.html.erb

<%= link_to 'Download', url_for(:format=>"xlsx") %>

Click that link at http://0.0.0.0:3000/posts and here is what you get:

In the next post, we will take a look at localization of field names, using finder methods to restrict the data and customizing the columns reported.

For now, here are a couple of hints:

acts_as_xlsx :columns => [:created_at, :name, :title, :content, :'comments.last.content']
Post.to_xlsx :columns => [:created_at, :name, :title], :i18n => 'activerecord.attributes'

Excel With Ruby and Ruby on Rails

2011-12-03T10:33:00.000+09:00

It happened, didn't it? Some client asked you if they could get all that great data you are generating in an excel spreadsheet because they need to share it with their colleagues, or perform further analysis.
You probably went the easy route first - csv - and they complained. The dates are wrong, the leading '0's are missing, the percentage data is a string. You should not be too surprised. Comma-separated values are old technology and pre-date personal computers by more than a decade.

There are already few gems out there for generating excel data that let you modify an excel workbook but they all seem to missing a few key components. We need multiple sheets, formatting, styling, automated column widths, charts in excel, validation, and support for non-ascii text.

And now we have it.

axlsx and its rails plugin acts_as_xlsx

For those of you who actually look at the gems you are installing, start with the README's
http://github.com/randym/axlsx
http://github.com/randym/acts_as_xlsx

Every method covered in specs, every method with docs and always green on travis.ci.
well, usually green....