how can I upload and parse an Excel file in Rails?

Question

I want to be able to upload an Excel file that contains contact information. I then went to be able to parse it and create records for my Contact model.

My application is a Rails application.

I am using the paperclip gem on heroku, I've been able to parse vim cards into the Contact model, and am looking for something similar, but will go through all lines of the Excel file.

Gems that simplify the task and sample code to parse would be helpful!

Answer 1

Spreadsheet is the best Excel parser that I have found so far. It offers quite a lot of functionality.

You say you use Paperclip for attachments which is good. However, if you store the attachments in S3 (which I assume since you use Heroku) the syntax for passing the file to spreadsheet is a little different but not difficult.

Here is an example of the pure syntax that can be used and not placed in any classes or modules since I don't know how you intend to start the parsing of contacts.

# load the gem
require 'spreadsheet'

# In this example the model MyFile has_attached_file :attachment
@workbook = Spreadsheet.open(MyFile.first.attachment.to_file)

# Get the first worksheet in the Excel file
@worksheet = @workbook.worksheet(0)

# It can be a little tricky looping through the rows since the variable
# @worksheet.rows often seem to be empty, but this will work:
0.upto @worksheet.last_row_index do |index|
  # .row(index) will return the row which is a subclass of Array
  row = @worksheet.row(index)

  @contact = Contact.new
  #row[0] is the first cell in the current row, row[1] is the second cell, etc...
  @contact.first_name = row[0]
  @contact.last_name = row[1]

  @contact.save
end