Abhradip Abhradip - 4 months ago 34
Ruby Question

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 contailns only one column named as firstname. But I need only the row data in each iteration as string.

How to solve this issue?

Answer

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' },
  ...
]
Comments