This tutorial will cover how to read (or parse) an excel file with ruby. Here’s how you can do the same thing.

Installing Parseexcel

Parseexcel is a ruby port of the perl parseexcel module.

It’s installable via a nice gem like so:

 

 

gem install parseexcel

That’s that, now remember since it’s a gem library we have to tell our script to use the gem libs when we run the script from the console using the -rubygems switch.

Using Parseexcel

Parseexcel is a very straight forward library, you can’t do too much with it, but it gets the job done.

Getting a Workbook

 

 

Spreadsheet::ParseExcel.parse(filenameandpath)

This returns the actual excel file’s workbook, from there we need to determine what worksheet we’re on.

Getting a Worksheet

 

 

worksheet = workbook.worksheet(0)

Will return the first worksheet, you could also use the each method on the workbook to iterate over all the worksheets.

Iterating over rows and columns

The worksheet object has a very nice each method that will allow us to iterate over the rows like so

 

 

worksheet.each { |row|
j=0 i=0
if row != nil
row.each { |cell|

        if cell != nil
contents = cell.to_s(‘latin1′)
puts “Row: #{j} Cell: #{i} #{contents}”

        end
i = i+1
    }
j = j +1
end

}

Getting Cell Data

• Getting a String: cell.to_s(‘latin1′)
• Getting a Float: cell.to_s(‘latin1′)
• Getting a Int: cell.to_i
• Getting a Date: cell.date

Getting A Specific Cell

 

 

cell = row.at(3) #returns cell at column 3

A basic script for dumping an excel file

 

 

require ‘parseexcel’
#Open the excel file passed in from the commandline
workbook = Spreadsheet::ParseExcel.parse(ARGV[0])

#Get the first worksheet
worksheet = workbook.worksheet(0)

#cycle over every row
worksheet.each { |row|

j=0
i=0
if row != nil
#cycle over each cell in this row if it’s not an empty row

row.each { |cell|
if cell != nil
#Get the contents of the cell as a string

contents = cell.to_s(‘latin1′)
puts “Row: #{j} Cell: #{i}> #{contents}”

        end
i = i+1
}
end
}

To run the script, remember to use the -rubygems switch so that you can find the parsexcel library.

 

 

ruby -rubygems excelparse.rb myfile.xls