We start with a form with a big text area and submit button and an area to show any errors we may encounter while parsing

<% form_tag do -%>
 Paste your Excel data : <%= text_area_tag :data, @data %>
 Click to validate the data <%= submit_tag 'Validate' %>

 <ul>
 <% @errors.each do |error| %>
  <li><span ><%=error%></span></li>

 <% end unless @errors.blank? -%>
 </ul>

Then the controller action which let's the model parse the text and only saves if every person model is valid.

class PeopleController <  ApplicationController
 def import
  @errors = []
  people = Person.all_from_tab_delimited_text(params[:data])

  people.each  do |person|
   person.errors.each { |error| @errors << "#{error} on #{person.inspect}"} unless person.valid?
  end
 end
end 

Now we move onto the Person model where the parsing actually takes place
class

class Person < ActiveRecord::Base
def Person.all_from_tab_delimited_text(data)
 people = []
 CSV::Reader.parse(@data, "\t") do |row|
  person = Person.new(:first_name"> row[0], :last_name => row[1])
  people << person
 end
 people
end
end

We now have a working allbeit simple implementation that even does some simple error checking to give the user feedback if their data cannot be imported. Of course the example here is simple and the real system has additional features and complexity that I didn't show here. Some of these are

• The import into a 2-step operation where we first ask the user to click 'Validate' then return a table showing the data as we parsed it. This allows the user to review and verify it was parsed correctly (i.e. the columns were in the correct order). Only on this second page do we provide an 'import' button that actually persists the new data.
• The data can not only create new people but also update existing ones. The example above with just first and last name is contrived so this wouldn't make sense but imagine an example where there are many other columns like address, phone number, etc. Our model code is a bit more complex in that it first tries to find a person with the same first and last names. If it succeeds it will then update that person and only creates a new one if there is no match.
• Some of the additional columns in the real system refer to other models in our system. To deal with these our model code must find other kinds of model objects and pass those into the Person.new. Ensuring the proper errors are sent back to the user in this case is fairly tricky as they may not always show up as part of the ActiveRecord validations.
  

Downloading data from our app into Excel
Moving data in this direction was even easier as we just relied on the fact that Excel will open csv files as long as our Rails app returns an Excel MIME type. We add a line to register the mime type with the csv extension

# config/initializers/mime_types.rb
Mime::Type.register 'application/vnd.ms-excel', :csv

Then we had to do for this was add a respond_to.xsl in the appropriate places of our controller

class PeopleController < ApplicationController
def index
 people = Person.all
 respond_to do |format|
  format.html # index.html.erb
  format.csv  do
   render :text => @people.collect { |person| person.to_csv}.join("\n")
  end
 end
end

and then add a to_csv to the model

class Person < ActiveRecord::Base
def to_csv
 "#{first_name}\t#{last_name}"
end
end

This only works for very simple Excel data on a single worksheet but when that's true it makes your job of working with that data so much simpler!