Build Status Code Climate Coverage Status Gem Version

Roo implements read access for all common spreadsheet types. It can handle: * Excel 2007 - 2013 formats (xlsx, xlsm) * LibreOffice / formats (ods) * CSV * Excel 97, Excel 2002 XML, and Excel 2003 XML formats when using the roo-xls gem (xls, xml) * Google spreadsheets with read/write access when using roo-google


Install as a gem

$ gem install roo

Or add it to your Gemfile

gem 'roo', '~> 2.1.0'


Opening a spreadsheet

require 'roo'

xlsx ='./new_prices.xlsx')
xlsx ="./new_prices.xlsx")

# Use the extension option if the extension is ambiguous.
xlsx ='./rails_temp_upload', extension: :xlsx)
# => Returns basic info about the spreadsheet file can accept both paths and File instances.

Working with sheets

# => ['Info', 'Sheet 2', 'Sheet 3']   # an Array of sheet names in the workbook


# Set the last sheet as the default sheet.
ods.default_sheet = ods.sheets.last
ods.default_sheet = s.sheets[3]
ods.default_sheet = 'Sheet 3'

# Iterate through each sheet
ods.each_with_pagename do |name, sheet|
  p sheet.row(1)

Accessing rows and columns

Roo uses Excel's numbering for rows, columns and cells, so 1 is the first index, not 0 as it is in an Array

# returns the first row of the spreadsheet.

# returns the first column of the spreadsheet.

Almost all methods have an optional argument sheet. If this parameter is omitted, the default_sheet will be used.

# => 1             # the number of the first row
# => 42            # the number of the last row
# => 1             # the number of the first column
# => 10            # the number of the last column

Accessing cells

You can access the top-left cell in the following ways


# Access the second sheet's top-left cell.

Querying a spreadsheet

Use each to iterate over each row.

If each is given a hash with the names of some columns, then each will generate a hash with the columns supplied for each row.

sheet.each(id: 'ID', name: 'FULL_NAME') do |hash|
  puts hash.inspect
  # => { id: 1, name: 'John Smith' }

Use sheet.parse to return an array of rows. Column names can be a String or a Regexp.

sheet.parse(:id => /UPC|SKU/,:qty => /ATS*\sATP\s*QTY\z/)
# => [{:upc => 727880013358, :qty => 12}, ...]

Use the :header_search option to locate the header row and assign the header names.

sheet.parse(header_search: [/UPC*SKU/,/ATS*\sATP\s*QTY\z/])

Use the :clean option to strip out control characters and surrounding white space.

sheet.parse(:clean => true)

Exporting spreadsheets

Roo has the ability to export sheets using the following formats. It will only export the default_sheet.


Excel (xlsx and xlsm) Support

Stream rows from an Excelx spreadsheet.

xlsx ="./test_data/test_small.xlsx")
xlsx.each_row_streaming do |row|
  puts row.inspect # Array of Excelx::Cell objects

By default blank cells will be excluded from the array. To keep them, use the option pad_cells = true. (They will be set to nil in the array) ruby xlsx.each_row_streaming(pad_cells: true) do |row| puts row.inspect # Array of Excelx::Cell objects end

To stream only some of the rows, you can use the max_rows and offsetoptions. ruby xlsx.each_row_streaming(offset: 1) do |row| # Will exclude first (inevitably header) row puts row.inspect # Array of Excelx::Cell objects end

xlsx.each_row_streaming(max_rows: 3) do |row| # Will yield 4 rows (it's automatically incremented by 1) after the supplied offset.
  puts row.inspect # Array of Excelx::Cell objects

Iterate over each row

xlsx.each_row do |row|

Roo::Excelx also provides these helpful methods.

xlsx.excelx_type(3, 'C')
# => :numeric_or_formula

xlsx.cell(3, 'C')
# => 600000383.0

# => '0600000383'

Roo::Excelx can access celltype, comments, font information, formulas, hyperlinks and labels.

xlsx.comment(1,1, ods.sheets[-1])
xlsx.formula('A', 2)

OpenOffice / LibreOffice Support

Roo::OpenOffice has support for encrypted OpenOffice spreadsheets.

# Load an encrypted OpenOffice Spreadsheet
ods ="myspreadsheet.ods", :password => "password")

Roo::OpenOffice can access celltype, comments, font information, formulas and labels.

# => :percentage

ods.comment(1,1, ods.sheets[-1])

# => false

ods.formula('A', 2)

CSV Support

# Load a CSV file
s ="mycsv.csv")

Because Roo uses the standard CSV library, you can use options available to that library to parse csv files. You can pass options using the csv_options key.

For instance, you can load tab-delimited files (.tsv), and you can use a particular encoding when opening the file.

# Load a tab-delimited csv
s ="mytsv.tsv", csv_options: {col_sep: "\t"})

# Load a csv with an explicit encoding
s ="mycsv.csv", csv_options: {encoding: Encoding::ISO_8859_1})

Upgrading from Roo 1.13.x

If you use .xls or Google spreadsheets, you will need to install roo-xls or roo-google to continue using that functionality.

Roo's public methods have stayed relatively consistent between 1.13.x and 2.0.0, but please check the Changelog to better understand the changes made since 1.13.x.



  1. Fork it ([my-github-username]/roo/fork )
  2. Create your feature branch (git checkout -b my-new-feature)
  3. Commit your changes (git commit -am 'My new feature')
  4. Push to the branch (git push origin my-new-feature)
  5. Create a new Pull Request


If you find an issue, please create a gist and refer to it in an issue (sample gist). Here are some instructions for creating such a gist.

  1. Create a gist with code that creates the error.
  2. Clone the gist repo locally, add a stripped down version of the offending spreadsheet to the gist repo, and push the gist's changes master.
  3. Paste the gist url here.


Roo uses an MIT License