TSTool / Command / ReadTableCellsFromExcel

Overview
Command Editor
Command Syntax
Examples
Troubleshooting
See Also

Overview

The ReadTableCellsFromExcel command reads cells from a Microsoft Excel worksheet and transfers them into a table row. Currently a single table row can receive the output. The command is designed to extract values from named ranges in the Excel file, for example to harvest data from a data-entry form. Use this command in a template to iterate through rows in a table matched with separate Excel files.

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 showing Excel file parameters.

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

The following dialog is used to edit the command and illustrates the syntax of the command showing Excel cell map parameters.

ReadTableCellsFromExcel Command Editor for Excel Cell Map Parameters (see full-size image)

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

ReadTableCellsFromExcel Command Editor for Table Parameters (see full-size image)

Command Syntax

The command syntax is as follows:

ReadTableCellsFromExcel(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 will increase performance because later commands will not need to reread the workbook. Make sure to close the file in the last command that writes to the Excel file.	`False`
`ColumnCellMap` required	Indicate how to map table column names to Excel addresses. The format of the parameter is: `ColumnName1:ExcelAddress1,` `ColumnName2:ExcelAddress2,...` where the Excel addresses can be specified using `A1` notation, or named range. The column names cannot be repeated.	None – must be specified.
`TableID`	Identifier for table to be updated. None – must be specified.
`ColumnIncludeFilters`	Indicate table column names and pattern to use to include rows, in order to match a single row for output. The format of the parameter is: `ColumnName1:Pattern1,` `ColumnName2:Pattern2,...` where patterns can contain `*` to match a substring. For example, if used with a template to loop through table rows, use to match a unique identifier value in a column.	Include all rows (which will generate an error if the number of rows is not equal to one)
`IfTableRowNotFound`	Indicate the action if the row is not found for the transfer, one of: `Append` – append a new row to the table `Ignore` – ignore the data `Warn` – generate a warning `Fail` – generate a failure	`Warn`

Examples

See the automated tests.

Troubleshooting

See the main TSTool Troubleshooting documentation.