You can use SenseTalk to access data stored in Microsoft Excel format. Using the functions in this section, SenseTalk reads data directly from individual cell values.
On this page:
Working with Excel Files in SenseTalk
You can update and write values back to an Excel spreadsheet file, and you can update properties, such as text color or font size, in a cell. For example, you could configure SenseTalk to read data from a spreadsheet, use this data to drive tests, then write the test results back to the spreadsheet.
To work with an Excel file and to support the functions shown below, first define the Excel file you want to use in Eggplant Functional:
Example:
set MyExcelFile to Workbook(ResourcePath("TestCases.xlsx")) // Sets MyExcelFile as a reference variable for the TestCases.xlsx Excel file
After this command runs, SenseTalk can read from and write to TestCases.xlsx using the name you provided in the command (i.e., MyExcelFile).
To directly read data from or write data to the file, use these functions and properties:
The Workbook() Function identifies the workbook file and provides a starting point for accessing it using the other functions.
The Worksheet Function identifies a specific worksheet within a workbook.
The Cell Function gives access to the value and properties of a specific cell within a worksheet.
The CellRange Function lets you quickly access multiple cells in a range of rows, columns, or rectangular region of a worksheet.
Creating Multiple Connections to an Excel File
While Eggplant Functional allows you to create more than one connection to an Excel file, doing so can lead to issues and errors. For example, if you connect to an Excel file as both a workbook (described on this page) and as a database (described here), Eggplant Functional might return an error message like InternalWorkbookError - data source needed but no workbook value. You can fix this error by closing one of the connections.
You can also create separate connections to look at different tables in an Excel workbook, as shown below.
Example:
set myDB1 to {file:ResourcePath("Book1.xlsx"), type:"excel", writeable:"yes"}
set mySheet to table("Sheet1") of myDB1
set myDB2 to {file:ResourcePath("Book1.xlsx"), type:"excel", writeable:"yes"}
set myOtherSheet to table("Sheet2") of myDB2
Workbook Function
Behavior: The Workbook function is the starting point for direct access to an Excel spreadsheet. This function accepts one parameter, which is the path name to an Excel file with the .xlsx file extension, and returns a Workbook reference.
Parameters: The path name to an Excel file.
Syntax:
Workbook(<ExcelFile>.xlsx)
Reading Syntax Definitions
Syntax definitions for language elements follow these formatting guidelines:
- boldface: Indicates words and characters that must be typed exactly
- italic: Indicates expressions or other variable elements
- {} (curly braces): Indicate optional elements.
- [] (square brackets) separated by | (vertical pipes): Indicate alternative options where one or the other can be used, but not both.
Example syntax:
In this example, "open file" is required and must be typed exactly. "fileName" is a variable element; it is the path to and name of the file being opened. The following expression is optional and indicates why the file is being opened. If this expression is added, "for" is required and must be typed exactly. One of the following must be included, but only one, and they also must be typed exactly: "reading", "writing", "readwrite", "appending", or "updating".
Example:
set MyExcelFile to Workbook(ResourcePath("TestCases.xlsx")) // Sets MyExcelFile as a reference variable for the TestCases.xlsx Excel file
Example:
set MyExcelFile to Workbook("TestCases.xlsx") // Sets MyExcelFile as a reference variable for the TestCases.xlsx Excel file. The workbook() function looks in the default suite directory for the TestCases.xlsx Excel file
The following read-only properties are available for the Workbook function:
| Property | Definition |
|---|---|
| worksheetCount | The number of worksheets contained in a workbook. |
| worksheetNames | A list of the names of all of the worksheets in a workbook. |
Related:
- Worksheet Function: Use this function to access a specific worksheet within a workbook.
- Cell Function: Use this function to access the value or properties of an individual cell.
- CellRange Function: Use this function to access a range of cells, rows, or columns.
Worksheet Function
Behavior: This function gives access to a particular worksheet within a workbook. This function returns a Worksheet reference.
Parameters: Pass parameters using one of the following approaches:
- Pass both a workbook reference and a sheet identifier, as shown in the first syntax line below.
- Call it as a function of the workbook and include one additional parameter (the sheet identifier), as shown in the second syntax line below. The sheet identifier can be either the name of the sheet or its number (1 for the first sheet in the workbook, 2 for the second, etc.).
Syntax:
Worksheet(<workbook>, <sheet_identifier>)
<workbook>.Worksheet(<sheet_identifier>)
Reading Syntax Definitions
Syntax definitions for language elements follow these formatting guidelines:
- boldface: Indicates words and characters that must be typed exactly
- italic: Indicates expressions or other variable elements
- {} (curly braces): Indicate optional elements.
- [] (square brackets) separated by | (vertical pipes): Indicate alternative options where one or the other can be used, but not both.
Example syntax:
In this example, "open file" is required and must be typed exactly. "fileName" is a variable element; it is the path to and name of the file being opened. The following expression is optional and indicates why the file is being opened. If this expression is added, "for" is required and must be typed exactly. One of the following must be included, but only one, and they also must be typed exactly: "reading", "writing", "readwrite", "appending", or "updating".
Example:
set worksheet2 to Worksheet(MyExcelFile, 2) // Sets the second worksheet in the excel file to worksheet2
Example:
set worksheet1 to MyExcelFile.Worksheet("Customer Budget") // Sets the worksheet named "Customer Budget" to worksheet1
The following properties are available for the Worksheet() function:
| Property | Definition |
|---|---|
| name | The name of a sheet .within a workbook. |
| workbook | The workbook to which a sheet belongs. |
Related:
- Workbook Function: Use this function to access an Excel workbook file.
- Cell Function: Use this function to access the value or properties of an individual cell.
- CellRange Function: Use this function to access a range of cells, rows, or columns.
Cell Function
Behavior: Use this function to access the value or properties of an individual cell. If a workbook reference is used instead of a worksheet reference, the function uses the first worksheet in the workbook. It can be called by passing two parameters (a worksheet reference and a cell identifier), or by calling it as a function of the worksheet or workbook with the cell identifier as its parameter. The cell identifier can be a traditional cell name, such as "C6", or a pair of numbers identifying the row and column, such as (6,3). The value returned by the Cell function might be a string, number, Boolean, or date, depending on what is stored in the spreadsheet cell.
Parameters: Pass parameters using either of the following approaches:
-
Pass a worksheet reference and a cell identifier.
-
Call the Cell function as a function of the worksheet with the cell identifier as its parameter.
Syntax:
Pass a worksheet reference and a cell identifier:
Cell (<worksheetReference>, <cellIdentifier>)
Call the Cell() function as a function of the worksheet with the cell identifier as its parameter:
<worksheetReference>.<cellIdentifier>()
You can use SenseTalk commands to read or write the property values of cells in Excel spreadsheets. To display the cell property of a worksheet's cell:
put the <property> of <cellIdentifier> <cell reference> of <worksheet reference>
To set the cell property of a worksheet's cell:
set the <property> of <cellIdentifier> <cell reference> of <worksheet reference> to <new value>
Reading Syntax Definitions
Syntax definitions for language elements follow these formatting guidelines:
- boldface: Indicates words and characters that must be typed exactly
- italic: Indicates expressions or other variable elements
- {} (curly braces): Indicate optional elements.
- [] (square brackets) separated by | (vertical pipes): Indicate alternative options where one or the other can be used, but not both.
Example syntax:
In this example, "open file" is required and must be typed exactly. "fileName" is a variable element; it is the path to and name of the file being opened. The following expression is optional and indicates why the file is being opened. If this expression is added, "for" is required and must be typed exactly. One of the following must be included, but only one, and they also must be typed exactly: "reading", "writing", "readwrite", "appending", or "updating".
Example: Pass a worksheet reference and a cell identifier:
put Cell(worksheet1, "A3") into cellValue1
Example: Call the Cell function as a function of the worksheet with the cell identifier as its parameter:
put worksheet1.Cell(4,7) into workingBalance
Example: Pass a worksheet reference and a cell identifier:
add 1 to worksheet1's Cell("E3") — updates cell E3 of worksheet1
Example: Pass a worksheet reference and a cell identifier:
put the date into Cell(worksheet1 ( 3,5)) — stores the date into row 3, column 5 of worksheet1
Example: Using the properties in the supported cell properties table, shown below, read and display the specified cell property of the specified cell in worksheet1:
put the <property> of cell("A3") of worksheet1
Example: The Cell function also acts as a container, letting you store a new value into a cell:
put 23 into Cell ("B4") of worksheet1— sets the cell value in a specified cell in the worksheet
Example: You can also access cell properties:
put the cellType of Cell("A3") of worksheet1— displays the cellType property value from the specified cell in the worksheet
Example: Using the properties shown in the Supported Cell Function Properties table, shown below, set the specified cell property of the specified cell in worksheet1 to the specified <property value>:
set the <property> of Cell("A3") of worksheet1 to "<property value>"
| Property | Definition |
|---|---|
| cellType | The cellType property supports values of Number, String, Date, Boolean, Blank, Empty, or one of the following error strings: #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, or #N/A |
| columnHidden | A Boolean (true or false) that indicates whether the column containing the cell is hidden. |
|
fontColor |
The color of the text in the cell. |
| fontName | The font name of the text shown in the cell. |
| fontSize | The font size of the text shown in the cell. |
| formula | The formula present in the cell (if the cell contains a formula). |
| rowHidden | A Boolean (true or false) that indicates whether the row containing the cell is hidden. |
| worksheet | The worksheet to which a cell belongs. |
Related:
- Workbook Function: Use this function to access an Excel workbook file.
- Worksheet Function: Use this function to access a specific worksheet within a workbook.
- CellRange Function: Use this function to access a range of cells, rows, or columns.
CellRange Function
Behavior: This function is used to access a range of worksheet cells. It can be called by passing three parameters (a worksheet reference and two cell identifiers), or by calling it as a function of the worksheet or workbook with the cell identifiers as its parameters. The cell identifier can be traditional cell names, such as "C6" and "E8", or a pair of letters identifying the columns, such as ("C","E"). The values returned by the cellRange function are, for example, a set of values from the worksheet using the "C6" and "E8" cells as the inclusive borders of a rectangle of cell values or a list of lists of columns C through E. The cellRange function can also specify a range of rows by passing a beginning and ending row number. One other way to use the cellRange function is to pass a single string containing two values such as cellRange("C:E") (a range of columns) or cellRange("4:12") (a range of rows), separated by a ":". The cellRange function always returns a list of lists, even if the range includes only a single cell.
Parameters: Pass parameters using any of the following approaches:
-
Call the cellRange function as a function of the worksheet and include cell identifiers as its parameters.
-
Call the cellRange function as a function of the worksheet and include two column identifiers.
-
Call the cellRange function as a function of the worksheet and include two row identifiers.
-
Call the cellRange function as a function of the worksheet and include a range of columns separated by a colon as its parameters.
-
Call the cellRange function as a function of the worksheet and include a range of rows separated by a colon as its parameters.
Syntax:
Call the cellRange function as a function of the worksheet and include cell identifiers as its parameters:
<worksheet>.cellRange(<first cell identifier>, <second cell identifier>)
Call the cellRange function as a function of the worksheet and include two column identifiers:
<worksheet>.cellRange (<first column letter>, <second column letter>)
Call the cellRange function as a function of the worksheet and include two row identifiers:
<worksheet>.cellRange (<first row number>, <second row number>)
Call the cellRange function as a function of the worksheet and include a range of columns separated by a colon as its parameters:
<worksheet>.cellRange ("<first column letter>:<second column letter>")
Call the cellRange() function as a function of the worksheet and include a range of rows separated by a colon as its parameters:
<worksheet>.cellRange ("<first row number>:<second row number>")
Reading Syntax Definitions
Syntax definitions for language elements follow these formatting guidelines:
- boldface: Indicates words and characters that must be typed exactly
- italic: Indicates expressions or other variable elements
- {} (curly braces): Indicate optional elements.
- [] (square brackets) separated by | (vertical pipes): Indicate alternative options where one or the other can be used, but not both.
Example syntax:
In this example, "open file" is required and must be typed exactly. "fileName" is a variable element; it is the path to and name of the file being opened. The following expression is optional and indicates why the file is being opened. If this expression is added, "for" is required and must be typed exactly. One of the following must be included, but only one, and they also must be typed exactly: "reading", "writing", "readwrite", "appending", or "updating".
Example:
put worksheet1.cellRange ("C6", "E8") into columnsBasic --creates a list of lists for a range of cells
Example:
put worksheet1.cellRange("C","E") into columnsBasic -- creates a list of lists for a range of columns
Example:
put cellRange("6","8") of worksheet1 into columnsBasic -- creates a list of lists for a range of rows
Example:
put cellRange("C:E") of worksheet1 into columnsBasic -- using colons, create a list of lists for a range of columns
Example:
put cellRange("6:8") of worksheet1 into columnsBasic -- using colons, create a list of lists for a range of columns
Example:
put worksheet1's cellRange into allCellList -- Assigns a list of lists to the allCellList variable
Example:
put cellRange(1) of worksheet1 into firstRow -- Assigns a list of lists containing the values of row 1 to the firstRow variable
Example:
put worksheet1.cellRange("A","E") into columnsBasic-- Assigns a list of lists for each row for columns A to E to the columnsBasic variable
Example:
put cellRange("B3","D5") of worksheet1 into rectRange --Assigns a list of lists within a rectangular range defined by the cells to the rectRange variable
Related:
-
Workbook Function: Use this function to access an Excel workbook file.
-
Worksheet Function: Use this function to access a specific worksheet within a workbook.
-
Cell Function: Use this function to access the value or properties of an individual cell.
Eggplantsoftware.com | Documentation Home | User Forums | Support | Copyright © 2022 Eggplant