h1. roo
h2. What
This gem allows you to access the content of open-office and Excel spreadsheets (.ods/.xls).
h2. Installing
sudo gem install roo
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:
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|
to
4.upto(oo.last_row) do |line|
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 true if there is a formula
oo.formula(row,col) returns the formula in this cell in a string variable (like "=SUM([.A1:.M13])"). 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, formual.
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")
h3. 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.
h3. 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:
table(border:1px solid black).
|feature|Open Office|Excel|
|default_sheet|as name|as number|
|formulas|yes|no|
|to_yaml|yes|yes|
Old .sxc OpenOffice files are currently not supported - please load these files and save as an "OpenDocument Spreadsheet (.ods)".
h2. 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":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.
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ürs Testen unter Windows