'''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 "\
\
\
\
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)
}
======
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 "\n"
#DETERMINE IF THERE ARE ANY STYLES THAT EXSIT FOR THE WORKBOOK;
if {[info exists StylesCounter($workbook)]} {
#STYLE(S) HAVE BEEN FOUND, NOW WE NEED TO GENERATE THE APPRORIATE XML TAGS;
for {set WbSc 2} {$WbSc <= $StylesCounter($workbook)} {incr WbSc} {
set WbSn $workbook.S$WbSc
set StyleName S$WbSc
#CHECK THAT THE STYLES ARRAY EXIST FIRST;
if {[array exists StylesArray]} {
#BEGIN GENERATING THE DYNAMIC STYLE CONTENT;
append xml " \n"
}
}
append xml " \n"
#BEGIN TO ADD THE DATA FOR EACH WORKSHEET IN THE WORKBOOK;
for {set WS 1} {$WS <= $WorkSheets($workbook)} {incr WS} {
append xml " \n";
#SET THE WORKSHEET PARAMETERS;
append xml "
\n";
#VERIFY IF THERE ARE ANY COLUMN ATTRIBUTES IN THE ARRAY THAT WE NEED TO SET;
set ColumnStyleKeys [array names ColStyle ${workbook}.WS${WS}*];
if {[llength $ColumnStyleKeys]} {
set ColString ""
#COLUMN STYLES EXIST, PROCESS THEM;
foreach Key $ColumnStyleKeys {
set cidx [lindex [split $Key .] 2];
switch $Key [list \
"${workbook}.WS${WS}.$cidx.-w" { append ColString " ss:Width=\"$ColStyle($Key)\"" } \
"${workbook}.WS${WS}.$cidx.-afw" { append ColString " ss:AutoFitWidth=\"$ColStyle($Key)\""} \
"${workbook}.WS${WS}.$cidx.-h" { append ColString " ss:Hidden=\"$ColStyle($Key)\"" } \
]
}
if {$ColString != ""} {
append xml " \n"
}
}
set SkippedRowFlag 0;
set SkippedColFlag 0;
for {set R 1} {$R <= $RowCounter($workbook.WS${WS})} {incr R} {
#CHECK IF DATA EXIST IN THIS ROW;
set RowCount [array names Data ${workbook}.WS${WS}.$R.*];
if {[llength $RowCount] > 0} {
#IF ROWS HAVE BEEN SKIPPED INDICATE THE ROW INDEX;
if {$SkippedRowFlag} {
append xml " \n"
#RESET THE FLAG;
set SkippedRowFlag 0;
} else {
append xml " \n"
}
#COLUMN LOOP;
set DataTypeFlag 0;
for {set C 1} {$C <= $ColCounter($workbook.WS${WS})} {incr C} {
#CHECK IF DATA EXIST FOR THE ROW & COL IDX;
if {[info exist Data(${workbook}.WS${WS}.$R.$C)]} {
set DataValue $Data(${workbook}.WS${WS}.$R.$C)
#CHECK THAT THERE IS ACTUALLY DATA AND IT IS NOT JUST BLANK,
#IF IT IS BLANK (ONLY HAS A SPACE) IT CAN BE SKIPPED;
if {[string map {" " ""} $DataValue] eq ""} {
set SkippedColFlag 1;
continue;
}
#IF A COLUMN HAS BEEN SKIPPED INDICATE IT ON THE COL INDEX;
if {$SkippedColFlag} {
append xml " "
#DETERMINE THE FORMAT OF THE DATA POSSIBLE VALUES ARE: DateTime Number String
switch -regexp -matchvar DataList -- $DataValue {
^(0?[1-9]|[12][0-9]|3[01])[-[:space:]\\/\.](0?[1-9]|1[012])[-[:space:]\\/\.]((?:19|20)?[0-9]{2})$ {
#DMY
lassign $DataList Full Day Month Year
if {[string length $Year] == 2} {
if {$Year <= 20} {
#ASSUME THAT WE ARE REFERENCING THE 21ST CENTURY;
set Year "20$Year"
} else {
#ASSUME THAT THE USER IS REFERENCING THE 20TH CENTURY;
set Year "19$Year"
}
}
set NomalizedDate [clock scan $Month/$Day/$Year -format {%m/%d/%Y}]
append xml "[clock format $NomalizedDate -format {%Y-%m-%dT00:00:00.000}]\n"
}
^(0?[1-9]|1[012])[-[:space:]\\/\.](0?[1-9]|[12][0-9]|3[01])[-[:space:]\\/\.]((?:19|20)?[0-9]{2})$ {
#MDY
puts $DataList
lassign $DataList Full Month Day Year
if {[string length $Year] == 2} {
if {$Year <= 20} {
#ASSUME THAT WE ARE REFERENCING THE 21ST CENTURY;
set Year "20$Year"
} else {
#ASSUME THAT THE USER IS REFERENCING THE 20TH CENTURY;
set Year "19$Year"
}
}
set NomalizedDate [clock scan $Month/$Day/$Year -format {%m/%d/%Y}]
append xml "[clock format $NomalizedDate -format {%Y-%m-%dT00:00:00.000}]\n"
}
^((?:19|20)[0-9]{2})[-[:space:]\\/\.](0?[1-9]|1[012])[-[:space:]\\/\.](0?[1-9]|[12][0-9]|3[01])$ {
#YMD
lassign $DataList Full Year Month Day
if {[string length $Year] == 2} {
if {$Year <= 20} {
#ASSUME THAT WE ARE REFERENCING THE 21ST CENTURY;
set Year "20$Year"
} else {
#ASSUME THAT THE USER IS REFERENCING THE 20TH CENTURY;
set Year "19$Year"
}
}
set NomalizedDate [clock scan $Month/$Day/$Year -format {%m/%d/%Y}]
append xml "[clock format $NomalizedDate -format {%Y-%m-%dT00:00:00.000}]\n"
}
^[-]?[0-9]*\.?[0-9]+$ {
append xml "$DataValue\n";
}
default {
append xml "[string map {\" " ' ' < < > > & &} $DataValue]\n";
}
}
#END THE SWITCH STATEMENT FOR STING TYPE;
} else {
#NO DATA EXIST IN THIS COLUMN, SKIP IT AND SET FLAG;
set SkippedColFlag 1;
set PrevNumber $C
}
}
#END COLUMN LOOP;
append xml " \n"
} else {
set SkippedRowFlag 1
}
}
#END ROW LOOP;
append xml "
\n"
append xml " \n"
}
#END WORKSHEET LOOP;
append xml " \n"
append xml " \n"
append xml " \n"
append xml " \n"
append xml " \n"
append xml " \n"
append xml " \n"
append xml " 600\n"
append xml " 600\n"
append xml " \n"
append xml " \n"
append xml " \n"
append xml " \n"
append xml " 3\n"
append xml " 1\n"
append xml " 1\n"
append xml " \n"
append xml " \n"
append xml " False\n"
append xml " False\n"
append xml " \n"
append xml "\n"
} else {
error "At least one Worksheet must exist in the workbook"
}
}
#INITILIZE THE DEFAULT VALUES FOR COLUMN INDEX;
Tcl2ExXML::_CreateColIdx
======
<> Package | Windows | XML