Skip to content

StateDMI / Command / ReadTableCellsFromExcel

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.

StateDMI 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 following dialog is used to edit the command and illustrates the syntax of the command showing Excel file parameters. See also the full-size image.

ReadTableCellsFromExcel command editor

ReadTableCellsFromExcel Command Editor Showing Excel File Parameters

The following dialog is used to edit the command and illustrates the syntax of the command showing Excel cell map parameters. See also the full-size image.

ReadTableCellsFromExcel Map

ReadTableCellsFromExcel Command Editor Showing Excel Cell Map Parameters

The following dialog is used to edit the command and illustrates the syntax of the command showing table parameters. See also the full-size image.

ReadTableCellsFromExcel Table command editor

ReadTableCellsFromExcel Command Editor Showing Table Parameters

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 troubleshooting documentation.

See Also