Rails exported xls file opens different on excel and libre office

Question

My application knows how to export xls file, but exported file show all information only when it is opened with LibreOffice, Microsoft Excel shows only one part of whole file.

My Tour class:

class Tour < ActiveRecord::Base
  belongs_to :tournament
  has_and_belongs_to_many :pilots, :join_table => :rounds


  def self.to_csv(options = {})
    CSV.generate(options) do |csv|
      csv << column_names
      all.each do |tour|
        csv << tour.attributes.values_at(*column_names)
      end
    end
  end
end

Tournaments controller:

  def show
    @tournament = Tournament.includes(:pilots => :country).find(params[:id])
    @pilots = @tournament.pilots
    @tours = @tournament.tours.includes(:pilots => :country)

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

Link in show view:

= link_to "Download xls", admin_tournament_path(format: "xls"), :class => "btn"

And my show.xls.erb:

<?xml version="1.0"?>
<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:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40">
  <Worksheet ss:Name="Sheet1">
    <% @tours.each_with_index do |tour, index| %>
      <Table>
        <Row></Row>
        <Row>
          <Cell><Data ss:Type="String"><%= "Tour #{index + 1}" %></Data></Cell>
        </Row>
        <Row>
          <Cell><Data ss:Type="String">#</Data></Cell>
          <Cell><Data ss:Type="String">Pilot Name</Data></Cell>
          <Cell><Data ss:Type="String">Pilot Country</Data></Cell>
        </Row>
      <% tour.pilots.each_with_index do |pilot, index| %>
        <Row>
          <Cell><Data ss:Type="Number"><%= index + 1 %></Data></Cell>
          <Cell><Data ss:Type="String"><%= pilot.name %></Data></Cell>
          <Cell><Data ss:Type="String"><%= pilot.country.name %></Data></Cell>
        </Row>
      <% end %>
      </Table>
    <% end %>
  </Worksheet>
</Workbook>

When I open downloaded file with Microsoft excel, it shows only the first round, LibreOffice show all existing 13 rounds..

Answer 1

Actually I am pretty surprised that LibreOffice can display it, cause according to the Worksheet Type xsd, it says Only one instance of a Table element is valid for a single worksheet.:

  .....
  <xsd:element name="Table" type="TableType" minOccurs="0">
        <xsd:annotation>
            <xsd:documentation>Defines the table to contain the cells of the current worksheet. Only one instance of a Table element is valid for a single worksheet.</xsd:documentation>
        </xsd:annotation>
    </xsd:element>
    .....

So I recommend you either just print rows within one table element like this:

 <Table>
 <% @tours.each_with_index do |tour, index| %>      
    <Row></Row>
    <Row>
      <Cell><Data ss:Type="String"><%= "Tour #{index + 1}" %></Data></Cell>
    </Row>
    <Row>
      <Cell><Data ss:Type="String">#</Data></Cell>
      <Cell><Data ss:Type="String">Pilot Name</Data></Cell>
      <Cell><Data ss:Type="String">Pilot Country</Data></Cell>
    </Row>
  <% tour.pilots.each_with_index do |pilot, index| %>
    <Row>
      <Cell><Data ss:Type="Number"><%= index + 1 %></Data></Cell>
      <Cell><Data ss:Type="String"><%= pilot.name %></Data></Cell>
      <Cell><Data ss:Type="String"><%= pilot.country.name %></Data></Cell>
    </Row>
  <% end %> 
<% end %>
</Table>

Or you can put them into different worksheets like this:

<% @tours.each_with_index do |tour, index| %>
  <Worksheet ss:Name="<%= "Tour #{index + 1}-Sheet" %>">
  <Table>
    <Row></Row>
    <Row>
      <Cell><Data ss:Type="String"><%= "Tour #{index + 1}" %></Data></Cell>
    </Row>
    <Row>
      <Cell><Data ss:Type="String">#</Data></Cell>
      <Cell><Data ss:Type="String">Pilot Name</Data></Cell>
      <Cell><Data ss:Type="String">Pilot Country</Data></Cell>
    </Row>
  <% tour.pilots.each_with_index do |pilot, index| %>
    <Row>
      <Cell><Data ss:Type="Number"><%= index + 1 %></Data></Cell>
      <Cell><Data ss:Type="String"><%= pilot.name %></Data></Cell>
      <Cell><Data ss:Type="String"><%= pilot.country.name %></Data></Cell>
    </Row>
  <% end %>
  </Table>
  </Worksheet>
<% end %>

Hope it helps, thanks.