Updated 2014-01-02 05:59:25 by pooryorick

A little spreadsheet, by Richard Suchenwirth

See Also  edit


Description  edit

Richard Suchenwirth 2001-04-12: Here is a frame with gridded entry widgets. Those with sunken relief can be edited, and on every edit the column, row, and grand total (bottom right corner, marked with solid outline) get recomputed by a write trace.

A field whose content is not accepted as numeric by expr is ignored for the summing, so you can add row/column header text or leave fields blank.

In contrast to real spreadsheets, this is limited in functionality. A step further would be to allow specification of "formulas" for cells, so they get recomputed if one of their inputs change; file I/O, etc.
namespace eval spreadsheet {
    proc create {w args} {
        upvar #0 $w a
        array set a {-rows 3 -cols 2 -width 8} ;# default parameters
        array set a $args                      ;# maybe override defaults
        set a(last) $a(-rows),$a(-cols)        ;# keep grand total field
        trace var a w spreadsheet::redo
        frame $w
        for {set y 0} {$y<=$a(-rows)} {incr y} {
            set row [list]
            for {set x 0} {$x<=$a(-cols)} {incr x} {
                set e [entry $w.$y,$x -width $a(-width) \
                    -textvar ::$w\($y,$x) -just right]
                if {$x==$a(-cols) || $y==$a(-rows)} {
                    $e config -state disabled -background grey -relief flat
                lappend row $e
            eval grid $row -sticky news
        $e config -relief solid
        set w
    proc redo {_var el op} {
        upvar #0 $_var a
        if {$el!=$a(last)} {
            foreach {y x} [split $el ,] break
            if {$x!=""} {
                sum $_var $y,* $y,$a(-cols)
                sum $_var *,$x $a(-rows),$x                
            } ;# otherwise 'el' was not a cell index
        }     ;# prevent endless recalculation of grand total
    proc sum {_var pat target} {
        upvar #0 $_var a
        set sum 0
        set it "" ;# default if no addition succeeds
        foreach i [array names a $pat] {
            if {$i!=$target} {
                catch {set it [set sum [expr {$sum+$a($i)}]]}
            } ;# don't pull the total into the total
        set ::$_var\($target) $it
# test and usage example (NB: initial values can be set at creation time)
pack [spreadsheet::create .s 0,0 Header 1,0 47 2,1 11] -fill both

Anyone familiar with Tktable able to show what it would take to provide the above functionality using that extension? Not as an argument against this code- just to show an alternative.

AM 2007-03-08: Recently I came across one of my "experiments". I called it demand-driven computing, I think. The idea was to ask for the value of a variable if it was not yet known (it works via traces):
... register a question for variable size
... register a formula to compute the volume of a cube 
puts "The volume of a cube of size $size is: $volume" 

When the puts command is run, we will need the value of "size". So the question is asked. Then the variable "volume" needs to be determined - done via the registered formula. That technique could be the basis for a more refined spreadsheet. (Of course recursion needs to be taken care of in a gentle way ...)