Working with Excel data without working with Excel

July 23, 2008

I am currently working on an application to replace manual MS Excel files that get emailed around and while the business users are looking forward to an interactive web app they are also wedded to their excel files. One of the key requirements we need to meet is allowing them to move their data back and forth.

In the past I've worked with COM and Java libraries to manipulate the files in their native formats and both were painful experiences I did not want to relive. I'm going to talk about how we solved the problem without the need to work with any Office libraries.

Loading Excel data into our Application
The approach we took was to let the users extract the data themselves with a simple copy-and-paste. The user would copy the cells of the excel file and then paste that tab delimited plain text into an HTML form.

Let's say we're trying to import a list of people with a first and last name. The Excel sheet would look like this

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' %>

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

<% end unless @errors.blank? -%>
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?
Now we move onto the Person model where the parsing actually takes place
class Person < ActiveRecord::Base
def Person.all_from_tab_delimited_text(data)
people = []
CSV::Reader.parse(@data, "\t") do |row|
person ="> row[0], :last_name => row[1])
people << person
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 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/', :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")
and then add a to_csv to the model
class Person < ActiveRecord::Base
def to_csv

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!