roo

Get Version 0.6.1

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("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

which produces this output


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

With the newly written methods

first_column, last_column, first_row and last_row

you can change line 8 from


  4.upto(12) do |line|


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

Cell Types

oo.celltype(row,col) returns the type of a cell. Currently these types will be returned:

:float
:string
:date
:percentage
:formula

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 true if there is a formula

oo.formula(row,col) returns the formula in this cell in a string variable (like ”=SUM”). You can do whatever you want with this expression. If there is no formula in this cell nil is returned.

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

oo.formulas returns all formulas in the selected spreadsheet in an array like this:


  [[1,2,"=SUM(.A1:.B1)"],
   [1,3,"=SIN(.C3)"],
   [1,4,"=COS(.D4)"]]

Each entry consists of the elements row, col, formula.

Note: oo.cell(row,col) is the same for ordinary cells and formulas. So you can use the computated value of a formula. If you have to distinguish if a cell is a formula use #formula?

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

YAML-Output

You can generate YAML-Output from your spreadsheet data. The method is called:

oo.to_yaml # => produces YAML output from the entire default spreadsheet oo.to_yaml({“myattribute1” => “myvalue1”, “myattribute2” => “myvalue2”) # => YAML output with additional attributes oo.to_yaml({..}, 2,10, 300,10) # => only the rectangle from row 2, column 10 to row 300, column 10 will be returned

If you omit one or more parameters the maximum boundaries of your spreadsheet will be used.

With the YAML output you can import your data in a Ruby on Rails application in a manner that spreadsheet data can accessed in a Rails application.

This is not limited to a Rails application – you can also do further evaluations with your data.

CSV-Output

You can generate output in csv-format with


oo.to_csv

to write to the standard output or


oo.to_csv("somefile.txt")

to write to a file.

Using MS-Excel spreadsheets

You can also access MS-Excel spreadsheat.

Replace Openoffice with

oo = Excel.new("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,..).~~

Formulas can only be handled in OpenOffice-spreadsheets.

Features in OpenOffice and Excel:

feature	Open Office	Excel
formulas	yes	no
to_yaml	yes	yes
celltype	:percentage	:float

The parseexcel-gem does not support the celltype ‘percentage’ but uses ‘float’ instead. This is not a big deal as you can also use ‘float’ to do calculate with these cells.

Old .sxc OpenOffice files are currently not supported – please load these files and save it as an “OpenDocument Spreadsheet (.ods)”.

Accessing Spreadsheet over the Web

You can even read openoffice or excel-spreadsheets from a http-address:

oo = Excel.new("http://www.somedomain.com/simple_spreadsheet.xls").
oo = Openoffice.new("http://www.somedomain.com/simple_spreadsheet.ods").

or a zipped file:

oo = Excel.new("http://www.somedomain.com/simple_spreadsheet.xls.zip",:zip).
oo = Openoffice.new("http://www.somedomain.com/simple_spreadsheet.ods.zip",:zip).

after working with a spreadsheet from the web you have to call

oo.remove_tmp

to delete the temporary local copy of the spreadsheet file. If you dont call this method you will have subdirectories names ‘oo_xxxxx’ which you can remove manually. Calling remove_tmp is not the best solution to clean temporary files – i will provide a better solution in the next releases.

Remote Access

You can even access your spreadsheet data from a remote machine via SOAP. The examples directory shows a little example how to do this. If you like, you can extend these functions or restrict the access to certain cells. Remote access with SOAP is nothing specific to roo, you can do this with every Rub object, but i thought it would nice to give an example what could be done with roo.

With Ruby on Rails

You can even use roo within your web application. On the project page there is an example named roorails.tgz, which shows a short example how you can display a spreadsheet table on a web page (see files app/controllers/spreadsheet_controller.rb and app/views/spreadsheet/index.rhtml).

To display this example:

unpack in any directory
cd roorails
ruby script/server
point your browser to http://localhost:3000/spreadsheet/

Where is it used?

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

The author of this gem uses roo for the generation of weekly reports which are (automatically) sent to his customers (Thomas Preymesser, Homepage: www.thopre.com, Blog: thopre.wordpress.com, email me: thopre@gmail.com)

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

Or you can write directly in the project wiki at http://roo.rubyforge.org/wiki/wiki.pl?Who’s_Using_Roo

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.

Documentation

rdoc

Only the Openoffice- and Excel-parts of this gem are currently working – the Google-Spreadheets are experimental and are currently NOT working. Don’t use this!

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

How to submit patches

Read the 8 steps for fixing other people’s code and for section 8b: Submit patch to Google Groups, use the Google Group above.

The trunk repository is svn://rubyforge.org/var/svn/gorank/trunk for anonymous access.

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 spreadsheet gem is used. My functions are a convenient wrapper around the functions of this gem
Dirk Huth fürs Testen unter Windows

Dr Nic, 8th October 2007
Theme extended from Paul Battley