Export to Excel in Rails 2
June 07, 2008
h2. 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:
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:
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
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:
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:
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!