Importing Excel sheet to Rails Application

This blog is about importing an excel sheet in the Rails application. I am assuming you’ve already installed the Rails environment. I am starting from the project creation. We’ll create a project that can import and read data from an excel sheet, and we can then save the data in our database. Let’s create a project on the hot topic, Covid. We’ll create a table called ‘hospital_data’ that contains hospital data, for example, hospital name, no. of available beds, no. of available oxygen cans.

So let’s start. First of all, we’ll create a Rails app. For that, open your terminal and run the following command.

rails new covid-help

It will create all the required files and directories for the project. Now, let’s create a model/table.

rails g model HospitalDatum name: string oxygen_can: integer bed: integer

It will create a model and a migration as well. Now, we need to update the schema file. To update it, run:

rails db:migrate

Now, we need to create a controller that handles the data and updates the tables in the database.

rails g controller home index new

It will create a controller named home_controller with two methods, index and new, and two view files called index.html.erb and new.html.erb in app/views/homes. You can give any suitable name for your controller.

Open routes.rb from config/routes.rb and add the following line.

root :to => "homes#index"

It will create an index page as a root page. To create routes, add

resources :homes

Coming to the controller, we’ll add methods.

Now, we can code our app.

Open your new.html.erb file and add this.

<%= form_for @hospital_data, url: homes_path, method: :post do |f| %>
    <div class="container">
        <div class="row">
            <%= f.label :name,'Hospital Name', class: 'col-md-3'%>
            <%= f.text_field :name, class: 'col-md-3' %>
        </div>

        <div class="row">
            <%= f.label :name,'Oxygen Concentrators', class: 'col-md-3'%>
            <%= f.text_field :oxygen_can, class: 'col-md-3' %>
        </div>

        <div class="row">
            <%= f.label :name,'Beds', class: 'col-md-3'%>
            <%= f.text_field :bed, class: 'col-md-3' %>
        </div>

        <div class="row">
            <%= f.submit 'Add Data', class: 'btn btn-primary col-sm-2' %>
            <%= link_to 'Back', homes_path, class: 'btn btn-primary col-sm-2' %>
        </div>
    </div>
<% end %>

Open index.html.erb and add this.

<%= link_to 'Add new', new_home_path %>

<div class="">
    <div class="row">
        <h3 class="col-md-3 item1"> Index </h3>
        <h3 class="col-md-3 item1"> Hospital Name </h3>
        <h3 class="col-md-3 item1"> Oxygen Cans </h3>
        <h3 class="col-md-3 item1"> No. of Beds </h3>
    </div>


    <% @hospital_data.each do |data| %>
        <div class="row">
            <p class="col-md-3 item1"> 1 </p>
            <p class="col-md-3 item1"><%=  data.name %> </p>
            <p class="col-md-3 item1"><%= data.oxygen_can %> </p>
            <p class="col-md-3 item1"><%= data.bed %> </p>
        </div>
    <% end %>
</div>

I’ve used Bootstrap cdn.

In your application.html.erb add these lined before <%= yield %> tag.

<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>

And add the following before tag.

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">

Open homes_controller.rb and add,

def index
    @hospital_data = HospitalDatum.all
  end

  def new
    @hospital_data = HospitalDatum.new
  end

  def create
    @hospital_data = HospitalDatum.new(params_data)

    if @hospital_data.save
      redirect_to root_path
    else
      render :new
    end
  end

  private

  def params_data 
    params.require(:hospital_datum).permit(:name, :oxygen_can, :bed)
  end

We’re done with half of the part. Try to add data via new form and check the data in rails console.

Now the thing is, we want our app to use an excel sheet to fetch data. Suppose you’ve data of almost 50–60 hospitals. You don’t wanna create record one by one. So here comes the use of Excel sheet in Rails.

To use it, we need to add a gem called ‘roo-xls’. You can go through its documentation here. roo-xls

Open Gemfile and add,

gem 'roo-xls'

Run bundle install

Now that we’ve installed the gem, we can use its functionalities.

We want our form to get submitted on a different URL. Open routes.rb

Change resources:homes to

resources :homes do
    post :bulk_add_data, on: :collection
end

This will create a post method called bulk_add_data in the controller. Open http://localhost:3000/rails/info/routes to see the route.

Now that the route has been created, we can use it here.

In your new.html.erb, add this code before the form tag.

<div class="container">
    <%= form_tag(bulk_add_data_homes_path,  multipart: true, class: 'btn btn-primary') do %>
        <%= file_field_tag 'xml_file' %>
        <%= submit_tag 'OK', class: 'btn btn-secondary' %>
    <% end %>
</div>

multipart: true is used here because we’re uploading a file. Here’s a link to its documentation.

It will look like this.

Coming back to the controller, let’s create a method called bulk_add_data. We’ve used xml-file in file_field_tag thus we’ll get the exact name in params.

xlsx = Roo::Spreadsheet.open(file)

It will open the selected file to read. Open controller and write into bulk_add_data method.

def bulk_add_data
    file = params[:xml_file]
    xlsx = Roo::Spreadsheet.open(file, extension: :xlsx)
    count = xlsx.count
    for i in 1...count do
      hospital_name = xlsx.row(i+1)[0]
      oxygen_can = xlsx.row(i+1)[1]
      bed = xlsx.row(i+1)[2]

      @hospital_data = HospitalDatum.new(name: hospital_name,   oxygen_can: oxygen_can, bed: bed)
      @hospital_data.save
    end
    redirect_to root_path
  end

Now let’s run our app. My excel file looks like this.

After selecting this file, I’ll get this data in my database which will further render on my index page.

And we’re done.

Thank you so much for reading it till the end. This is my first blog on Medium. I’m open to suggestions.