h1. roo

h2. What

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

h2. Installing

<pre syntax="ruby">sudo gem install roo</pre>

h2. 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).

h2. Demonstration of usage

Supposed you have created this spreadsheet:

<img src="Screenshot-spreadsheet.png">

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

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

<pre>
<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
</code>
</pre>

which produces this output

<pre>
<code>
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
</code>
</pre>

With the newly written methods 

<code>first_column</code>,
<code>last_column</code>,
<code>first_row</code> and
<code>last_row</code>

you can change line 8 from

<pre>
<code>
  8 4.upto(1000) do |line|
</code>
</pre>

to

<pre>
<code>
  8 4.upto(oo.last_row) do |line|
</code>
</pre>

h3. Using MS-Excel spreadsheets

You can also access MS-Excel spreadsheat.

Replace Openoffice with 
<pre>
<code>oo = Excel.new("/home/tp/Desktop/simple_spreadsheet.xls").

</code>
</pre>

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,..).


h2. Documentation

"rdoc":rdoc/index.html

h2. Feature Requests / Bugs

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

h2. Forum

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

h2. Wiki

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

h2. License

This code is free to use under the terms of Ruby

h2. Contact

Comments are welcome. Send an email to "Thomas Preymesser":mailto:thopre@gmail.com.

h2. Thanks

* "Dr Nic Williams":http://rubyforge.org/users/nicwilliams/ for his wonderful gem '"newgem":http://rubyforge.org/projects/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