rspreadsheet

Manipulating spreadsheets with Ruby. Read, modify, write or create new OpenDocument Spreadsheet files from ruby code.

The gem allows you to acces your file and modify any cell of it, without touching the rest of the file, which makes it compatible with all advanced features of ODS files (both existing and future ones). You do not have to worry if it supports feature XY, if it does not, it won't touch it. This itself makes it distinct from most of similar gems. Alhought this gem is still in beta stage I use in everyday and it works fine.

sheet = Rspreadsheet.open('./test.ods').worksheets(1)

sheet.B5 = 'Cat'
sheet.B5                    #=>  'Cat'
sheet['B', 5]               #=>  'Cat'
sheet['B', 5] = 'Mouse'
sheet.row(5).cell(2).value  #=>  'Mouse'

# working with cell format
sheet.cell(5,2).format.bold = true
sheet.cell(5,2).format.background_color = '#FF0000'

# calculate sum of cells in row
sheet.row(5).cellvalues.sum
sheet.row(5).cells.sum{ |cell| cell.value.to_f }

# or set formula to a cell
sheet.cell('A1').formula='=SUM(A2:A9)'

# insert company logo to the file
sheet.insert_image_to('10mm','15mm','company_logo.png')

# iterating over list of people and displaying the data
total = 0
sheet.rows.each do |row|
  puts "Sponsor #{row[1]} with email #{row[2]} has donated #{row[3]} USD."
  total += row[3].to_f
end
puts "Totally fundraised #{total} USD"

# saving file
book.save
book.save('different_filename.ods')

• Detailed Guide to using Rspreadsheet is available.
• More examples of lots of alternative syntax you can leave you comments and suggestions there

Add this line to your application's Gemfile:

gem 'rspreadsheet'

And then execute:

$ bundle

Or install it yourself as:

$ gem install rspreadsheet

If you get any error, have a look at troubleshooting

File a bug report. I appreciate any feedback and even if you can not help with code, it is interesting for me to hear from you. Different people have different needs and I want to hear about them.

Alhought this gem is still in beta stage I use in everyday and it works fine. Any suggestions regarding the syntax is very welcomed.

File a bug report or fork it and file a pull request. You do not have to contribute a code, failing tests are welcomed as well.

I would be glad to safe myself work, but surprisingly, there are not that many gems for OpenDocument spreadsheets. Most of them also look abandoned and inactive, or can only read or write spreadsheets, but not modify them. I have investigated these options (you might as well):

• ruby-ods - this one seems abandoned, or even as if it never really started
• rodf- this only serves as builder, it can not read existing files or modify them
• rods - this is pretty ok, but it has terrible syntax. I first thought of writing wrapper around it, but it turned to be not so easy. Also last commit is 2 years old.
• rubiod - this one is quite ok, the syntax is definitely better that in rods, but it seems also very abandoned. It does not support formats. This is a closest match.
• spreadsheet - this does not work with OpenDocument and even with Excel has issues in modyfying document. However since it is supposedly used, and has quite good syntax it might be inspirative. I also find the way this gem handles lazy writing of new rows to Spreadsheet object flawed, as well as strange accesibility of rows array object, which if assigned breaks consistency of sheet.
• roo can only read spreadsheets and not modify and write them back.

One of the main ideas is that the manipulation with OpenDOcument files should be forward compatible and as much current data preserving as possible. The parts of the file which are not needed for the change should not be changed. This is different to some of the mentioned gems, which generate the document from scratch, therefore any advanced features present in the original file which are not directly supported are lost.

• Advanced Guide how to use the gem
• Configuration of the gem
• Code documentation is hosted on rubydoc.info
• Changelog
• Documentation for developers containing ideas for future development and documentation on testing tools

The gem is available as open source under the terms of the GPL licence.