← Back to home

Export to Excel in Rails 2

June 8th, 2008

UPDATE: I released a Rails plugin that makes this much easier, http://arydjmal.com/2009/1/11/to_xls-plugin-export-to-excel-in-rails-the-easy-way.

Rails makes it super-easy to export anything to Excel. I took this example from a client’s project; they wanted to export to excel the data generated from orders/index.

I will summarize this in 3 simple steps:

First, you have to register the mime type in your config/initializers/mime_types.rb. This is needed for the respond_to block in the controller, and of course you have to do it only once.



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

Restart the server so the new configuration is loaded.

Now we are ready to respond in xls format; in this example http://localhost:3000/orders will render index.html.erb and http://localhost:3000/orders.xls will create a xls. So in the app/controllers/orders_controllers.rb we can have something like this:

1
2
3
4
5
6
7
8

def index
  @orders = Order.recent

  respond_to do |format|
    format.html
    format.xls
  end
end

Pretty clean, right? Finally, we need to create the excel file, and since Excel can read xml in the app/views/orders/index.xls.builder we have something like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

xml.instruct! :xml, :version=>"1.0", :encoding=>"UTF-8" 
xml.Workbook({
  'xmlns'      => "urn:schemas-microsoft-com:office:spreadsheet", 
  'xmlns:o'    => "urn:schemas-microsoft-com:office:office",
  'xmlns:x'    => "urn:schemas-microsoft-com:office:excel",    
  'xmlns:html' => "http://www.w3.org/TR/REC-html40",
  'xmlns:ss'   => "urn:schemas-microsoft-com:office:spreadsheet" 
  }) do

  xml.Worksheet 'ss:Name' => 'Recent Orders' do
    xml.Table do

      # Header
      xml.Row do
          xml.Cell { xml.Data 'ID', 'ss:Type' => 'String' }
          xml.Cell { xml.Data 'Date', 'ss:Type' => 'String' }
          xml.Cell { xml.Data 'Description', 'ss:Type' => 'String' }
      end

      # Rows
      for order in @orders
        xml.Row do
            xml.Cell { xml.Data order.id, 'ss:Type' => 'Number' }
            xml.Cell { xml.Data order.date, 'ss:Type' => 'String' }
            xml.Cell { xml.Data order.description, 'ss:Type' => 'String' }
        end
      end

    end
  end
end

With this 3 easy steps you can give a export to xls any view.

So my export to excel link for the view would be:



link_to 'Export to Excel', formatted_orders_url(:xls)

Now, the bigger reason I wrote this mini tutorial, was that this set-up gave me some trouble on IE. It was always rendering format.xls so my little-ugly-hack was to make sure that params[:format] is xls

1
2
3
4
5
6
7
8

def index
  @orders = Order.recent

  respond_to do |format|
    format.html
    format.xls if params[:format] == 'xls'
  end
end

Please, let me know if you have a better way to solve this problem.

Now, if you plan to have more than one xls view, I suggest to create this helper in app/helpers/application_helper.rb:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

def excel_document(xml, &block)

  xml.instruct! :xml, :version=>"1.0", :encoding=>"UTF-8" 
  xml.Workbook({
    'xmlns'      => "urn:schemas-microsoft-com:office:spreadsheet", 
    'xmlns:o'    => "urn:schemas-microsoft-com:office:office",
    'xmlns:x'    => "urn:schemas-microsoft-com:office:excel",    
    'xmlns:html' => "http://www.w3.org/TR/REC-html40",
    'xmlns:ss'   => "urn:schemas-microsoft-com:office:spreadsheet" 
  }) do

    xml.Styles do
      xml.Style 'ss:ID' => 'Default', 'ss:Name' => 'Normal' do
        xml.Alignment 'ss:Vertical' => 'Bottom'
        xml.Borders
        xml.Font 'ss:FontName' => 'Arial'
        xml.Interior
        xml.NumberFormat
        xml.Protection
      end
    end

    yield block

  end
end

So now the view would be:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

excel_document(xml) do
  xml.Worksheet 'ss:Name' => 'Recent Orders' do
    xml.Table do

      # Header
      xml.Row do
          xml.Cell { xml.Data 'ID', 'ss:Type' => 'String' }
          xml.Cell { xml.Data 'Date', 'ss:Type' => 'String' }
          xml.Cell { xml.Data 'Description', 'ss:Type' => 'String' }
      end

      # Rows
      for order in @orders
        xml.Row do
            xml.Cell { xml.Data order.id, 'ss:Type' => 'Number' }
            xml.Cell { xml.Data order.date, 'ss:Type' => 'String' }
            xml.Cell { xml.Data order.description, 'ss:Type' => 'String' }
        end
      end

    end
  end
end

NOTE: This works with Office 2003+ for windows and Office 2004+ for Mac, it will not work with Office 2000, Numbers or QuickLook. So if this is a problem you could try another solution.

UPDATE: Check out my to_csv plugin for better excel compatibility!

← Back to home