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

Purpose

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.

Implementation

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.

Modeling

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

Create string type name and email columns in users table.

View

html:app/views/users/index.html.erb

<h1>User list</h1>

<table>
  <thead>
    <tr>
      <th>ID</th>
      <th>name</th>
      <th>Email</th>
    </tr>
  </thead>
  <tbody>
    <% @users.each do |user| %>
      <tr>
        <td><%= user.id %></td>
        <td><%= user.name %></td>
        <td><%= user.email %></td>
      </tr>
    <% end %>
  </tbody>
</table>

<%= 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].

router

config/routes.rb

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

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

controller

app/controllers/users_controller.rb

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

  def import
    User.import(params[:file])
    redirect_to users_url
  end
end

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.

model

app/models/user.rb

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)
      user.save
    end
  end
end

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

Gemfile

gem "roo" #Postscript

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

config/application.rb

#abridgement
require 'rails/all'
require "roo"
#abridgement

This is OK.

Verification

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

You can register data from the following Excel file by pressing Select File. After registration, check that the screen has changed as follows.

Reference article

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

Postscript

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