---
name: ooxml
description: >
  Use this skill for all tasks involving the Tcl package `ooxml` (ECMA-376 Office
  Open XML: XLSX and DOCX).
  Triggers for XLSX: reading or writing Excel files in Tcl, using the ooxml package,
  generating XLSX files from Tcl, exporting Tcl data to Excel, exporting Tcl tablelist
  to Excel, cell formatting (font, color, border, number format), formulas in XLSX,
  hyperlinks in XLSX, calculating column widths, converting coordinates between
  numeric (row,col) and Excel notation (A1).
  Triggers for DOCX: creating or generating Word documents from Tcl, ooxml::docx,
  WordprocessingML, paragraphs and character/paragraph styles, tables in Word,
  images in docx, headers/footers, footnotes/endnotes, comments, fields (page
  numbers etc.), bookmarks and hyperlinks in docx, numbered/bulleted lists,
  textboxes, math formulas (OMML) in Word, docx templates, search and replace
  in docx.
  Always use when the user mentions ooxml, xl_read, xl_write, ooxml::docx, XLSX,
  or DOCX in combination with Tcl.
---

# ooxml – Tcl Package for XLSX + DOCX (ECMA-376)

**Project:** https://fossil.sowaswie.de/ooxml/index  
**License:** BSD-3  
**Author:** Alexander Schöpe (+ Rolf Ade, Harald Oehlmann, Miguel Bañón)

Two subsystems: `package require ooxml` reads and writes Excel workbooks
(XLSX), `package require ooxml::docx` (since ooxml 1.11) creates Word
documents (DOCX / WordprocessingML).

## Dependencies

- Tcl >= 8.6.7
- Reading XLSX: `tcllib` module `zipfile::decode` **or** `vfs::zip >= 1.0.4` (recommended); Tcl 9 uses `zipfs`
- `tdom >= 0.9.0`; the docx writer (`ooxml::docx`) requires `tdom >= 0.9.6`

---

## Core Commands

### Reading

```tcl
package require ooxml

# Load all sheets → array
array set workbook [::ooxml::xl_read file.xlsx]

# Options:
#   -valuesonly         Data only, no formatting
#   -keylist            Return key list instead of array
#   -sheets PATTERN     Load only sheet IDs matching pattern
#   -sheetnames PATTERN Load only sheets with matching names
#   -datefmt FORMAT     Date format (default: "%Y-%m-%d %H:%M:%S")
#   -as array           (currently the only output option)

# Get sheet information
set info [::ooxml::xl_sheets file.xlsx]

# Delete a sheet from array
::ooxml::arrayDeleteSheet workbook 0
::ooxml::arrayDeleteSheet workbook "Sheet1" -sheetname
```

> **Note:** `presetstyles`, `presetsheets` and `arrayDeleteSheet` expect the
> **array name** (without `$`), not its value.

### Reading – Result Structure

`xl_read` returns a flat array. Keys use `S`=sheet id (0-based),
`R`,`C`=row,column (0-based):

| Key | Content |
|---|---|
| `sheets` | List of sheet IDs that were read |
| `S,n` | Sheet name |
| `S,max_row` / `S,max_column` | Largest used row / column index |
| `S,v,R,C` | **Cell value** (always present) |
| `S,d,R,C` | Raw date/time serial (when value is a formatted date) |
| `S,s,R,C` | Style ID of the cell |
| `S,t,R,C` | Cell type (`s` = shared string) |
| `S,c,R,C` | Excel A1 reference of the cell |
| `S,f,R,C` | Formula dict: `f` formula, `i` shared idx, `r` shared ref |
| `S,l,IDX` | Hyperlink dict: `l` URL, `t` tooltip |
| `S,k` | Keylist dict row→columns (only with `-keylist`) |
| `S,filter` `S,freeze` `S,merge` `S,printarea` `S,rowheight` `S,pageMargins` `S,pageSetup` | Sheet properties |
| `S,cols` / `S,col,N` | Number of column definitions / dict with properties of column definition N |
| `s,@` `s,styles,*` | Style metadata (consumed by `presetstyles`) |
| `sheetmap` | Added by `presetsheets`: original sheet id → new worksheet handle |

With `-valuesonly` only `S,v,R,C` plus `sheets` / `S,n` / `S,max_*` are present.

```tcl
# Iterate all value cells of sheet 0
foreach key [lsort -dictionary [array names workbook 0,v,*]] {
    lassign [split $key ,] sheet tag row col
    set value $workbook($key)
}
```

### Writing – Basic Structure

```tcl
package require ooxml

set sp [::ooxml::xl_write new \
    -creator    "Author Name" \
    -created    [clock seconds] \
    -modifiedby "Name" \
    -modified   [clock seconds] \
    -application "MyApp"]

set sheet [$sp worksheet "Sheet 1"]

# Write cells and save file
$sp write output.xlsx
$sp destroy
```

---

## Cell Addressing

> **Recommendation:** When *writing* xlsx files, prefer the **autoincrement**
> of rows and columns wherever possible: call `$sp row $sheet` once per row,
> then write cells with bare `$sp cell $sheet <value>` (no `-index`). Reserve
> explicit `-index` for deliberately jumping or skipping. Order-driven writing
> avoids off-by-one and index-bookkeeping errors and keeps the code readable.

Two equivalent methods:

```tcl
# Numeric (row,col, 0-based)
$sp cell $sheet 12.34   -index 0,0
$sp cell $sheet {Text}  -index 1,0 -string

# Excel notation (A1-style)
$sp cell $sheet 5.6     -index B1
$sp cell $sheet {}      -index C1 -formula {A1+B1}
```

**Using autoincrement** (without `-index`):

```tcl
$sp row $sheet
$sp cell $sheet Value1
$sp cell $sheet Value2   ;# next column automatically
$sp row $sheet           ;# next row
$sp cell $sheet Value3
```

Rows and columns are auto-incremented and can be skipped with `-index`:

```tcl
$sp row $sheet
$sp cell $sheet A           ;# 0,0
$sp cell $sheet B -index 4  ;# 0,4  (columns 1-3 remain empty)
$sp cell $sheet C           ;# 0,5
$sp row $sheet -index 3     ;# jump to row 3
$sp cell $sheet D           ;# 3,0
```

Result: A at 0,0 (A1), B at 0,4 (E1), C at 0,5 (F1), D at 3,0 (A4).

---

## Helper Functions

```tcl
# Coordinate conversion
::ooxml::StringToRowColumn A1    ;# → 0,0
::ooxml::RowColumnToString 0,0   ;# → A1
::ooxml::Column 0               ;# → A

# Calculate column width based on character count
::ooxml::CalcColumnWidth 16      ;# → 16.7109375

# Set default path
::ooxml::Default path "/tmp"
```

---

## Formatting

All style elements are defined first, then referenced by their ID.

### Number Format (NUMFMTID)

```tcl
set fmtDate     [$sp numberformat -date]
set fmtDateTime [$sp numberformat -datetime]
set fmtIso      [$sp numberformat -iso8601]
set fmtDec      [$sp numberformat -decimal -red -separator]
set fmtPct      [$sp numberformat -percent]
set fmtText     [$sp numberformat -string]
set fmtCustom   [$sp numberformat -format {#,##0.00 "€"}]
# -tag NAME allows referencing by name instead of ID
```

### Font (FONTID)

```tcl
set fHeader [$sp font \
    -name  "Arial" \
    -size  12 \
    -bold \
    -color Blue]
```

### Fill Pattern (FILLID)

```tcl
set fFill [$sp fill \
    -patterntype solid \
    -fgcolor     Yellow]
```

### Border (BORDERID)

Sides: `-left` `-right` `-top` `-bottom` `-diagonal`, each with a `…style` and
`…color` variant. Diagonal additionally takes `-diagonaldirection up|down`.

```tcl
set fBorder [$sp border \
    -bottomstyle thin \
    -bottomcolor Black]

set fDiag [$sp border -diagonalstyle medium -diagonaldirection up]
```

### Cell Style (STYLEID)

```tcl
set sHeader [$sp style \
    -font       $fHeader \
    -fill       $fFill \
    -border     $fBorder \
    -numfmt     $fmtDate \
    -horizontal center \
    -vertical   top \
    -rotate     0 \
    -wrap]      ;# -wrap is a flag: enable text wrapping
# also: -xf XFID (base cell format record), -list, -tag NAME
```

Apply style to column or cell:

```tcl
$sp column $sheet -index 0 -style $sHeader -width 20
$sp cell   $sheet "Value" -index 0,0 -style $sHeader
```

---

## Column and Row Definitions

```tcl
# Column
$sp column $sheet \
    -index       0 \
    -to          3 \
    -width       15 \
    -style       $styleId \
    -bestfit \
    -customwidth \
    -string \
    -nozero \
    -calcfit

# Row
$sp row $sheet -index 0 -height 20
```

---

## Cell Options

| Option | Description |
|---|---|
| `-string` | Always store value as string |
| `-nostring` | Override column-level `-string` for this cell |
| `-nozero` | Do not output zero values |
| `-zero` | Override column-level `-nozero` for this cell |
| `-formula FORMULA` | Enter an Excel formula |
| `-formulaidx N` | Shared formula index (integer starting at 0) |
| `-formularef A1:B5` | Range of the shared formula |
| `-hyperlink URL` | `http(s)://...` link |
| `-tooltip TEXT` | Tooltip (only evaluated with `-hyperlink`) |
| `-height H` | Set row height for this row |
| `-style STYLEID` | Style for this cell |
| `-globalstyle` | Apply the column/global style to this cell |

---

## Formulas

Simple formula — the cell value stays empty, Excel computes it:

```tcl
$sp row $sheet
$sp cell $sheet 3                    ;# A1
$sp cell $sheet 5                    ;# B1
$sp cell $sheet {} -formula A1+B1    ;# C1
```

**Shared formula:** define it once with `-formula` + `-formularef` (the range
it spans) + `-formulaidx` (an integer id, starting at 0). Dependent cells
reference only `-formulaidx`. `-formularef` accepts an A1 range *or* a numeric
`r,c:r,c` range:

```tcl
# Vertical shared formula C3:C5
$sp cell $sheet 1  -index 2,0
$sp cell $sheet 2  -index 2,1
$sp cell $sheet {} -index 2,2 -formula {A3+B3} -formularef C3:C5 -formulaidx 0
$sp cell $sheet {} -index 3,2 -formulaidx 0      ;# reuses idx 0
$sp cell $sheet {} -index C5  -formulaidx 0

# Horizontal shared formula, numeric ref
$sp cell $sheet {} -index A9  -formula {A7+A8} -formularef 8,0:8,2 -formulaidx 1
$sp cell $sheet {} -index 8,1 -formulaidx 1
```

---

## Additional Features

```tcl
# Autofilter
$sp autofilter $sheet 0,0 0,5

# Freeze panes (row/column)
$sp freeze $sheet 1,0    ;# freeze first row

# Merge cells (corner indices, numeric or A1)
$sp merge $sheet 40,0 40,2
$sp merge $sheet A42 A44

# Print area (per sheet, numeric or A1)
$sp printarea $sheet A1 C3
$sp printarea $sheet 1,1 2,2

# Explicit row height (independent of `row -height`)
$sp rowheight $sheet 33 20    ;# row index 33 -> height 20

# Page margins (doubles in inch) and page setup
$sp pageMargins $sheet -left 0.5 -right 0.5 -top 1 -bottom 1 -header 0.5 -footer 0.5
$sp pageSetup   $sheet -orientation landscape -paperSize 9   ;# 9 = A4, 1 = Letter

# Workbook view
$sp view -activetab 0 -x 100 -y 100 -width 14400 -height 9000

# Set default date style
set dateStyle [$sp style -numfmt [$sp numberformat -datetime]]
$sp defaultdatestyle $dateStyle

# Read existing workbook and reuse styles/data
array set wb [::ooxml::xl_read original.xlsx]
set sp [::ooxml::xl_write new]
$sp presetstyles wb   ;# inherit styles
$sp presetsheets wb   ;# inherit styles + data
```

---

## tablelist → XLSX

```tcl
package require ooxml

::ooxml::tablelist_to_xl $lb \
    -file         output.xlsx \
    -path         /tmp \
    -creator      "Name" \
    -name         "Sheet 1" \
    -rootonly \
    -addtimestamp \
    -globalstyle \
    -callback     myCallback

# Callback signature:
proc myCallback {sp sheet maxcol col title width align sortmode hide} {
    # col == -1: called once for the entire workbook
    if {$col == -1} {
        set dateStyle [$sp style -numfmt [$sp numberformat -datetime]]
        $sp defaultdatestyle $dateStyle
    } else {
        switch -- $align {
            center { $sp column $sheet -index $col -style [$sp style -horizontal center] }
            right  { $sp column $sheet -index $col -style [$sp style -horizontal right] }
            default {}
        }
    }
}
```

---

## Color Constants (Selection)

Named colors: `Aqua Black Blue Canary Fuchsia Gray Green Lime Maroon Navy Orange Olive Purple Red Silver Teal White Yellow` (and many more, indexed 0-65)  
Alternatively: `RGB` or `aRGB` notation.

## Border Line Styles

`dashDot dashDotDot dashed dotted double hair medium mediumDashDot mediumDashDotDot none slantDashDot thick thin`

## Pattern Types

`solid none darkDown darkGray darkGrid lightDown lightGray lightGrid lightHorizontal lightTrellis lightUp lightVertical mediumGray`

---

## Complete Example: Write File with Formatting

```tcl
package require ooxml

set sp [::ooxml::xl_write new -creator "Me"]
set sheet [$sp worksheet "Report"]

# Define styles
set boldFont  [$sp font -bold -size 12 -color White]
set bgFill    [$sp fill -patterntype solid -fgcolor Blue]
set hdrStyle  [$sp style -font $boldFont -fill $bgFill -horizontal center]
set numStyle  [$sp style -numfmt [$sp numberformat -decimal -separator]]
set dateStyle [$sp style -numfmt [$sp numberformat -date]]

# Configure columns
$sp column $sheet -index 0 -width 20 -style $hdrStyle
$sp column $sheet -index 1 -width 12 -style $numStyle
$sp column $sheet -index 2 -width 14 -style $dateStyle

# Header row
$sp row $sheet
$sp cell $sheet "Name"   -index 0 -string
$sp cell $sheet "Amount" -index 1 -string
$sp cell $sheet "Date"   -index 2 -string

# Data row
$sp row $sheet
$sp cell $sheet "John Doe"       -index 0 -string
$sp cell $sheet 1234.56          -index 1
$sp cell $sheet [clock seconds]  -index 2

# Autofilter + freeze first row
$sp autofilter $sheet 0,0 0,2
$sp freeze     $sheet 1,0

$sp write report.xlsx
$sp destroy
```

---

# DOCX – Creating Word Documents (`ooxml::docx`, since 1.11)

```tcl
package require ooxml::docx

set doc [ooxml::docx::docx new]      ;# empty document
$doc paragraph "Hello, World!"
$doc write hello.docx                ;# suffix .docx appended if missing
$doc destroy
```

Start from an existing file as template: `ooxml::docx::docx new template.docx`.
Content is added top-down; methods take required arguments first, then
`-option value` pairs. All option values are type checked, unknown
options/keys raise a Tcl error. On/off options accept boolean values
(`on`/`off`, `1`/`0`, `true`/`false`).

Document properties: `$doc configure -creator "Me" -title "Report" -language de-DE ...`
(also `-created -modified -subject -keywords -description -revision -version` …)

## Text, Styles, Formatting

```tcl
$doc paragraph "Text" -pstyle StyleName -cstyle StyleName -align center -bold on
$doc append " more text in the same paragraph" -italic on
$doc br            ;# line break     $doc tab       ;# tab
$doc pagebreak     ;# page break
```

**Character options** (for `paragraph`, `append`, `url`, `field`, …):
`-bold -italic -underline single|double|dotted|wave|…` `-strike -dstrike
-emboss -color RRGGBB -font Name -fontsize N -highlight yellow|red|…
-verticalAlign baseline|superscript|subscript -rtl -noProof -cstyle Ref`

**Paragraph options:** `-align start|center|end|both|…` `-background RRGGBB
-shading {type clear fill RRGGBB} -indentation {start 1cm firstLine 5mm}
-spacing {before 6pt after 6pt line 360 lineRule auto} -keepLines -keepNext
-pageBreakBefore -widowControl -bidi -numberingStyle ID -level N
-textframe {width … height … wrap …} -suppressLineNumbers`

**Measurements:** integer plus unit `mm cm in pt pc pi` (or bare integer,
unit depends on option, mostly twips; for image/textbox `-dimension` a bare
integer means EMU, 914400 EMU = 1 inch).

**Named styles** — define once, reference by display name or style ID
(`-pstyle`, `-cstyle`, `-style`, `-basedon`, `-refstyle`):

```tcl
$doc style paragraphdefault -fontsize 22 -font "Times New Roman"
$doc style paragraph "Heading Blue" -bold on -color 0000FF -fontsize 28
$doc style character "Warn" -color FF0000 -bold on
$doc style table "MyTable" -tableBorders {…} -conditional {firstRow {…}}
$doc paragraph "Chapter 1" -pstyle "Heading Blue"
```

Subcommands: `ids|names styletype`, `delete styletype name`.

## Tables

```tcl
# Quick: nested list, one element per row
$doc simpletable {{A1 B1 C1} {A2 B2 C2}} -columnwidths {3cm 3cm 3cm}

# Full control: table → tablerow → tablecell (cells hold any content,
# even nested tables and images)
$doc table -style MyTable -columnwidths {5cm 5cm} -width {type auto} {
    $doc tablerow -headerrow on {
        $doc tablecell -background DDDDDD {$doc paragraph "Head 1"}
        $doc tablecell {$doc paragraph "Head 2"}
    }
    $doc tablerow {
        $doc tablecell -span 2 -vAlign center {$doc paragraph "spans 2 cols"}
    }
}
```

Cell options: `-span N` (grid columns), `-hspan/-vspan restart|continue`
(merging), `-vAlign`, `-background RRGGBB` | `-shading {…}` (mutually
exclusive), `-cellWidth {type dxa value 3cm}`, borders
(`-topBorder {type single color 000000 borderwidth 4} …`), margins.
Row options: `-rowHeight {value 1cm hRule atLeast}`, `-headerrow on`
(repeat on every page), `-cantSplit on`.

## Images, Textboxes, Header/Footer, Sections

```tcl
$doc image photo.jpg inline -dimension {width 3cm height 3cm}
$doc image photo.jpg anchor -dimension {width 3cm height 3cm} -wrapMode square \
    -positionH margin -alignH right -positionV paragraph -posOffsetV 0

$doc textbox -dimension {width 5cm height 2cm} {$doc paragraph "boxed"}

$doc header {$doc paragraph "My Header"} hdrId hdrObjId
$doc footer {$doc paragraph "Page "; $doc field PAGE; $doc append " of "; $doc field NUMPAGES} ftrId
$doc pagesetup -defaultHeader $hdrId -defaultFooter $ftrId \
    -sizeAndOrientation {width 29.7cm height 21cm orientation landscape} \
    -margins {top 2cm bottom 2cm}

# Sections: sectionstart … sectionend with the same options as pagesetup
```

## Notes, Comments, Fields, Links, Lists

```tcl
$doc footnote {$doc paragraph "footnote text"}          ;# also: endnote
$doc simplecomment "comment text" -author "Me" -initials AS
set id [$doc commentrangestart]                          ;# comment a text range
$doc paragraph "commented content"
$doc simplecommentrangeend $id "range comment"

$doc field PAGE          ;# AUTHOR CREATEDATE DATE FILESIZE PAGE NUMPAGES
                         ;# SAVEDATE SECTION TIME TITLE USERNAME (+ switches)

$doc url "Tcl homepage" https://www.tcl-lang.org -color 0000FF -underline single
$doc mark target1        ;# set jump target;  markstart/markend for spans
$doc jumpto "go to target" target1

$doc numbering abstractNum 0 {
    {-numberFormat decimal -levelText "%1." -align start}
    {-numberFormat bullet  -levelText "•"}
}
$doc paragraph "first item" -numberingStyle 0 -level 0
```

## Math (OMML)

```tcl
$doc math -display on -jc center {
    $doc mfrac {$doc mrun a} {$doc mrun b}
    $doc mnary -char "∑" -sub {$doc mrun i=1} -sup {$doc mrun n} {$doc mrun i}
}
```

Math methods (only valid inside a `math` script): `mrun mfrac msub msup
msubsup msqrt mroot mnary mfunc mlimlow mlimupp maccent mbar mdelim
mdelimarg mgroupchr mmatrix mmatcell`.

## Document Settings and Power Tools

```tcl
$doc settings -defaultTabStop 708 -autoHyphenation on -trackRevisions on \
    -mirrorMargins on -evenAndOddHeaders on -documentProtection {edit readOnly enforcement on}

$doc appendTo $objectId {…}     ;# append content later to a created table/
                                ;# row/cell/header/footer/foot-/endnote/comment/textbox
$doc replace "%%NAME%%" "John"  ;# simple template substitution (uniform runs only)
$doc addXML $xmlFragment        ;# insert raw WordprocessingML
$doc import styles other.docx   ;# take over word/styles.xml (or numbering, …)
$doc readpart word/settings.xml file.xml   ;# writepart to export a part
$doc xmlparts                   ;# list XML parts
$doc selectNodes {//w:p} ?part? ;# XPath (alias: xpath), w:/r:/m:… prefixes predefined
```

---

## References

- **Man-Page (XLSX):** https://fossil.sowaswie.de/ooxml/wiki?name=man-page — in the source tree: `doc/ooxml.md` (complete command/method reference)
- **DOCX documentation:** `doc/ooxml-docx.md` in the source tree (complete option/method reference)
- **Examples:** https://fossil.sowaswie.de/ooxml/wiki?name=examples — in the source tree: `examples/xlsx-*.tcl` and `examples/docx-example*.tcl`
- **Tools:** https://fossil.sowaswie.de/ooxml/wiki?name=tools
- **Downloads:** https://fossil.sowaswie.de/ooxml/uv/download.html