I've been thinking about writing a very simple spreadsheet in tcl for a few weeks now. It would store its data in [array get] format with cells named in the R1C1 style. The main issue is that I wanted a much more flexable expr command. I thought through various preprocesing schemes to use before calling expr to evaluate the contents of a cell, then I found this handy patch[L1 ]. You eighter like this or you don't.
Cell reference expansion is done with regsub, while the patched core allows procs to be used in expr.
array set Sheet {
R1C1 { 3 }
R1C2 { 5 + 4 }
R1C3 R1C4
R1C4 -6
R1C5 { min(4, R1C3) }
R1C6 { "TTText" }
R2C7 { sum(R1C1:R1C2, 100, R1C1:R1C2, 5) }
}
proc min { a b } {
return [expr $a < $b ? $a : $b]
}
proc sum { args } {
return [substexpr [join [join $args] +]]
}
proc range { r1 c1 rn cn } {
set list {}
for { set r $r1 } { $r <= $rn } { incr r } {
for { set c $c1 } { $c <= $cn } { incr c } {
lappend list R${r}C${c}
} }
return $list
}
proc substexpr { cell } {
global Cells
regsub -all -- {R([0-9]+)C([0-9]+):R([0-9]+)C([0-9]+)} $cell \
{range(\1, \2, \3, \4)} code
regsub -all -- {(R[0-9]+C[0-9]+)} $code {$Cells(\1)} code
if { [catch { set result [expr $code] } reply] } { puts $reply }
return $result
}
proc Sheet { name indx op } {
global Sheet
global Cells
if { [catch { set val $Sheet($indx) } reply] } {
set Cells($indx) 0
return
}
}
proc Cells { name indx op } {
global Cells
global Sheet
if { ![catch { set val $Cells($indx) } reply] } {
return
}
if { [catch { set Cells($indx) [substexpr $Sheet($indx)] }] } {
set Cells($indx) 0
}
}
array set Cells {}
trace variable Cells r Cells
trace variable Sheet r Sheet
foreach cell [array names Sheet] {
puts "$cell = $Cells($cell)"
}