Roo Ruby Library

You can install Roo as a gem or add it to your Gemfile.

To install via command line:

$ gem install roo

To add to a Gemfile:

gem "roo", "~> 3.0.0"

For large Excel files, use each_row_streaming to avoid loading the entire file into memory. This yields Excelx::Cell objects.

Options:

• pad_cells: true: Includes blank cells as nil (otherwise they are skipped).
• offset: n: Skips the first $n$ rows.
• max_rows: n: Limits the number of rows yielded (after the offset).

Roo allows you to list, select, and iterate through sheets in a workbook.

• sheets: Returns an array of sheet names.
• sheet(name_or_index): Selects a specific sheet by name or 0-based index.
• default_sheet=: Sets the active sheet for subsequent calls.
• each_with_pagename: Iterates through all sheets, yielding both the name and the sheet object.

You can iterate through rows or parse them into structured data.

• each(hash_mapping): Iterates through rows, yielding a hash where keys are your provided labels and values are the column contents.
• parse(options): Returns an array of hashes.
  • headers: true: Includes the header row.
  • header_search: [Regexp]: Locates the header row using pattern matching.
  • clean: true: Strips control characters and whitespace.
  • id: /regex/: Map specific columns using regex.

Roo uses 1-based indexing for rows and columns (matching Excel).

• row(n): Returns the $n$-th row.
• column(n): Returns the $n$-th column.
• first_row, last_row, first_column, last_column: Returns boundary indices.
• cell(row, col): Accesses a cell using numeric indices or Excel-style coordinates (e.g., 'A', 1).
• a1: A shortcut for accessing the top-left cell.

Most methods accept an optional sheet argument to target a specific sheet without changing the default_sheet.

You can export the current default_sheet to various formats using to_csv, to_matrix, to_xml, or to_yaml.

For to_csv, you can specify a file object or a custom separator.

sheet.to_csv
sheet.to_csv(File.new("/path/to/output.csv"))
sheet.to_csv(separator: ":")

Use Roo::Spreadsheet.open to automatically detect the correct parser for your file. It accepts string paths or File instances. You can also explicitly provide the extension option if the file lacks one.

Supported formats include:

• Excel 2007 - 2013 (xlsx, xlsm)
• LibreOffice / OpenOffice.org (ods)
• CSV
• Excel 97/2002/2003 (requires roo-xls gem)
• Google Spreadsheets (requires roo-google gem)

require 'roo'

# Automatic detection
file_name = './new_prices.xlsx'
xlsx = Roo::Spreadsheet.open(file_name)

# Explicit extension
file_name = './rails_temp_upload'
xlsx = Roo::Spreadsheet.open(file_name, extension: :xlsx)

You can extract metadata such as comments and styling from cells.

• comment(row, col, sheet = nil): Returns the comment text for the specified cell, or nil if no comment exists.
• comments(sheet = nil): Returns an array of all comments in the sheet as [row, col, comment].
• font(row, col, sheet = nil): Returns a Roo::Font object representing the style of the cell (e.g., bold, italic, underline).

Roo::OpenOffice allows you to inspect the underlying formulas and data types of cells.

• formula(row, col, sheet = nil): Returns the formula string at the specified location. Returns nil if no formula exists.
• formula?(row, col, sheet = nil): Returns true if the cell contains a formula, false otherwise.
• formulas(sheet = nil): Returns an array of all formulas in the specified sheet as [row, col, formula].
• celltype(row, col, sheet = nil): Returns the type of the cell. Supported types include :float, :string, :date, :percentage, :formula, :time, and :datetime.