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-spreadsheet3.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("simple_spreadsheet.ods")
  7 oo.default_sheet = oo.sheets.first
  8 4.upto(12) do |line|
  9   date       = oo.cell(line,'A')
 10   start_time = oo.cell(line,'B')
 11   end_time   = oo.cell(line,'C')
 12   pause      = oo.cell(line,'D')
 13   sum        = (end_time - start_time) - pause
 14   comment    = oo.cell(line,'F')
 15   amount     = sum * HOURLY_RATE
 16   if date
 17     puts "#{date}\t#{sum}\t#{amount}\t#{comment}"
 18   end
 19 end
</code>
</pre>

which produces this output

<pre>
<code>
2007-05-07      1.0     123.45  Task 1
2007-05-07      1.75    216.0375        Task 1
2007-05-07      1.0     123.45  Task 2
2007-05-08      1.0     123.45  Task 2
2007-05-08      1.0     123.45  Task 3
2007-05-08      0.5     61.725  Task 3
2007-05-14      0.5     61.725  Task 3
2007-05-14      0.5     61.725  Task 3
2007-05-15      1.0     123.45  Task 3
</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>
  4.upto(12) do |line|
</code>
</pre>

to

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

h3. Formulas

Formulas in Openoffice-Spreadsheets can be handled. 

oo.celltype(row,col) returns :formula if there is a formula in this cell.

oo.formula(row,col) returns the formula in this cell in a string variable.
If there is no formula in this cell nil is return

oo.cell(row,col) returns the computed result of the formula (as it was saved in the file, no recalculation is done in this Gem).

Please note: formulas in Excel-Spreadsheets cannot be handled (this is another gem, see: "Thanks")

h3. Using MS-Excel spreadsheets

You can also access MS-Excel spreadsheat.

Replace Openoffice with 
<pre>
<code>oo = Excel.new("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,..).

Formulas cannot be handled in Excel-spreadsheets.


h2. Where is it used?

How do you use roo? What are you doing with roo?

If you have an interesting application where you use roo then write me a short description of your project and i will publish it here (write, if your email-address should be published or not). 

If you don't want to publish the details you can also write me an email and state, that it should not be published - i am just curious to hear, where it is used.

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 "spreadsheet':http://rubyforge.org/projects/spreadsheet/ gem is used. My functions are a convenient wrapper around the functions of this gem
* Dirk Huth f&uuml;rs Testen unter Windows