The Logic of Parsing Excel Files

Advertisers put their content in the medium that their marketing targets use i.e. advertise on Facebook if your customers use Facebook. However, when I act as the medium between knowledge experts and a web app that delivers that expertise to users, I want those experts to collborate with me using a platform they already know. In the case of inputing data, I ask them to use an Excel sheet, and I use the Ruby gem “Roo” to get that info into a database.

Ruby Basics of Roo

Using Roo is simple:

1. gem install roo
2. require ‘roo’
3. s = Roo::Excelx.new(“myspreadsheet.xlsx”)
4. s.default_sheet = s.sheets.first
5. define your attributes by column
6. iterate through each row of your excel sheet, with a starting and ending row

1
2
3
4
5
6
7
8
9
10
11
12
13

start_row = 3
end_row   = 78

(start_row..end_row).each do |line|
  attr_one   = oo.cell(line, 'C')
  attr_two   = oo.cell(line, 'D')
  attr_three = oo.cell(line, 'E')
  attr_four  = oo.cell(line, 'F')
  attr_five  = oo.cell(line, 'G')

  object     = Object.create(:attr_one => attr_one, :attr_two => :attr_two)
  detail     = object.details.create(:attr_three => attr_three, :attr_four => attr_four, :attr_five => attr_five)
end

Other Ways of Using Roo

You can create a checklist system where Excel rows are filled with an “x” if an entry meets a column criteria. For example, if you’re tracking how many continents you’ve visited, you can put an “x” under each continent visited (such that columns B-H are Europe, Arica, etc.). You can also define your own ruby methods to transform the excel data as needed.

Roo is a simple but powerful tool for collborating with knowledge experts to build a powerful web app. Find more information at its Github to parse things like CSV and OpenOffice.