即日起在codingBlog上分享您的技术经验即可获得积分,积分可兑换现金哦。

在Rails中,如何将记录作为CSV文件返回

栈溢出 Eric 3℃ 0评论
本文目录
[隐藏]

1.原始问题:in rails, how to return records as a csv file

I have a simple database table called “Entries”:

class CreateEntries < ActiveRecord::Migration
  def self.up
    create_table :entries do |t|
      t.string :firstName
      t.string :lastName
      #etc.
      t.timestamps
    end
  end

  def self.down
    drop_table :entries
  end
end

How do I write a handler that will return the contents of the Entries table as a CSV file (ideally in a way that it will automatically open in Excel)?

class EntriesController < ApplicationController

  def getcsv
    @entries = Entry.find( :all )

    # ??? NOW WHAT ????

  end

end

2.被采纳答案

There is a plugin called FasterCSV that handles this wonderfully.

3.其他高票答案

3.1.第1个答案

FasterCSV is definitely the way to go, but if you want to serve it directly from your Rails app, you'll want to set up some response headers, too.

I keep a method around to set up the filename and necessary headers:

def render_csv(filename = nil)
  filename ||= params[:action]
  filename += '.csv'

  if request.env['HTTP_USER_AGENT'] =~ /msie/i
    headers['Pragma'] = 'public'
    headers["Content-type"] = "text/plain" 
    headers['Cache-Control'] = 'no-cache, must-revalidate, post-check=0, pre-check=0'
    headers['Content-Disposition'] = "attachment; filename=\"#{filename}\"" 
    headers['Expires'] = "0" 
  else
    headers["Content-Type"] ||= 'text/csv'
    headers["Content-Disposition"] = "attachment; filename=\"#{filename}\"" 
  end

  render :layout => false
end

Using that makes it easy to have something like this in my controller:

respond_to do |wants|
  wants.csv do
    render_csv("users-#{Time.now.strftime("%Y%m%d")}")
  end
end

And have a view that looks like this: (generate_csv is from FasterCSV)

UserID,Email,Password,ActivationURL,Messages
<%= generate_csv do |csv|
  @users.each do |user|
    csv << [ user[:id], user[:email], user[:password], user[:url], user[:message] ]
  end
end %>

3.2.第2个答案

I accepted (and voted up!) @Brian's answer, for first pointing me to FasterCSV. Then when I googled to find the gem, I also found a fairly complete example at this wiki page. Putting them together, I settled on the following code.

By the way, the command to install the gem is:
sudo gem install fastercsv
(all lower case)

require 'fastercsv'

class EntriesController < ApplicationController

  def getcsv
      entries = Entry.find(:all)
      csv_string = FasterCSV.generate do |csv| 
            csv << ["first","last"]
            entries.each do |e|
              csv << [e.firstName,e.lastName]
            end
          end
          send_data csv_string, :type => "text/plain", 
           :filename=>"entries.csv",
           :disposition => 'attachment'

  end


end

3.3.第3个答案

Another way to do this without using FasterCSV:

Require ruby's csv library in an initializer file like config/initializers/dependencies.rb

require "csv"

As some background the following code is based off of Ryan Bate's Advanced Search Form that creates a search resource. In my case the show method of the search resource will return the results of a previously saved search. It also responds to csv, and uses a view template to format the desired output.

  def show
    @advertiser_search = AdvertiserSearch.find(params[:id])
    @advertisers = @advertiser_search.search(params[:page])
    respond_to do |format|
      format.html # show.html.erb
      format.csv  # show.csv.erb
    end
  end

The show.csv.erb file looks like the following:

<%- headers = ["Id", "Name", "Account Number", "Publisher", "Product Name", "Status"] -%>
<%= CSV.generate_line headers %>
<%- @advertiser_search.advertisers.each do |advertiser| -%>
<%- advertiser.subscriptions.each do |subscription| -%>
<%- row = [ advertiser.id,
            advertiser.name,
            advertiser.external_id,
            advertiser.publisher.name,
            publisher_product_name(subscription),
            subscription.state ] -%>
<%=   CSV.generate_line row %>
<%- end -%>
<%- end -%>

On the html version of the report page I have a link to export the report that the user is viewing. The following is the link_to that returns the csv version of the report:

<%= link_to "Export Report", formatted_advertiser_search_path(@advertiser_search, :csv) %>

3.4.第4个答案

Take a look into the FasterCSV gem.

If all you need is excel support, you might also look into generating a xls directly. (See Spreadsheet::Excel)

gem install fastercsv
gem install spreadsheet-excel

I find these options good for opening the csv file in Windows Excel:

FasterCSV.generate(:col_sep => ";", :row_sep => "\r\n") { |csv| ... }

As for the ActiveRecord part, something like this would do:

CSV_FIELDS = %w[ title created_at etc ]
FasterCSV.generate do |csv|
  Entry.all.map { |r| CSV_FIELDS.map { |m| r.send m }  }.each { |row| csv << row }
end

转载请注明:CodingBlog » 在Rails中,如何将记录作为CSV文件返回

喜欢 (0)or分享 (0)
发表我的评论
取消评论

*

表情