NAME

::ooxml::xl_sheets, ::ooxml::xl_read, ::ooxml::xl_write, ::ooxml::tablelist_to_xl - Read and create ECMA-376 Office Open XML Spreadsheets (the .xlsx format)

SYNOPSIS

package require ooxml
::ooxml::xl_sheets file
::ooxml::xl_read file ?-option value ...?
::ooxml::xl_write new ?-option value ...?
::ooxml::tablelist_to_xl tablelist ?-option value ...?

DESCRIPTION

The commands of this package read or create ECMA-376 Office Open XML Spreadsheets (LibreOffice and Excel “.xlsx” files). The three most important are:

::ooxml::xl_read - imports a .xlsx spreadsheet file into a Tcl array
::ooxml::xl_write - creates a spreadsheet object command
::ooxml::tablelist_to_xl - exports a Tcl tablelist to a .xlsx spreadsheet

In this documentation the term workbook means a whole spreadsheet with all its tables. The term worksheet means a specific table of the workbook it belongs to.

All row, column and sheet indices used by this package are zero-based. Cell positions may be given numerically as row,column or in Excel notation; see CELL INDEX.

The package provides the following commands:

::ooxml::arrayDeleteSheet arrayName sheetId ?-sheetname?

Deletes one worksheet from an array read with ::ooxml::xl_read and renumbers the remaining sheets. The array is passed by name (without $). By default sheetId is the zero-based sheet index; with the -sheetname option it is the worksheet name.

  ::ooxml::arrayDeleteSheet workbook 0
  ::ooxml::arrayDeleteSheet workbook "Sheet1" -sheetname

::ooxml::CalcColumnWidth numberOfCharacters ?maximumDigitWidth? ?pixelPadding?

Returns the column width value (for the column method -width option) that fits the given number of characters. The optional arguments maximumDigitWidth (default 7) and pixelPadding (default 5) describe the metrics of the standard font.

  ::ooxml::CalcColumnWidth 16   ;# -> 16.7109375

::ooxml::Column column

Converts the zero-based column number into the column name in Excel notation (0 -> A, 1 -> B, 26 -> AA, …).

::ooxml::Default name value

Sets a package wide default. The only currently supported name is path: the default directory of written files (used e.g. by ::ooxml::tablelist_to_xl if no -path is given). An empty value resets it to the current directory.

::ooxml::RowColumnToString rowcol

Converts a numerical cell index (row,column, zero-based) into Excel notation (0,0 -> A1).

::ooxml::StringToRowColumn name

Converts a cell position in Excel notation into the numerical form (A1 -> 0,0).

::ooxml::tablelist_to_xl tablelist ?-option value …?

Exports the content of the Tk tablelist widget tablelist to a .xlsx file. The recognized options are:

  -callback command: Command to be called once per workbook and
  once per column, to customize styles and column properties.
  See below for the arguments. Default is the built-in callback
  ::ooxml::tablelist_to_xl_callback.

  -path path: Directory of the output file.

  -file filename: Name of the output file (a .xlsx suffix is
  appended if missing).

  -creator name: Creator property of the spreadsheet.

  -name name: Name of the (single) worksheet.

  -rootonly: Export only the root level of the tablelist (no
  child items).

  -addtimestamp: Add a timestamp to the output file name.

  -globalstyle: Write the cells with the column styles set by
  the callback.

The callback is called with the arguments

  spreadsheet sheet maxcol column title width align sortmode hide

where spreadsheet is the spreadsheet object, sheet the worksheet handle and maxcol the number of columns. It is called once with column = -1 for the whole workbook (for example to set the default date style) and then once per column with the tablelist column properties title, width, align, sortmode and hide.

  proc myCallback {spreadsheet sheet maxcol column title width align sortmode hide} {
      if {$column == -1} {
          set datestyle [$spreadsheet style -numfmt [$spreadsheet numberformat -datetime]]
          $spreadsheet defaultdatestyle $datestyle
      } elseif {$align eq "right"} {
          $spreadsheet column $sheet -index $column -style [$spreadsheet style -horizontal right]
      }
  }

::ooxml::xl_read file ?-option value …?

Reads the given .xlsx file and returns its contents as a flat Tcl array (to be caught with array set). See RESULT ARRAY for the keys of the returned array. The recognized options are:

  -valuesonly: Read only the cell values, no formatting,
  formulas or sheet properties.

  -keylist: Additionally build the S,k key (see
  RESULT ARRAY), a dictionary of the non-empty columns per row.

  -sheets pattern: Read only the worksheets whose zero-based
  sheet index matches the glob pattern.

  -sheetnames pattern: Read only the worksheets whose name
  matches the glob pattern.

  If neither -sheets nor -sheetnames is given all worksheets
  are read.

  -datefmt format: [clock format] string used to convert date
  cells into the value keys (default "%Y-%m-%d %H:%M:%S").

  -as array: Format of the return value. Currently array is
  the only supported value.

A simple example:

  array set workbook [::ooxml::xl_read file.xlsx]
  foreach key [lsort -dictionary [array names workbook 0,v,*]] {
      lassign [split $key ,] sheet tag row col
      puts "$row,$col = $workbook($key)"
  }

::ooxml::xl_sheets file

Returns information about the worksheets of the given .xlsx file without reading any cell data. The result is a Tcl list with two elements per worksheet: the zero-based sheet index and a dictionary with the keys sheetId (the internal sheet id), name (the display name of the worksheet) and rId (the internal relationship id).

  foreach {index info} [::ooxml::xl_sheets file.xlsx] {
      puts "$index: [dict get $info name]"
  }

::ooxml::xl_write new ?-option value …?

Creates a spreadsheet object command to build a workbook from scratch:

  set spreadsheet [::ooxml::xl_write new ?-option value ...?]

The recognized options are:

  -creator name: The creator property of the spreadsheet.

  -created timestamp: The creation time property of the
  spreadsheet. The value is either an integer (seconds, as
  returned by [clock seconds]) or a date string in local time.

  -modifiedby name: The modified-by property of the
  spreadsheet.

  -modified timestamp: The modification time property of the
  spreadsheet. For the value see -created.

  -application name: The application property of the
  spreadsheet.

The created spreadsheet object commands currently support the following methods:

autofilter sheet indexFrom indexTo

Adds an autofilter (filter and sort controls on the header row) to the worksheet, spanning from cell indexFrom to cell indexTo (see CELL INDEX).

  $spreadsheet autofilter $sheet 0,0 0,5

border ?-option value …?

Defines a border style for the current workbook and returns an ID (BORDERID) to refer to that style. The recognized options are:

  -list: Returns the list of currently defined border styles
  instead of a BORDERID.

  -leftstyle BORDERLINESTYLE
  -rightstyle BORDERLINESTYLE
  -topstyle BORDERLINESTYLE
  -bottomstyle BORDERLINESTYLE
  -diagonalstyle BORDERLINESTYLE

  See BORDERLINESTYLE for the valid values.

  -leftcolor COLOR
  -rightcolor COLOR
  -topcolor COLOR
  -bottomcolor COLOR
  -diagonalcolor COLOR

  See COLOR for the valid values.

  -diagonaldirection (up|down)

  -tag tagname: Gives the border style a name. This name may be
  used instead of the returned BORDERID.

See BORDERLINESTYLE and COLOR for the valid option values.

cell sheet ?data? ?-option value …?

Writes the value data into a cell of the worksheet sheet and returns the position of the written cell as row,column. Without the -index option the cell is placed in the current row at the next column (see CELL INDEX for the autoincrement behaviour). The recognized options are:

  -index index: Position of the cell, either as row,column or
  in Excel notation. Also accepts a bare column number to place
  the cell in the current row with columns skipped.

  -style STYLEID: Style of the cell, from the method style.

  -formula formula: Enter an Excel formula (e.g. "A1+B1"). The
  data argument is usually empty then, the application
  computes the value.

  -formulaidx integer: Shared formula index. A cell defining a
  shared formula gives -formula, -formularef and -formulaidx;
  the dependent cells give only -formulaidx.

  -formularef range: The range the shared formula spans, either
  in Excel notation (C3:C5) or numerically (2,2:4,2).

  -string: Store the value as string in any case.

  -nostring: Override a column-level -string setting for this
  cell.

  -nozero: Do not output the value if it is zero.

  -zero: Override a column-level -nozero setting for this cell.

  -globalstyle: Apply the column style to this cell.

  -height height: Sets the row height of the current row.

  -hyperlink url: Makes the cell a hyperlink to url.

  -tooltip text: Tooltip for the hyperlink (only used together
  with -hyperlink).

column sheet ?-option value …?

Defines the properties of a column of the worksheet sheet and returns the column number. Without the -index option the next column is used. The recognized options are:

  -index index: Column number.

  -to index: Last column number, if the definition should span
  a range of columns.

  -width width: Column width in characters of the standard font
  (may be fractional, see also ::ooxml::CalcColumnWidth).

  -style STYLEID: Default style for the cells of the column.
  Note that a cell only uses it if written with -globalstyle.

  -bestfit: Sets the bestFit flag of the column.

  -customwidth: Sets the customWidth flag of the column.

  -string: Store the values of the cells of this column as
  string in any case (a cell may override this with -nostring).

  -nozero: Do not output zero values in this column (a cell may
  override this with -zero).

  -calcfit: Calculates the column width from the length of the
  longest value written to the column (via
  ::ooxml::CalcColumnWidth) if that is wider than a given
  -width.

defaultdatestyle STYLEID

Sets the default style for date cells. Values recognized as dates are formatted with this style if the cell does not have an explicit -style.

  set datestyle [$spreadsheet style -numfmt [$spreadsheet numberformat -datetime]]
  $spreadsheet defaultdatestyle $datestyle

fill ?-option value …?

Defines a fill style for the current workbook and returns an ID (FILLID) to refer to that style. The recognized options are:

  -list: Returns the list of currently defined fills instead of
  a FILLID.

  -patterntype PATTERNTYPE: See PATTERNTYPE for the usual
  values (default "none").

  -fgcolor COLOR: Foreground (pattern) color. See COLOR for the
  valid values.

  -bgcolor COLOR: Background color. See COLOR for the valid
  values.

  -tag tagname: Gives the fill a name. This name may be used
  instead of the returned FILLID.

See PATTERNTYPE and COLOR for the valid option values.

font ?-option value …?

Defines a font for the current workbook and returns an ID (FONTID) to refer to that font. The recognized options are:

  -list: Returns the list of currently defined fonts instead of
  a FONTID.

  -name name: Font name (default "Calibri").

  -family family: Font family number (default 2).

  -size size: Font size in points (default 12).

  -color COLOR: See COLOR for the valid values.

  -scheme scheme: Font scheme (default "minor").

  -bold: Bold text.

  -italic: Italic text.

  -underline: Underlined text.

  -tag tagname: Gives the font a name. This name may be used
  instead of the returned FONTID.

See COLOR for the valid -color values.

freeze sheet index

Freezes the panes above and left of the given cell index (see CELL INDEX), so they stay visible while scrolling.

  $spreadsheet freeze $sheet 1,0   ;# freeze the first row

merge sheet indexFrom indexTo

Merges the cell range with the corners indexFrom and indexTo (see CELL INDEX) into one cell.

  $spreadsheet merge $sheet 40,0 40,2
  $spreadsheet merge $sheet A42 A44

numberformat ?-option value …?

Defines a number format for the current workbook and returns an ID (NUMFMTID) to refer to that format. The recognized options are:

  -format format: Any Excel format string (e.g. {#,##0.00 "€"}).

  -general: Excel general format.

  -date: Date format.

  -time: Time format.

  -datetime: Date/time format.

  -iso8601: Date/time in ISO 8601 notation.

  -number: Integer.

  -decimal: Decimal number with 2 decimal places.

  -red: Color negative values red (may be combined with -number
  and -decimal).

  -separator: Thousands separators (may be combined with
  -number and -decimal).

  -fraction: Fractions.

  -scientific: Scientific notation.

  -percent: Percentage.

  -text, -string: Text format.

  -tag tagname: Gives the format a name. This name may be used
  instead of the returned NUMFMTID.

pageMargins sheet ?-option value …?

Sets the page margins of the worksheet sheet. The recognized options are:

  -left -right -top -bottom -header -footer

  All options expect a floating point value in inch.

If some page margins of a worksheet are set with this method then all margins of that worksheet are set, either from the options given in the method call or from the current pageMarginsDefault values, if a margin was not in the method call.

pageMarginsDefault ?-option value …?

Sets the page margins of all worksheets which do not have individual margin settings. The options are the same as for pageMargins. The default margins are:

Margin	Inch
left	0.75
right	0.75
top	1
bottom	1
header	0.5
footer	0.5

pageSetup sheet ?-option value …?

Sets the page setup of the worksheet sheet.

Caveat: If no page setup properties at all are set for a sheet, applications use defaults derived from other sources. But if at least one page setup property is explicitly set with this method, the other properties not set will get the default value from the specification, which may be another value than without any page setup at all. For example if you only set the -orientation of a sheet then this may also result in another page size (because the application page size default may differ from the default recommended by the specification, which is Letter paper).

The recognized options are:

  -blackAndWhite 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 boolean: Print without graphics. Default: false.

  -errors (displayed|blank|dash|NA): How to print cell values
  of cells with errors. Default: displayed.

  -firstPageNumber integer: Page number of the first printed
  page; see -useFirstPageNumber. Default: 1.

  -fitToHeight integer: Number of vertical pages to fit on.
  Default: 1.

  -fitToWidth integer: Number of horizontal pages to fit on.
  Default: 1.

  -horizontalDpi integer: Horizontal print resolution of the
  device. Default: 600.

  -orientation (default|portrait|landscape): Orientation of the
  page. Default: default.

  -pageOrder (downThenOver|overThenDown): Order of the printed
  pages. Default: downThenOver.

  -paperHeight height: Height of custom paper as a number
  immediately followed by a unit identifier (as 297mm or 11in).
  No default.

  -paperWidth width: Width of custom paper as a number
  immediately followed by a unit identifier (as 210mm or
  8.5in). No default.

  The specification says that when -paperHeight and -paperWidth
  are specified, the rendering application shall ignore
  -paperSize.

  -paperSize paperSizeID: Specifies the paper size. Default: 1
  (Letter). See PAPER SIZE IDS for the defined values; the
  behaviour for other values is implementation-defined.

  -scale integer: Print scaling in percent. The rendering
  application may respect only values ranging from 10 to 400.
  Overridden when -fitToWidth and/or -fitToHeight are in use.
  Default: 100.

  -useFirstPageNumber boolean: Use the -firstPageNumber value
  for the first page number and do not auto number the pages.
  Default: false.

  -usePrinterDefaults boolean: Use the printer defaults (or the
  defaults of the print job).

  -verticalDpi integer: Vertical print resolution of the
  device. Default: 600.

See PAPER SIZE IDS for the possible -paperSize values.

presetsheets arrayName

Creates worksheets from an array previously read with ::ooxml::xl_read, including their data. The array is passed by name (without $). The sheets are created in their original order. The method adds the key sheetmap to the array: a dict mapping the original sheet index to the newly created worksheet handle. Usually combined with presetstyles:

  array set workbook [::ooxml::xl_read original.xlsx]
  set spreadsheet [::ooxml::xl_write new]
  $spreadsheet presetstyles workbook
  $spreadsheet presetsheets workbook

presetstyles arrayName

Takes over the style definitions (number formats, fonts, fills, borders and cell styles) from an array previously read with ::ooxml::xl_read. The array is passed by name (without $).

printarea sheet indexFrom indexTo

Sets the print area of the worksheet sheet, spanning from cell indexFrom to cell indexTo (see CELL INDEX).

  $spreadsheet printarea $sheet A1 C3
  $spreadsheet printarea $sheet 1,1 2,2

row sheet ?-option value …?

Starts a new row in the worksheet sheet and returns the row number. Without the -index option the next row is used; the column autoincrement is reset (see CELL INDEX). The recognized options are:

  -index index: Row number to jump to.

  -height height: Row height in points.

rowheight sheet row height

Sets the height of the given row (zero-based) of the worksheet sheet to height points, independently of writing cells.

  $spreadsheet rowheight $sheet 33 20

style ?-option value …?

Defines a cell style for the current workbook and returns an ID (STYLEID) to refer to that style. The recognized options are:

  -list: Returns the list of currently defined styles instead
  of a STYLEID.

  -numfmt NUMFMTID: From the method numberformat.

  -font FONTID: From the method font.

  -fill FILLID: From the method fill.

  -border BORDERID: From the method border.

  -xf XFID: Base cell format record to derive from.

  -horizontal alignment: Horizontal alignment of the cell
  content (e.g. left, center, right).

  -vertical alignment: Vertical alignment of the cell content
  (e.g. top, center, bottom).

  -rotate degree: Rotation of the cell content in degrees.

  -wrap: Enables text wrapping within the cell.

  -tag tagname: Gives the style a name. This name may be used
  instead of the returned STYLEID.

view ?-option value …?

Sets the workbook view properties. The recognized options are:

  -activetab tab: Zero-based index of the worksheet shown when
  the file is opened.

  -x twips -y twips: Position of the workbook window.

  -width twips -height twips: Size of the workbook window.

  -list: Returns the current view settings instead of setting
  them.

worksheet name

Creates a new worksheet with the given name and returns the sheet handle (SHEETID) used as first argument by the cell related methods. The characters : \ / ? * in the name are replaced and the name is truncated to the 31 characters allowed by the format.

write filename

Writes the spreadsheet to the file filename. If the filename does not have the suffix .xlsx then it is appended. If filename is empty, spreadsheetml.xlsx is used.

RESULT ARRAY

::ooxml::xl_read returns a flat array. In the following key table S stands for the zero-based sheet index and R,C for the zero-based row and column index:

With the -valuesonly option only the sheets, S,n, S,max_row, S,max_column and S,v,R,C keys are present.

CELL INDEX

Methods and options expecting a cell position accept it in two equivalent forms: numerically as row,column with zero-based row and column numbers, or in Excel notation (column letters followed by the one-based row number). 0,0 and A1 describe the same cell, 3,2 and C4 also.

The methods row, column and cell work with an autoincrement mechanism: without an explicit -index option row starts the next row, cell writes to the next column of the current row and column defines the next column. An explicit -index repositions; the autoincrement continues from there.

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

For writing prefer the autoincrement wherever possible: call $spreadsheet row $sheet once per row and write the cells in order without -index. This avoids off-by-one and index bookkeeping errors.

OPTION VALUE TYPES

Several option values share the same value types. These types are:

COLOR

There are several kinds 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:

BORDERLINESTYLE

The list of possible values is:

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

PATTERNTYPE

An ECMA-376 fill pattern type. The usual values are:

none
solid
darkDown
darkGray
darkGrid
darkHorizontal
darkTrellis
darkUp
darkVertical
gray0625
gray125
lightDown
lightGray
lightGrid
lightHorizontal
lightTrellis
lightUp
lightVertical
mediumGray

PAPER SIZE IDS

The possible -paperSize values and their meaning are:

DEPENDENCIES

Tcl >= 8.6.7
tdom >= 0.9.0

Reading .xlsx files uses the zlib command built into Tcl and needs no additional package. Only if an archive cannot be handled by the built-in reader (e.g. ZIP64), one of the optional fallback backends is used: the Tcl 9 zipfs, tcllib’s zipfile::decode or tclvfs vfs::zip >= 1.0.4.