TSTool / Command / WriteTimeSeriesToExcel

Overview
Command Editor
Command Syntax
Examples
Troubleshooting
See Also

Overview

The WriteTimeSeriesToExcel command writes one or more time series to an Excel workbook. The following functionality is provided:

Time series are written in columns (see WriteTimeSeriesToExcelBlock for alternate formatting options).
The worksheet and position in worksheet can be specified.
The output can be created or appended.
Separate columns can be written for date/time, date, and/or time. Currently date/time values are written as strings but Excel date objects will be enabled in the future.
Cell comments can be formatted using data flags and other time series properties.

TSTool uses the Apache POI software to read/write the Excel file and consequently functionality is constrained by the features of that software package.

Command Editor

The command is available in the following TSTool menu:

Commands / Spreadsheet Processing

The following dialog is used to edit the command and illustrates the syntax of the command.

WriteTimeSeriesToExcel Command Editor (see full-size image)

The following dialog is used to edit the command and illustrates the syntax of the command for output parameters.

WriteTimeSeriesToExcel Command Editor for Output Parameters (see full-size image)

The following dialog is used to edit the command and illustrates the syntax of the command for column and cell comment parameters.

WriteTimeSeriesToExcel Command Editor for Column and Cell Comments Parameters (ee also the full-size image)

The following dialog is used to edit the command and illustrates the syntax of the command for column style formatting parameters.

WriteTimeSeriesToExcel Command Editor for Column Style Formatting Parameters (see full-size image)

The following dialog is used to edit the command and illustrates the syntax of the command for data value cell style formatting parameters.

WriteTimeSeriesToExcel Command Editor for Data Value Cell Style Formatting Parameters (see full-size image)

Command Syntax

The command syntax is as follows:

WriteTimeSeriesToExcel(Parameter="Value",...)

Command Parameters

Parameter	Description	Default
`TSList`	Indicates the list of time series to be processed, one of: `AllMatchingTSID` – all time series that match the TSID (single TSID or TSID with wildcards) will be processed. `AllTS` – all time series before the command. `EnsembleID` – all time series in the ensemble will be processed (see the EnsembleID parameter). `FirstMatchingTSID` – the first time series that matches the TSID (single TSID or TSID with wildcards) will be processed. `LastMatchingTSID` – the last time series that matches the TSID (single TSID or TSID with wildcards) will be processed. `SelectedTS` – the time series are those selected with the `SelectTimeSeries` command.	`AllTS`
`TSID`	The time series identifier or alias for the time series to be processed, using the `*` wildcard character to match multiple time series. Can be specified using `${Property}`.	Required if `TSList=*TSID`
`EnsembleID`	The ensemble to be processed, if processing an ensemble. Can be specified using `${Property}`.	Required if `TSList=*EnsembleID`
`MissingValue`	Value to write to Excel for missing data values.	Original missing value.
`Precision`	The number of digits after the decimal for data values.	Determine from units.
`OutputStart`	The date/time for the start of the output. Can be specified using processor `${Property}`.	Use the global output period.
`OutputEnd`	The date/time for the end of the output. Can be specified using processor `${Property}`.	Use the global output period.
`OutputFile` required	The name of the Excel workbook file (`.xls` or `.xlsx`) to write, as an absolute path or relative to the command file location. If the Excel file does not exist it will be created. Can be specified using processor `${Property}`.	None – must be specified.
`Append`	Indicate whether the sheet being written should appended to an existing workbook.	`False` – create a new workbook.
`Worksheet`	The name of the worksheet in the workbook to write. If the worksheet does not exist it will be created. Can be specified using processor `${Property}`.	Write to the first worksheet.
`ExcelAddress`	Indicates the block of cells to write, using Excel address notation (e.g., `A1:D10`).	Must specify address using one of available address parameters.
`ExcelNamedRange`	Indicates the block of cells to write, using an Excel named range.	Must specify address using one of available address parameters.
`ExcelTableName`	Indicates the block of cells to write, using an Excel named range.	Must specify address using one of available address parameters.
`KeepOpen`	Indicate whether to keep the Excel file open (`True`) or close after creating (`False`). Keeping the file open will increase performance because later commands will not need to reread the workbook. Make sure to close the file in the last Excel command.	`False`
`DateTimeColumn`	The name of the column for the date/time.	Date if day, month, or year interval, `DateTime` otherwise.
`DateTimeFormatterType`	Specify the date/time formatter type, which indicates the syntax for `DateTimeFormat`. Currently, only `C` is supported, corresponding to the C programming language strftime() function, which is also used by other software.	`C`
`DateTimeFormat`	The format used to expand the date/time corresponding to each time series data value. The format string can contain literal strings and specifiers supported by the `DateTimeFormatterType`.
`DateColumn`	The name of the column for the date, if date and time need to be in separate columns.	`Date`
`DateFormatterType`	Specify the date/time formatter type, which indicates the syntax for `DateFormat`. Currently, only `C` is supported, corresponding to the C programming language strftime() function, which is also used by other software.	`C`
`DateFormat`	The format used to expand the date/time corresponding to each time series data value. The format string can contain literal strings and specifiers supported by the `DateFormatterType`.
`TimeColumn`	The name of the column for the time, if date and time need to be in separate columns.	`Time`
`TimeFormatterType`	Specify the date/time formatter type, which indicates the syntax for `TimeFormat`. Currently, only `C` is supported, corresponding to the C programming language strftime() function, which is also used by other software.	`C`
`TimeFormat`	The format used to expand the date/time corresponding to each time series data value. The format string can contain literal strings and specifiers supported by the `TimeFormatterType`.
`ValueColumns`	The name(s) of the column(s) corresponding to each time series, to use for the values. Specify with `%` formatters, `${ts:property}` and `${property}`. In the future a parameter may be added to more specifically define the column names.	`%L_%T`
`Author`	Name to use in comments for author.	No author
`ColumnComment`	A string to format for column heading comments for each time series. See `ValueColumns` for formatting options.	No comments.
`ColumnCommentWidth`	Width of column comments (number of columns).	`6`
`ColumnCommentHeight`	Height of column comments (number of rows).	Number of lines in comment.
`ValueComment`	See `ValueColumns` for formatting options. The string `${tsdata:flag}` can also be specified to include the data flag for the cell.	No comments.
`SkipValueCommentIfNoFlag`	Indicate whether the `ValueComment` should be skipped if the data flag for a cell is blank.	`True`
`CommentWidth`	Width of data value comments (number of columns).	`6`
`CommentHeight`	Height of data value comments (number of rows).	Number of lines in comment.
`ColumnConditionTableID`	Identifier for condition table used to format column headings (see below). Can be specified using processor `${Property}`.	Style formatting is not used.
`ColumnStyleTableID`	Identifier for style table used to format column headings (see below). Can be specified using processor `${Property}`.	Style formatting is not used.
`ConditionTableID`	Identifier for condition table used to format data value cells (see below). Can be specified using processor `${Property}`.	Style formatting is not used.
`StyleTableID`	Identifier for style table used to format data value cells (see below). Can be specified using processor `${Property}`.	Style formatting is not used.
`LegendWorksheet`	Name of worksheet where the legend should be created. The legend displays conditions and styles.	Time series worksheet.
`LegendAddress`	Address `A1`, etc. for upper-left of legend.	No legend will be created.

Excel cell formatting consists of number formatting, cell colors, cell width, etc. The Style Formatting tab provides general formatting capabilities for data cells. Consider the following time series data table, where the goal is to write the TSTool time series to Excel and format cells to indicate specific conditions of interest. This approach is implemented similarly in the WriteTableToExcel command.

Data Table used with WriteTimeSeriesToExcel Command Style Formatting (see full-size image)

To configure style-based formatting, a style table is defined listing properties for formatting cells. This table can be defined as a CSV file, Excel worksheet or other format and read into TSTool using a suitable command. The following figure illustrates a basic style table, which can be shared among commands.

Style Table used with WriteTimeSeriesToExcel Command for Specific Checks and Formatting (see full-size image)

The following style table column names are recognized. The default values for cell style properties not listed in the table are those provided by Excel.

Recognized Style Table Column Names

Column Name	Description	Default
`StyleID`	An identifier for the style, which is used in the format table below.	None – must be specified.
`FillForegroundColor`	The foreground fill color as a named color (e.g., `Red`), RGB triplet (`255,255,255`), or hex color `0xFFFFFF`. The following named colors are recognized: `black`, `blue`, `cyan`, `darkgray`, `gray`, `green`, `lightgray`, `magenta`, `none`, `orange`, `pink`, `red`, `white`, `yellow`.	No fill color.
`FillPattern`	Fill pattern for cells using `FillForegroundColor` and `FillBackgroundColor`.	Currently always defaults to solid - fill pattern is ignored.

The condition table indicates how the styles are used for time series data. The following example indicates that any time series with identifier (or alias) starting with ts should be processed to evaluate for missing, negative, and zero values.

Condition Table used with WriteTimeSeriesToExcel Command for Specific Checks and Formatting (see full-size image)

The column names for the condition table must be specified as shown. The Condition column recognizes the following specifiers:

${ts:property} – a time series property, which can be used to format comments for column headings or data value cells
${tsdata:value} – the time series data value, used to evaluate numerical conditions (cannot be used to format column headings)
${tsdata:flag} – the time series flag, used to evaluate string conditions (cannot be used to format column headings)

Values on the left and right of the operator must be separated with spaces to facilitate parsing the condition. The Condition column recognizes the following operators:

Condition Table Operators

Operator	Description**
`<`	Less than.
`<=`	Less than or equal to.
`==`	Equal to. Specify the right-side value as missing to check for missing.
`!=`	Not equal to. Specify the right-side value as missing to check for missing.
`>`	Greater than.
`>=`	Greater than or equal to.
`contains`	Specify for string values to check for substring (case-dependent).
`startswith`	Specify for string values to check for substring at start (case-dependent).
`endswith`	Specify for string values to check for substring at end (case-dependent).

Multiple conditions can be specified by using AND (surrounded by a single space) between conditions.

The following figure illustrates the output from the above example.

WriteTimeSeriesToExcel Command Example Output for Specific Checks and Formatting (see full-size image)

The following example illustrates using multiple conditions to implement a color scale.

Style Table used with WriteTimeSeriesToExcel Command for a Color Scale (see full-size image)

Condition Table used with WriteTimeSeriesToExcel Command for a Color Scale

The following is the output.

WriteTimeSeriesToExcel Command Example Output for Style Formatting (see full-size image)

Examples

See the automated tests.

Troubleshooting

See the main TSTool Troubleshooting documentation.