A little spreadsheet

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 (8 march 2007) 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 ...)