samedi 25 avril 2015

How can I change my .xls.erb file to create a new sheet for each row?


I am following the RailsCast tutorial for Exporting CSV and Excel. My goal is to export to excel. Everything is working fine except that I would like each Product to have its on sheet. Below is the code per the tutorial and then my attempts at adjusting for my needs.

/config/application.rb

require File.expand_path('../boot', __FILE__)

require 'csv'
require 'rails/all'

/config/initializers/mime_types.rb

Mime::Type.register "application/xls", :xls

/app/models/product.rb

class Product < ActiveRecord::Base
  attr_accessible :name, :price, :released_on

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

app/controllers/products.rb

class ProductsController < ApplicationController
  def index
    @products = Product.order(:name)
    respond_to do |format|
      format.html
      format.csv { send_data @products.to_csv }
      format.xls
    end
  end
end

/app/views/products/index.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://ift.tt/qQdaDR">
  <Worksheet ss:Name="Sheet1">
    <Table>
      <Row>
        <Cell><Data ss:Type="String">ID</Data></Cell>
        <Cell><Data ss:Type="String">Name</Data></Cell>
        <Cell><Data ss:Type="String">Release Date</Data></Cell>
        <Cell><Data ss:Type="String">Price</Data></Cell>
      </Row>
    <% @products.each do |product| %>
      <Row>
        <Cell><Data ss:Type="Number"><%= product.id %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.name %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.released_on %></Data></Cell>
        <Cell><Data ss:Type="Number"><%= product.price %></Data></Cell>
      </Row>
    <% end %>
    </Table>
  </Worksheet>
</Workbook>

All of that works to create an excel sheet with a column of Products and other columns for the attributes. As an attempt to create individual worksheets for each Product, I iterated through Products above the element in my .xls.erb. This led to the excel file being unreadable though.

/app/views/products/index.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://ift.tt/qQdaDR">
 <% @products.each do |product| %>
  <Worksheet ss:Name="Sheet1">
    <Table>
      <Row>
        <Cell><Data ss:Type="String">ID</Data></Cell>
        <Cell><Data ss:Type="String">Name</Data></Cell>
        <Cell><Data ss:Type="String">Release Date</Data></Cell>
        <Cell><Data ss:Type="String">Price</Data></Cell>
      </Row>
      <Row>
        <Cell><Data ss:Type="Number"><%= product.id %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.name %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.released_on %></Data></Cell>
        <Cell><Data ss:Type="Number"><%= product.price %></Data></Cell>
      </Row>
    </Table>
  </Worksheet>
 <% end %>
</Workbook>

How can I change my .xls.erb file to create a new sheet for each Product row?


Aucun commentaire:

Enregistrer un commentaire