How to parse Excel spreadsheet in Ruby via Roo

Roo is a Ruby gem which can be used to parse most common spreadsheet formats.

Roo implements read access for all common spreadsheet types. It supports Excel 2007 - 2013 formats (xlsx, xlsm), LibreOffice / OpenOffice.org formats (ods), and CSV.

Installation

First, install Roo by running this command (assuming you have Ruby development environment configured):

gem install roo

The test excel spreadsheet

I have created a demo Excel spreadsheet as follows:

How to use Roo to parse Excel spreadsheet

Now we can use classes/methods provided in Roo to access Excel data. Below is my example to read the data from Excel spreadsheet.

# gem install roo

require 'roo'

xsl = Roo::Spreadsheet.open('./test1.xlsx')

xsl.sheets.each do |sheet_name|
  puts ''
  puts sheet_name
  puts '--------------'
  sheet = xsl.sheet(sheet_name)

  if !sheet.nil?
    last_row    = sheet.last_row
    last_column = sheet.last_column

    if !last_row.nil? and !last_column.nil?
      for row in 1..last_row
        for col in 1..last_column
          v = sheet.cell(row, col)

          if v.nil?
            puts "NIL"
          else
            puts "["+row.to_s+","+col.to_s+"]: " + sheet.cell(row, col).to_s
          end
        end
      end
    else
      puts 'Seems no data in sheet: ' + sheet_name
    end
  end
end

The output

To run the above script, just type:

ruby testexcel.rb

It outputs:

Conclusion

From the above demo, we can see it is very straightforward to use Roo to parse Excel spreadsheet. Excel 97, Excel 2002 XML, Excel 2003 XML formats, and Google spreadsheets can be parsed by using some extensions.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---