Skip to content

TSTool / Command / ReadPropertiesFromExcel


Overview

The ReadPropertiesFromExcel command reads cells from a Microsoft Excel worksheet and sets processor properties. This is useful when Excel is used to configure an analysis. The properties can be used directly by TSTool for commands that support ${Property} notation or can be used to expand a template command file. Property object types are determined from Excel cell type/format unless the type is specified with command parameters. If issues arise, check the Excel cell type and format.

TSTool uses the Apache POI software to read/write the Excel file and consequently functionality is constrained by the features of that software package. Check the TSTool Help / About menu for information on the POI version.

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.

ReadPropertiesFromExcel command editor for Excel parameters

ReadPropertiesFromExcel Command Editor for Excel Parameters (see full-size image)

The following dialog is used to edit the command and illustrates the syntax of the command for Excel to properties map.

ReadPropertiesFromExcel command editor for Excel to properties map parameters

ReadPropertiesFromExcel Command Editor for Excel to Properties Map Parameter (see full-size image)

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

ReadPropertiesFromExcel for Properties parameters

ReadPropertiesFromExcel Command Editor for Properties Parameters (see full-size image)

Command Syntax

The command syntax is as follows:

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

Command Parameters

Parameter                      Description Default                           
InputFile
required
The name of the Excel workbook file (*.xls or *.xlsx) to read, as an absolute path or relative to the command file location. None – must be specified.
Worksheet The name of the worksheet in the workbook to read. Read the first worksheet.
KeepOpen Indicate whether to keep the Excel file open (True) or close after creating (False). Keeping the file open can increase performance because later commands will not need to reread the workbook. False
PropertyCellMap
required
Indicate how to map property names to Excel addresses. The format of the parameter is:
PropertyName1:ExcelAddress1,
PropertyName2:ExcelAddress2,...
where the Excel addresses can be specified using A1 notation, named range, or * to use a named range that matches the property name. The property names cannot be repeated.
None – must be specified.
BooleanProperties A list of property names to be defined as Boolean objects. Determine from Excel cell type and format.
DateTimeProperties A list of property names to be defined as date/time objects. Determine from Excel cell type and format.
IntegerProperties A list of property names to be defined as integer objects. Determine from Excel cell type and format.

Examples

See the automated tests.

Troubleshooting

See the main TSTool Troubleshooting documentation.

See Also