将数据表导出成excel

[转][http://tb.blog.csdn.net/TrackBack.aspx?PostId=1844264]

首先是安装 gem install spreadsheet-excel

其次 在script 文件夹中新建一个叫export的文件，里面代码如下：

#!/usr/bin/env ruby

require File.dirname(__FILE__) + '/../config/environment'
require "spreadsheet/excel"
include Spreadsheet
users=User.find_by_sql("select count(*) from users where created_at < '#{Time.now.strftime("%Y-%m-%d")}' and created_at > '#{1.day.ago.strftime("%Y-%m-%d")}'")

workbook = Excel.new("#{RAILS_ROOT}/public/reports/report_#{Time.now.strftime('%Y-%m-%d')}.xls")
worksheet = workbook.add_worksheet("Report of #{1.day.ago.strftime('%Y-%m-%d')}")

worksheet.write(0, 0, "#{1.day.ago.strftime('%Y-%m-%d')}")

worksheet.write(1, 0, "Daily Registered users")

worksheet.write(2, 0, "#{users[0].count.to_i}")

workbook.close
第三：执行ruby script/export. 到public文件夹中看一下，是不是多了一个excel表格。

有时我们需要将导出来的表格作为邮件附件发送出去。那么我们需要在上面的代码底部，加入这段：
Notifier.deliver_export
model代码如下：

class Notifier < ActionMailer::Base

def export
@recipients = "XXX@gmail.com" #收件人邮箱
#@cc = "XXX@sina.com","XXX@163.com" #抄送人邮箱
@subject = "#{Time.now.strftime('%Y-%m-%d')} data"
@body = ""
@data = ""
File.open("#{RAILS_ROOT}/public/reports/report_#{Time.now.strftime('%Y-%m-%d')}.xls", "rb").each { |fp| @data<<fp }
attachment :content_type =>"application/vnd.ms-excel",
:filename => "export_#{Time.now.strftime('%Y-%m-%d')}.xls" ,
:body => @data
end
end
这样就实现了使用spreadsheet-excel 把数据导入到Excel表格中，并且作为邮件附件发送给别人了。