Excel File Interaction

You can use SenseTalk to access data stored in a Microsoft Excel (.xlsx) format. Using the functions in this section, SenseTalk reads data directly from individual cell values. 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.

This option does not support SenseTalk’s database capabilities. See Working with Excel in SenseTalk to configure SenseTalk to work with Excel using most of SenseTalk’s database capabilities.

Note: As a best practice, any files referenced within a SenseTalk script should be added to Eggplant Functional through the Resources pane in the Suite window. This method stores files to the Resources directory within the suite directory for the given suite. Although SenseTalk can access files stored elsewhere on the local file system, using the Resources directory provides additional capabilities. See the Resources Pane for more information.
Note: Excel color functions support only index and RGB colors. If a function shown in this section reads and processes an unsupported font color, it returns a color value of 0,0,1.

To configure SenseTalk to integrate with an Excel file, TestCases.xlsx, and to support the functions shown below, run a command similar to the one shown in the following example:

Example:

set MyExcelFile to Workbook(ResourcePath("TestCases.xlsx")) // Sets MyExcelFile as a reference variable for the TestCases.xlsx Excel file

After this command finishes, SenseTalk can read from and write to the TestCases.xlsx file using the name you provided in the command (MyExcelFile). Although the name shown in the example could be any name you choose, the examples shown below use MyExcelFile for consistency. 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.

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)

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

Tip: Use this Workbook() function reference with the Worksheet(), Cell() or CellRange() functions to access the contents of an Excel file, or to obtain other information about the workbook.

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

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>)

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:

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>

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:

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>")

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:

 

This topic was last updated on June 21, 2019, at 02:49:49 PM.

Eggplant icon Eggplantsoftware.com | Documentation Home | User Forums | Support | Copyright © 2019 Eggplant