Index: examples/sample1.tcl ================================================================== --- examples/sample1.tcl +++ examples/sample1.tcl @@ -1,11 +1,13 @@ #!/bin/sh #\ exec tclsh8.6 "$0" "$@" -#package require ooxml -source ../ooxml.tcl +set auto_path [linsert $auto_path 0 ..] +if {[catch {package require ooxml}]} { + source ../ooxml.tcl +} source array.tcl set spreadsheet [::ooxml::xl_write new -creator {Alexander Schöpe}] if {[set sheet [$spreadsheet worksheet {Tabelle 1}]] > -1} { Index: examples/sample2.tcl ================================================================== --- examples/sample2.tcl +++ examples/sample2.tcl @@ -1,11 +1,13 @@ #!/bin/sh #\ exec tclsh8.6 "$0" "$@" -#package require ooxml -source ../ooxml.tcl +set auto_path [linsert $auto_path 0 ..] +if {[catch {package require ooxml}]} { + source ../ooxml.tcl +} source array.tcl set spreadsheet [::ooxml::xl_write new -creator {Alexander Schöpe}] if {[set sheet [$spreadsheet worksheet {Tabelle 1}]] > -1} { Index: examples/sample3.tcl ================================================================== --- examples/sample3.tcl +++ examples/sample3.tcl @@ -3,12 +3,14 @@ exec wish8.6 "$0" "$@" package require Tk package require tablelist -#package require ooxml -source ../ooxml.tcl +set auto_path [linsert $auto_path 0 ..] +if {[catch {package require ooxml}]} { + source ../ooxml.tcl +} source array.tcl # build Tablelist from array Index: examples/sample4.tcl ================================================================== --- examples/sample4.tcl +++ examples/sample4.tcl @@ -1,12 +1,12 @@ #!/bin/sh #\ exec tclsh8.6 "$0" "$@" -lappend auto_path . +set auto_path [linsert $auto_path 0 ..] if {[catch {package require ooxml}]} { - source ../ooxml.tcl + source ../ooxml.tcl } source array.tcl set spreadsheet [::ooxml::xl_write new -creator {Alexander Schöpe}] Index: examples/sample5.tcl ================================================================== --- examples/sample5.tcl +++ examples/sample5.tcl @@ -1,11 +1,13 @@ #!/bin/sh #\ exec tclsh8.6 "$0" "$@" -#package require ooxml -source ../ooxml.tcl +set auto_path [linsert $auto_path 0 ..] +if {[catch {package require ooxml}]} { + source ../ooxml.tcl +} set spreadsheet [::ooxml::xl_write new -creator {Alexander Schöpe}] if {[set sheet [$spreadsheet worksheet {Tabelle 1}]] > -1} { set date [$spreadsheet style -numfmt [$spreadsheet numberformat -datetime]] $spreadsheet defaultdatestyle $date Index: examples/sample6.tcl ================================================================== --- examples/sample6.tcl +++ examples/sample6.tcl @@ -1,11 +1,13 @@ #!/bin/sh #\ exec tclsh8.6 "$0" "$@" -#package require ooxml -source ../ooxml.tcl +set auto_path [linsert $auto_path 0 ..] +if {[catch {package require ooxml}]} { + source ../ooxml.tcl +} set spreadsheet [::ooxml::xl_write new -creator {Alexander Schöpe}] if {[set sheet [$spreadsheet worksheet {Blatt 1}]] > -1} { set bold [$spreadsheet style -font [$spreadsheet font -bold]] set italic [$spreadsheet style -font [$spreadsheet font -italic]] Index: examples/sample7.tcl ================================================================== --- examples/sample7.tcl +++ examples/sample7.tcl @@ -1,15 +1,17 @@ #!/bin/sh #\ exec tclsh8.6 "$0" "$@" -#package require ooxml -source ../ooxml.tcl +set auto_path [linsert $auto_path 0 ..] +if {[catch {package require ooxml}]} { + source ../ooxml.tcl +} array set workbook [ooxml::xl_read original_excel.xlsx] set spreadsheet [::ooxml::xl_write new] $spreadsheet presetstyles workbook $spreadsheet presetsheets workbook $spreadsheet write export7.xlsx $spreadsheet destroy Index: examples/sample8.tcl ================================================================== --- examples/sample8.tcl +++ examples/sample8.tcl @@ -1,11 +1,13 @@ #!/bin/sh #\ exec tclsh8.6 "$0" "$@" -#package require ooxml -source ../ooxml.tcl +set auto_path [linsert $auto_path 0 ..] +if {[catch {package require ooxml}]} { + source ../ooxml.tcl +} array set workbook [ooxml::xl_read form8.xlsx] set data(NAME) {Erika Mustermann} set data(ANSCHRIFT) {Heidestrasse 17} ADDED examples/sample9.tcl Index: examples/sample9.tcl ================================================================== --- examples/sample9.tcl +++ examples/sample9.tcl @@ -0,0 +1,17 @@ +#!/bin/sh +#\ +exec tclsh8.6 "$0" "$@" + +set auto_path [linsert $auto_path 0 ..] +if {[catch {package require ooxml}]} { + source ../ooxml.tcl +} + +set spreadsheet [::ooxml::xl_write new -creator {User A} -created {2019-08-10 10:01:30} -modifiedby {User B} -modified {2019-08-10 12:30:01} -application {Tcl Example Script 9}] +set wrap [$spreadsheet style -wrap] + +if {[set sheet [$spreadsheet worksheet {Sheet 1}]] > -1} { + $spreadsheet cell $sheet {this text will be automatically wrapped by excel} -index A1 -style $wrap + $spreadsheet write export9.xlsx +} +$spreadsheet destroy Index: ooxml.tcl ================================================================== --- ooxml.tcl +++ ooxml.tcl @@ -96,10 +96,14 @@ # # ::ooxml::xl_write # # constructor args # -creator CREATOR +# -created UTC-TIMESTAMP +# -modifiedby NAME +# -modified UTC-TIMESTAMP +# -application NAME # return class # # method numberformat args # -format FORMAT -general -date -time -datetime -iso8601 -number -decimal -red -separator -fraction -scientific -percent -text -string # return NUMFMTID @@ -157,20 +161,20 @@ # Callback arguments # spreadsheet sheet maxcol column title width align sortmode hide # -package require Tcl 8.6 -package require vfs::zip +package require Tcl 8.6.7- package require tdom 0.9.0- package require msgcat namespace eval ::ooxml { namespace export xl_sheets xl_read xl_write variable defaults + variable initNodeCmds variable predefNumFmts variable predefColors variable predefColorsName variable predefColorsARBG variable predefBorderLineStyles @@ -459,10 +463,86 @@ msgcat::mcset zh Book \u5de5\u4f5c\u7c3f msgcat::mcset zh Worksheets \u5de5\u4f5c\u8868 msgcat::mcset zh Sheet \u5de5\u4f5c\u8868 } +# ooxml::timet_to_dos +# +# Convert a unix timestamp into a DOS timestamp for ZIP times. +# +# DOS timestamps are 32 bits split into bit regions as follows: +# 24 16 8 0 +# +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +# |Y|Y|Y|Y|Y|Y|Y|m| |m|m|m|d|d|d|d|d| |h|h|h|h|h|m|m|m| |m|m|m|s|s|s|s|s| +# +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +# +# From tcllib / zipfile::mkzip +proc ::ooxml::timet_to_dos {time_t} { + set s [clock format $time_t -format {%Y %m %e %k %M %S}] + scan $s {%d %d %d %d %d %d} year month day hour min sec + expr {(($year-1980) << 25) | ($month << 21) | ($day << 16) + | ($hour << 11) | ($min << 5) | ($sec >> 1)} +} + +# ooxml::add_str_to_archive -- +# +# Add a string as a single file with string as content with +# argument path to a zip archive. The zipchan channel must +# already be open and binary. The return value is the central +# directory record that will need to be used when finalizing +# the zip archive. +# +# Derived from tcllib / zipfile::mkzip::add_file_to_archive +proc ::ooxml::add_str_to_archive {zipchan path data {comment ""}} { + set mtime [timet_to_dos [clock seconds]] + set utfpath [encoding convertto utf-8 $path] + set utfcomment [encoding convertto utf-8 $comment] + set flags [expr {(1<<11)}] ;# utf-8 comment and path + set method 0 ;# store 0, deflate 8 + set attr 0 ;# text or binary (default binary) + set version 20 ;# minumum version req'd to extract + set extra "" + set crc 0 + set size 0 + set csize 0 + set seekable [expr {[tell $zipchan] != -1}] + set attrex 0x81b60020 ;# 0o100666 (-rw-rw-rw-) + + set utfdata [encoding convertto utf-8 $data] + set size [string length $utfdata] + + set offset [tell $zipchan] + set local [binary format a4sssiiiiss PK\03\04 \ + $version $flags $method $mtime $crc $csize $size \ + [string length $utfpath] [string length $extra]] + append local $utfpath $extra + puts -nonewline $zipchan $local + + set crc [::zlib crc32 $utfdata] + set cdata [::zlib deflate $utfdata] + if {[string length $cdata] < $size} { + set method 8 + set utfdata $cdata + } + set csize [string length $utfdata] + puts -nonewline $zipchan $utfdata + + # update the header + set local [binary format a4sssiiii PK\03\04 \ + $version $flags $method $mtime $crc $csize $size] + set current [tell $zipchan] + seek $zipchan $offset + puts -nonewline $zipchan $local + seek $zipchan $current + + set hdr [binary format a4ssssiiiisssssii PK\01\02 0x0317 \ + $version $flags $method $mtime $crc $csize $size \ + [string length $utfpath] [string length $extra]\ + [string length $utfcomment] 0 $attr $attrex $offset] + append hdr $utfpath $extra $utfcomment + return $hdr +} proc ::ooxml::Default { name value } { variable defaults switch -- $name { @@ -667,144 +747,10 @@ } } return {} } - -proc ::ooxml::ZipInitialize { *v file } { - upvar ${*v} v - - set fd [open $file w] - set v(fd) $fd - set v(base) [tell $fd] - set v(toc) {} - fconfigure $fd -translation binary -encoding binary -} - - -proc ::ooxml::ZipEmit { *v s } { - upvar ${*v} v - - puts -nonewline $v(fd) $s -} - - -proc ::ooxml::ZipDosTime { sec } { - set f [clock format $sec -format {%Y %m %d %H %M %S} -gmt 1] - regsub -all { 0(\d)} $f { \1} f - foreach {Y M D h m s} $f break - set date [expr {(($Y-1980)<<9) | ($M<<5) | $D}] - set time [expr {($h<<11) | ($m<<5) | ($s>>1)}] - return [list $date $time] -} - - -proc ::ooxml::ZipAddEntry { *v name contents {date {}} {force 0} } { - upvar ${*v} v - - if {$date eq {}} { - set date [clock seconds] - } - lassign [ZipDosTime $date] date time - set flag 0 - set type 0 ;# stored - set fsize [string length $contents] - set csize $fsize - set fnlen [string length $name] - - if {$force > 0 && $force != [string length $contents]} { - set csize $fsize - set fsize $force - set type 8 ;# if we're passing in compressed data, it's deflated - } - - if {[catch {zlib crc32 $contents} crc]} { - set crc 0 - } elseif {$type == 0} { - set cdata [zlib deflate $contents 9] - if {[string length $cdata] < [string length $contents]} { - set contents $cdata - set csize [string length $cdata] - set type 8 ;# deflate - } - } - - lappend v(toc) "[binary format a2c6ssssiiiss4ii PK {1 2 20 0 20 0} $flag $type $time $date $crc $csize $fsize $fnlen {0 0 0 0} 128 [tell $v(fd)]]$name" - - ZipEmit v [binary format a2c4ssssiiiss PK {3 4 20 0} $flag $type $time $date $crc $csize $fsize $fnlen 0] - ZipEmit v $name - ZipEmit v $contents -} - - -proc ::ooxml::ZipAddDirectory { *v name {date {}} {force 0} } { - upvar ${*v} v - - set name "${name}/" - if {$date eq {}} { - set date [clock seconds] - } - lassign [ZipDosTime $date] date time - set flag 0 - set type 0 ;# stored - set fsize 0 - set csize 0 - set fnlen [string length $name] - set crc 0 - - lappend v(toc) "[binary format a2c6ssssiiiss4ii PK {1 2 20 0 20 0} $flag $type $time $date $crc $csize $fsize $fnlen {0 0 0 0} 128 [tell $v(fd)]]$name" - - ZipEmit v [binary format a2c4ssssiiiss PK {3 4 20 0} $flag $type $time $date $crc $csize $fsize $fnlen 0] - ZipEmit v $name -} - - -proc ::ooxml::ZipFinalize { *v } { - upvar ${*v} v - - set pos [tell $v(fd)] - set ntoc [llength $v(toc)] - foreach x $v(toc) { - ZipEmit v $x - } - set v(toc) {} - - set len [expr {[tell $v(fd)] - $pos}] - incr pos -$v(base) - - ZipEmit v [binary format a2c2ssssiis PK {5 6} 0 0 $ntoc $ntoc $len $pos 0] - - close $v(fd) -} - - -proc ::ooxml::Zip { zipfile directory files } { - array set v { fd {} base {} toc {} } - - # this code is a rewrite and extension of the zipper code found - # at http://equi4.com/critlib/ and http://wiki.tcl.tk/36689 - # by Tom Krehbiel 2012 krehbiel.tom at gmail dot com - - - ZipInitialize v $zipfile - foreach file $files { - regsub {^\./} $file {} to - set from [file join [file normalize $directory] $to] - if {[file isfile $from]} { - set fd [open $from r] - fconfigure $fd -translation binary -encoding binary - ZipAddEntry v $to [read $fd] [file mtime $from] - close $fd - } elseif {[file isdir $from]} { - ZipAddDirectory v $to [file mtime $from] - lappend dirs $file - } - } - ZipFinalize v -} - - proc ::ooxml::Column { col } { set name {} while {$col >= 0} { set char [binary format c [expr {($col % 26) + 65}]] set name $char$name @@ -898,10 +844,12 @@ # # ooxml::xl_sheets # proc ::ooxml::xl_sheets { file } { + package require vfs::zip + set sheets {} set mnt [vfs::zip::Mount $file xlsx] set rels 0 @@ -953,10 +901,12 @@ # proc ::ooxml::xl_read { file args } { variable predefNumFmts + package require vfs::zip + array set cellXfs {} array set numFmts [array get predefNumFmts] array set sharedStrings {} set sheets {} @@ -964,11 +914,10 @@ error $opts(-errmsg) } if {[string trim $opts(sheets)] eq {} && [string trim $opts(sheetnames)] eq {}} { set opts(sheetnames) * } - set mnt [vfs::zip::Mount $file xlsx] set rels 0 if {![catch {open xlsx/xl/_rels/workbook.xml.rels r} fd]} { @@ -1446,10 +1395,79 @@ } } return [array get wb] } +# Internal helper +proc ooxml::Dom2zip {zf node path cd count} { + upvar $cd mycd + upvar $count mycount + append mycd [::ooxml::add_str_to_archive $zf $path \ + [$node asXML -indent none -xmlDeclaration 1 \ + -encString "UTF-8"]] + incr mycount +} + + +# +# ooxml::InitNodeCommands +# + + +proc ooxml::InitNodeCommands {} { + variable initNodeCmds + + if {[info exists initNodeCmds] && $initNodeCmds} return + + set elementNodes { + AppVersion Application + Company + Default DocSecurity + HeadingPairs HyperlinksChanged + LinksUpToDate + Override + Relationship + ScaleCrop SharedDoc + TitlesOfParts + a:accent1 a:accent2 a:accent3 a:accent4 a:accent5 a:accent6 a:alpha a:bevelT a:bgFillStyleLst a:bodyPr a:camera + a:clrScheme a:cs a:dk1 a:dk2 a:ea a:effectLst a:effectRef a:effectStyle a:effectStyleLst a:extraClrSchemeLst + a:fillRef a:fillStyleLst a:fillToRect a:fmtScheme a:folHlink a:font a:fontRef a:fontScheme a:gradFill a:gs + a:gsLst a:hlink a:latin a:lightRig a:lin a:ln a:lnDef a:lnRef a:lnStyleLst a:lstStyle a:lt1 a:lt2 a:majorFont + a:minorFont a:objectDefaults a:outerShdw a:path a:prstDash a:rot a:satMod a:scene3d a:schemeClr a:shade + a:solidFill a:sp3d a:spDef a:spPr a:srgbClr a:style a:sysClr a:themeElements a:tint + alignment autoFilter + b bgColor bookViews border borders bottom + c calcPr cellStyle cellStyleXfs cellStyles cellXfs col color cols + cp:lastModifiedBy + dc:creator + dcterms:created dcterms:modified + definedName definedNames diagonal dimension dxfs + f family fgColor fileVersion fill fills font fonts + i + left + mergeCell mergeCells + name numFmt numFmts + pageMargins pane patternFill + right row + scheme sheet sheetData sheetFormatPr sheetView sheetViews sheets si sz + t tableStyles top + u + v + vt:i4 vt:lpstr vt:lpstrvt:lpstr vt:variant vt:vector + workbookPr workbookView + xf + } + + namespace eval ::ooxml "dom createNodeCmd textNode Text; namespace export Text" + + foreach tag $elementNodes { + namespace eval ::ooxml "dom createNodeCmd -tagName $tag elementNode Tag_$tag; namespace export Tag_$tag" + } + + set initNodeCmds 1 +} + # # ooxml::xl_write # @@ -1464,21 +1482,44 @@ my variable styles my variable fills my variable borders my variable cols - if {[::ooxml::Getopt opts {creator.arg {unknown}} $args]} { + if {[::ooxml::Getopt opts {creator.arg {unknown} created.arg {} modifiedby.arg {} modified.arg {} application.arg {}} $args]} { error $opts(-errmsg) } set obj(blockPreset) 0 set obj(encoding) utf-8 set obj(indent) none - set obj(creator) $opts(creator) - set obj(created) [clock format [clock seconds] -format %Y-%m-%dT%H:%M:%SZ -gmt 1] + if {[string trim $opts(creator)] eq {}} { + set obj(creator) {unknown} + } else { + set obj(creator) $opts(creator) + } + if {[string trim $opts(created)] eq {} || [catch {clock scan $opts(created)}]} { + set obj(created) [clock format [clock seconds] -format %Y-%m-%dT%H:%M:%SZ -gmt 1] + } else { + set obj(created) [clock format [clock scan $opts(created) -gmt 1] -format %Y-%m-%dT%H:%M:%SZ -gmt 1] + } + if {[string trim $opts(modifiedby)] eq {}} { + set obj(lastModifiedBy) $opts(creator) + } else { + set obj(lastModifiedBy) $opts(modifiedby) + } + if {[string trim $opts(modified)] eq {} || [catch {clock scan $opts(modified)}]} { + set obj(modified) [clock format [clock seconds] -format %Y-%m-%dT%H:%M:%SZ -gmt 1] + } else { + set obj(modified) [clock format [clock scan $opts(modified) -gmt 1] -format %Y-%m-%dT%H:%M:%SZ -gmt 1] + } + if {[string trim $opts(application)] eq {}} { + set obj(application) {Tcl - Office Open XML - Spreadsheet} + } else { + set obj(application) $opts(application) + } set obj(sheets) 0 array set sheets {} set obj(sharedStrings) 0 @@ -1973,13 +2014,78 @@ method cell { sheet {data {}} args } { my variable obj my variable cells my variable cols - if {[::ooxml::Getopt opts {index.arg {} style.arg 0 formula.arg {} string nozero globalstyle height.arg {}} $args]} { - error $opts(-errmsg) + array set opts { + index "" + style 0 + formula "" + string 0 + nozero 0 + globalstyle 0 + height "" + } + set len [llength $args] + set loopInd 0 + while {$loopInd < $len} { + switch -- [lindex $args $loopInd] { + "-index" { + incr loopInd + if {$loopInd < $len} { + set opts(index) [lindex $args $loopInd] + incr loopInd + } else { + error "-index: missing argument" + } + } + "-style" { + incr loopInd + if {$loopInd < $len} { + set opts(style) [lindex $args $loopInd] + incr loopInd + } else { + error "-style: missing argument" + } + } + "-formula" { + incr loopInd + if {$loopInd < $len} { + set opts(formula) [lindex $args $loopInd] + incr loopInd + } else { + error "-formula: missing argument" + } + } + "-string" { + set opts(string) 1 + incr loopInd + } + "-nozero" { + set opts(nozero) 1 + } + "-globalstyle" { + set opts(globalstyle) 1 + incr loopInd + } + "-height" { + incr loopInd + if {$loopInd < $len} { + set opts(height) [lindex $args $loopInd] + incr loopInd + } else { + error "-height: missing argument" + } + } + default { + error "unknown option [lindex $args $loopInd]" + } + } } + # if {[::ooxml::Getopt opts {index.arg {} style.arg 0 formula.arg {} string nozero globalstyle height.arg {}} $args]} { + # error $opts(-errmsg) + # } if {!$obj(callRow,$obj(sheets))} { set obj(callRow,$obj(sheets)) 1 incr obj(row,$sheet) } @@ -2225,10 +2331,31 @@ if {[::ooxml::Getopt opts {holdcontainerdirectory} $args]} { error $opts(-errmsg) } + ooxml::InitNodeCommands + namespace import ::ooxml::Tag_* ::ooxml::Text + + # Initialize zip file + set file [string trim $file] + if {$file eq {}} { + set file {spreadsheetml.xlsx} + } + if {[file extension $file] ne {.xlsx}} { + append file {.xlsx} + } + if {[catch {set zf [open $file w]}]} { + error "Unable to write $file" + } + fconfigure $zf \ + -encoding binary \ + -translation binary \ + -eofchar {} + set count 0 + set cd "" + foreach {n v} [array get cells] { if {[dict exists $v t] && [dict get $v t] eq {s} && [dict exists $v v] && [dict get $v v] ne {}} { set thisv [dict get $v v] if {[info exists lookup($thisv)]} { set pos $lookup($thisv) @@ -2243,33 +2370,29 @@ } unset -nocomplain n v array unset lookup # _rels/.rels - set doc [set obj(doc,_rels/.rels) [dom createDocument Relationships]] + set doc [dom createDocument Relationships] set root [$doc documentElement] set rId 0 - dom createNodeCmd -tagName Relationship elementNode Tag_Relationship - $root setAttribute xmlns http://schemas.openxmlformats.org/package/2006/relationships $root appendFromScript { Tag_Relationship Id rId1 Type http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument Target xl/workbook.xml {} Tag_Relationship Id rId2 Type http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties Target docProps/app.xml {} Tag_Relationship Id rId3 Type http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties Target docProps/core.xml {} } - + ::ooxml::Dom2zip $zf $root "_rels/.rels" cd count + $doc delete # [Content_Types].xml - set doc [set obj(doc,\[Content_Types\].xml) [dom createDocument Types]] + set doc [dom createDocument Types] set root [$doc documentElement] - foreach tag {Default Override} { - dom createNodeCmd -tagName $tag elementNode Tag_$tag - } $root setAttribute xmlns http://schemas.openxmlformats.org/package/2006/content-types $root appendFromScript { Tag_Default Extension xml ContentType application/xml {} @@ -2287,27 +2410,22 @@ Tag_Override PartName /xl/calcChain.xml ContentType application/vnd.openxmlformats-officedocument.spreadsheetml.calcChain+xml {} } Tag_Override PartName /docProps/core.xml ContentType application/vnd.openxmlformats-package.core-properties+xml {} Tag_Override PartName /docProps/app.xml ContentType application/vnd.openxmlformats-officedocument.extended-properties+xml {} } - + ::ooxml::Dom2zip $zf $root "\[Content_Types\].xml" cd count + $doc delete # docProps/app.xml - set doc [set obj(doc,docProps/app.xml) [dom createDocument Properties]] + set doc [set obj(doc,) [dom createDocument Properties]] set root [$doc documentElement] - dom createNodeCmd textNode Text - foreach tag {AppVersion Application Company DocSecurity HeadingPairs HyperlinksChanged LinksUpToDate ScaleCrop SharedDoc TitlesOfParts - vt:i4 vt:lpstrvt:lpstr vt:lpstr vt:variant vt:vector} { - dom createNodeCmd -tagName $tag elementNode Tag_$tag - } - $root setAttribute xmlns http://schemas.openxmlformats.org/officeDocument/2006/extended-properties $root setAttribute xmlns:vt http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes $root appendFromScript { - Tag_Application { Text {Tcl - Office Open XML - Spreadsheet} } + Tag_Application { Text $obj(application) } Tag_DocSecurity { Text 0 } Tag_ScaleCrop { Text false } Tag_HeadingPairs { Tag_vt:vector size 2 baseType variant { Tag_vt:variant { @@ -2331,41 +2449,36 @@ Tag_LinksUpToDate { Text false } Tag_SharedDoc { Text false } Tag_HyperlinksChanged { Text false } Tag_AppVersion { Text 1.0 } } - + ::ooxml::Dom2zip $zf $root "docProps/app.xml" cd count + $doc delete # docProps/core.xml - set doc [set obj(doc,docProps/core.xml) [dom createDocument cp:coreProperties]] + set doc [dom createDocument cp:coreProperties] set root [$doc documentElement] - dom createNodeCmd textNode Text - foreach tag {cp:lastModifiedBy dc:creator dcterms:created dcterms:modified} { - dom createNodeCmd -tagName $tag elementNode Tag_$tag - } - $root setAttribute xmlns:cp http://schemas.openxmlformats.org/package/2006/metadata/core-properties $root setAttribute xmlns:dc http://purl.org/dc/elements/1.1/ $root setAttribute xmlns:dcterms http://purl.org/dc/terms/ $root setAttribute xmlns:dcmitype http://purl.org/dc/dcmitype/ $root setAttribute xmlns:xsi http://www.w3.org/2001/XMLSchema-instance $root appendFromScript { Tag_dc:creator { Text $obj(creator) } - Tag_cp:lastModifiedBy { Text $obj(creator) } + Tag_cp:lastModifiedBy { Text $obj(lastModifiedBy) } Tag_dcterms:created xsi:type dcterms:W3CDTF { Text $obj(created) } - Tag_dcterms:modified xsi:type dcterms:W3CDTF { Text $obj(created) } + Tag_dcterms:modified xsi:type dcterms:W3CDTF { Text $obj(modified) } } - + ::ooxml::Dom2zip $zf $root "docProps/core.xml" cd count + $doc delete # xl/_rels/workbook.xml.rels - set doc [set obj(doc,xl/_rels/workbook.xml.rels) [dom createDocument Relationships]] + set doc [dom createDocument Relationships] set root [$doc documentElement] - dom createNodeCmd -tagName Relationship elementNode Tag_Relationship - $root setAttribute xmlns http://schemas.openxmlformats.org/package/2006/relationships $root appendFromScript { for {set ws 1} {$ws <= $obj(sheets)} {incr ws} { Tag_Relationship Id rId$ws Type http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet Target worksheets/sheet${ws}.xml {} @@ -2378,22 +2491,19 @@ } if {$obj(calcChain)} { Tag_Relationship Id rId[incr rId] Type http://schemas.openxmlformats.org/officeDocument/2006/relationships/calcChain Target calcChain.xml {} } } + ::ooxml::Dom2zip $zf $root "xl/_rels/workbook.xml.rels" cd count + $doc delete # xl/sharedStrings.xml if {$obj(sharedStrings) > 0} { - set doc [set obj(doc,xl/sharedStrings.xml) [dom createDocument sst]] + set doc [dom createDocument sst] set root [$doc documentElement] - dom createNodeCmd textNode Text - foreach tag {si t} { - dom createNodeCmd -tagName $tag elementNode Tag_$tag - } - $root setAttribute xmlns http://schemas.openxmlformats.org/spreadsheetml/2006/main $root setAttribute count [llength $sharedStrings] $root setAttribute uniqueCount [llength $sharedStrings] $root appendFromScript { @@ -2400,39 +2510,38 @@ foreach string $sharedStrings { Tag_si { Tag_t { Text $string } } } + # garbage collection + set sharedStrings {} } + ::ooxml::Dom2zip $zf $root "xl/sharedStrings.xml" cd count + $doc delete } # xl/calcChain.xml if {$obj(calcChain)} { - set doc [set obj(doc,xl/calcChain.xml) [dom createDocument calcChain]] + set doc [dom createDocument calcChain] set root [$doc documentElement] - dom createNodeCmd -tagName c elementNode Tag_c - $root setAttribute xmlns http://schemas.openxmlformats.org/spreadsheetml/2006/main $root appendFromScript { Tag_c r C1 i 3 l 1 {} Tag_c r A3 i 2 {} } + ::ooxml::Dom2zip $zf $root "xl/calcChain.xml" cd count + $doc delete } # xl/styles.xml - set doc [set obj(doc,xl/styles.xml) [dom createDocument styleSheet]] + set doc [dom createDocument styleSheet] set root [$doc documentElement] - foreach tag {alignment b bgColor border borders bottom cellStyle cellStyleXfs cellStyles cellXfs color diagonal dxfs family - fgColor fill fills font fonts i left name numFmt numFmts patternFill right scheme sz tableStyles top u xf} { - dom createNodeCmd -tagName $tag elementNode Tag_$tag - } - $root setAttribute xmlns http://schemas.openxmlformats.org/spreadsheetml/2006/main $root setAttribute xmlns:mc http://schemas.openxmlformats.org/markup-compatibility/2006 $root setAttribute xmlns:x14ac http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac $root setAttribute mc:Ignorable x14ac @@ -2559,25 +2668,18 @@ Tag_cellStyle name Standard xfId 0 builtinId 0 {} } Tag_dxfs count 0 {} Tag_tableStyles count 0 {} } + ::ooxml::Dom2zip $zf $root "xl/styles.xml" cd count + $doc delete # xl/theme/theme1.xml - set doc [set obj(doc,xl/theme/theme1.xml) [dom createDocument a:theme]] + set doc [dom createDocument a:theme] set root [$doc documentElement] - foreach tag {a:accent1 a:accent2 a:accent3 a:accent4 a:accent5 a:accent6 a:alpha a:bevelT a:bgFillStyleLst a:bodyPr a:camera - a:clrScheme a:cs a:dk1 a:dk2 a:ea a:effectLst a:effectRef a:effectStyle a:effectStyleLst a:extraClrSchemeLst - a:fillRef a:fillStyleLst a:fillToRect a:fmtScheme a:folHlink a:font a:fontRef a:fontScheme a:gradFill a:gs a:gsLst - a:hlink a:latin a:lightRig a:lin a:ln a:lnDef a:lnRef a:lnStyleLst a:lstStyle a:lt1 a:lt2 a:majorFont a:minorFont - a:objectDefaults a:outerShdw a:path a:prstDash a:rot a:satMod a:scene3d a:schemeClr a:shade a:solidFill a:sp3d - a:spDef a:spPr a:srgbClr a:style a:sysClr a:themeElements a:tint} { - dom createNodeCmd -tagName $tag elementNode Tag_$tag - } - $root setAttribute xmlns:a http://schemas.openxmlformats.org/drawingml/2006/main $root setAttribute name Office-Design $root appendFromScript { Tag_a:themeElements { @@ -2898,21 +3000,18 @@ } } } Tag_a:extraClrSchemeLst {} } + ::ooxml::Dom2zip $zf $root "xl/theme/theme1.xml" cd count + $doc delete # xl/workbook.xml - set doc [set obj(doc,xl/workbook.xml) [dom createDocument workbook]] + set doc [dom createDocument workbook] set root [$doc documentElement] - dom createNodeCmd textNode Text - foreach tag {bookViews calcPr definedName definedNames fileVersion sheet sheets workbookPr workbookView} { - dom createNodeCmd -tagName $tag elementNode Tag_$tag - } - $root setAttribute xmlns http://schemas.openxmlformats.org/spreadsheetml/2006/main $root setAttribute xmlns:r http://schemas.openxmlformats.org/officeDocument/2006/relationships $root appendFromScript { Tag_fileVersion appName xl lastEdited 5 lowestEdited 5 rupBuild 5000 {} @@ -2931,20 +3030,18 @@ } } Tag_calcPr calcId 140000 concurrentCalc 0 {} # fullCalcOnLoad 1 } + ::ooxml::Dom2zip $zf $root "xl/workbook.xml" cd count + $doc delete # xl/worksheets/sheet1.xml SHEET - dom createNodeCmd textNode Text - foreach tag {autoFilter c col cols dimension f mergeCell mergeCells pageMargins pane row sheetData sheetFormatPr sheetView sheetViews v} { - dom createNodeCmd -tagName $tag elementNode Tag_$tag - } for {set ws 1} {$ws <= $obj(sheets)} {incr ws} { - set doc [set obj(doc,xl/worksheets/sheet$ws.xml) [dom createDocument worksheet]] + set doc [dom createDocument worksheet] set root [$doc documentElement] $root setAttribute xmlns http://schemas.openxmlformats.org/spreadsheetml/2006/main $root setAttribute xmlns:r http://schemas.openxmlformats.org/officeDocument/2006/relationships $root setAttribute xmlns:mc http://schemas.openxmlformats.org/markup-compatibility/2006 $root setAttribute xmlns:x14ac http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac @@ -2996,10 +3093,12 @@ } } } elseif {[dict exists $cells($idx) s] && [string is integer -strict [dict get $cells($idx) s]] && [dict get $cells($idx) s] > 0} { Tag_c r [::ooxml::RowColumnToString $row,$col] s [dict get $cells($idx) s] {} } + # garbage collection + unset -nocomplain cells($idx) } } } } if {$obj(autofilter,$ws) ne {}} { @@ -3039,45 +3138,21 @@ Tag_col {*}$attr {} } } } } - } - - # Content-Type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet - set file [string trim $file] - if {$file eq {}} { - set file {spreadsheetml.xlsx} - } - if {[file extension $file] ne {.xlsx}} { - append file {.xlsx} - } - set path [file dirname $file] - set uid [format xl_%X [clock microseconds]] - set filesToZip {} - foreach {tag doc} [array get obj doc,*] { - lappend filesToZip [set docname [lindex [split $tag ,] 1]] - set xmlfile [file join $path $uid $docname] - file mkdir [file dirname $xmlfile] - if {![catch {open $xmlfile w} fd]} { - fconfigure $fd -encoding utf-8 - #puts $fd "" - puts $fd [[$doc documentElement] asXML -indent $obj(indent) -xmlDeclaration 1 -encString [string toupper $obj(encoding)]] - close $fd - $doc delete - } - } - set pwd [pwd] - cd [file join $path $uid] - if {$path eq {.}} { - set file [file join .. $file] - } - ::ooxml::Zip $file . $filesToZip - cd $pwd - if {!$opts(holdcontainerdirectory)} { - file delete -force [file join $path $uid] - } + ::ooxml::Dom2zip $zf $root "xl/worksheets/sheet$ws.xml" cd count + $doc delete + } + + # Finalize zip. + set cdoffset [tell $zf] + set endrec [binary format a4ssssiis PK\05\06 0 0 \ + $count $count [string length $cd] $cdoffset 0] + puts -nonewline $zf $cd + puts -nonewline $zf $endrec + close $zf return 0 } }