A client wanted to export some tabular data from the database to an editable format, so xls was an obvious choice. Before I mastered the export of the same piece of data into pdf format using Prawn gem, now I want to present you an easy way to achieve fairly the same effect, but in Excel output.
Let’s assume we have a list of clients in an array clients:
Name | Surname | Age |
John | Smiths | 23 |
Ann | Gooday | 97 |
…. |
In order to export data to Excel, we will use the Ruby spreadsheet gem (project’s page: http://spreadsheet.ch/ and at Rubygem: http://rubygems.org/gems/spreadsheet). The gem provides a flexible way to manipulate, import and export xls data.
- First, we need to register new mimetype – xls.
Let’s create config/initializers/mime_types.rb and paste:Mime::Type.register 'application/vnd.ms-excel', :xls
- Next, we have to extend the selected controller’s action to respond to xls
def index @clients = Client.all require 'spreadsheet' respond_to do |format| format.pdf format.xls { clients = Spreadsheet::Workbook.new list = clients.create_worksheet :name => 'List of cliets' list.row(0).concat %w{Name Surname Age} @clients.each_with_index { |client, i| list.row(i+1).push client.name,client.surname,client.age } header_format = Spreadsheet::Format.new :color => :green, :weight => :bold list.row(0).default_format = header_format #output to blob object blob = StringIO.new("") clients.write blob #respond with blob object as a file send_data blob.string, :type => :xls, :filename => "client_list.xls" } end
- In the end, we can create a link in the view: link_to clients_path(:format => :xls)
- Click the link and enjoy the xls being downloaded. Remember you can style and/or add additional worksheets to your Excel workbook – refer to spreadsheet manual if needed.