Coding is like gardening...

Exporting from Rails to Excel

There are various methods for exporting data from Rails to an Excel spreadsheet, some more complicated than others.

One easy way we’ve used a few times is to make use of Excel’s ability to interpret HTML tables as spreadsheet rows and columns. For example, i can set up a helper method to turn a collection of tasks into a table in a simple HTML page:

module TasksHelper
  include ActiveSupport::Inflector

  def generate_xls(tasks)
    output_columns = [:title, :due_date]

    returning String.new do |str|
      str << "
"
      str << "
"
      output_columns.each do |col|
        str << "


"
      end
      str << "

"

      tasks.each do |task|
        str << "
"
          output_columns.each do |col|
            str << "


"
          end
        str << "

"
      end
      str << "
#{humanize(col)}
#{task.send(col)}

"
    end
  end

end

The environment.rb file is going to have to know about the .xls extension:

Mime::Type.register "application/vnd.ms-excel", :xls

The TasksController can now be taught to respond to .xls and send_data as a file for download:

class TasksController < ApplicationController
  include TasksHelper

  def index
    @tasks = Task.find(:all)

    respond_to do |format|
      format.html # index.html.erb
      format.xls {
        send_data(generate_xls(@tasks),
          :filename => 'all_tasks.xls',
          :type => 'application/vnd.ms-excel')
      }
    end
  end

end

When visiting /tasks.xls we will now be given the option to download all_tasks.xls which Excel will parse quite happily as a spreadsheet.

One important consideration: Excel is not overly fond of Unicode, and Rails by default will send data in UTF-8 format. If the data contains Unicode characters such as curly quotation marks, you will get some weird output like “ in the spreadsheet.

Not to worry! We can use Ruby’s implementation of the iconv API to convert to Excel’s preferred format, ISO-8859-15.

class TasksController < ApplicationController
  include TasksHelper

  def index
    @tasks = Task.find(:all)

    respond_to do |format|
      format.html # index.html.erb
      format.xls {
        require 'iconv'
        converter = Iconv.new('ISO-8859-15//IGNORE//TRANSLIT','UTF-8')
        send_data(converter.iconv(generate_xls(@tasks)),
          :filename => 'all_tasks.xls',
          :type => 'application/vnd.ms-excel')
      }
    end
  end

end

The Unicode curly quotes are transformed into standard quotes, and Excel is happy again!

Acknowledgements and references:

How to export data as CSV
Working with UTF-8 in PDF::Writer and Ruby on Rails
Iconv Ruby class

Posted on: Tuesday, February 3rd, 2009 at 2:57 pm In category: code, how-to. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.