How to have proper borders for excel worksheet cells, excel export in Ruby on Rails

Question

In my index action of the controller I have got this:

def index
    @vehicles = Vehicle.all
    respond_to do |format|
    format.html
    format.xls
    end
end

I have got this in my index.xls.erb template:
 <table>
      <tr>
        <th>Manufacturer</th>
        <th>Model</th>
        <th>Version</th>
        <th>CC</th>
        <th>BHP</th>
      </tr>
    <% @vehicles.each do |vehicle| %>
      <tr>
        <td><%= vehicle.manufacturer.name %></td>
        <td><%= vehicle.model %></td>
        <td><%= vehicle.version %></td>
        <td><%= vehicle.engine_cc %></td>
        <td><%= vehicle.power_bhp %></td>
       <% end %>
  </table>

 and I have registered the mime_type for xls like this:
 Mime::Type.register 'application/vnd.ms-excel', :xls

But this does not have usual cell borders that MS Excel shows. tried what was suggested in Railscasts i.e., http://railscasts.com/episodes/362-exporting-csv-and-excel. but that leads basic input / output error while trying to open the file (may be file is malformed in that case). any help is appreciated

Answer 1

If you are looking to create valid excel data from rails, please have a look at axlsx. In the spirit of full disclosure, I am the author of the gem, but it really is the best thing out there for making excel (xlsx) documents.

You can find out more about the gem here: https://github.com/randym/axlsx

And there are tutorials for using it in rails here: http://axlsx.blog.randym.net/

There are also some secondary gems like acts_as_xlsx and axlsx_rails that make model exports and report generation very easy.

If you have any questions, you can also usually find me (JST) in

axlsx on freenode.

With axlsx, you would do something like this:

require 'axlsx'
p = Axlsx::Package.new
p.workbook.add_worksheet(:name => 'vehicles') do |sheet|
  sheet.add_row %w(Manufacturer Model Version CC BHP), :style => Axlsx::STYLE_THIN_BORDER
  @vehicles.each do |vehicle|
    data = [vehicle.manufacturer.name, vehicle.model, 
            vehicle.version, vehicle.engine_cc,
            vehicle.power_bhp]
    sheet.add_row data, :style => Axlsx::STYLE_THIN_BORDER
  end
end
p.serialize('vehicles.xlsx')

There is a lot more you can do as well, like custom styling, charts, hyperlinks, images etc. Please give it a go if you think it fits your development style.