Reading and writing Excel files using Ruby on a server without Excel installed

Question

I need to read and write (-> transform) Excel files on a Linux server, which of course does not have Excel installed. For Python there exists http://www.python-excel.org/. Is there something similar for Ruby? Processing of the latest Office format is probably not required. Just old xls files should be enough.

Answer 1

I agree with Gonzih, and I use roo fairly regularly. It allows me to read, write, and write using a template file. The project is fairly well documented on their site.

I always use something like:

input = Excel.new(path)
output = Array.new
input.default_sheet = input.sheets[sheet]
start.upto(input.last_row) do |row|
  output << input.row(row)
end

p output
=> a nested array representing the spreadsheat.

p output[0]
=> [row1_column_a, row1_column_b...]

to read a spreadsheet. note that the roo gem requires you to use Excelx.new instead of Excel.new if your file is a .xlsx.

to write you can:

book = Spreadsheet::Workbook.new
write_sheet = book.create_worksheet
row_num = 0
input.each do |row|
  write_sheet.row(row_num).replace row
  row_num +=1
end
book.write "/path/to/save/to.xls"

where input is an array structured just like output was