Using Ruby to parse an Excel document

If you work in the finance domain you are often confronted with data in spreadsheets. I’ve used a number of ways to read spreadsheets before like Scriptom (Groovy) or POM (Java). Ruby comes with a windows specific OLE bridge which allows you to use COM to communicate with a number of different MS applications. The pickaxe has more details.

Here is a 4 line working example of opening an Excel document, choosing a worksheet then printing the values of a range:

require 'win32ole'
excel = WIN32OLE::new('excel.Application')
sheet = excel.Workbooks.Open('c:\excel.xls').Worksheets('worksheet')
sheet.Range('A1:A3').columns.each { |col| col.cells.each { |cell| puts cell['Value'] } }