CODEWITHSUNDEEP
ruby-on-railsrubystringexcelimport
Abhradip
Abhradip

Reputation: 413

How to parse a single excel row data in rails

I am developing a ruby on rails app and I need to import an excel file for that. I am using spreadsheet gem.
Now my problem is that I can not get a single row data from excel file.

My code is here:

 def actual_import(file)
   puts "In the actual_import method"
   spreadsheet= Employee.open_spreadsheet(file)
   header=spreadsheet.row(1)
   (2..spreadsheet.last_row).each do |i|
     row=Hash[[header,spreadsheet.row(i)].transpose]
     $send_name=row.to_hash.slice('firstname')
     puts $send_name.to_s       #this prints a weird result,,described below
     puts $send_name.length
     create
   end
 end

And my open_spreadsheet method in Employee model is:

def self.open_spreadsheet(file)
    case File.extname(file.original_filename)
    #when ".csv" then Roo::Csv.new (file.path nil, :ignore)
    when ".xlsx" then Roo::Excelx.new (file.path)
    #when ".xlsx" then Excelx.new (file.path, nil, :ignore)
    else raise "Unknown file type: #{file.original_filename}"
    end
end

Now $send_name prints {"firstname"=>"Abc"} and when I use $send_name.length it gives me 1 . But I need to capture only "Abc".
My excel file contains only one column named as firstname. But I need only the row data in each iteration as string.

How to solve this issue?

Upvotes: 1

Views: 2005

Answers (1)

Aleksey
Aleksey

Reputation: 2309

I recommend to use BatchFactory gem.
It uses Roo gem under the hood.
BatchFactory can read all excel file rows as array of hashes which is very handy to work with. 

require 'batch_factory'
factory = BatchFactory.from_file 'filename.xlsx', keys: [:header1, :header2]
factory.rows

This will give you 

[
  { header1: 'value11', header2: 'value12' },
  { header1: 'value21', header2: 'value22' },
  ...
]

In your case you can do

factory = BatchFactory.from_file 'filename.xlsx', keys: [:firstname]
firstnames = factory.rows.map { |row| row[:firstname] }

This will give your an array of all values from firstname column.

UPDATE
You can even omit rows in factory.rows.map because BatchFactory implement some method_missing, i.e.

firstnames = factory.map { |row| row[:firstname] }

Upvotes: 1

Related Questions