Title: How can I change my .xls.erb file to create a new sheet for each row?
Tags: ruby-on-rails;excel;csv;ruby-on-rails-4;xls
Question: 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
Mim75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0Type.register "application/xls", :xls
/app/models/product.rb
class Product < ActiveRecor75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0Base
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
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
/app/views/products/index.xls.erb
<?xml version="1.0"?&gt;
<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"&gt;
<Worksheet ss:Name="Sheet1"&gt;
<Table&gt;
<Row&gt;
<Cell&gt;<Data ss:Type="String"&gt;ID</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;Name</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;Release Date</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;Price</Data&gt;</Cell&gt;
</Row&gt;
<% @products.each do |product| %&gt;
<Row&gt;
<Cell&gt;<Data ss:Type="Number"&gt;<%= product.id %&gt;</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;<%= product.name %&gt;</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;<%= product.released_on %&gt;</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="Number"&gt;<%= product.price %&gt;</Data&gt;</Cell&gt;
</Row&gt;
<% end %&gt;
</Table&gt;
</Worksheet&gt;
</Workbook&gt;
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"?&gt;
<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"&gt;
<% @products.each do |product| %&gt;
<Worksheet ss:Name="Sheet1"&gt;
<Table&gt;
<Row&gt;
<Cell&gt;<Data ss:Type="String"&gt;ID</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;Name</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;Release Date</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;Price</Data&gt;</Cell&gt;
</Row&gt;
<Row&gt;
<Cell&gt;<Data ss:Type="Number"&gt;<%= product.id %&gt;</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;<%= product.name %&gt;</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="String"&gt;<%= product.released_on %&gt;</Data&gt;</Cell&gt;
<Cell&gt;<Data ss:Type="Number"&gt;<%= product.price %&gt;</Data&gt;</Cell&gt;
</Row&gt;
</Table&gt;
</Worksheet&gt;
<% end %&gt;
</Workbook&gt;
How can I change my .xls.erb file to create a new sheet for each Product row?
Comment: Thank you.  I needed to use   and then  "> @AxelRichter.  Please write as an answer and I will mark as correct.  Thank you for your help!

Comment: `` Since this XML line is constant, all worksheets will have the name "Sheet1". This is not possible and leads to the error. You have to get `ss:Name="Sheet1"` variable somehow.

Comment: Maybe `">`

Comment: I have no clue about ruby. So I can't really answer this question properly. My hint was only about the used SpreadsheetML. Maybe it was the main hint. But you could answer your own question now and explain why you had used `.each_with_index do...` and how this works. This I could only guess.



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 0, 'CommentCount': 4, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2015:00:04/25/15', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<ruby-on-rails><excel><csv><ruby-on-rails-4><xls>', 'Title': 'How can I change my .xls.erb file to create a new sheet for each row?', 'ViewCount': 1366, 'id': 29866493, 'user_AboutMe': '', 'user_AccountId': 3386930.0, 'user_DisplayName': 'MicFin', 'user_Reputation': 2399.0, 'user_Views': 275.0, 'user_id': 2842709.0}}