This tutorial will cover how to read (or parse) an excel file with ruby. I had to write a script to unpivot some data for a co-worker that saved him hours of time, and I got to write a ruby script, so it was a win-win. 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:
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
This returns the actual excel file's workbook, from there we need to determine what worksheet we're on.
Getting a Worksheet
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
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
A basic script for dumping an excel file
#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.