This 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 "\
\
\
\
Ashrait\
[clock format [clock seconds] -format {%Y-%m-%dT%H:%M:%SZ}]\
Xor Technologies\
\
\
\
\
"
if {[info exists excel::styles($workbook)]} {
for {set d 2} {$d<=$excel::styles($workbook)} {incr d} {
set styleName s$d
append xml ""
}
}
append xml ""
for {set d 1} {$d<=$excel::workSheets($workbook)} {incr d} {
append xml "\
"
set workSheet $excel::data(workSheet,$d)
for {set i 1} {$i<=$excel::rowCounter($workSheet)} {incr i} {
append xml ""
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 ""
} else {
append xml ">"
}
append xml "$dataValue | "
}
append xml "
"
}
append xml "
"
}
append xml ""
}
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)
}
======
----
Usage 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] 13 Jul 2005'' - 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] 05 Aug 2008'' -updated to latest version
----
''[AElfwine] 15 Sep 2008''
- Adding a missing close parenthesys in an array
- Correcting examples : adding [list ..]
----
''[leprechau] 27 Oct 2008''
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 "