Spreadsheet in ruby and ror

This Article explains how to import data in ruby and ROR using spreadsheet gem

Spreadsheet using ruby

Let’s go directly in the topic

First create ruby .rb file call it ruby spreadsheet or call it what name you liked we have to require spreadsheet library on top of that file

create new object from Spreadsheet::Workbook class

book = Spreadsheet::Workbook.new

now we have to created one plain sheet_1 = book.create_worksheet

worksheet we can use specific name for our file by adding this line sheet_1.name = 'Test spreadsheet'

sheet_1.row(0).concat %w{Name Country specialist}

here we go now we have done to much just we have to get our list we can get the list from anywhere but in our case i have amazing list for my teammate and some of the best developers at Clickapps

row[0] = 'Majed Bojan'
row[1] =  'Yemen'
row[2] = 'Fullstack Developer'

row = sheet1.row(2)
row[0] = 'Ali Sheiba'
row[1] =  'Las Vegas'
row[2] = 'Full-stack Developer'

row = sheet1.row(3)
row[0] = 'Mohammed Balfaqi'
row[1] =  'Yemen'
row[2] = 'ROR Developer'

row = sheet1.row(4)
row[0] = 'Mohammed Basalah'
row[1] =  'Yemen'
row[2] = 'ROR Developer'

row = sheet1.row(5)
row[0] = 'Mohammed Aljefry'
row[1] =  'Yemen'
row[2] = 'Full-stack Developer'

Here we go!

Add sheet format

sheet_1.row(0).height = 18 # chose 18 for height 
format = Spreadsheet::Format.new :color => :blue, :weight => :bold, :size => 18 # adding 
sheet_1.row(0).default_format = format
bold = Spreadsheet::Format.new :weight => :bold

Finally will choose the location we need to save our file in my case i will save it in the desktop

book.write ‘/home/bojan/Desktop/spreadsheet.xls’
require 'spreadsheet'
Spreadsheet.client_encoding = 'UTF-8'
# will create new worksheet
book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet
sheet1.name = 'Test spreadsheet'
sheet1.row(0).concat %w{Name Country speacialist}
row = sheet1.row(1)
row[0] = 'Majed Bojan'
row[1] =  'Yemen'
row[2] = 'Fullstack Developer'
row = sheet1.row(2)
row[0] = 'Ali Sheiba'
row[1] =  'Yemen'
row[2] = 'Fullstack Developer'
row = sheet1.row(3)
row[0] = 'Mohammed Balfaqi'
row[1] =  'Yemen'
row[2] = 'ROR Developer'
row = sheet1.row(4)
row[0] = 'Mohammed Basalah'
row[1] =  'Yemen'
row[2] = 'ROR Developer'
row = sheet1.row(5)
row[0] = 'Mohammed Aljefry'
row[1] =  'Yemen'
row[2] = 'Fullstack Developer'
sheet1.row(0).height = 18
format = Spreadsheet::Format.new :color => :blue,
                                 :weight => :bold,
                                 :size => 18
sheet1.row(0).default_format = format
bold = Spreadsheet::Format.new :weight => :bold
book.write '/home/bojan/Desktop/spreedsheet.xls'

Spreadsheet using Rails

Second will try exporting users from database

For rails first we have to create rails project and will call it rails_spreedsheet or you can call it whatever name you liked!

Create rails app and bundling it

rails new rails_spreadsheet --api --database=postgresql

we have to install some dependencies as will need them, so please copy those gems into your gem file

gem 'factory_bot_rails' # factory to gererate random records
gem 'spreadsheet'       # gem to convert into xls files
gem 'faker'             # to generate a random data

Then run bundle

After you runing bundle prepare database.yml and secrets.yml and ignore them then create examples for them

Now let’s generate user model and put some fake data

rails g model user name:string email:string birth_date:datetime nationality_no:string

Run rails db:migrate

Add fake data in db/seeds.rb

100.times do
User.create(
name: Faker::Name.name_with_middle,
email: Faker::Internet.email,
nationality_no: Faker::Number.number(5),
birth_date: Faker::Date.birthday(18, 65)
)
end

Add this line into config/initializers/mime_types.rb

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

Add MimeResponds includes in app/controllers/application_controller.rb

include ActionController::MimeResponds

Generate users controller rails g controller v1/users and add routes

namespace :v1 do
  resources :users, only: :index
end

Our controller will look

class V1::UsersController < ApplicationController

  def index
    users = User.all

    respond_to do |format|

      format.json { render json: {users: users } }

      format.xls  do
        task = Spreadsheet::Workbook.new
        sheet = task.create_worksheet

        rows_format = Spreadsheet::Format.new color: :purple,
        weight: :normal,
        size: 13,
        align: :center

        users.each.with_index(1) do |task, i|
          sheet.row(i).concat task.slice(:name, :email, :nationality_no, :birth_date).values

          sheet.row(i).height = 25
          sheet.column(i).width = 30
          sheet.row(i).default_format = rows_format
        end

        head_format = Spreadsheet::Format.new color: :blue,
        weight: :bold,
        size:    14,
        pattern_bg_color:  :pattern_bg,
        pattern:  2,
        vertical_align: :middle,
        align:  :center

        sheet.row(0).concat %w{name description is_complete deadline employee_id}
        sheet.row(0).height = 25
        sheet.column(0).width = 30
        sheet.row(0).each.with_index { |c, i| sheet.row(0).set_format(i, head_format) }

        temp_file = StringIO.new
        task.write(temp_file)
        send_data(temp_file.string, :filename => "users.xls", :disposition => 'inline')
      end
    end

  end
end

To see the source code you can visit -> https://github.com/MajedBojan/spreedsheet-ruby-and-ror