From: ben@...
Date: 2006-11-23T03:15:34+09:00
Subject: Streaming Spreadsheet in Ruby

(This posting can be ignored unless you live-or-die by a *nix command
line.)

        http://rubyforge.org/projects/sss/

On the other hand, have you ever wanted to do some quick math on a CSV
without waiting to launch Excel or Gnumeric? (Or you're logged in
remotely.) Maybe you'd like to print the average timestamp across all
lines in a log file? Or you might wish "cut" split columns with a
regexp, not just a delimiter?

I just GPL'ed a project of mine for doing spreadsheet style
calculations on the command line. It's probably easiest to explain with
an example. Say you have a sample file called "data.csv", which looks
like this:

    Year,Change,TOTAL
    2001,34.5,100.1
    2002,36.6,101.13
    2003,-11,90.5
    2004,0,95

And then you call the Streaming Spreadsheet like so:

    $ cat data.csv | sss 'b=sum(b)' 'c=sd(c)' 'c1="full total"'

You'll get this on standard out:

    Year    Change  full total
    2001    34.5    100.1
    2002    36.6    101.13
    2003    -11     90.5
    2004    0       95
            60.1
                    4.91642400531117

Note that "cell" C1 has been changed, and those last two lines added
with the sum of the B column, and the standard deviation of the C.
Since it's really just a tarted up "eval," more complicated stuff also
works:

    $ cat data.csv |sss 'd=(123**2.3).to_i'

On standard out:

    Year    Change  TOTAL
    2001    34.5    100.1
    2002    36.6    101.13
    2003    -11     90.5
    2004    0       95
                            64088

The script is a moderately-clever 350 lines of Ruby (IMHO). For
instance, it takes advantage of a quirk in parsing this sort of thing:

    eval("b32:c35")

This string of code ends up trying to call a method named "b32" with
one argument, the symbol ":c35". Perfect for returning a range of cells
via "def method_missing()"!

I hope it's useful for someone else right now, but the project is very
much still in beta.

        http://rubyforge.org/projects/sss/