Artifact 8e9af22574db8fdd0de01894d6f0cdb06a975c7e01bad5a024425d7fc0cf1c44:
- File doc/ooxml.md — part of check-in [c0dafb4334] at 2024-11-26 00:15:33 on branch trunk — Saved minor ooxml.md enhancements. (user: rolf size: 21296) [more...]
| % ooxml(n) | ooxml user documentation |
NAME
::ooxml::xl_sheets, ::ooxml::read, ::ooxml::write - Read and create ECMA-376 Office Open XML Spreadsheets (the .xlsx format)
SYNOPSIS
package require ooxml
::ooxml::xl_sheets file
::ooxml::xl_read file args
::ooxml::xl_write args
DESCRIPTION
The commands of this package read or create ECMA-376 Office Open XML Spreadsheets (libreoffice and Excel ".xlsx" files). The most important three are:
::ooxml::xl\_read - imports a .xlsx spreadsheet files into a Tcl array
::ooxml::xl\_write - creates spreadsheet object command
::ooxml::tablelist_to_xl - export a Tcl tablelist to a .xlsx spreadsheet
During this documentation the term workbook means a whole spreadsheet with all its tables. The term worksheet means a specific table out of the worksheet it consits to.
To create a spreadsheet from scratch use the ::ooxml::xl_write command, which returns a spreadsheet object command:
set spreadsheet [::ooxml::xl_write new ?options?]
The following options are currently supported:
-creator NAME
: Creator specifies the creator property of the spreadsheet.
-created UTC-TIMESTAMP
: Created specifies the creation time property of the spreadsheet.
-nodifiedby NAME
: Modifiedby specifies the modified by property of the spreadsheet.
-modified UTC-TIMESTAMP
: Modified specifies the modified property of the spreadsheet.
-application NAME
: Application specifies the application property of the spreadsheet.
The created spreadsheet object commands currently supports the following methods:
numberformat args
: Defines an number format for the current workbook and returns an ID to refer that style.
The following options are currently supported:
: **-format** *FORMAT*
FORMAT can be any Excel format-string
: **-general**
Excel general-format
: **-date**
Date format
: **-time**
Time format
: **-datetime**
Date/Time format
: **-iso8601**
Date/Time in ISO8601 notation
: **-number**
Integer
: **-decimal**
Decimal number with 2 decimal places
: **-red**
Color red on negative values (can be combined with number and
decimal)
: **-separator**
Thousand separators (can be combined with number and decimal)
: **-fraction**
Fractions
: **-scientific**
Scientific numbers
: **-percent**
Percentage
: **-text**
**-string**
Text
: **-tag** *TAGNAME*
This option gives the format a name. This name may be used
instead of the returned ID.
font args
: Defines a font for the current workbook and returns an ID to refer that style.
The following options are currently supported:
: **-list**
Returns the list of currently defined fonts instead of an ID.
: **-name** *NAME*
(default = "Calibri")
: **-family** *FAMILY*
(defauft = 2)
: **-size** *SIZE*
(default = 12)
: -**color** *COLOR*
See [COLOR](#color) for the valid values.
: **-scheme** *SCHEME*
(default = "minor")
: **-bold**
: **-italic**
: **-underline**
: **-tag** *TAGNAME*
This option gives the font a name. This name may be used instead
of the returned ID.
fill args
: Defines a fill style for the current workbook and returns an ID to refer that style.
The following options are currently supported:
: **-list**
Returns the list of currently defined fills, in stead of FILLID.
: **-patterntype** *PATTERNTYPE*
(default = "none")
: **-fgcolor** *COLOR*
See [COLOR](#color) for the valid values.
: **-bgcolor** *COLOR*
See [COLOR](#color) for the valid values.
: **-tag** *TAGNAME*
To give the format a name. This name can also be used as FILLID.
border args
: Defines a border style for the current workbook and returns an ID to refer to that style.
The following options are currently supported:
: **-list**
Returns the list of currently defined border style IDs, instead
of BORDERID.
: -leftstyle BORDERLINESTYLE
: -rightstyle BORDERLINESTYLE
: -topstyle ORDERLINESTYLE
: -bottomstyle BORDERLINESTYLE
: -diagonalstyle BORDERLINESTYLE
See [BORDERLINESTYLE](#borderlinestyle) for the valid values.
: -leftcolor COLOR
: -rightcolor COLOR
: -topcolor COLOR
: -bottomcolor COLOR
: -diagonalcolor COLOR
See [COLOR](#color) for the valid values.
: -diagonaldirection (up|down)
The value must either *up* or *down*.
: -tag TAGNAME
To give the border style a name. This name can also be used as BORDERID.
return BORDERID
style args
: Defines a style for the current workbook and returns an ID to refer to that style.
The following options are currently supported:
: **-list**
Returns the list of currently defined border style IDs, instead
of STYLEID.
: -numfmt NUMFMTID
: -font FONTID
From method font.
: -fill FILLID
From method fill.
: -border BORDERID
From method border.
: -horizontal HORIZONTAL
: -vertical VERTICAL
: -rotate DEGREE
: -tag TAGNAME
To give the format a name. This name can also be used as STYLEID.
defaultdatestyle STYLEID
:
worksheet name
:
column sheet args
:
row sheet args
:
cell sheet args
:
autofilter sheet indexFrom indexTo
:
freeze sheet index
:
presetstyles
:
presetsheets
:
view args
:
pageMargins sheet args
: This method sets the page margins for the worksheet.
The currently supported arguments are:
**-left**
**-right**
**-top**
**-bottom**
**-header**
**-footer**
: This options all expect a floating point in inch as value for
the according margin.
If some page margins for a worksheet are set with this
method all margins for that worksheet are set, either from the
options given in the method call or from the current
pageMarginsDefault, if a margin was not in the method call.
pageMarginsDefault
: This method sets the page Margins of all worksheets which have not individual worksheet margin settings.
The currently supported arguments are:
**-left**
**-right**
**-top**
**-bottom**
**-header**
**-footer**
: This options all expect a floating point in inch as value for
the according margin.
The default margins are
Margin Inch
-------- -------
left 0.75
right 0.75
top 1
bottom 1
header 0.5
footer 0.5
pageSetup sheet args
: This method sets the page setup of the worksheet.
**Caveat**: If for a sheet no page setup propertiers at all are
set at least some applications use defaults derivated from other
sources. But if at least one page setup propertier is explicitly
set with this method some other propertiers not set will get the
default value from the specification, which may be another value
than without any page property setting. For example if you just
set only the **-orientation** of a sheet then this may also result
in another page size setting (because the application page size
default may be differ from the default recommended by the
specification (which would in this case Letter pape).
The currently supported arguments are:
**-blackAndWhite** *xsd boolean*
: Print black and white. Default: false
**-cellComments** *(none|asDisplayed|atEnd)*
: How to print cell comments. Default: none
**-copies** *integer*
: Number of copies to print. Default: 1
**-draft** *xsd boolean*
: Print without graphics. Default: false
**-errors** *(displayed|blank|dash|NA)*
: Specifies how to print cell values for cells with errors.
Default: displayed
**-firstPageNumber** *integer*
: Page number for first printed page. If no value is specified,
then 'automatic' is assumed. Default: 1
**-fitToHeight** *integer*
: Number of vertical pages to fit on. Default: 1
**-fitToWidth** *integer*
: Number of horizontal pages to fit on. Default: 1
**-horziontalDpi** *integer*
: Horizontal print resolution of the device. Default: 600
**-orientation** *(default|portrait|landscape)*
: Orientation of the page. Default: default
**-pageOrder** *(downThenOver|overThenDown)*
: Order of printed pages. Default: downThenOver
**-paperHeight** *height*
: Height of custom paper as a number followed by a unit
identifier (as 297mm or 11in).
The specification say that when the *-paperHeight* and
*-paperWidth* are specified, the application used to render
shall ignore *-paperSize*.
No default.
**-paperSize** *paperSizeID*
: Specifies the paper size to use by *paperSizeID*.
When *paperSizeID* values not present in the below list are
used, the behavior is implementation- defined.
The specification say that when the *-paperHeight* and
*-paperWidth* are specified, the application used to render
shall ignore *-paperSize*.
Default: 1 (which is Letter Size)
The possible *paperSizeID*s and their meaning are:
Paper Size ID Paper Size
------------- ------------------
1 Letter paper (8.5 in. by 11 in.)
2 Letter small paper (8.5 in. by 11 in.)
3 Tabloid paper (11 in. by 17 in.)
4 Ledger paper (17 in. by 11 in.)
5 Legal paper (8.5 in. by 14 in.)
6 Statement paper (5.5 in. by 8.5 in.)
7 Executive paper (7.25 in. by 10.5 in.)
8 A3 paper (297 mm by 420 mm)
9 A4 paper (210 mm by 297 mm)
10 A4 small paper (210 mm by 297 mm)
11 A5 paper (148 mm by 210 mm)
12 B4 paper (250 mm by 353 mm)
13 B5 paper (176 mm by 250 mm)
14 Folio paper (8.5 in. by 13 in.)
15 Quarto paper (215 mm by 275 mm)
16 Standard paper (10 in. by 14 in.)
17 Standard paper (11 in. by 17 in.)
18 Note paper (8.5 in. by 11 in.)
19 #9 envelope (3.875 in. by 8.875 in.)
20 #10 envelope (4.125 in. by 9.5 in.)
21 #11 envelope (4.5 in. by 10.375 in.)
22 #12 envelope (4.75 in. by 11 in.)
23 #14 envelope (5 in. by 11.5 in.)
24 C paper (17 in. by 22 in.)
25 D paper (22 in. by 34 in.)
26 E paper (34 in. by 44 in.)
27 DL envelope (110 mm by 220 mm)
28 C5 envelope (162 mm by 229 mm)
29 C3 envelope (324 mm by 458 mm)
30 C4 envelope (229 mm by 324 mm)
31 C6 envelope (114 mm by 162 mm)
32 C65 envelope (114 mm by 229 mm)
33 B4 envelope (250 mm by 353 mm)
34 B5 envelope (176 mm by 250 mm)
35 B6 envelope (176 mm by 125 mm)
36 Italy envelope (110 mm by 230 mm)
37 Monarch envelope (3.875 in. by 7.5 in.).
38 6 3/4 envelope (3.625 in. by 6.5 in.)
39 US standard fanfold (14.875 in. by 11 in.)
40 German standard fanfold (8.5 in. by 12 in.)
41 German legal fanfold (8.5 in. by 13 in.)
42 ISO B4 (250 mm by 353 mm)
43 Japanese double postcard (200 mm by 148 mm)
44 Standard paper (9 in. by 11 in.)
45 Standard paper (10 in. by 11 in.)
46 Standard paper (15 in. by 11 in.)
47 Invite envelope (220 mm by 220 mm)
50 Letter extra paper (9.275 in. by 12 in.)
51 Legal extra paper (9.275 in. by 15 in.)
52 Tabloid extra paper (11.69 in. by 18 in.)
53 A4 extra paper (236 mm by 322 mm)
54 Letter transverse paper (8.275 in. by 11 in.)
55 A4 transverse paper (210 mm by 297 mm)
56 Letter extra transverse paper (9.275 in. by 12 in.)
57 SuperA/SuperA/A4 paper (227 mm by 356 mm)
58 SuperB/SuperB/A3 paper (305 mm by 487 mm)
59 Letter plus paper (8.5 in. by 12.69 in.)
60 A4 plus paper (210 mm by 330 mm)
61 A5 transverse paper (148 mm by 210 mm)
62 JIS B5 transverse paper (182 mm by 257 mm)
63 A3 extra paper (322 mm by 445 mm)
64 A5 extra paper (174 mm by 235 mm)
65 ISO B5 extra paper (201 mm by 276 mm)
66 A2 paper (420 mm by 594 mm)
67 A3 transverse paper (297 mm by 420 mm)
68 A3 extra transverse paper (322 mm by 445 mm)
69 Japanese Double Postcard (200 mm x 148 mm)
70 A6 (105 mm x 148 mm)
71 Japanese Envelope Kaku #2
72 Japanese Envelope Kaku #3
73 Japanese Envelope Chou #3
74 Japanese Envelope Chou #4
75 Letter Rotated (11in x 8 1/2 11 in)
76 A3 Rotated (420 mm x 297 mm)
77 A4 Rotated (297 mm x 210 mm)
78 A5 Rotated (210 mm x 148 mm)
79 B4 (JIS) Rotated (364 mm x 257 mm)
80 B5 (JIS) Rotated (257 mm x 182 mm)
81 Japanese Postcard Rotated (148 mm x 100 mm)
82 Double Japanese Postcard Rotated (148 mm x 200 mm)
83 A6 Rotated (148 mm x 105 mm)
84 Japanese Envelope Kaku #2 Rotated
85 Japanese Envelope Kaku #3 Rotated
86 Japanese Envelope Chou #3 Rotated
87 Japanese Envelope Chou #4 Rotated
88 B6 (JIS) (128 mm x 182 mm)
89 B6 (JIS) Rotated (182 mm x 128 mm)
90 (12 in x 11 in)
91 Japanese Envelope You #4
92 Japanese Envelope You #4 Rotated
93 PRC 16K (146 mm x 215 mm)
94 PRC 32K (97 mm x 151 mm)
95 PRC 32K(Big) (97 mm x 151 mm)
96 PRC Envelope #1 (102 mm x 165 mm)
97 PRC Envelope #2 (102 mm x 176 mm)
98 PRC Envelope #3 (125 mm x 176 mm)
99 PRC Envelope #4 (110 mm x 208 mm)
100 PRC Envelope #5 (110 mm x 220 mm)
101 PRC Envelope #6 (120 mm x 230 mm)
102 PRC Envelope #7 (160 mm x 230 mm)
103 PRC Envelope #8 (120 mm x 309 mm)
104 PRC Envelope #9 (229 mm x 324 mm)
105 PRC Envelope #10 (324 mm x 458 mm)
106 PRC 16K Rotated
107 PRC 32K Rotated
108 PRC 32K(Big) Rotated
109 PRC Envelope #1 Rotated (165 mm x 102 mm)
110 PRC Envelope #2 Rotated (176 mm x 102 mm)
111 PRC Envelope #3 Rotated (176 mm x 125 mm)
112 PRC Envelope #4 Rotated (208 mm x 110 mm)
113 PRC Envelope #5 Rotated (220 mm x 110 mm)
114 PRC Envelope #6 Rotated (230 mm x 120 mm)
115 PRC Envelope #7 Rotated (230 mm x 160 mm)
116 PRC Envelope #8 Rotated (309 mm x 120 mm)
117 PRC Envelope #9 Rotated (324 mm x 229 mm)
118 PRC Envelope #10 Rotated (458 mm x 324 mm)
**-paperWide** *width*
: Wide of custom paper as a number followed by a unit
identifier (as 21cm or 8.5in).
The specification say that when the *-paperHeight* and
*-paperWidth* are specified, the application used to render
shall ignore *-paperSize*.
No default.
**-scale** *integer*
: Print scaling in percent. The rendering applicationi may
respect only values ranging from 10 to 400.
This setting is overridden when *-fitToWidth* and/or
*-fitToHeight* are in use.
Default: 100
**-useFirstPageNumber** *xsd boolean*
: Use *-firstPageNumber* value for first page number, and do not
auto number the pages. Default: false
**-verticalDpi** *integer*
: Vertical print resolution of the device. Default: 600
write filename
: Writes the spreadsheet to the file filename.
COLOR
Serveral method options expect a COLOR argument. There are several kind of valid values.
The value may be auto or none.
If not, the value may be the index number of a predefined color (an integer between 0 and 65 (including)). See the list below.
If not, the value may be the name of a predefined color. Case is ignored. See the list below.
If not, the value may be a 6 digit hexadecimal number, which is then used as RGB value.
The list of predefined colors is:
Color ID Name (A)RGB
0 Black 00000000 1 White 00FFFFFF 2 Red 00FF0000 3 Lime 0000FF00 4 Blue 000000FF 5 Yellow 00FFFF00 6 Fuchsia 00FF00FF 7 Aqua 0000FFFF 8 Black 00000000 9 White 00FFFFFF 10 Red 00FF0000 11 Lime 0000FF00 12 Blue 000000FF 13 Yellow 00FFFF00 14 Fuchsia 00FF00FF 15 Aqua 0000FFFF 16 Maroon 00800000 17 Green 00008000 18 Navy 00000080 19 Olive 00808000 20 Purple 00800080 21 Teal 00008080 22 Silver 00C0C0C0 23 Gray 00808080 24 Portage 009999FF 25 Lipstick 00993366 26 Cream 00FFFFCC 27 LightCyan 00CCFFFF 28 Purple 00660066 29 LightCoral 00FF8080 30 NavyBlue 000066CC 31 LavenderBlue 00CCCCFF 32 Navy 00000080 33 Fuchsia 00FF00FF 34 Yellow 00FFFF00 35 Aqua 0000FFFF 36 Purple 00800080 37 Maroon 00800000 38 Teal 00008080 39 Blue 000000FF 40 DeepSkyBlue 0000CCFF 41 LightCyan 00CCFFFF 42 BlueRomance 00CCFFCC 43 Canary 00FFFF99 44 LightSkyBlue 0099CCFF 45 CarnationPink 00FF99CC 46 Mauve 00CC99FF 47 PeachOrange 00FFCC99 48 RoyalBlue 003366FF 49 MediumTurquoise 0033CCCC 50 Citrus 0099CC00 51 TangerineYellow 00FFCC00 52 OrangePeel 00FF9900 53 SafetyOrange 00FF6600 54 Scampi 00666699 55 Nobel 00969696 56 PrussianBlue 00003366 57 Eucalyptus 00339966 58 Myrtle 00003300 59 Karaka 00333300 60 SaddleBrown 00993300 61 Lipstick 00993366 62 DarkSlateBlue 00333399 63 NightRider 00333333 64 SystemForeground n/a 65 SystemBackground n/a
BORDERLINESTYLE
Serveral method options expect a COLOR argument. The list of possible values are:
dashDot
dashDotDot
dashed
dotted
double
hair
medium
mediumDashDot
mediumDashDotDot
mediumDashDotDot
none
slantDashDot
thick
thin
DEPENDENCIES
Tcl >= 8.6.7
tclvfs::zip >= 1.0.4 or tcllib::zip::decode or Tcl 9 (only for xlsx reading)
tdom >= 0.9.0