#####################################################################
    # Access procedures for SYLK files reading/writing
    #
    # (c) Copyright 2003 LMS Deutschland GmbH
    #                    Holger Zeinert
    #####################################################################
    # feel free to use without any warrenty
    #####################################################################
    #
    # internal data structure of a SYLK file is stored in an array:
    #
    #   SYLK(id)
    #       identification of the SYLK file
    #   SYLK(<x>,<y>,value)
    #       value of cell
    #   SYLK(<x>,<y>,formula)
    #       formula to calculate value
    #       (a cell needs a value; if a formula is given, but no value, the cell
    #       will not be stored into the SYLK file)
    #   SYLK(<x>,<y>,format)
    #       formatstring of cell
    #   SYLK(xMax), SYLK(yMax)
    #       Boundary X and Y, i.e. maximum column and row index
    #   SYLK(header)
    #       part of the header, which is not handled; this
    #       is used to write it unmodified (uninterpreted)
    #       back to the file
    #
    #####################################################################

    package provide Sylk 0.2


    #--------------------------------------------------------------------
    # parse X and Y values from a SYLK record
    #
    # Both values are optional. If a value is missing, the corresponding
    # value is not modified
    #--------------------------------------------------------------------
    proc parsePosition {record xName yName} {
        upvar $xName x
        upvar $yName y

        if {[regexp {;Y([0-9]+)(;|$)} $record dummy value]} {
            set y $value
        }
        if {[regexp {;X([0-9]+)(;|$)} $record dummy value]} {
            set x $value
        }
    }

    #--------------------------------------------------------------------
    # read a SYLK file into the internal structure
    #--------------------------------------------------------------------
    proc readSYLK {filename arrayName} {
        upvar $arrayName SYLK

        if {[catch {set fp [open $filename r]}]} {
            return 0
        }

        # store table boundaries
        set xMax 0
        set yMax 0

        # hold current postition
        set xAct 0
        set yAct 0

        while {![eof $fp]} {
            gets $fp line
            switch -regexp $line {
                ^ID {
                    # e.g. ID;PWXL;N;E
                    regexp {^ID;(.*)$} $line dummy value
                    set SYLK(id) $value
                }
                #^P -
                ^O {
                    # ignore some default formatting information,
                    # which I did not find documented
                }
                ^B {
                    # e.g. B;Y107;X105;D0 0 106 104
                    regexp {^B;Y([0-9]+);X([0-9]+)} $line dummy yMax xMax
                }
                ^F {
                    parsePosition $line xAct yAct
                    if {$xAct == 0 && $yAct == 0} {
                        # still header formats
                        lappend SYLK(header) $line
                    } else {
                        # cell formats
                        # Dec 2007 extend to handle ;f format ;p excel index
                        if {[regexp {;S([^;]*)(;|$)} $line dummy style]} {
                            set SYLK($xAct,$yAct,format) $style
                        }
                        if {[regexp {;P([^;]*)} $line dummy pfmt]} {
                            set SYLK($xAct,$yAct,pformindex) $pfmt
                        }
                        if {[regexp {;F([^;]*)} $line dummy fmt]} {
                            set SYLK($xAct,$yAct,numformat) $fmt
                        }
                    }
                }
                ^C {
                    # e.g. C;Y1;X1;K"Name"
                    parsePosition $line xAct yAct
                    if {[regexp {;K([^;]*)(;E|$)} $line dummy value]} {
                        regsub {"(.*)"} $value {\1} value
                        set SYLK($xAct,$yAct,value) $value
                    }

                    # with formula? E.g. C;K-1188;E(R[+1]C-0.5)*(R3C2-R2C2)/R4C2+R2C2
                    if {[regexp {;E(.*)(;|$)} $line dummy value]} {
        # [GWM] to convert relative positions in formulae
                        set value [substrelatives $value $xAct $yAct] ;# gwm 03.12.07
                        set SYLK($xAct,$yAct,formula) $value
                    }
                }
                ^NN { ;# gwm 23.12.07 name labels
                # eg NN;Naltitud;ER3C4
                    regsub "NN;N(.*);E.*" $line {\1} name
                    regsub ".*;E(.*)" $line {\1} cell
                    set SYLK(name,$name) $cell
                }
                ^E$ {
                    # SYLK file ends here
                    break
                }
                default {
                    # add to header (uninterpreted)
                    lappend SYLK(header) $line
                }
            }
        }
        close $fp

        set SYLK(xMax) $xMax
        set SYLK(yMax) $yMax

        return 1
    }

    #--------------------------------------------------------------------
    # write a SYLK file from the internal structure
    #--------------------------------------------------------------------
    proc writeSYLK {filename arrayName} {
        upvar $arrayName SYLK

        set fp [open $filename w]

        # write
        if {[info exists SYLK(id)]} {
            puts $fp "ID;$SYLK(id)"
        } else {
            puts $fp "ID;PWXL;N;E"
        }

        # boundary?
        if {[info exists SYLK(yMax)] && [info exists SYLK(yMax)]} {
            puts $fp "B;Y$SYLK(yMax);X$SYLK(xMax)"
        }

        # first, write header if existing
        if {[info exists SYLK(header)]} {
            foreach line $SYLK(header) {
                puts $fp $line
            }
        }

        # write data
        foreach idx [lsort -dictionary [array names SYLK *,*,value]] {
            regexp {([0-9]+),([0-9]+),} $idx dummy x y

            if {[info exists SYLK($x,$y,format)]} {
                set line "F;S$SYLK($x,$y,format);Y$y;X$x"
                puts $fp $line
            }

            if {[string is double $SYLK($x,$y,value)]} {
                set value $SYLK($x,$y,value)
            } else {
                set value "\"$SYLK($x,$y,value)\""
            }
            set line "C;Y$y;X$x;K$value"
            if {[info exists SYLK($x,$y,formula)]} {
                append line ";E$SYLK($x,$y,formula)"
            }
            puts $fp $line
        }

        # end-of-file marker
        puts $fp "E"

        close $fp
    }

    # GWM dec 2007 substitute relative references in formula
    # in Excel (at least) saved slk files use references such as R[-2]C[+2]
    # to refer to cells 2 above and 2 to right of current cell.
    proc substrelatives {formula xAct yAct} {
        # [GWM] converts relative positions in formulae
        # eg R[-1]C[+1] refers to cell row above, col to right.
        # eg RC[+1] R[-1]C refers to cell same row above, or same column as previous.
        # Assume no cell depends on itself to avoid circular references.
        # step 1 replace [+ or - N with [expression xy+/-N] exact cell is found.
        set f1 $formula
        set f1 [regsub -all {C\[(.\d*)]} $f1 {C[expr $xAct\1]}]
        set f1 [regsub -all {R\[(.\d*)]} $f1 {R[expr $yAct\1]}]
        set f1 [subst $f1]
        # now convert the 'same row' formula and 'same col' with bracket after
        # care needed to avoid changing any letter C eg in a name such as sCheme to sCNNheme
        set f1 [string map {RC R${yAct}C} [subst -nocommands {$f1}]]
        set f1 [regsub -all {(R.\d*C)(\D)} $f1 {\1${xAct}\2}]
        set f1 [regsub -all {(R.\d*C)\Z} $f1 {\1${xAct}}]

        if {[catch {subst $f1} errmsg]} {puts "Erro subst $formula:> $f1 - $errmsg"}
        # substitute the expressions on return
        return [subst $f1]
    }