Tiny Excel-like app in plain Tcl/Tk
dbohdan 2015-03-25: The following code is a Tk spreadsheet inspired, title included, by http://jsfiddle.net/ondras/hYfN3/ . Halfway through writing it I found the tiny spreadsheet and lifted the idea of tracing variable reads to trigger recalculation from it. One important difference from the tiny spreadsheet is that TEAIPTT does not require a Tcl expr patch.
Feature comparison with the JavaScript version
| ✓ Under 30 lines of plain Tcl/Tk (rather than vanilla JS*) |
| ✓ Libraries used: none |
| ✓ Excel-like syntax (formulas start with "=") |
| ✓ Support for arbitrary expressions (=A1+B2*C3) |
| ✓ Circular reference prevention |
| ✗ Automatic persistence |
* Lines are assumed to be up to 100 characters long. The reason is that the under-30-line JavaScript spreadsheet uses lines longer than 80 characters, 100 is the next common limit, and using lines of unlimited length would feel like cheating.
The smallest JS spreadsheet is currently this one: http://xem.github.io/sheet/ (227 bytes for the minimal version, 267b if we add automatic persistence).
Screenshot
Code
#!/usr/bin/wish
package require Tk 8.6-10 ;# needed for lmap
foreach row {0 1 2 3 4 5 6} {
grid {*}[lmap {column} {"" A B C D E F} {
set cell $column$row
if {$column eq "" || $row == 0} {
::ttk::label .label$cell -text [expr {$row ? $row : $column}]
} else {
set ::formula($cell) [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]
}
}]
}
proc recalc {cell args} {
catch {set ::$cell [uplevel #0 [list \
expr [regsub -all {([A-F][1-6])} $::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
}
# --- That's all, folks! ---# The following code is optional. It sets up canned worksheets for demonstration
# purposes.
proc setFormula {cell value} {
set ::formula($cell) $value;
recalc $cell
}
proc demo1 {} {
wm title . "Demo 1"
set ::A1 "** Demo 1 **"
set ::C1 "use TAB-key"
set ::D1 "or mouse to"
set ::E1 "move around"
set ::A3 " +"
set ::B4 "=="
set ::B2 "17"
set ::B3 "4"
set ::formula(B5) "B2+B3"; recalc B5
set ::C5 "Fieldnames"
set ::D5 "uppercase !"
}
proc demo2 {} {
wm title . "Demo 2"
set ::A1 "** Demo 2 **"
set ::A2 "Art.1"; set ::B2 "55.5"
set ::A3 "Art.2"; 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 . "Demo 3"
set ::F1 "** Demo 3 **"
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"
}
# Uncomment only one of the following lines to select the demo you want:
demo1; focus -f .cellB2
#demo2; focus -f .cellB2
#demo3; focus -f .cellB1Discussion
AMG: Exchanged the row and column foreach lines so that the tab key moves across rather than down.
dbohdan 2015-03-28: xem, it is neat just how much functionality your spreadsheet packs in 0x00-0xE2 but it looks like it only recalculates formulas one step at a time. I.e., if you have A1 set to =B1 and B1 set to =C1 and then put the number 5 into C1 it will take two onblur events before the value propagates back to A1. This also means you can create stable infinite loops that will cycle values and NaNs between cells.
I wonder how much you could golf the Tcl/Tk version if you try to optimize for character count rather than line count and relax the requirement on recalc. Any takers? A page like Golfed spreadsheet would be appropriate for that. I also wonder what would be the minimal overhead of adding full recalculation with circular reference prevention to xem's JS version.
aspect 2015-03-29: Fixed deletion of formulas and reduced by using lmap, taking advantage of -textvariable to eliminate the set-cell proc and eliminating the test in recalc because expr will error on an empty expression. Without set-cell, some explicit selection management is required on focusin, reducing the saving. This might be improved with some careful event binding, rather than -validate.
bsg 2015-03-30: I've taken this Tiny spreadsheet and tried to make it generally useful as an utility tool: MINISS - Mini Spread Sheet.
HJG 2016-01-27 - An empty spreadsheet doesn't look too impressive, so I added some demos.
Nothing fancy, just simple calculations like price * amount, tax, sum.
Also, the previous version without lmap could run with tcl 8.5.
So, if you wanted to pack this into a Starkit/Tclkit, you could use an older and smaller runtime.
2016-01-28 - Adapting those demos for MINISS, I found a better, shorter way to code them.
Note: that formula "sum(D1..D3)" in demo3 needs some code that was present in the old version 16 of this page , just before the fork to MINISS.
gold 5/29/2021. Made no changes to above code (by me), but added pix, buttons, and console cosmetics.
# Uncomment only one of the following lines to select the demo you want:
# demo1; focus -f .cellB2
# demo2; focus -f .cellB2
# demo3; focus -f .cellB1
# add cosmetics below to bottom of file
# added statements for math ops and TCLLIB library for Console
package require math::numtheory
package require math::constants
package require math::trig
package require math
frame .frame -relief flat -bg aquamarine4
frame .buttons -bg aquamarine4
proc self_help {} {
set msg "Tiny TexTCeL Spreadsheet
from TCL ,
;# self help listing
field names are upper case
use mouse to move to cells"
tk_messageBox -title "Self_Help" -message $msg }
proc about {} {
set msg "Tiny TexTCeL Spreadsheet
from TCL "
tk_messageBox -title "About" -message $msg }
proc clearx {} {
foreach i {1 2 3 4 5 6 7 8 } { break
} }
::ttk::button .calculator -text "Solve" -command {demo1 ;wm title . "Tiny TexTCeL Spreadsheet" }
::ttk::button .test2 -text "Testcase1" -command {demo1 ;wm title . "Tiny TexTCeL Spreadsheet" }
::ttk::button .test3 -text "Testcase2" -command {demo2 ;wm title . "Tiny TexTCeL Spreadsheet" }
::ttk::button .test4 -text "Testcase3" -command {demo3 ;wm title . "Tiny TexTCeL Spreadsheet" }
::ttk::button .clearallx -text clear -command {clearx }
::ttk::button .self_help -text self_help -command {self_help}
::ttk::button .about -text about -command { about }
::ttk::button .cons -text report -command { about }
::ttk::button .exit -text exit -command {exit}
pack .calculator -in .buttons -side top -padx 10 -pady 5
pack .clearallx .cons .self_help .about .exit .test4 .test3 .test2 -side bottom -in .buttons
grid .frame .buttons -sticky ns -pady {0 10}
. configure -background palegreen -highlightcolor brown -relief raised -border 30
wm title . "Tiny TexTCeL Spreadsheet"
. configure -background palegreen -highlightcolor brown -relief raised -border 30
namespace path {::tcl::mathop ::tcl::mathfunc math::numtheory math::trig math::constants }
console show
console eval {.console config -bg palegreen}
console eval {.console config -font {fixed 20 bold}}
console eval {wm geometry . 40x20}
console eval {wm title . " Tiny TexTCeL Spreadsheet Console Output & Self_Help, screen grab and paste from console 2 to texteditor"}
console eval {. configure -background orange -highlightcolor brown -relief raised -border 30}
console eval { proc self_helpx {} {
set msg "in TCL, large black type on green
from TCL,
self help listing
Conventional text editor formulas grabbed
from internet screens can be pasted
into green console "
tk_messageBox -title "self_helpxx" -message $msg } }
console eval {.menubar.help add command -label Self_help -command self_helpx }