Exporting to CSV from complex rails query

Question

I was following this railscast video but I'm having serious difficulties to export my data to excel (or CSV for this matter).

I'm using will_paginate in some data that I show on frontend like this:

sql = "select complex..."
@data = paginate_by_sql([sql],
                :per_page => params[:rows],
                :page => params[:page])

so, as is, I thought this should work:

respond_to do |format|
    format.html
    format.xls { send_data @data.to_csv(:col_sep => "\t") }
end

and it actually downloaded a xls file correctly by the content is all messed up, it shows one row per column and something like this as content:

#<Product:0x00000004c83328>

PS -> using rails latest version

:: EDIT :: By one row per column I mean one row only on my excel sheet and on this row

COLUMN A = #<Product:0x00000004c83328>

COLUMN B = #<Product:0x00000004c83329>

COLUMN C = #<Product:0x00000004c8333> (30 columns)

UPDATE

Did a simple exercise for testing and end up with all columns again in one column only:

csv_string = CSV.generate(:col_sep => ",") do |csv|
  csv << ["row", "of", "CSV", "data"]
  csv << ["another", "row"]
end

respond_to do |format|
    format.html
    format.csv { send_data csv_string,
        :type => 'text/csv; charset=iso-8859-1; header=present',
        :disposition => "attachment; filename=records.csv" }
end

(:col_sep => ",") is optional I suppose.

result:

excel output

score 0 · Answer 1 · answered Jun 11 '13 at 19:40

0

You are almost there, you just need to map each of your Product model objects because you are calling to_s on them, which is undefined.

So if you just want a list of titles, you could override the to_s method on your model (app/models/product.rb) to do something like:

def to_s
  title
end

Or, to generate a comma separated list you could do

def to_s
  [title, price, availability].join(',')
end

answered Jun 11 '13 at 19:40

ipd

5,674
3
34
49

I am sorry, I didn't understand what you mean by me calling a `to_s`...I'm just calling `paginate_by_sql`. Although I realize the output seems like model converted to string... it's strange alright. I must be close... – tostasqb Jun 12 '13 at 11:18
Yes, if you call to_s on a model, like `Person` you will get output like `#`. Which is the default string conversion for an ActiveRecord Model. So, i'm suggesting you override this method in your model by defining a new `to_s` method. – ipd Jun 12 '13 at 20:29

score 0 · Accepted Answer · answered Jun 17 '13 at 11:44

ok, so this is what I did.

modified the responder to this...

respond_to do |format|
    format.xls
end

and created a template called nameofmethod.xls.erb with this...

<?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">
    <Table>
      <Row>
      <% @data.first.attributes.keys.each do |column| %>
        <Cell><Data ss:Type="String"><%=column%></Data></Cell>
      <% end %>
      </Row>
    <% @data.each do |row| %>
      <Row>
      <% row.attributes.values.each do |column| %>
        <Cell><Data ss:Type="String"><%= column %></Data></Cell>
      <% end %>
      </Row>
    <% end %>
    </Table>
  </Worksheet>
</Workbook>

This is pretty dynamic for any kind of ActiveRecord Object. For now this will have to do...

Steven Barragán · Answer 3 · 2015-10-30T07:36:48.593

0

You might use this gem, you can export any complex query easily.

https://github.com/stevenbarragan/julia_builder

edited Oct 30 '15 at 07:36

answered Oct 29 '15 at 10:59

Steven Barragán

1,144
1
11
21

Exporting to CSV from complex rails query

3 Answers3