[RUBY] Save data from excel file using Rails gem roo


When a user wants to post multiple data to the app, he wants to be able to select an excel file and save it in the database.


Launch the app

rails new sample_app -d postgresql
rails db:create

Create a new app with rails new and create a database.

Controller creation

rails g controller users index

Create a User controller with an index action. The router and view are also created together.


rails g model user name:string email:string
rails db:migrate

Create string type name and email columns in users table.



<h1>User list</h1>

    <% @users.each do |user| %>
        <td><%= user.id %></td>
        <td><%= user.name %></td>
        <td><%= user.email %></td>
    <% end %>

<%= form_with url: import_users_path, local: true do |form| %>
  <%= form.file_field :file %>
  <%= form.submit %>
<% end %>

Post form generation. It does not save the file itself, but allows User to be registered with the action: import that sent params [: file].



Rails.application.routes.draw do
  resources :users do
    collection do
      post :import

Add the routing for the: import action of the Post method to User.



class UsersController < ApplicationController
  def index
    @users = User.all

  def import
    redirect_to users_url

The index action is the same as when using scaffold, get all records and assign to @users. The import action takes params [: file] and uses the import method to create a record in the users table. Write the definition of import method in the model.



class User < ApplicationRecord
  def self.import(file)
    xlsx = Roo::Excelx.new(file.tempfile)
    xlsx.each_row_streaming(offset: 1) do |row|
      user = self.new(id: row[0].value, name: row[1].value, email: row[2].value)
      next if self.pluck(:id).include?(user.id)

Since it is a model method of User model, it is defined in the form of def self.import. params [: file] .tempfile contains the path of the sent file. Read the file with Roo :: Excelx.new (file.tempfile) and assign it to the variable xlsx. Executes processing for each line of the file read by each_row_streaming method. With the offset option, you can specify the number of lines to skip processing (skip the item field on the first line). If the same id exists in the users table in next if self.pluck (: id) .include? (User.id), the process is skipped.

Get ready to use gem roo.

Ready to use gem roo


gem "roo" #Postscript

Bundle install in the terminal. Also, add require "roo" to config / application.rb


require 'rails/all'
require "roo"

This is OK.


Start the server, access "http: // localhost: 3000 / users" and check the screen.

test.png You can register data from the following Excel file by pressing Select File. エクセル.PNG After registration, check that the screen has changed as follows. test.png

Reference article

https://qiita.com/seitarooodayo/items/c9d6955a12ca0b1fd1d4 https://qiita.com/guri3/items/f20487516311b2a3db37 It was really helpful! Thank you!


I will apply it and create a function to register the employee list at once in the application.

Recommended Posts

Save data from excel file using Rails gem roo
[Rails] Save images using carrierwave
Add & save from rails database creation
[Rails] Manage multiple models using devise gem
Data processing using stream API from Java 8
Insert data into DB using Yaml file
[Rails] Manage data for each seed file
Save files and folders using File Manager
[Rails] How to handle data using enum
[Rails] Yeah! Active Strage! Competent Gem! ~ You can save images from two tables ~
Implement share button in Rails 6 without using Gem
[Rails] Display of multi-layered data using select boxes
[Rails] Implementation of batch processing using whenever (gem)
[Rails] Creating a breadcrumb trail using Gem gretel