'''excel xml''' is a package that creates [Excel] [XML] files compatible with
Office 2003.

This is the package itself:


======
package provide excel 1.1

namespace eval excel:: {
    variable workbooks 0
    variable workbooksArray
    variable workSheets
    variable workSheetsArray
    variable styles
    variable columnDefault
    variable data
    variable rowCounter 
    variable columnsIndex
    array set columnsIndex [list A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10 K 11 L 12 M 13 N 14 O 15 P 16 Q 17 R 18 S 19 T 20 U 21 V 22 W 23 X 24 Y 25 Z 26 AA 27 AB 28 AC 29 AD 30 AE 31]
}

proc excel::createWorkbook {} {
#
# @comment  create a workbook pointer
# @result         pointer to created workbook
#
    incr excel::workbooks
    set workbookName workbook$excel::workbooks
    set excel::workbooksArray($workbookName) 1
    return $workbookName
}

proc excel::createWorkSheet {workbook name} {
#
# @comment  create a worksheet pointer
# @argument workbook pointer to a workbook
# @argument name name of the worksheet
# @result         pointer to a worksheet
#
    variable data
    if {[info exists excel::workbooksArray($workbook)]} {
        if {![info exists ::excel::workSheets($workbook)]} {
            set excel::workSheets($workbook) 1
        } else {
            incr excel::workSheets($workbook)
        }
        set workSheetName workSheet[string range ${workbook} 8 end].$excel::workSheets($workbook)
        set data(workSheet,$::excel::workSheets($workbook),name) $name
        set data(workSheet,$::excel::workSheets($workbook)) $workSheetName
        set data(workSheet,$workSheetName) 1
        set excel::rowCounter($workSheetName) 0
        return $workSheetName
    } else {
        error "$workbook is not a valid workbook"
    }
}

proc excel::createStyle {workbook args} {
#
# @comment  create an excel style
# @argument workbook pointer to a workbook
# @argument args argument list
# @result style pointer
#
    variable data
    if {[info exists excel::styles($workbook)]} {
    incr excel::styles($workbook)
    } else {
    set excel::styles($workbook) 2
    }
    set styleName s$excel::styles($workbook)
    foreach {name value} $args {
# check that name is valid
        if {[lsearch "-font -fontcolor -background -bold" $name]==-1} {
            error "style option $name option is not supported"
        }
        set data($workbook,styles,$styleName,$name) $value
    }
    return $styleName

}

proc excel::setColumnType {workSheet columnIndex type} {
#
# @comment define a column type
# @argument workSheet pointer to a workSheet
# @argument columnIndex index of column
# @argument type of column
# @result  column type is changed
#
    variable data
    _checkSpreadSheet $workSheet
    set data($workSheet,row,$columnIndex,type) [string totitle $type]
}

proc excel::_checkSpreadSheet {workSheet} {
    variable data
    if {![info exists data(workSheet,$workSheet)]} {
        error "$workSheet is not a valid workSheet"
    }
}

proc excel::addRow {workSheet columnsDataList} {
#
# @comment add row to excel worksheet
# @argument workSheet pointer to a workSheet
# @argument args list of variables
# @result row id
#
    variable data
    set i 0
    incr excel::rowCounter($workSheet)
    set data($workSheet,$excel::rowCounter($workSheet),length) [llength $columnsDataList]
    foreach arg $columnsDataList {
        incr i
        if {[llength $arg]>1} { 
            if {[lsearch [list String Number] [lindex $arg 1]]!=-1} {

                if {[llength $arg]>2} {
                    set data($workSheet,$excel::rowCounter($workSheet),$i,style) [lindex $arg end]
                }
                set data($workSheet,$excel::rowCounter($workSheet),$i,type) [string totitle [lindex $arg end-1]]
                set value [lindex $arg 0]
            } else {
                set value $arg
            }
        } else {
            set value $arg
        }
        set data($workSheet,$excel::rowCounter($workSheet),$i,data) $value
    }
        return row$excel::rowCounter($workSheet)
}

proc excel::asXml {workbook} {
#
# @comment returns excel workbook as xml
# @argument workbook pointer to a workbook
# @result workbook xml representation
#
    variable data
    variable rowCounter
    set xml "<?xml version='1.0'?>\
<?mso-application progid='Excel.Sheet'?>\
<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'\
xmlns:o='urn:schemas-microsoft-com:office:office'\
xmlns:x='urn:schemas-microsoft-com:office:excel'\
xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'\
xmlns:html='http://www.w3.org/TR/REC-html40'>\
<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>\
<Author>Ashrait</Author>\
<Created>[clock format [clock seconds] -format {%Y-%m-%dT%H:%M:%SZ}]</Created>\
<Company>Xor Technologies</Company>\
</DocumentProperties>\
<Styles>\
 <Style ss:ID='Default' ss:Name='Normal'>\
   <Alignment ss:Vertical='Bottom'/>\
   <Font x:CharSet='177'/>\
  </Style>\
  <Style ss:ID='s21'>\
   <Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>\
   <Font x:Family='Swiss' ss:Color='#000080' ss:Bold='1'/>\
   <Interior ss:Color='#99CCFF' ss:Pattern='Solid'/>\
  </Style>\
  <Style ss:ID='s22'>\
   <Alignment ss:Vertical='Bottom'/>\
   <Borders>\
    <Border ss:Position='Top' ss:LineStyle='Double' ss:Weight='3'/>\
   </Borders>\
   <Font x:CharSet='177' x:Family='Swiss' ss:Bold='1'/>\
  </Style>"
if {[info exists excel::styles($workbook)]} {
    for {set d 2} {$d<=$excel::styles($workbook)} {incr d} {
        set styleName s$d
        append xml "<Style ss:ID='$styleName'><Alignment ss:Vertical='Bottom'/>"
        if {[info exists data($workbook,styles,$styleName,-font)] || [info exists data($workbook,styles,$styleName,-fontcolor)]} {
            append xml "<Font x:CharSet='177'"
            if {[info exists data($workbook,styles,$styleName,-font)]} {
                append xml " ss:FontName='$data($workbook,styles,$styleName,-font)'"
            }
            if {[info exists data($workbook,styles,$styleName,-fontcolor)]} {
                append xml " ss:Color='$data($workbook,styles,$styleName,-fontcolor)'"
            }
            if {[info exists data($workbook,styles,$styleName,-bold)]} {
                append xml " ss:Bold='1'"
            }
            append xml "/>"
        }
        if {[info exists data($workbook,styles,$styleName,-background)]} {
            append xml "<Interior ss:Color='$data($workbook,styles,$styleName,-background)' ss:Pattern='Solid'/>"
        }
        append xml "</Style>"
    }
}
    append xml "</Styles>"
    for {set d 1} {$d<=$excel::workSheets($workbook)} {incr d} {
        append xml "<Worksheet ss:Name='$excel::data(workSheet,$d,name)'>\
<Table x:FullColumns='1' x:FullRows='1'>"
        set workSheet $excel::data(workSheet,$d)
        for {set i 1} {$i<=$excel::rowCounter($workSheet)} {incr i} {
            append xml "<Row>"
            for {set j 1} {$j<=$data($workSheet,$i,length)} {incr j} {

                set dataValue $data($workSheet,$i,$j,data)
                if {[string index $dataValue 0]=="="} {
                    append xml "<Cell ss:Formula='$dataValue'" 
                    set dataValue ""
                    set numeric 1
                } else {
                    if {[string is double -strict $dataValue]} {
                        set numeric 1
                    } else {
                        set numeric 0
                    }
                    append xml "<Cell"
                }
                if {[info exists data($workSheet,$i,$j,type)]} {
                    set type $data($workSheet,$i,$j,type)
                } else {
                    if {[info exists data($workSheet,row,$j,type)]} {
                    set type $data($workSheet,row,$j,type)
                    } elseif {$numeric} {
                        set type "Number"
                    } else {
                        set type "String"
                    }
                }
                if {[info exists data($workSheet,$i,$j,style)]} {
                    append xml " ss:StyleID='$data($workSheet,$i,$j,style)'>"
                } else {
                    append xml ">"
                }
                append xml "<Data ss:Type='$type'>$dataValue</Data></Cell>"
            }
            append xml "</Row>"
        }
        append xml "</Table></Worksheet>"
    }
    append xml "</Workbook>"
}

proc excel::deleteWorkbook {workbook} {
#
# @comment  delete a workbook pointer
# @argument workbook pointer to a workbook
# @result undecoded string
#
    variable data
    for {set d 1} {$d<=$excel::workSheets($workbook)} {incr d} {
        array unset data $d
            set workSheet $excel::data(workSheet,$d)
        for {set i 1} {$i<=$excel::rowCounter($workSheet)} {incr i} {
            array unset data $workSheet*
        }
        unset $excel::rowCounter($workSheet)
    }
}

proc excel::addTitle {workSheet columnsDataList} {
#
# @comment  delete a workbook pointer
# @argument workbook pointer to a workbook
# @result undecoded string
#
    foreach arg $columnsDataList {
        lappend newArgs  [list $arg String s21]
    }
    addRow $workSheet $newArgs
}

proc excel::addTotal {workSheet columnsDataList} {
#
# @comment  delete a workbook pointer
# @argument workbook pointer to a workbook
# @result undecoded string
#
    foreach arg $columnsDataList {
        lappend newArgs  [list $arg String s22]
    }
    addRow $workSheet  $newArgs
}

proc excel::setCell {workSheet row column value} {
#
# @comment  delete a workbook pointer
# @argument workbook pointer to a workbook
# @result undecoded string
#
    variable data
        set data($workSheet,$row,$excel::columnsIndex($column),data) $value
}

proc excel::getCurrentRow {workSheet} {
#
# @comment  delete a workbook pointer
# @argument workbook pointer to a workbook
# @result undecoded string
#
    return $excel::rowCounter($workSheet)
}
======
   
Example:

======
# create workbook
set book [excel::createWorkbook]
# create worksheets
set worksheet [excel::createWorkSheet $book "test"]
set worksheet2 [excel::createWorkSheet $book "hello"]
# define default row types
excel::setColumnType $worksheet2 1 number
excel::setColumnType $worksheet2 2 number
excel::setColumnType $worksheet2 3 number
# create style
set style [excel::createStyle $book -font Arial -background black -fontcolor red]

# add simple row
excel::addRow $worksheet [list 2 1 2 3]
# add row with different style
excel::addRow $worksheet [list 2 1 2 [list 4 number $style]]
# add row with formula
excel::addRow $worksheet [list 1 2 {=sum(rc[-2]+rc[-1])} ]

# get the excel as xml
set xml [excel::asXml $book]
======

----

[escargo] 2005-07-13: After correcting a couple of typographical errors and
reordering the code so I could read it with [wish-reaper], I added a few lines
to produce an output file.

======
set fd [open "excel[clock seconds].xml" "w"]
puts $fd $xml
close $fd
======

[yahalom] 2008-08-05: updated to latest version

----

[AElfwine] 2008-09-15: 

   * Adding a missing close parenthesys in an array
   * Correcting examples : adding [list ..]

----

[leprechau] 2008-10-27

This package has some severe problems dealing with multiple simultaneous
workbooks/worksheets.  Consider the following simple scenerio:

======
set book [excel::createWorkbook]
set book2 [excel::createWorkbook]
set ws [excel::createWorkSheet $book "hello there"]
set ws2 [excel::createWorkSheet $book2 testing]
excel::addRow $ws [list 1 2 3 4 5 6]
excel::addRow $ws2 [list 4 5 6 7 8 9]
set fid [open book1.xml w]
puts $fid [excel::asXml $book]; close $fid
set fid [open book2.xml w]
puts $fid [excel::asXml $book2]; close $fid
excel::deleteWorkbook $book
excel::deleteWorkbook $book2
======

Firstly, both book1.xml and book2.xml will contain the row '4 5 6 7 8 9' and
the first row is lost completely.  Secondly, it will throw error when you try
to delete the second book with a nonexistent rowConter element.  In addition to
that, the information in the 'data' array is not cleaned completely from either
book.  I am going to give this package a re-write to allow for multiple
simultaneous books and worksheets and will post the results here.

----

[mjjensen] 2012-09-12 12:49:55:

Just used this in anger - here are my patches ...

======
--- excel.tcl-dist        2012-08-26 04:02:59.806443173 +1000
+++ excel.tcl        2012-09-08 11:31:47.011915254 +1000
@@ -63,7 +63,7 @@
     set styleName s$excel::styles($workbook)
     foreach {name value} $args {
 # check that name is valid
-        if {[lsearch "-font -fontcolor -background -bold" $name]==-1} {
+        if {[lsearch "-font -fontcolor -background -bold -numfmt" $name]==-1} {
             error "style option $name option is not supported"
         }
         set data($workbook,styles,$styleName,$name) $value
@@ -105,7 +105,7 @@
     foreach arg $columnsDataList {
         incr i
         if {[llength $arg]>1} {
-            if {[lsearch [list String Number] [lindex $arg 1]]!=-1} {
+            if {[lsearch [list String Number DateTime] [lindex $arg 1]]!=-1} {
 
                 if {[llength $arg]>2} {
                     set data($workSheet,$excel::rowCounter($workSheet),$i,style) [lindex $arg end]
@@ -122,6 +122,30 @@
     }
         return row$excel::rowCounter($workSheet)
 }
+
+proc excel::addRowLists {workSheet columnsDataList} {
+#
+# @comment add row to excel worksheet
+# @argument workSheet pointer to a workSheet
+# @argument args list of variables
+# @result row id
+#
+    variable data
+    set i 0
+    incr excel::rowCounter($workSheet)
+    set data($workSheet,$excel::rowCounter($workSheet),length) [llength $columnsDataList]
+    foreach arg $columnsDataList {
+        incr i
+        set data($workSheet,$excel::rowCounter($workSheet),$i,data) [lindex $arg 0]
+        if {[llength $arg] > 1} {
+            set data($workSheet,$excel::rowCounter($workSheet),$i,type) [string totitle [lindex $arg 1]]
+            if {[llength $arg] > 2} {
+                set data($workSheet,$excel::rowCounter($workSheet),$i,style) [lindex $arg 2]
+            }
+        }
+    }
+    return row$excel::rowCounter($workSheet)
+}
 proc excel::asXml {workbook} {
 #
 # @comment returns excel workbook as xml
@@ -162,7 +186,12 @@
 if {[info exists excel::styles($workbook)]} {
     for {set d 2} {$d<=$excel::styles($workbook)} {incr d} {
         set styleName s$d
-        append xml "<Style ss:ID='$styleName'><Alignment ss:Vertical='Bottom'/>"
+        append xml "<Style ss:ID='$styleName'>"
+        if {[info exists data($workbook,styles,$styleName,-numfmt)]} {
+            set numfmt [regsub -all {\-} $data($workbook,styles,$styleName,-numfmt) {\-}]
+            append xml "<NumberFormat ss:Format='$numfmt'/>"
+        }
+        append xml "<Alignment ss:Vertical='Bottom'/>"
         if {[info exists data($workbook,styles,$styleName,-font)] || [info exists data($workbook,styles,$styleName,-fontcolor)]} {
             append xml "<Font x:CharSet='177'"
             if {[info exists data($workbook,styles,$styleName,-font)]} {
@@ -207,8 +236,8 @@
                 if {[info exists data($workSheet,$i,$j,type)]} {
                     set type $data($workSheet,$i,$j,type)
                 } else {
-                    if {[info exists data($workSheet,row,$j,type)]} {
-                    set type $data($workSheet,row,$j,type)
+                    if {[info exists data($workSheet,row,$i,type)]} {
+                    set type $data($workSheet,row,$i,type)
                     } elseif {$numeric} {
                         set type "Number"
                     } else {
@@ -220,6 +249,9 @@
                 } else {
                     append xml ">"
                 }
+                if {$type == "Datetime"} {
+                    set type "DateTime"
+                }
                 append xml "<Data ss:Type='$type'>$dataValue</Data></Cell>"
             }
             append xml "</Row>"
@@ -236,12 +268,14 @@
 #
     variable data
     for {set d 1} {$d<=$excel::workSheets($workbook)} {incr d} {
-        array unset data $d
-            set workSheet $excel::data(workSheet,$d)
+        # array unset data $d
+        foreach {n v} [array get data $d] { unset data($n) }
+        set workSheet $excel::data(workSheet,$d)
         for {set i 1} {$i<=$excel::rowCounter($workSheet)} {incr i} {
-            array unset data $workSheet*
+            # array unset data $workSheet*
+            foreach {n v} [array get data $workSheet*] { unset data($n) }
         }
-        unset $excel::rowCounter($workSheet)
+        unset excel::rowCounter($workSheet)
     }
 }
 proc excel::addTitle {workSheet columnsDataList} {
@@ -253,7 +287,7 @@
     foreach arg $columnsDataList {
         lappend newArgs  [list $arg String s21]
     }
-    addRow $workSheet $newArgs
+    addRowLists $workSheet $newArgs
 }
 proc excel::addTotal {workSheet columnsDataList} {
 #
@@ -264,7 +298,7 @@
     foreach arg $columnsDataList {
         lappend newArgs  [list $arg String s22]
     }
-    addRow $workSheet  $newArgs
+    addRowLists $workSheet  $newArgs
 }
 proc excel::setCell {workSheet row column value} {
 #
======

<<categories>> Package | Windows | XML