Simple way of importing and exporting excel, csv file for ruby on rails application using roo gem

Create a Rails Application where student data can upload as excel and can be download as excel file.

#Gemfile

gem 'roo'

#Student Table migration File

class CreateStudents < ActiveRecord::Migration
  def change
    create_table :students do |t|

        t.string :name
        t.integer :roll_no
        t.integer :marks1
        t.integer :marks2
        t.integer :marks3
        t.integer :avg

      t.timestamps null: false
    end
  end
end

#student_controller.rb

class StudentController < ApplicationController
  def s_home
    @products = Student.all

    respond_to do |format|
      format.html
      format.csv { send_data @products.to_csv }
      format.xls { send_data @products.to_csv(col_sep: "\t"), filename: 'your_file_name.xls'}
    end
  end

  def import
      puts params[:file]
      Student.import(params[:file])
      redirect_to student_s_home_url, notice: "Products imported."
  end

end

#index.html.erb

<h2>Import Excel Data</h2>
<%= form_tag student_import_path, multipart: true do %>
  <%= file_field_tag :file %>
  <%= submit_tag "Import" %>
<% end %>

<h1>Students Avg Marks</h1>
<table border="1">
<tr><th>Name</th><th>Roll No</th><th>Marks1</th><th>Marks2</th><th>Marks3</th><th>AVG</th></tr>
<%@products.each do |t|%>
  <tr>
  <td><%=t.name%></td>
  <td><%= t.roll_no%></td>
  <td><%= t.marks1%></td>
  <td><%= t.marks2%></td>
  <td><%= t.marks3%></td>
  <td><%=avg = (t.marks1 + t.marks2 + t.marks3)/3 %></td>
  </tr>
  <%end%>
  </table>

  <p>
  Download:
  <%#= link_to "CSV", student_s_home_url(format: "csv") %> |
  <%= link_to "Excel File", student_s_home_url(format: "xls") %>
</p>

#/config/initializers/mime_types.rb

Mime::Type.register "application/xls", :xls

config/routes.rb
resources :products do
  collection { post :import }
end

#config/application.rb

require 'csv'
require 'iconv'

#student.rb

class Student < ActiveRecord::Base

  def self.to_csv(options = {})
    CSV.generate(options) do |csv|
      csv << column_names
      all.each do |product|
        csv << product.attributes.values_at(*column_names)
      end
    end
  end


def self.import(file)
  spreadsheet = open_spreadsheet(file)
  header = spreadsheet.row(1)
  (2..spreadsheet.last_row).each do |i|
    row = Hash[[header, spreadsheet.row(i)].transpose]
    product = find_by_id(row["id"]) || new
    product.attributes = row.to_hash.slice(*row.to_hash.keys)
    product.save!
  end
end

def self.open_spreadsheet(file)
  case File.extname(file.original_filename)
   when '.csv' then Roo::Csv.new(file.path, nil, :ignore)
   when '.xls' then Roo::Excel.new(file.path)
   when '.xlsx' then Roo::Excelx.new(file.path, nil, :ignore)
   else raise "Unknown file type: #{file.original_filename}"
  end
end
end

Resource:
Import excel file
http://railscasts.com/episodes/396-importing-csv-and-excel
Export excel file
http://railscasts.com/episodes/362-exporting-csv-and-excel?view=asciicast

Sample Image: