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

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 w开发者_开发技巧ould be helpful!

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

I had a similar requirement in one of my Rails 2.1.0 application. I solved it in the following manner:

In the 'lib' folder I wrote a module like this:

require 'spreadsheet'

module DataReader
  def read_bata(path_to_file)
    begin
      sheet = book.worksheet 0
      sheet.each 2 do |row|
        unless row[0].blank?
          # Create model and save it to DB
          ...
        end
      end
    rescue Exception => e
      puts e
    end
  end
end

Had a model Upload:

class Upload < AR::Base
  has_attached_file :doc, 
    :url => "datafiles/:id",
    :path => ":rails_root/uploads/:id/:style/:basename.:extension"

  # validations, if any
end

Generated an UploadsController which would handle the file upload and save it to appropriate location. I used Paperclip for file upload.

class UploadsController < AC
  include DataReader

  def new 
    @upload = Upload.new
  end

  def create
    @upload = Upload.new(params[:upload])

    @upload.save
    file_path = "uploads/#{@upload.id}/original/#{@upload.doc_file_name}"
    @upload.read = DataReader.read_data(file_path)

    # respond_to block
  end
end

Read about 'spreadsheet' library here and here. You can make appropriate improvements and make the technique work in Rails 3. Hope this helps.

I made a gem to achieve this easily. I called it Parxer and...

• It's built on to of roo gem.
• It allows you to parse xls, xlsx and csv files.
• Has a DSL to handle:
  • Column mapping.
  • File, row, and column/cell validation.
  • Column/cell formatting.