I am trying to put an ''export to excel'' link to dump the
entire table
to a comma delimited spreadsheet.
I get the spreadsheet flying out of the brower ok, however the
contents do not seem to escape the html.
Can anybody provide some insight on what I need to do to get a
spreadsheet out of a table dump?
ie.
caption, header, caption
row, data, data, data
row, data, data, data
The Controller:
def exportexcel
@headers["Content-Type"] = "application/vnd.ms-excel"
@servers = Servers.find_all
send_data(render_to_string(''servers/excel''), :filename
=>
''intelservers.xls'')
end
def excel
@servers = Servers.find_all
end
The View ( note the ",") :
<% for column in Servers.content_columns %>
<%= column.human_name %>,
<% end %>
<% for servers in @servers %>
<% for column in Servers.content_columns %>
<%=h servers.send(column.name) %>,
<% end %>
<% end %>
On 6/22/05, Ron Sweeney <ron.sweeney-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I am trying to put an ''export to excel'' link to dump the entire table > to a comma delimited spreadsheet. > > I get the spreadsheet flying out of the brower ok, however the > contents do not seem to escape the html. > > Can anybody provide some insight on what I need to do to get a > spreadsheet out of a table dump? >I don''t know about actually getting an excel content type and format correct, but one thing you will want to do is render_without_layout In case you have a layout for this controller. Otherwise you will get a mix of the HTML from the layout in the rest of your data. Matt
And, do some checking on each field value to text-delimit (i.e., wrap in double-quotes) fields like SSN values, etc., that aren''t obviously date or textual data and could be misinterpreted as numerical values. If you have a 12-digit integer, for example, Excel will want to automatically convert it to a floating point value (and you''ll lose data in the process). On 6/22/05, Belorion <belorion-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 6/22/05, Ron Sweeney <ron.sweeney-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > I am trying to put an ''export to excel'' link to dump the entire table > > to a comma delimited spreadsheet. > > > > I get the spreadsheet flying out of the brower ok, however the > > contents do not seem to escape the html. > > > > Can anybody provide some insight on what I need to do to get a > > spreadsheet out of a table dump? > > > > I don''t know about actually getting an excel content type and format > correct, but one thing you will want to do is > > render_without_layout > > In case you have a layout for this controller. Otherwise you will get > a mix of the HTML from the layout in the rest of your data. > > Matt > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
You need to export data to CSV format understood by all spreadsheets.
This can be best done using the ruby CSV module from the standard ruby
library, please see [1] for API manual.
In the end you should have something like:
CSV.open(''result.csv'', ''w'') do |writer|
writer << caption
writer << row1
writer << row2
end
where caption, row1, row3 are arrays. I think that quoting will be
dealt with automaticaly.
Zsombor
[1] http://www.ruby-doc.org/stdlib/libdoc/csv/rdoc/index.html
--
http://deezsombor.blogspot.com
Ron Sweeney wrote:>I am trying to put an ''export to excel'' link to dump the entire table >to a comma delimited spreadsheet. > >One useful feature when it comes to outputting to Excel is that it can import HTML tables. You can have a page with a table in and output it with an excel header and excel will open it and convert to tables. You can also format the data with inline CSS, put formulas in etc... It''s pretty useful and can be quite powerful, and also easier to deal with than converting to CSV as you can use a standard template and preview it in the browser to see what it will look like in excel. hth -- R.Livsey www.livsey.org
Hi
I export my data as "tab delimited" and it seems to work pretty fine.
controller action:
def export
@headers["Content-type"] =
"application/vnd.ms-excel"
@exps = Experiment.find_all
render_without_layout
end
view (export.rhtml):
<%for e in @exps%>
<%= e.project_name %> <%= e.annotation %><%end%>
Note:
* I''ve separated the fields with a <tab> and not spaces.
* having <% end %> at the end of the line does not create
an empty line in the Excel document!
hope this helps!
-gnana
On 6/23/05, Ron Sweeney
<ron.sweeney-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> I am trying to put an ''export to excel'' link to dump the
entire table
> to a comma delimited spreadsheet.
>
> I get the spreadsheet flying out of the brower ok, however the
> contents do not seem to escape the html.
>
> Can anybody provide some insight on what I need to do to get a
> spreadsheet out of a table dump?
>
> ie.
>
> caption, header, caption
> row, data, data, data
> row, data, data, data
>
>
> The Controller:
>
>
> def exportexcel
> @headers["Content-Type"] = "application/vnd.ms-excel"
> @servers = Servers.find_all
> send_data(render_to_string(''servers/excel''),
:filename =>
> ''intelservers.xls'')
>
> end
>
> def excel
> @servers = Servers.find_all
> end
>
> The View ( note the ",") :
>
> <% for column in Servers.content_columns %>
> <%= column.human_name %>,
> <% end %>
> <% for servers in @servers %>
> <% for column in Servers.content_columns %>
> <%=h servers.send(column.name) %>,
> <% end %>
> <% end %>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>