<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<link rel="stylesheet" href="stylesheets/screen.css" type="text/css" media="screen" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
roo
</title>
<script src="javascripts/rounded_corners_lite.inc.js" type="text/javascript"></script>
<style>
</style>
<script type="text/javascript">
window.onload = function() {
settings = {
tl: { radius: 10 },
tr: { radius: 10 },
bl: { radius: 10 },
br: { radius: 10 },
antiAlias: true,
autoPad: true,
validTags: ["div"]
}
var versionBox = new curvyCorners(settings, document.getElementById("version"));
versionBox.applyCornersToAll();
}
</script>
</head>
<body>
<div id="main">
<h1>roo</h1>
<div id="version" class="clickable" onclick='document.location = "http://rubyforge.org/projects/roo"; return false'>
Get Version
<a href="http://rubyforge.org/projects/roo" class="numbers">0.9.0</a>
</div>
<h2>What</h2>
<p>This gem allows you to access the content of</p>
<ul>
<li>Open-office spreadsheets (.ods)</li>
<li>Excel spreadsheets (.xls) and</li>
<li>Google (online) spreadsheets</li>
</ul>
<h2>Installing</h2>
<pre syntax="ruby">[sudo] gem install roo</pre>
<h2>The basics</h2>
<p>Currently only read-access is implemented.
Google spreadsheets can be read and written.</p>
<p>Please note that the upper left cell of a table is numbered (1,1) or (1,’A’) (not 0,0).</p>
<h2>Demonstration of usage</h2>
<p>Supposed you have created this spreadsheet:</p>
<p><img src="Screenshot-spreadsheet3.png"></p>
<p>which includes the amount of work you have done for a customer.</p>
<p>You can now process this spreadsheet with the following sample code.</p>
<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>
<p>which produces this output</p>
<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>
<p>With the methods</p>
<code>first_column</code>,
<code>last_column</code>,
<code>first_row</code> and
<code>last_row</code>
<p>you can change line 8 from</p>
<pre>
<code>
4.upto(12) do |line|
</code>
</pre>
<p>to</p>
<pre>
<code>
4.upto(oo.last_row) do |line|
</code>
</pre>
<h3>Cell Types</h3>
<p>oo.<strong>celltype</strong>(row,col) returns the type of a cell. Currently these types will be
returned:</p>
<ul>
<li>:float</li>
<li>:string</li>
<li>:date</li>
<li>:percentage</li>
<li>:formula</li>
</ul>
<h3>Write access</h3>
<p>Cells in a google spreadsheet can be read or written.</p>
<p>To write to a cell use the method call:</p>
<pre>
<code>
oo.set_value(row, col, value)
</code>
</pre>
<p>There is an example in the examples folder which illustrates write access.</p>
<h3>Formulas</h3>
<p>Formulas in Openoffice- and Google-Spreadsheets can be handled.</p>
<p>oo.<strong>celltype</strong>(row,col) returns :formula if there is a formula in this cell.</p>
<p>oo.<strong>formula?</strong>(row,col) returns true if there is a formula</p>
<p>oo.<strong>formula</strong>(row,col) returns the formula in this cell in a string variable (like ”=<acronym title="[.A1:.M13]">SUM</acronym>”). You can do whatever you want with this expression.
If there is no formula in this cell nil is returned.</p>
<p>oo.<strong>cell</strong>(row,col) returns the computed result of the formula (as it was saved in the file, no recalculation is done in this Gem).</p>
<p>oo.<strong>formulas</strong> returns all formulas in the selected spreadsheet in an array like this:</p>
<pre>
<code>
[[1,2,"=SUM(.A1:.B1)"],
[1,3,"=SIN(.C3)"],
[1,4,"=COS(.D4)"]]
</code>
</pre>
<p>Each entry consists of the elements row, col, formula.</p>
<p>Note: oo.<strong>cell</strong>(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 #<strong>formula</strong>?</p>
<p>Please note: formulas in Excel-Spreadsheets cannot be handled (this is another gem, see: “Thanks”)</p>
<h3><span class="caps">YAML</span>-Output</h3>
<p>You can generate <span class="caps">YAML</span>-Output from your spreadsheet data. The method is called:</p>
<p>oo.to_yaml # => produces <span class="caps">YAML</span> output from the entire default spreadsheet
oo.to_yaml({“myattribute1” => “myvalue1”, “myattribute2” => “myvalue2”)
# => <span class="caps">YAML</span> 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</p>
<p>If you omit one or more parameters the maximum boundaries of your spreadsheet will be used.</p>
<p>With the <span class="caps">YAML</span> output you can import your data in a Ruby on Rails application in a manner that spreadsheet data can accessed in a Rails application.</p>
<p>This is not limited to a Rails application – you can also do further evaluations with your data.</p>
<h3><span class="caps">CSV</span>-Output</h3>
<p>You can generate output in csv-format with</p>
<pre>
<code>
oo.to_csv
</code>
</pre>
<p>to write to the standard output or</p>
<pre>
<code>
oo.to_csv("somefile.txt")
</code>
</pre>
<p>to write to a file.</p>
<h3>Using MS-Excel spreadsheets</h3>
<p>You can also access MS-Excel spreadsheat.</p>
Replace Openoffice with
<pre>
<code>oo = Excel.new("simple_spreadsheet.xls").
</code>
</pre>
All methode are the same for OpenOffice-, Excel- and Google-objects.
<strike> 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,..).
</strike>
<p>Formulas can only be handled in OpenOffice- and Google-spreadsheets.</p>
<p>Features in OpenOffice/Excel/Google:</p>
<table class="border:1px solid black">
<tr>
<td>feature</td>
<td>Open Office</td>
<td>Excel</td>
<td>Google</td>
</tr>
<tr>
<td>formulas</td>
<td>yes</td>
<td>no</td>
<td>yes</td>
</tr>
<tr>
<td>celltype</td>
<td>:percentage</td>
<td>:float</td>
<td>:percentage</td>
</tr>
<tr>
<td>access</td>
<td>read-only</td>
<td>read-only</td>
<td>read and write</td>
</tr>
</table>
<p>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 calculations with these cells.</p>
<p>Old .sxc OpenOffice files are currently not supported – please load these files and save it as an “OpenDocument Spreadsheet (.ods)”.</p>
<p>Instead of a filename the Google#new method needs the ‘key’ of a Google-Spreadsheet. This key can be copied from the <span class="caps">URL</span> when you display a google spreadsheet with your browser.</p>
<p>If you want to use Google spreadsheets you must either have set the
environment variables ‘GOOGLE_MAIL’ and ‘GOOGLE_PASSWORD’ or you pass
the Google-name and -password to the Google#new method.</p>
<p>Setting these variables can be done if you add these lines to your
~/.bashrc (or similar setup file within other shells):</p>
<pre><code>export GOOGLE_MAIL="yourname@gmail.com"
export GOOGLE_PASSWORD="mysecretpassword"</code></pre>
<p>This gem does not check if you are allowed to access a specific google spreadsheet. If it’s not your own spreadsheet or you are not allowed to read or wwrite to a spreadsheet the behaviour is not defined (but it will not work ;-) ).</p>
<h3>Accessing Spreadsheet over the Web</h3>
You can even read openoffice or excel-spreadsheets from a http-address:
<pre>
<code>oo = Excel.new("http://www.somedomain.com/simple_spreadsheet.xls").
oo = Openoffice.new("http://www.somedomain.com/simple_spreadsheet.ods").
</code>
</pre>
or a zipped file:
<pre>
<code>oo = Excel.new("http://www.somedomain.com/simple_spreadsheet.xls.zip",:zip).
oo = Openoffice.new("http://www.somedomain.com/simple_spreadsheet.ods.zip",:zip).
</code>
</pre>
after working with a spreadsheet from the web you have to call
<pre>
<code>oo.remove_tmp
</code>
</pre>
<p>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.</p>
<h3>Remote Access</h3>
<p>You can even access your spreadsheet data from a remote machine via <span class="caps">SOAP</span>. 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 <span class="caps">SOAP</span> 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.</p>
<h3>Excel spreadsheets / Openoffice spreadsheets / Google spreadsheets with Ruby on Rails</h3>
<p>There is a simple helper method to display a spreadsheet at your application page:</p>
<ul>
<li>in your controller, add “require ‘roo’” and ”@rspreadsheet = Openoffice.new(“numbers1.ods”)” or
”@rspreadsheet = Openoffice.new(“http://www.somehost.com/data/numbers1.od s”)” to get access to your spreadsheet file</li>
<li>use it in any view of your application with ”<%= spreadsheet @rspreadsheet, [“Tabelle”] %><br/>” or one of the following formats</li>
</ul>
<p><img src="Roo_Spreadsheets_Rails.png"></p>
<h2>Where is it used?</h2>
<p>How do you use roo? What are you doing with roo?</p>
<ul>
<li>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)</li>
</ul>
<p>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).</p>
<p>Or you can write directly in the project wiki at <a href="http://roo.rubyforge.org/wiki/wiki.pl?Who's_Using_Roo">http://roo.rubyforge.org/wiki/wiki.pl?Who’s_Using_Roo</a></p>
<p>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.</p>
<h2>Documentation</h2>
<p><a href="rdoc/index.html">rdoc</a></p>
<strike>
Only the Openoffice- and Excel-parts of this gem are currently working – the Google-Spreadheets are experimental and are currently <span class="caps">NOT</span> working. Don’t use this!
</strike>
<p>Google spreadsheet can now be used.</p>
<h2>Feature Requests / Bugs</h2>
<p>Submit Feature Requests and bugs here: <a href="http://rubyforge.org/tracker/?group_id=3729">http://rubyforge.org/tracker/?group_id=3729</a></p>
<h2>Forum</h2>
<p>International Group:
<a href="http://groups.google.com/group/ruby-roo">http://groups.google.com/group/ruby-roo</a></p>
<p>Deutschsprachige Group:
<a href="http://groups.google.com/group/ruby-roo-de">http://groups.google.com/group/ruby-roo-de</a></p>
<h2>Wiki</h2>
<p><a href="http://roo.rubyforge.org/wiki/wiki.pl">http://roo.rubyforge.org/wiki/wiki.pl</a></p>
<h2>How to submit patches</h2>
<p>Read the <a href="http://drnicwilliams.com/2007/06/01/8-steps-for-fixing-other-peoples-code/">8 steps for fixing other people’s code</a> and for section <a href="http://drnicwilliams.com/2007/06/01/8-steps-for-fixing-other-peoples-code/#8b-google-groups">8b: Submit patch to Google Groups</a>, use the Google Group above.</p>
<p><!—
The trunk repository is <code>svn://rubyforge.org/var/svn/gorank/trunk</code> for anonymous access.
—></p>
<h2>License</h2>
<p>This code is free to use under the terms of Ruby</p>
<h2>Contact</h2>
<p>Comments are welcome. Send an email to <a href="mailto:thopre@gmail.com">Thomas Preymesser</a>.</p>
<h2>Thanks</h2>
<ul>
<li><a href="http://rubyforge.org/users/nicwilliams/">Dr Nic Williams</a> for his wonderful gem ‘<a href="http://rubyforge.org/projects/newgem/">newgem</a>’ which makes it very convenient to create, manage and publish Ruby gems</li>
<li>for the Excel-part the <a href="http://rubyforge.org/projects/spreadsheet/">spreadsheet</a> gem is used. My functions are a convenient wrapper around the functions of this gem</li>
<li>Dirk Huth fürs Testen unter Windows</li>
</ul>
<p class="coda">
<a href="mailto:drnicwilliams@gmail.com">Dr Nic</a>, 24th January 2008<br>
Theme extended from <a href="http://rb2js.rubyforge.org/">Paul Battley</a>
</p>
</div>
<!-- insert site tracking codes here, like Google Urchin -->
</body>
</html>