roo

Get Version 0.2.2

What

This gem allows you to access the content of open-office and Excel spreadsheets (.ods/.xls).

Installing

sudo gem install roo

The basics

Currently only read-access is implemented.

Please note that the upper left cell of a table is numbered (1,1) or (1,’A’) (not 0,0).

Demonstration of usage

Supposed you have created this spreadsheet:

which includes the amount of work you have done for a customer.

You can now process this spreadsheet with the following sample code.


  1 require 'rubygems'
  2 require 'roo'
  3 
  4 HOURLY_RATE = 123.45
  5 
  6 oo = Openoffice.new("/home/tp/Desktop/simple_spreadsheet.ods")
  7 oo.default_sheet = oo.sheets.first
  8 4.upto(1000) do |line|
  9   # puts "line: "+line.to_s
 10   date       = oo.cell(line,'A')
 11   start_time = oo.cell(line,'B')
 12   start_time = 0.0 unless start_time
 13   end_time   = oo.cell(line,'C')
 14   end_time   = 0.0 unless end_time
 15   pause      = oo.cell(line,'D')
 16   pause      = 0.0 unless pause
 17   sum        = (end_time-start_time)-pause
 18   comment    = oo.cell(line,'F')
 19   amount     = sum * HOURLY_RATE
 20   if date
 21     puts "#{date}\t#{sum}\t#{amount}\t#{comment}" 
 22   end
 23 end

which produces this output


2007-05-07      1.75    216.0375        lokale lauff��hige Umgebung aufgesetzt, Datenbank eingerichtet, Repository erstellt und anf��nglichen Sourcecode eingestellt
2007-05-07      1.0     123.45  Userstories erfasst
2007-05-08      1.0     123.45  Test neuen User anlegen, Perioden (��berschneidend) anzulegen
2007-05-08      1.0     123.45  Bestandsaufnahme/Aufwandssch��tzung/Korrespondenz
2007-05-08      0.5     61.725  Functional Test Ueberschneidung
2007-05-14      0.5     61.725
2007-05-14      0.5     61.725
2007-05-15      1.0     123.45
2007-05-15      0.75    92.5875 G��stebuch
2007-05-15      0.5     61.725  G��stebuch
2007-05-15      0.5     61.725  G��stebuch
2007-05-16      1.0     123.45  G��stebuch
2007-05-17      0.25    30.8625 G��stebuch
2007-05-17      1.25    154.3125        G��stebuch
2007-05-18      3.0     370.35  G��stebuch
2007-05-18      1.75    216.0375        G��stebuch

With the newly written methods

first_column, last_column, first_row and last_row

you can change line 8 from


  8 4.upto(1000) do |line|


  8 4.upto(oo.last_row) do |line|

Using MS-Excel spreadsheets

You can also access MS-Excel spreadsheat.

Replace Openoffice with

oo = Excel.new("/home/tp/Desktop/simple_spreadsheet.xls").

all methode are the same for OpenOffice and Excel-objects. The only difference is the setting of the default-worksheet. OpenOffice uses the name of the worksheet whereas Excel needs the index of the worksheet (1,2,3,..).

Documentation

rdoc

Feature Requests / Bugs

Submit Feature Requests and bugs here: http://rubyforge.org/tracker/?group_id=3729

Forum

http://groups.google.com/group/ruby-roo

Wiki

http://roo.rubyforge.org/wiki/wiki.pl

License

This code is free to use under the terms of Ruby

Contact

Comments are welcome. Send an email to Thomas Preymesser.

Thanks

Dr Nic Williams for his wonderful gem ‘newgem’ which makes it very convenient to create, manage and publish Ruby gems
for the Excel-part the ‘parseexcel’ gem is used. My functions are a convenient wrapper around the functions of this gem

Dr Nic, 1st June 2007
Theme extended from Paul Battley