tiny spreadsheet

Difference between version 32 and 33 - Previous - Next

'''tiny spreadsheet''', by [John Roll],  is a toy [spreadsheet] implementation



** See Also **

   [spreadsheet]:   



** Description **

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]`.  I thought through various preprocesing schemes to use before calling
`[expr]` to evaluate the contents of a cell, then I found this handy
[http://www.elf.org/etc/tcl-expr-patch.html%|%patch].  I
[http://cfa-www.harvard.edu/~john/tcl8.4.0-expr.patch%|%ported it] to 8.4.0.You either like this or you don't. - [TWu] 2025-01-20 referenced link is gone,
please use [https://web.archive.org/web/20041110203039/cfa-www.harvard.edu/~john/tcl8.4.0-expr.patch%|%WayBack-Machine].

Cell reference expansion is done with regsub, while the patched core allows
procs to be used in expr.  Literal cell values are stored in the Sheet array.
Displayed cell values (evaluated) are cached in the Cells array.  This could be
used with the tkTable widget or a simple array of entrys.  I'm going to try to
work out the refering and referenced cell sets for each cell and detect
circular references next. 

======
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 {}
catch {console show}

trace add variable Cells read Cells
trace add variable Sheet read Sheet

foreach cell [array names Sheet] {
    puts "$cell = $Cells($cell)"
}
======

<<categories>> Application | GUI | Spreadsheet