Excel and Ruby compatibility

Question

I followed this Railscasts link for CSV export from Railsto export my html table (dynamically generated) to xls and it downloads my excel document when I do .xls

However, when I open it in iWork Numbers, I get the following error:

Import Warning - This is a tab delimited document, not a valid Excel document. The data might look different.

I see my source code of the table in the document.

Here is the code I use to generate the table:

<table border ="1">
        <tr>enter code here
    <th><%= get_column_name "#{@level1}"%></th>
    <th><%= get_column_name "#{@level2}"%></th>
    <th><%= get_column_name "#{@level3}"%></th>
    <th>abcd</th>
    </tr>
        <% @grp.each do |key, value| %>
            <% value.each do |k2, v2|%>
                <% v2.each do |k3, v3|%>
                        <tr>
                            <td><% if "#{@level1}" == "abcd"  && Person.exists?(key.to_i) %><%= "#{Person.find(key.to_i).get_name}" %><% else %><%= "#{key}" %><% end %></td>
                            <td><% if "#{@level2}" == "abcd"  && Person.exists?(k2.to_i) %><%= "#{Person.find(k2.to_i).get_name}" %><% else %><%= "#{k2}" %><% end %></td>
                            <td><% if "#{@level3}" == "abcd"  && Person.exists?(k3.to_i) %><%= "#{Person.find(k3.to_i).get_name}" %><% else %><%= "#{k3}" %><% end %></td>
                            <td><%= "#{v3.count}" if !v3.nil? %></td>
                        </tr>
                <%end%>
            <%end%>
        <%end%>
</table>

Can somebody please tell me if it's the problem with Numbers or something else. I am a student and can't really afford to buy excel just to check this.

Best,

I think it's probably just a Numbers issue. However, you should consider making a CSV or TSV instead of relying on Excel's HTML->spreadsheet function, so you can actually view your spreadsheet (and others who don't use Excel). It's very easy to do too -- `<%= row.join("\t") %>` is all you need for a valid TSV row. — jmdeldin, Oct 11 '12 at 09:14
You could take a look at existing gems for writing and parsing Excel .xls data. Here's a [short list](http://www.knight.io/categories/spreadsheets-ruby). — Thomas Klemm, Oct 11 '12 at 09:17
@jmdeldin Could you please elaborate on that? I am new to rails as well. Thanks! — anipendakur, Oct 12 '12 at 00:50
@jmdeldin , You were right. It was the problem with Numbers only. I checked it on my friend's computer and it works perfectly fine in Excel! — anipendakur, Oct 12 '12 at 22:43
@anipendakur: Sorry for the delay. I posted a response to clarify CSVs and rendering them. Hope it helps! — jmdeldin, Oct 16 '12 at 05:25

score 1 · Accepted Answer · answered Oct 16 '12 at 05:24

A more portable approach is to just generate CSV or TSV (tab-delimited) files. This is definitely a good idea if you don't have Excel and if you don't need to deal with the horrors of XLS[X].

The Railscast you linked to gave one solution using the CSV library. It handles quoting fields for you, which is really nice as we'll see in a bit. I'll outline the manual approach as well:

Let's assume you have this action in your controller:

# widget_controller.rb
def index
  @widgets = Widget.ordered_by_name
  respond_to do |format|
    format.csv
  end
end

And then something like this in your template will work:

<%# index.csv.erb %>
ID,Name
<% @widgets.each do |w| %>
<%= w.id %>,<%= w.name %>
<% end %>

Or more succinctly:

<%# index.csv.erb %>
<%= %w(ID Name).join(",") %>
<%= @widgets.map { |w| [w.id, w.name].join(",") }.join("\n") %>

You will need to take care around quoting. For instance, if widgets[0].name returned foo,bar, your data would look like:

ID,Name
1,foo,bar
2,baz

The solution is to just quote the field, or if you don't want to think about this...use CSV.generate :).

thank you for the reply. But, I have a dynamically generated hash every time the page is loaded/refreshed. I got the picture however. I have to render a view in CSV, pass the hash as a parameter; finally quote the fields before joining. I will try this and see if this works in iWork Numbers. Thank you :) — anipendakur, Oct 17 '12 at 06:30

Excel and Ruby compatibility

1 Answers1