Get Excel Table
Verb: excelGetTable
Available from: <Standard>
Gets the value of a table from an Excel file, based on its row and column coordinates.
Syntax
excelGetTable --file(Excel) [--getfirstsheet(Boolean)] --sheet(String) [--entiretable(Boolean)] [--userange(Boolean)] --range(String) --fromrow(Numeric) --fromcolumn(Numeric) [--torow(Numeric)] [--tocolumn(Numeric)] [--hasheaders(Boolean)] (DataTable)=value (Numeric)=rows (Numeric)=columns
Inputs
| Script | Designer | Required | AcceptedTypes | Description |
|---|---|---|---|---|
| --file | File | Required | Excel | Excel file from which values are retrieved. |
| --getfirstsheet | Use First Spreadsheet | Optional | Boolean | When enabled, it takes the data available in the first spreadsheet. |
| --sheet | Spreadsheet | Only whenUse First Spreadsheet is False | Text | Name of the spreadsheet that contains the desired table. |
| --entiretable | Get Entire Table | Optional | Boolean | When enabled, obtains the value of the entire table. |
| --userange | Use Interval | Optional | Boolean | When enabled, it allows you to define the interval between the table cells. |
| --range | Interval | Only whenUse Interval is True | Text | Specifies the range of cells from which the table is retrieved.
The range is defined by separating the start and end cells with a colon (:). Example: A1: D5 |
| --fromrow | From Row | Only whenUse Interval is False | Number | Defines the starting row from which data should be retrieved. |
| --fromcolumn | From Column | Only whenUse Interval is False | Number | Defines the initial column from which data should be retrieved. |
| --torow | To Row | Optional | Number | Defines the last row to retrieve data from. |
| --tocolumn | To Column | Optional | Number | Defines the last column to retrieve data from. |
| --hasheaders | Has Headers | Optional | Boolean | When enabled, it considers the first row of the spreadsheet as a header. |
Outputs
| Script | Designer | AcceptedTypes | Description |
|---|---|---|---|
| value | Table | Data Table | Returns the Data Table obtained from the Excel file. |
| rows | Rows | Number | Returns the total number of rows of the obtained data table. |
| columns | Columns | Number | Returns the total number of columns of the obtained data table. |
Example
The command retrieves all the contents of the first spreadsheet from the Excel file named "excelFile". After that, the contents of the data table and the number of rows and columns of the spreadsheet in question are displayed on the IBM Studio console.
defVar --name excelFile --type Excel
defVar --name dataTable --type DataTable
defVar --name rows --type Numeric
defVar --name columns --type Numeric
excelOpen --file "excelTableFile.xlsx" excelFile=value
excelGetTable --file ${excelFile} --getfirstsheet --entiretable dataTable=value rows=rows columns=columns
excelClose --file ${excelFile} --save
logMessage --message "\r\nData Table: ${dataTable}\r\nRows: ${rows}\r\nColumns: ${columns}\r\n" --type "Info"
// Result: Data Table: information 01, information 02, information 03
// Rows: 1
// Columns: 3
Download File
To execute the sample script, it is necessary to download the file and inform its path in the File parameter of the Open Excel File command.
Remarks
For the correct operation of the Get Excel Table command, an Excel file must be opened using the Open Excel File command.