rails中导出excel或者csv格式

#version 0.1 #by wangmh@gradsoft.com.cn 2010-6-1 module ExportData # => 说明 # 支持导出excel,导出csv格式，其中csv作了导出window下的编码utf8到gbk2310.缺点是csv不能设置单元格的类型 # # # => 支持下面参数的定义，及多级别定义 # showfield = ['products_name','quantity','company','name','address','zip','mobile','phone','remark','other_requirements'] # showfieldname = ['产品名称', '数量','单位', '姓名', '地址', '邮编', '手机', '电话', '备注', '其他需求'] # showfield = ['lessons_user.lesson.name', 'lessons_user.user.icard', 'lessons_user.user.profile.name', 'lessons_user.user.email', 'lessons_user.user.profile.style', 'passed', 'certificated', 'lessons_user.created_at', 'updated_at'] # showfieldname = ['课程', '身份证', '姓名', '邮箱', '类别', '是否通过', '是否发证', '报名时间', '考试结束时间'] # => 使用方法 # showfield内容采用的不适record[]而是record.send()，showfield里的内容可以是自己定义的实例方法，自己组装数据。 # 例子： # def send_orders_data # order_show_field = ['products_name','quantity','company','name','address','zip','mobile','phone','remark','other_requirements'] # order_show_field_name = ['产品名称', '数量','单位', '姓名', '地址', '邮编', '手机', '电话', '备注', '其他需求'] # @orders = Oe2010Order.find(:all,:conditions=>search_params_for_order,:order=>'id') # @outfile = "orders_" + Time.now.strftime("%Y-%m-%d") + ".xls" # csv_data = export_excel(order_show_field_name,order_show_field,@orders) # send_data csv_data, # :type => 'text/csv; charset=gb2312; header=present', # :disposition => "attachment; filename=#{@outfile}" # end # def export_excel(showfieldname, showfield, records, &block) #生成单元格 def cell_tag(str) "<Cell><Data ss:Type=/"String/"> #{str}</Data></Cell>" end def row_tag(str) "<Row>#{str}</Row>" end #excle头 output = '<?xml version="1.0" encoding="UTF-8"?><Workbook 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" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office"><Worksheet ss:Name="Sheet1"><Table>' temp = "" showfieldname.each { |item| temp << cell_tag(item) } output << row_tag(temp) records.each do |record| temp = "" showfield.each do |item| if item.index('.').blank? v = record.send(item) else #获取关联对象的值，比如lessons_user.lesson.name obj = record item.split('.').each {|m| obj = obj.send(m)} v = obj end v = v.strftime("%Y-%m-%d %H:%M:%S") if (item == "created_at")||item == "updated_at" #格式化时间 v = block.call item, v if block_given? #将key, value传给block，将返回结果作为新的value temp << cell_tag(v) end output << row_tag(temp) end output << '</Table></Worksheet></Workbook>' output end def export_csv(showfieldname, showfield, records, &block) new_showfieldname = [] showfieldname.each { |item| new_showfieldname << convert_gb(item) #需要转码到gb } csv_string = FasterCSV.generate do |csv| csv << new_showfieldname records.each do |record| arr = [] showfield.each do |item| if item.index('.').blank? v = record.send(item) else #获取关联对象的值，比如lessons_user.lesson.name obj = record item.split('.').each {|m| obj = obj.send(m)} v = obj end v = v.strftime("%Y-%m-%d %H:%M:%S") if (item == "created_at")||item == "updated_at" #格式化时间 v = block.call item, v if block_given? #将key, value传给block，将返回结果作为新的value arr.push(convert_gb(v)) end csv << arr end end end def convert_gb(str) Iconv.iconv("GBK","UTF-8", str.to_s) end end