Import and Export Spreadsheet and CSV file

1. rails g scaffold product title:string description:text

first you have to put gem in gemfile

gem 'roo-xls'
gem 'axlsx_rails'
gem 'zip-zip'

run bundle

In application.rb
   require 'csv'

2. In prodouct model copy and paste code

# Import spread and csv file
 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)
     # xlsx = Roo::Excelx.new(file.path)
    case File.extname(file.original_filename)
    when ".csv" then Csv.new(file.path, nil, :ignore)
    when ".xls" then Roo::Excel.new(file.path)
    when ".xlsx" then Roo::Excelx.new(file.path)
    else raise "Unknown file type: #{file.original_filename}"
   end
 end

# Download or export CSV and Spreadsheet
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

3. In product Controller

def index
    @products = Product.all
    respond_to do |format|
      format.html
      #Download or export csv and spreadsheet
      format.csv { send_data @products.to_csv }
      format.xls { send_data @products.to_csv(col_sep: "\t") }
    end
  end


  def import
    Product.import(params[:file])
    redirect_to root_url, notice: "Products imported."
  end

4. in route.rb

  resources :products do
    collection { post :import }
  end

5. In views products/index.html.erb

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

<h2>Import Products</h2>
<%= form_tag import_products_path, multipart: true do %>
  <%= file_field_tag :file %>
  <%= submit_tag "Import" %>
<% end %>

Important Note: When you will download spreadsheet and csv file you have to remove some column id,created_at, updated_at from both file.if you will not remove that column in you file will get error "id not null column created_at, updated_at". You make sure remove these three column(id, created_at, updated_at) in both file