Excel File Interaction

You can use SenseTalk to access data stored in Microsoft Excel format. Using the functions in this section, SenseTalk reads and writes data directly in individual cell values.

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.

Important

The functions described here are distinct from SenseTalk’s database capabilities. To access the contents of a spreadsheet as a database, see Working with Excel as a Database.

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 directly read data from or write data to an Excel 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.

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 object which holds a reference to the workbook file.

Parameters: The path name to an Excel file.

Syntax:
Workbook( ExcelFileName )

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 object reference with the Worksheet, Cell or CellRange functions to access the contents of an Excel file, or to obtain other information about the workbook.

Workbook Properties

The following read-only properties are available for Workbook objects:

Property	Definition
worksheetCount	The number of worksheets contained in a workbook. Read-only.
worksheetNames	A list of the names of all of the worksheets in a workbook. Read-only.

Example:

put workbook(excelFilePath) into myWorkbook
put myWorkbook is a workbook --> True   (myWorkbook is a workbook object)
put myWorkbook's worksheetCount --> 3
put the worksheetNames of myWorkbook --> ["People","Departments","Facilities"]

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: The Worksheet function gives access to a particular worksheet within a workbook. This function returns a Worksheet object which refers to a specific worksheet.

Parameters: A workbook reference and a sheet identifier. The workbook reference can be passed as the first parameter to the function (the first syntax below), or the Worksheet function can be called as a function of the workbook object (the second and third syntaxes 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, sheetIdentifier )
worksheet( sheetIdentifier ) of workbook
workbook .worksheet( sheetIdentifier )

Example:

set worksheet2 to Worksheet(MyExcelFile, 2) // Sets worksheet2 to refer to the second worksheet in the Excel file

Example:

set custBudget to MyExcelFile.Worksheet("Customer Budget") // Sets custBudget to refer to the worksheet named "Customer Budget"

Worksheet Properties

The following properties are available for Worksheet objects:

Property	Definition
name	The name of a sheet within a workbook.
workbook	The workbook to which a sheet belongs (a Workbook object). Read-only.

Example:

put worksheet(1) of myWorkbook into mySheet // access the first worksheet in the workbook
put mySheet is a worksheet --> True   (mySheet is a worksheet object)
put mySheet's name --> "People"
put "Special" before mySheet's name
put the worksheetNames of mySheet's workbook --> ["SpecialPeople","Departments","Facilities"]

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 is a Cell object -- a reference to a specific cell in the worksheeet -- whose value is the cell contents. The cell object's value might be a string, number, Boolean, or date, depending on what is stored in the spreadsheet cell. A cell object is also a container, allowing its value to be changed just like a variable. In addition, a cell object has a number of properties that can be accessed or changed, such as the text font and color of 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:
cell( worksheetReference, cellIdentifier )
cell( cellIdentifier ) of worksheetReference
worksheetReference .cell( cellIdentifier )

Example: Accessing a cell value using a worksheet reference and a cell identifier:

put Cell(worksheet1, "A3") into cellValue1

Example: Accessing a cell value by using Cell as a function of the worksheet with the cell identifier as its parameter:

put worksheet1.Cell(4,7) into workingBalance

Example: Using a Cell object as a container to update a cell value:

add 1 to worksheet1's Cell("E3") // updates cell E3 of worksheet1

Example: Using a Cell object as a container to set the cell's value:

put the date into Cell(worksheet1, [3,5]) // stores the date into row 3, column 5 of worksheet1

Cell Properties

The following properties are available for Cell objects:

Property	Definition
cellType	The cellType property may be "Number", "String", "Date", "Boolean", "Blank", "Empty", or one of the following error strings: "#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", or "#N/A". Read-only.
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 the cell belongs (a Worksheet object). Read-only.

Example: Determining the CellType of a cell:

put "PartNum" into cell("D1") of myWorksheet
put the CellType of cell("D1") of myWorksheet --> "String"
put 50519 into cell("D2") of myWorksheet
put the CellType of cell("D2") of myWorksheet --> "Number"

Example: Accessing other cell properties:

put the fontColor of Cell("A3") of worksheet1 // displays the font color of the specified cell in the worksheet

Example: Increasing the size of text within a cell:

add 4 to the fontSize of Cell("A3") of worksheet1 // increase the fontSize by 4

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: The CellRange function is used to access a rectangular range of worksheet cells. It can be called by passing one, two, or three parameters (a worksheet reference and zero, one, or two cell/row/column identifiers), or by calling it as a function of the worksheet or workbook with the identifiers as its parameters.

The cellRange function always returns a list of lists, even if the range includes only a single cell. Each item in the outer list holds the values for one row in the range. Each item in the inner lists is a Cell object (as described for the Cell Function), one for each column within that row.

Parameters: A worksheet object (or workbook object, to access its first worksheet) and up to two cell, row, or column identifiers, as described below.

Syntax:
cellRange( worksheet, firstIdentifier, secondIdentifier )
cellRange( worksheet {, singleIdentifier } )
worksheet .cellRange( firstIdentifier, secondIdentifier )
worksheet .cellRange( { singleIdentifier } )

The range of cells can be specified in many different ways. When no identifiers are given, cellRange returns a range encompassing all of the rows and columns of the worksheet containing defined values.

When two identifiers are given, the options include:

• firstIdentifier and secondIdentifier both identify specific cells, such as "C6" and "E8" or [6,3] and [8,5]. The cells returned include the rectangular range of cells with those two cells as its opposite corners.
• firstIdentifier and secondIdentifier both specify column letters, such as "B" and "D". The returned range includes all of the cells from that range of columns inclusively (e.g. all of the cells in columns B, C, and D in our example).
• firstIdentifier and secondIdentifier both specify row numbers, such as 9 and 12. The returned range includes all of the cells from that range of rows inclusively (e.g. all of the cells in rows 9, 10, 11, and 12 in our example).

When only one identifier is given, the options include:

• singleIdentifier identifies a specific cell, such as "F9" or [2,13]. That single cell is returned (as a list containing another list containing the cell, since CellRange always returns a list of lists).

• singleIdentifier specifies a single column letter, such as "G". All of the cells in that column are returned.

• singleIdentifier specifies a single row number, such as 42. All of the cells in that row are returned.

• singleIdentifier is a string containing two identifiers separated by a ":" character, such as "A:H", "16:42", or "E3:H7". All of the cells in that range of columns, rows, or cells are returned.

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(2, lastRowNum) of worksheet1 into columnsBasic // creates a list of lists for all of the rows from 2 to lastRowNum

Example:

put cellRange("C:E") of worksheet1 into columnsBasic // using a colon, creates a list of lists for a range of columns

Example:

put cellRange("6:8") of worksheet1 into columnsBasic // using a colon, creates a list of lists for a range of rows

Example:

put worksheet1's cellRange into allCellList // Assigns a list of lists to the allCellList variable with all cells in the worksheet

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.