Ruby-on-rails 在 Rails 中，如何将记录作为 csv 文件返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/94502/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
in rails, how to return records as a csv file
提问by Eric
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)?
我如何编写一个处理程序，将 Entries 表的内容作为 CSV 文件返回（理想情况下，它会自动在 Excel 中打开）？
class EntriesController < ApplicationController
def getcsv
@entries = Entry.find( :all )
# ??? NOW WHAT ????
end
end
采纳答案by Brian
There is a plugin called FasterCSV that handles this wonderfully.
有一个名为 FasterCSV 的插件可以很好地处理这个问题。
回答by Clinton N. Dreisbach
FasterCSVis 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.
FasterCSV绝对是要走的路，但如果您想直接从 Rails 应用程序提供它，您还需要设置一些响应标头。
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_csvis from FasterCSV)
并有一个看起来像这样的视图：（generate_csv来自 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 %>
回答by Eric
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.
我接受了（并投了赞成票！）@Brian 的回答，首先将我指向 FasterCSV。然后当我用谷歌搜索找到 gem 时，我还在这个 wiki page 上找到了一个相当完整的例子。将它们放在一起，我决定使用以下代码。
By the way, the command to install the gem is: sudo gem install fastercsv (all lower case)
顺便说一句，安装gem的命令是：sudo gem install fastcsv（全部小写）
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
回答by rwc9u
Another way to do this without using FasterCSV:
另一种不使用 FasterCSV 的方法：
Require ruby's csv library in an initializer file like config/initializers/dependencies.rb
在 config/initializers/dependencies.rb 等初始化文件中需要 ruby 的 csv 库
require "csv"
As some background the following code is based off of Ryan Bate's Advanced Search Formthat 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.
作为一些背景，以下代码基于创建搜索资源的Ryan Bate 的高级搜索表单。在我的例子中，搜索资源的 show 方法将返回之前保存的搜索结果。它还响应 csv，并使用视图模板来格式化所需的输出。
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:
show.csv.erb 文件如下所示：
<%- 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:
在报告页面的 html 版本上，我有一个链接可以导出用户正在查看的报告。以下是返回报告的csv版本的link_to：
<%= link_to "Export Report", formatted_advertiser_search_path(@advertiser_search, :csv) %>
回答by kch
Take a look into the FasterCSVgem.
看看FasterCSVgem。
If all you need is excel support, you might also look into generating a xls directly. (See Spreadsheet::Excel)
如果您只需要 excel 支持，您也可以考虑直接生成 xls。（见电子表格::Excel）
gem install fastercsv
gem install spreadsheet-excel
I find these options good for opening the csv file in Windows Excel:
我发现这些选项适用于在 Windows Excel 中打开 csv 文件：
FasterCSV.generate(:col_sep => ";", :row_sep => "\r\n") { |csv| ... }
As for the ActiveRecord part, something like this would do:
至于 ActiveRecord 部分，可以这样做：
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
回答by JasonTrue
You need to set the Content-Type header in your response, then send the data. Content_Type: application/vnd.ms-excel should do the trick.
您需要在响应中设置 Content-Type 标头，然后发送数据。Content_Type: application/vnd.ms-excel 应该可以解决问题。
You may also want to set the Content-Disposition header so that it looks like an Excel document, and the browser picks a reasonable default file name; that's something like Content-Disposition: attachment; filename="#{suggested_name}.xls"
您可能还想设置 Content-Disposition 标题，使其看起来像 Excel 文档，并且浏览器会选择一个合理的默认文件名；这有点像 Content-Disposition:attachment; filename="#{suggested_name}.xls"
I suggest using the fastercsv ruby gem to generate your CSV, but there's also a builtin csv. The fastercsv sample code (from the gem's documentation) looks like this:
我建议使用 fastcsv ruby gem 来生成您的 CSV，但也有一个内置的 csv。fastcsv 示例代码（来自 gem 的文档）如下所示：
csv_string = FasterCSV.generate do |csv|
csv << ["row", "of", "CSV", "data"]
csv << ["another", "row"]
# ...
end
回答by rantler
The following approached worked well for my case and causes the browser to open the appropriate application for the CSV type after downloading.
以下方法适用于我的情况，并导致浏览器在下载后打开 CSV 类型的相应应用程序。
def index
respond_to do |format|
format.csv { return index_csv }
end
end
def index_csv
send_data(
method_that_returns_csv_data(...),
:type => 'text/csv',
:filename => 'export.csv',
:disposition => 'attachment'
)
end
回答by Henry Jacob
try a nice gem to generate CSV from Rails https://github.com/crafterm/comma
尝试一个不错的 gem 从 Rails https://github.com/crafterm/comma生成 CSV
回答by paul
Take a look at the CSV Shaper gem.
看看 CSV Shaper gem。
https://github.com/paulspringett/csv_shaper
https://github.com/paulspringett/csv_shaper
It has a nice DSL and works really well with Rails models. It also handles the response headers and allows filename customisation.
它有一个很好的 DSL，并且可以很好地与 Rails 模型配合使用。它还处理响应头并允许自定义文件名。
回答by boulder_ruby
If you're simply wanting to get the csv database yourself from the console you can do so in a few lines
如果您只是想从控制台自己获取 csv 数据库，您可以在几行中完成
tags = [Model.column_names]
rows = tags + Model.all.map(&:attributes).map(&:to_a).map { |m| m.inject([]) { |data, pair| data << pair.last } }
File.open("ss.csv", "w") {|f| f.write(rows.inject([]) { |csv, row| csv << CSV.generate_line(row) }.join(""))}
