#!/bin/sh
# \
exec /usr/local/bin/wish8.6 $0 $*

package require Tk 8.6

#
# Main program builds GUI.
# Optional arguments are ?#rows ?#columns??
#
proc main {args} {
    lassign {7 7} rows cols
    if {[llength $args] > 0} { set rows [lindex $args 0] }
    if {[llength $args] > 1} { set cols [lindex $args 1] }

    for {set row 0} {$row <= $rows} {incr row} {
        set columnName ""
        for {set column 0} {$column <= $cols} {incr column; inca columnName} {
            set cell $columnName$row
            set widget [if {$column == 0 || $row == 0} {
                ::ttk::label .label$cell -text [expr {$row ? $row : $columnName}]
            } else {
                set ::formula($columnName$row) [set ::$cell ""]
                trace add variable ::$cell read recalc
                ::ttk::entry .cell$cell -textvar ::$cell -width 10 -validate focus \
                    -validatecommand [list ::reveal-formula $cell %V %s]
            }]
            grid $widget -row $row -column $column
        }
    }
}

#
# Program the Arrow keys to move about the sheet
#
proc cell-arrow {dir w args} {
    set column [dict get [grid info $w] -column]
    set row    [dict get [grid info $w] -row]
    switch $dir {
        Left  { lassign [list [incr column -1] -row $row]       index axis axis-value }
        Right { lassign [list [incr column  1] -row $row]       index axis axis-value }
        Up    { lassign [list [incr row -1   ] -column $column] index axis axis-value }
        Down  { lassign [list [incr row  1   ] -column $column] index axis axis-value }
    }
    set x [lindex [lsort -dictionary [grid slaves . $axis ${axis-value}]] [expr {$index -1}]]
    if {[string match {*cell*} $x]} { focus $x }
}

bind TEntry <Key-Left>  {cell-arrow %K %W}
bind TEntry <Key-Right> {cell-arrow %K %W}
bind TEntry <Key-Up>    {cell-arrow %K %W}
bind TEntry <Key-Down>  {cell-arrow %K %W}

#
# inca - increment letter (column) sequence
#   A -> B -> C ... AA -> AB ... AZ -> BA -> BB 
#
set atab [split {ABCDEFGHIJKLMNOPQRSTUVWXYZ} {}]
proc inca {avar {by 1}} {
    upvar $avar a
    if {$a eq ""} {set a A; return}
    global atab
    set i $by
    foreach d [lreverse [split [string toupper $a] {}]] {
        set nxt [expr {([lsearch $atab $d] + $i) % 26}]
        set i [expr {($i>0 && !$nxt) ? 1 : 0}]
        lappend n [lindex $atab $nxt]
    }
    if {$i>0} { lappend n [lindex $atab 0] }
    set a [join [lreverse $n] ""]
}

proc recalc {cell args} {
    if {$::formula($cell) ne ""} {
        catch {set ::$cell [uplevel #0 [list \
               expr [regsub -all {([A-Z]+[1-9])} [expand-range $::formula($cell)] {$\1}]]]}
    }
}
proc reveal-formula {cell event value} {
    if {$event eq "focusin"} {
        if {$::formula($cell) ne ""} { set ::$cell =$::formula($cell) }
                .cell$cell selection range 0 end
                .cell$cell icursor end
    } else { ;# focusout
        if {![regexp {^=(.*)} $value -> ::formula($cell)]} { set ::formula($cell) "" }
        foreach otherCell [array names ::formula] { recalc $otherCell }
    }
    return 1
}

proc expand-range {arg} {
    while {[regexp {(([A-Z]+)([0-9]+)\.\.([A-Z]+)([0-9]+))} $arg -> pat leftcol leftrow rghtcol rghtrow]} {
        set l [list]
        for {set col $leftcol} {$col <= $rghtcol} {inca col} {
            for {set row $leftrow} {$row <= $rghtrow} {incr row} { lappend l ${leftcol}${row} }
        }
        set arg [regsub $pat $arg [join $l ,]]
    }
    return $arg
}

# Add excel like functions here:
proc ::tcl::mathfunc::sum {args} {
    ::tcl::mathop::+ {*}$args
}

proc ::tcl::mathfunc::average {args} {
    expr {[llength $args] ? [::tcl::mathop::+ {*}$args] / double([llength $args]) : "!ERR"}
}

# Demo:

 proc setFormula {cell value} {
     set ::formula($cell) $value;
     recalc $cell
 }

 proc demo1 {} {
    wm title . "MiniSpreadSheet - Demo1"
    set ::A1 "**Demo1**"
    set ::C1 "use TAB- &"
    set ::D1 "cursor-keys"
    set ::E1 "to move"
    set ::F1 "around"
    set ::A3 "               +"
    set ::B4 "=="
    set ::B2 "17"
    set ::B3 "4"
    set ::formula(B5) "B2+B3";  recalc B5
    set ::C5 "Fieldnames"
    set ::D5 "must be"
    set ::E5 "uppercase !"
 }
 proc demo2 {} {
    wm title . "MiniSpreadSheet - Demo2"
    set ::A1 "**Demo2**"
    set ::A2 "Article#001"; set      ::B2 "55.5"
    set ::A3 "Article#002"; set      ::B3 "44.5"
    set ::A4 " Sum:";       setFormula B4 "B2+B3"
    set ::A5 ".16";         setFormula B5 "B4*A5"
    set ::A6 " Total:";     setFormula B6 "B4+B5" 
 }
 proc demo3 {} {
    wm title . "MiniSpreadSheet - Demo3"
    set ::G1 "**Demo3**"
    set ::A1 "Article#001"; set      ::B1 "3";    set ::C1  "50";    setFormula D1 "B1*C1"
    set ::A2 "Article#002"; set      ::B2 "1";    set ::C2 "123.75"; setFormula D2 "B2*C2"
    set ::A3 "Article#003"; set      ::B3 "5";    set ::C3  "25.25"; setFormula D3 "B3*C3"
    set ::A4 " Sum:";       setFormula B4 "B1+B2+B3";  
                           #setFormula D4 "D1+D2+D3"
                            setFormula D4 "sum(D1..D3)"
    set ::A5 " Tax:";       set      ::B5 ".16";  setFormula D5 "D4*B5"
    set ::C6 " Total:";                           setFormula D6 "D4+D5"
 }

 main {*}$argv;  # focus -f .

 demo1; focus -f .cellB2
#demo2; focus -f .cellB2
#demo3; focus -f .cellB1