Reading Excel in Ruby With Roo and Caxlsx for Writing

Ruby and Rails applications meet spreadsheets at the edges: a bulk import of products or users for an admin, a monthly export for finance. The pair of gems used most often is Roo for reading and Caxlsx for writing. Roo gives one interface over several formats and has a streaming mode; Caxlsx builds workbooks with styles and charts. This post covers the common operations, the type behavior that surprises people, memory and the handling of uploads in Rails. It is a spoke of reading and writing Excel from JavaScript, Java and C Sharp.

Install

# Gemfile
gem "roo"          # reads xlsx, xlsm, ods, csv
gem "roo-xls"      # add this to read the old xls format
gem "caxlsx"       # writes xlsx

Run bundle install. Roo depends on nokogiri and rubyzip; keep them updated, since parser and zip libraries get security fixes.

Open and read

require "roo"

xlsx = Roo::Spreadsheet.open("orders.xlsx")      # picks the class from the extension
puts xlsx.sheets.inspect                          # ["Orders", "Notes"]

sheet = xlsx.sheet("Orders")                      # selects the sheet
puts sheet.first_row, sheet.last_row, sheet.first_column, sheet.last_column

Roo::Spreadsheet.open returns Roo::Excelx for xlsx. Always check last_row, which can include trailing rows with formatting only. A file whose extension does not match its content raises an error; pass extension: :xlsx when you open a temp file without one (for an upload).

Read cell by cell with one-based row and column numbers, or use the header helpers:

rows = sheet.parse(headers: true, clean: true)
# => [{"order"=>"0001", "date"=>#<Date 2025-03-14>, "amount"=>100.0}, ...]

parse(headers: true) uses the first row as keys and returns an array of hashes, including the header row itself as the first hash in some versions, so inspect the first element. clean: true trims whitespace and removes empty rows. You can also pass a mapping of key to header pattern: sheet.parse(order: /order/i, amount: /amount/i), which finds columns by matching the header text, so a reordered file still works, and raises if the pattern is not found. That is a good defensive feature for imports.

For manual loops:

(2..sheet.last_row).each do |r|
  order  = sheet.cell(r, 1)
  date   = sheet.cell(r, 2)
  amount = sheet.cell(r, 4)
  next if [order, date, amount].all?(&:nil?)
  # ...
end

Types

Roo converts values to Ruby types by cell type.

sheet.celltype(row, col) gives the type, and sheet.formula(row, col) returns the formula text of a formula cell. Roo returns the cached value, not a recalculation. A whole number such as 100 may come back as 100.0 or 100 depending on the cell, so convert with Integer(value) or value.to_i when you need an integer, and check that it is whole. Identifiers stored as text stay strings; identifiers stored as numbers lose their leading zeros, as in any library. Dates in the 1904 system are handled by Roo.

Use sheet.excelx_value(row, col) for the raw stored string and sheet.excelx_format(row, col) for the number format when you need to see how Excel stored a cell.

Large files: streaming

The default mode loads the sheet. For big files use the streaming iterator:

xlsx = Roo::Excelx.new("big.xlsx")
xlsx.each_row_streaming(pad_cells: true, offset: 1) do |row|
  order, date, customer, amount = row.map(&:value)
  # process one row, then let it go
end

each_row_streaming reads the XML incrementally, so memory stays small. pad_cells: true fills in missing cells with empty cell objects so the positions line up, and offset: 1 skips the header. Each cell is an object with value, coordinate and type. In this mode you cannot jump around the sheet, and some helper methods (last_row on the whole workbook, parse) are not available. For the memory arithmetic see reading a 500 MB Excel file without running out of memory. A job that imports the rows into a database should insert in batches (insert_all in Rails) in a transaction.

Write with Caxlsx

require "axlsx"

package = Axlsx::Package.new
wb = package.workbook

styles = wb.styles
head  = styles.add_style(b: true, bg_color: "DDEBF7")
date  = styles.add_style(format_code: "yyyy-mm-dd")
money = styles.add_style(format_code: "#,##0.00")

wb.add_worksheet(name: "Report") do |sheet|
  sheet.add_row ["Order", "Date", "Amount"], style: head
  sheet.add_row ["0007", Date.new(2025, 3, 14), 1234.5],
                style: [nil, date, money],
                types: [:string, :date, :float]
  sheet.sheet_view.pane do |pane|
    pane.top_left_cell = "A2"; pane.state = :frozen; pane.y_split = 1
  end
  sheet.auto_filter = "A1:C2"
  sheet.column_widths 12, 12, 14
end

package.serialize("report.xlsx")

The types: array states each cell's type, so "0007" stays text rather than being converted by inference. Define styles once with add_style and refer to them by the returned index. Caxlsx can also generate charts and conditional formatting. In Rails, caxlsx_rails adds an .xlsx.axlsx template format, or you can build the package in a controller and send_data package.to_stream.read, filename: "report.xlsx", type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".

For very large exports, build rows from a database cursor (find_each) and write directly, mindful that Caxlsx keeps the workbook in memory; for a million rows write CSV, or use a streaming writer gem such as fast_excel or xlsxtream.

If text from users goes into cells, guard against formula injection: Excel treats a cell beginning with =, +, - or @ as a formula when typed, and an xlsx string cell that starts that way is stored as text, which is safe, while a cell whose type is inferred as a formula is not. Always state :string for user data, so it is stored as a string (formula injection in Excel exports explains the attack in CSV form).

Uploads in Rails

An admin import is a common feature and a common source of problems.

• Limit the size at the web server and in the controller (file.size > 10.megabytes).

• Check the content type and the first bytes (PK\x03\x04), not just the extension.

• Do not parse in the request. Save the upload and enqueue a job (Active Job, Sidekiq). The job reads the file, validates it and records results.

• Validate the header and every row, collecting errors with row numbers (row_number + 1), and import nothing if any row fails, or import all valid rows and report the rest. Choose and say which.

• Use a transaction, so a failure halfway rolls back.

• Show the user the outcome: rows imported, rows rejected, the list of problems.

• Never pass a filename from the user to the file system unchecked; use Active Storage or a generated name.

def import(path)
  xlsx = Roo::Excelx.new(path)
  errors = []
  Product.transaction do
    xlsx.each_row_streaming(offset: 1).with_index(2) do |row, line|
      sku, name, price = row.map(&:value)
      p = Product.new(sku: sku.to_s.strip, name: name.to_s.strip, price: price)
      errors << "Row #{line}: #{p.errors.full_messages.join(', ')}" unless p.save
    end
    raise ActiveRecord::Rollback if errors.any?
  end
  errors
end

Check the result

Compare the number of rows read with the sheet, and a total with Excel. Print cell_type for each column of the first rows. Test a date, a boolean, a formula cell, a number stored as text and an empty row. Open a Caxlsx file in Excel. Run the importer with a bad file (a PDF named .xlsx, a workbook with a missing column) and read the message.

When Roo and Caxlsx are not the right tools

The data is huge. Convert to CSV or Parquet first (converting a huge Excel file to CSV or Parquet).

You need exact Excel formula calculation. Neither calculates.

The consumer is another program. Send CSV or JSON, not a workbook.

You need to edit an existing workbook while keeping its formatting. Neither Roo nor Caxlsx modifies files in place; use a library that does, or fill a template that you generate.

See the Roo repository and the Caxlsx repository. To convert a small result to JSON, use the CSV to JSON converter.