Updated 2014-03-23 21:22:31 by EMJ

tiny spreadsheet, by John Roll, is a toy spreadsheet implementation

See Also  edit

spreadsheet

Description  edit

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 patch. I ported it to 8.4.0. You either like this or you don't.

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 variable Cells r Cells
trace variable Sheet r Sheet

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