A bigger spreadsheet

GWM A fairly complete spreadsheet I am calling TXl. Allows formulae to be edited, user extendable functions etc. Once run, click on cell c6 and modify the formula to 22/7.0 to reevaluate the linked cells c7-c9. And so on. Note the special way of entering the functions mean and sum which are converted to the internal format.

This code has now been extended TekSelto read a SYLK format file and handle more complex data.

  package require Tk
  # TekSel A spreadsheet with options to set/change formulae.
  # The basic data structure of a cell is an array:
  # a Tk label with a formula, a value, a list of cells referred to by this cell.
  # An entry area for editing formula.
  # The list of traces causes referring cells to reevaluate.
    proc changeformula {vn newv} { ;# change variable vn to new formula
        uplevel #0 set ${vn}(formula) [subst -nocommands $newv]
        foreach trac [uplevel #0 set ${vn}(links)] { # unset traces on cell.
                eval trace remove $trac
        }
        uplevel #0 set ${vn}(links) \"\" ;# clear record of cell links
        set form [uplevel #0 subst $${vn}(formula)] ;# the formula
        foreach var [regsub -all {[-+/\*()]} $form { }] { ;# set watch on each variable
                # if variable in formula changes then change this result (& so on)
                if {[uplevel #0 info vars $var]!=""} { 
                   set cmd "variable ::$var {write} \"evaluate $vn\""
                   eval trace add $cmd
                   uplevel #0 lappend ${vn}(links) [list $cmd]
                }
        }
        evaluate $vn
    }
    proc evaluate {vn args} {
        set vtop [uplevel #0 subst $${vn}(formula)]
        foreach var [uplevel #0 set vlist] { ;# replace variables by formula
                set vtop [regsub -all $var $vtop [uplevel #0 set ${var}(value)]]
        }
        # evaluate the formula from [A little calculator]
        catch {expr [string map {/ *1.0/} [uplevel #0 expr $vtop]]} res
        uplevel #0 set ${vn}(value) [list $res]
    return $res
  }
  proc showformula {where tick var cell} {  ;# where is the input entry area. cell is where to copy formula from
    # copy formula from var to change formula area.
    $where delete 0 end; $where insert 0 [uplevel #0 set ${var}(formula)]
    # Change effect of Update button to send "where" to var
    $tick config -command "changeformula $var \[$where get\]"
  }
  #
  # create the cells. Let us call them A1, A2... b1,b2 etc like many other spreadsheets
  set vlist {} ;# list of cell names
  pack [frame .enterform]
  pack [button .enterform.tick -text "Update" -relief raised -width 8] -side left
  pack [entry .enterform.input -width 72] -side left
  pack [frame .rowtitle]
  pack [label .rowtitle.about -text "TXl" -width 4] -side left
  foreach column {a b c d e f g h} {
        pack [label .rowtitle.$column -text $column -width 16] -side left
  }
  foreach row {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15} {;#
    pack [frame .row$row]
    pack [label .row$row.r$row -text $row -width 4] -side left
    foreach column {a b c d e f g h} {
        set vname $column$row
        variable $vname
        lappend vlist $vname
        set ${vname}(formula) 0.0
        set ${vname}(value) 0.0
        set ${vname}(links) {}
        pack [label .row$row.$column -textvar ${vname}(value) -relief raised -width 16] -side left
        bind .row$row.$column <ButtonRelease> "showformula .enterform.input \
                .enterform.tick $vname .row$row.$column"
    }
  }
  foreach row {1 2 3 4} {
    foreach column {a b c d e} {
        set cprev [format %c [expr [scan $column %c]-1]]
        set cnext [format %c [expr [scan $column %c]+1]]
        set vname $column$row
        if {$row==1} { set f 1
        } elseif ($row==4) {set f 2
        } else {
                if {$column=="a"||$column=="e"} {set f 1
                } else {
                        set f ".25*(${cprev}$row+${cnext}$row"
                        append f "+${column}[expr {$row-1}]+${column}[expr {$row+1}])"
               }
        }
        changeformula $vname $f
    }
  }
  # some math functions:
  proc pi {} { return  3.1415926535897}
  changeformula a10 "[pi]"
  changeformula c6 "[pi]/4"
  changeformula b7 "sin(1)"
  changeformula b8 "sin(.1)"
  changeformula b9 "sin(.01)"
  changeformula c7 "sin(c6)"
  changeformula c8 "sin(c6*.1)"
  changeformula c9 "sin(c6*.01)"
  # Examples of extending the functions available:
  proc range {cstart cend} { ;# return list of variables in range start to end
        set col [string index $cstart 0]
        while {$col <= [string index $cend 0]} {
                set row [string index $cstart 1]
                while {$row <=[string index $cend 1]} {
                        lappend range $col$row
                        incr row
                }
                set col [format %c [expr [scan $col %c]+1]]
        }
        return $range
  }
  proc sum {cstart cend} { # sum converts to sum of cell names.
        foreach vn [range $cstart $cend] { append res "+$vn"}
        return "(${res})"
  }
  proc mean {cstart cend} { # mean sum of cell names divide by Ncells.
        set n 0
        foreach vn [range $cstart $cend] { append res "+$vn" ;  incr n}
        return "((${res})/[expr double($n)])"
  }
  changeformula a7 "[mean e1 e4]"
  changeformula a8 "[sum e1 e4]"
  changeformula a9 "[mean a1 e4]"

gold added in house pix bigger spreadsheet on tcl wiki screenshot png