Updated 2016-01-30 16:52:26 by HJG ▲

bsg 2015-03-30: I often need a spreadsheet to perform some quick calculations that are just a bit more complicated than can be done in my head or with a calculator. Using excel can be heavy with startup time, it's tendency to want to take over the whole screen, and the insistence on saving the work to disk. Seeing the Tiny Excel-like app in plain Tcl/Tk, I decided to make it actually useful. Here is my progress so far:

Code edit

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

  package require Tk 8.6

  set Prog(Title)    "MiniSS"
  set Prog(Version)  "v0.12"
  set Prog(Date)     "2016-01-27"
  set Prog(Author)   "bsg, HaJo"
  set Prog(Contact)  "tcl_wiki@nospam.com";      # Todo
  set Prog(HomePage) "http://wiki.tcl.tk/41318"
  set Prog(Options)  "7 7"

  set Prog(About)    {MiniSS: a mini-spreadsheet with excel-like formulas
that evolved from a 'Tiny Excel-like app in plain Tcl/Tk'.
}

  # delete all data in worksheet
  # Todo: check if there is an easier way, e.g. using unset
  proc clear {} {
    global rows cols

    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 ::formula($columnName$row) [set ::$cell ""]
         }
    }
  }

  #
  # Main program builds GUI
  # optional arguments are ?#rows ?#columns??
  #
  proc main {args} {
    global rows cols Prog

    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
        }
    }
 
    set ::status "$Prog(Title) $Prog(Version)"
    set statusbar [ttk::label .lb -textvar status -anchor w ]
    incr row
    grid $statusbar -row $row -column 0 -columnspan $column
    grid $statusbar -sticky ew
  }

  ## Menu:

  proc m+ {head name {cmd ""}} {
  #: Menu-creator "m+" by R.Suchenwirth and DKF, 2006-08-24, see http://wiki.tcl.tk/16327
    # Uses the undocumented and unsupported feature ::tk::UnderlineAmpersand
    if {![winfo exists .m.m$head]} {
       foreach {l u} [::tk::UnderlineAmpersand $head] break
       .m add cascade -label $l -underline $u -menu [menu .m.m$head -tearoff 0]
    }
    if {[regexp ^-+$ $name]} {
       .m.m$head add separator
    } else {
       foreach {l u} [::tk::UnderlineAmpersand $name] break
       .m.m$head add command -label $l -underline $u -comm $cmd
    }
  }

  #
  # Define menu
  #
  proc initMenu {} {

    . configure -menu [menu .m]
  # m+ &File &Open          { Dummy "Open" }
  # m+ &File &Save          { Dummy "Save" }
  # m+ &File -----
  # m+ &File &New           { clear $rows $cols }
    m+ &File &New           { clear }
    m+ &File -----
    m+ &File &Demo1         { demo1; focus -f .cellB2 }
    m+ &File &Demo2         { demo2; focus -f .cellB2 }
    m+ &File &Demo3         { demo3; focus -f .cellB1 }
    m+ &File &Demo4         { demo4; focus -f .cellB3 }
    m+ &File &Demo5         { demo5; focus -f .cellB2 }
    m+ &File -----
    m+ &File &Exit            exit

    m+ Edit &EditCell       { Dummy "Edit"   }
    m+ Edit &Insert         { Dummy "Insert" }
    m+ Edit &Goto           { Dummy "Goto"   }

    m+ Options &ColumnWidth { Dummy "ColumnWidth" }
    m+ Options &Protection  { Dummy "Protection"  }

    m+ &Help &About           About
    m+ &Help &Help            About;  # Todo: Help
    m+ &Help -----
    m+ &Help &Debug         { console show }
  }

 #---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+

  proc About {} {
  #: Short info about the program / using tk_messageBox
    global Prog
    set txt    "$Prog(Title) $Prog(Version) - "
    append txt "$Prog(Date)\nby $Prog(Author) - $Prog(Contact)\n\n$Prog(About)"
    tk_messageBox -icon info -message $txt -title "About $Prog(Title) $Prog(Version)"
  }

  # Todo: Help()

  proc Dummy {txt} {
  #: Alert: "Function X not implemented yet"
    bell
    tk_messageBox -icon warning  -title "Demo" \
                  -message "Function '$txt' not implemented yet."
  }

 #---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+

 #
 # Input-Dialog: (from http://wiki.tcl.tk/8692 "A little value dialog" by R.Suchenwirth)
 #
 proc edit_val {string } {
    set w [toplevel .edit]
    wm resizable $w 0 0
    wm title $w "Edit cell"

    label  $w.l -text    $string
   #entry  $w.e -textvar $w -bg white
    entry  $w.e -textvar ::val -width 20
   #set done -1
    set old  $::val

    button $w.ok     -text "  OK  " -command {set ::done 0}
    button $w.clear  -text "Clear " -command "set ::val {}"
    button $w.cancel -text "Cancel" -command "set ::val $old; set ::done 1"

    bind   $w.e <Return> {set done 1}
    bind   $w.e <Escape> "$w.cancel invoke"

    grid $w.l  -    -        -sticky news
    grid $w.e  -    -        -sticky news
   #grid $w.ok  $w.clear  $w.cancel
    grid $w.cancel  $w.clear  $w.ok

    raise $w .
    focus $w.e

    vwait ::done
    destroy $w
    return $::done
 }

 #---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+

 #
 # 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 }

    puts "Current cell: $row $column"
    set ::status  "Current cell: $column $row";  # Todo: convert columns to A..G
 }

 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}

 bind TEntry <Key-Escape> {bell}
 bind TEntry <Key-Return> {
      set val "abc";   # Todo: get value from current cell
      set res [edit_val "Enter value or formula:"]
      puts "Edit:$res/$val"
 }

 #
 # 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"}
 }

 # Demos:

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

 proc demo1 {} {
    clear
    wm title . "MiniSpreadSheet - Demo1"
    set ::A1 "**Demo1**"

    set ::C1 "use TAB- &"
    set ::D1 "cursor-keys"
    set ::E1 "to move"
    set ::F1 "around"

    set ::C2 "ENTER-key"
    set ::D2 "to edit a cell"

    set ::C3 "ESC-key"
    set ::D3 "to abort or"
    set ::E3 "undo"

    set ::A3 "                +";  # Todo: right-align
    set spc  "               "
    set ::A4 "$spc +"
    set ::B2 "17"
    set ::B3 "4"
    set ::B4 "0.25"
    set ::B5 "===="
    set ::A6 "Sum:"
    set ::A7 "$spc *"
    set ::formula(B6) "B2+B3+B4";  recalc B6

    set ::C6 "Fieldnames"
    set ::D6 "must be"
    set ::E6 "uppercase !"

    set ::B7 "0.1"
    set ::C7 "       ="
    set ::formula(D7) "B7*B6";     recalc D7

    return "B2"
 }
 proc demo2 {} {
    clear
    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:";       setFo B4 "B2+B3"
    set ::A5 ".16";         setFo B5 "B4*A5"
    set ::A6 " Total:";     setFo B6 "B4+B5"
 }
 proc demo3 {} {
    clear
    wm title . "MiniSpreadSheet - Demo3"
    set ::G1 "**Demo3**"
    set ::A1 "Article#001"; set ::B1 "3";    set ::C1  "50";    setFo D1 "B1*C1"
    set ::A2 "Article#002"; set ::B2 "1";    set ::C2 "123.75"; setFo D2 "B2*C2"
    set ::A3 "Article#003"; set ::B3 "5";    set ::C3  "25.25"; setFo D3 "B3*C3"
    set ::A4 " Sum:";       setFo B4 "B1+B2+B3";
                           #setFo D4 "D1+D2+D3"
                            setFo D4 "sum(D1..D3)"
    set ::A5 " Tax:";       set ::B5 ".16";  setFo D5 "D4*B5"
    set ::C6 " Total:";                      setFo D6 "D4+D5"
 }
 proc demo4 {} {
    clear
    wm title . "MiniSpreadSheet - Demo4"
    set ::A1 "**Demo4**"
 # Todo - Testdata for sum()
 }

 proc demo5 {} {
    clear
    wm title . "MiniSpreadSheet - Demo5"
    set ::G1 "**Demo5**"

    set ::A1 "Date:"
    set ::B1 "2016week01"

    set ::A2 "Day:";   set ::B2 "Mo";    set ::C2 "Di";    set ::D2 "Mi";    set ::E2 "Do";    set ::F2 "Fr";
    set ::A3 "Start:"; set ::B3 " 8.0";  set ::C3 " 7.75"; set ::D3 " 8.0";  set ::E3 " 8.25"; set ::F3 " 8.0";
    set ::A4 "End:";   set ::B4 "17.0";  set ::C4 "17.25"; set ::D4 "16.0";  set ::E4 "16.25"; set ::F4 "15.0";
    set ::A5 "Hours:"; setFo B5 "B4-B3"; setFo C5 "C4-C3"; setFo D5 "D4-D3"; setFo E5 "E4-E3"; setFo F5 "F4-F3";
    set ::A6 "Pause:"; set ::B6 "1.0";   set ::C6 "0.5";   set ::D6 "1.0";   set ::E6 "0.5";   set ::F6 "0";   
    set ::A7 "Total:"; setFo B7 "B5-B6"; setFo C7 "C5-C6"; setFo D7 "D5-D6"; setFo E7 "E5-E6"; setFo F7 "F4-F3";

    setFo G3 "sum(B3..F3)";        # ?? bug in sum() ??
    setFo G4 "sum(B4..F4)";        # ??
    setFo G5 "sum(B5..F5)";        # ??

    setFo G5 "sum(B6..F6)";        # ??
    setFo G6 "B6+C6+D6+E6+F6"

    setFo G2 "sum(B7..F7)";        # ??
    setFo G7 "B7+C7+D7+E7+F7"

 }

 main {*}$argv;  focus -f .cellA1
 initMenu

 demo1; focus -f .cellB2
#set cell1 [demo1]; focus -f .cell$cell1
#demo2; focus -f .cellB2
#demo3; focus -f .cellB1
#demo4; focus -f .cellB2
#demo5; focus -f .cellB3

Comments edit

AMG: Please considering using the [sum] and [average] procs presented at the bottom of [1]. These are more efficient, avoid expr injection attacks, and work correctly when averaging integers.

AMG: To anyone who may be curious, the code has been updated per my suggestion.

HJG 2016-01-27 - Added some simple demos, from TinyExcel.
This format for the data looks much more suitable for a possible load/save-procedure.

Nice work so far, and this would be my wishlist:

  • Cursor-movement: don't leave cell while editing a formula.
    Maybe by moving cell-edit to a popup-dialog:
  • Edit-dialog:
    • Return = open/exit edit-dialog, then recalc
    • Esc = Undo: exit edit-dialog without changing the cell
    • checkboxes for the format-options
  • Formatting:
    • Text: left-align, center, right-align, (e.g. ^center, >right), repeat (e.g. "*=")
    • Numbers: decimal places / EE / maybe currency
    • different background-colors for text, numbers, formulas
    • Option to protect formulas (i.e. make readonly), perhaps for the whole worksheet.
    • adjustable column-width
  • Load/save
  • Some more functions, e.g. min(), max(), date&time

HJG 2016-01-28: A first step to 'mainstream' this program: I added a menu, edit-dialog, statusbar and About-box.

A Help-dialog could be done just like the About-box, but something with a text-widget would be better. See also demo1, for a 'hands-on introduction.

The canned demos can be loaded from the file-menu (as a sort-of-replacement for load).

Demo5 is an attempt to provide something small but useful out-of-the-box, a timesheet for one week. It uses decimal time for now.

Maybe we can come up with some more of such small worksheets.

Category ApplicationCategory GUICategory Spreadsheet