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.
You can update and write values back to an Excel spreadsheet file, and 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.
Interactions with Excel databases occur through several special types of objects which serve as references to different elements within an Excel file. A workbook object refers to the workbook as a whole, and serves as the starting point for all access to the workbook file. A worksheet object is a reference to a single worksheet within a workbook. A cell object is a reference to a single cell within a worksheet.
Because these objects act as references to the respective parts of an Excel workbook file, changing any property of one of these objects or the value of a cell will change the corresponding aspect of the workbook itself. When passing one of these objects as a parameter to a command or function, you must explicitly pass it by reference if you want to access the workbook from the called handler. Otherwise SenseTalk will make a copy of the object and the value received by the called handler will no longer have a connection to the workbook.
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.
File References
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.
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.
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.
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
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 Object Properties
The following read-only properties are available for Workbook objects:
| Property | Definition |
|---|---|
colorMode | Defaults to "RGB" in order to interact with the workbook in Excel's RGB mode, but can be set to "RGB" or "Palette". In Palette mode, a "NoColor" value can be specified for elements that don’t have a color. Note Most common use will be to leave the colorMode set to "RGB". The main purpose of switching to Palette mode is to test for or set a color to "NoColor" which is not available in RGB mode. When in Palette mode, a limited number of colors are supported, so setting a color can result in a slightly different color. |
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 showing worksheetCount and worksheetNames:
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:
WorksheetFunction: Use this function to access a specific worksheet within a workbook.CellFunction: Use this function to access the value or properties of an individual cell.CellRangeFunction: 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 Object Properties
The following properties are available for Worksheet objects:
| Property | Definition |
|---|---|
name | The name of a sheet within a workbook. This property can be set to rename the worksheet. |
workbook | The workbook to which a sheet belongs (a Workbook object). Read-only. |
sequenceNumber | Each worksheet has a sequenceNumber property which indicates its sequence within the workbook. The first worksheet has a sequenceNumber of 1. You may set the sequenceNumber to a different value to move the worksheet to a different ordered position within the workbook. |
hidden | The hidden property of a worksheet indicates whether it is shown to the user or not. When retrieving the hidden property its value will be True (if the worksheet is hidden) or False (if it is visible). When setting the hidden property, it may be set to True or False, or to “Very” to set the worksheet to be “Very Hidden”. |
tabColor | The tabColor property of a worksheet specifies the color of the tab for that sheet. |
hyperlinks | The hyperlinks property of a worksheet is a read-only property which returns a list of all of the hyperlinks in the worksheet. Each hyperlink is returned as a property list with properties hyperlink, row, and column whose values are the link URL, and the row and column number of the cell. Related: Hyperlink Property of a cell |
Example of the name property:
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"]
Example of the workbook property:
put the worksheetNames of mySheet's workbook --> ["SpecialPeople","Departments","Facilities"]
Example of the sequenceNumber property:
// move a worksheet one position closer to the front of the workbook
if myWorksheet’s sequenceNumber > 1 then subtract 1 from the sequenceNumber of myWorksheet
Example of the hidden property:
turn off the hidden of worksheet(2) of myWorkbook // unhide the second worksheet
Example of the tabColor property:
set the tabColor of myWorksheet to yellow
Example of the hyperlinks property:
put the hyperlinks of myWorksheet
Related:
WorkbookFunction: Use this function to access an Excel workbook file.CellFunction: Use this function to access the value or properties of an individual cell.CellRangeFunction: 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
Cellfunction 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 Object Properties
The following properties are available for Cell objects:
| Property | Definition |
|---|---|
borderColor | The BorderColor property of a cell defines the color of the cell’s border (if any). Use the BorderStyle property to set the style of the border. |
borderStyle | The borderStyle property of a cell is the name of the style used in drawing the cell’s border. To display the border you must also set the borderColor property of the cell. The borderStyle may be set to one of: "DashDot", "DashDotDot", "Dashed", "Dotted", "Double", "Hair", "Medium", "MediumDashDot", "MediumDashDotDot", "MediumDashed", "None","SlantDashDot", "Thick", or "Thin". Spaces and capitalization in the name are ignored, so it is valid to set the borderStyle to something like “medium DashDot Dot”. |
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. |
columnWidth | The columnWidth property of a cell is the width of the column containing that cell in pixels. Changing the columnWidth of a cell will change the width of the entire column. |
fillPattern | The fillPattern property of a cell is the name of the fill pattern used in the cell’s background. To display the pattern you must also set one or both of the patternForegroundColor or patternBackgroundColor properties of the cell. The fillPattern may be set to one of: "DiagonalCrosshatch", "DiagonalStripe", "Gray12.5", "Gray25v, "Gray50", "Gray6.25", "Gray75", "HorizontalStripe", "None", "ReverseDiagonalStripe", "Solid", "ThickDiagonalCrosshatch", "ThinDiagonalCrosshatch", "ThinDiagonalStripe", "ThinHorizontalCrosshatch", "ThinHorizontalStripe", "ThinReverseDiagonalStripe", "ThinVerticalStripe", or "VerticalStripe". Spaces and capitalization in the name are ignored, so it is valid to set the fillPattern to something like “Thin diagonal CROSShatch”. |
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). |
horizontalAlignment | The horizontalAlignment property of a cell specifies the way the cell’s text contents are aligned within the cell horizontally (left to right). The horizontalAlignment may be set to one of: "Center", "Distributed", "Fill", "General", "Justify", "Left", "Merge", or "Right". Spaces and capitalization in the name are ignored, so it is valid to set the horizontalAlignment to something like “ disTRIButed ”. |
hyperlink | The hyperlink property of a cell shows the URL that the cell links to. You can set this value to change or add a hyperlink. Related: Hyperlinks Property of a Worksheet: a read-only property which returns a list of all of the hyperlinks in the worksheet. |
mergeRange | The MergeRange property of a cell identifies the full extent of the range of merged cells that the cell is part of, if any. If the cell is in a merge range, the value of the mergeRange property will be a list of two pairs of cell coordinates (each in [row#, column#] format). For example, if cell “A1” is in a merge range that extends from cell A1 to cell D2, the value of the mergeRange property will be [[1,1], [2,4]]. If the cell is not part of a merge range, its mergeRange is empty. When setting the mergeRange of a cell, you may specify the range as either a single value or as a pair of values, exactly as allowed for the CellRange function. Setting the mergeRange to empty will delete the cell’s associated merge range. |
patternForegroundColor, patternBackgroundColor | The patternForegroundColor and patternBackgroundColor properties of a cell define the foreground and background colors of the cell’s fillPattern (if any). Frequently only the pattern foreground color is set, and the background color is left un-set (white). |
rowHeight | The rowHeight property of a cell is the height of the row containing that cell in pixels. Changing the rowHeight of a cell will change the height of the entire row. |
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. |
verticalAlignment | The verticalAlignment property of a cell specifies the way the cell’s text contents are aligned within the cell vertically (top to bottom). The verticalAlignment may be set to one of: "Bottom", "Center", "Distributed", "Justify", or "Top". Spaces and capitalization in the name are ignored, so it is valid to set the verticalAlignment to something like “ CENTER ”. |
Example: Setting the borderColor of a cell
set the borderColor of cell(“A2”) of statusSheet to orange
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 the fontColor
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
Example: Setting the borderStyle property
set the borderStyle of salesSheet.cell(“A6”) to “slant DashDot”
Example: Expanding the columnWidth
add 10 to the columnWidth of cell(“D1”) of myWorksheet
Example: Setting the fillPattern
set the fillPattern of salesSheet.cell(“D4”) to “gray 12.5”
Example: Setting the horizontal alignment
set the horizontalAlignment of salesSheet.cell(“A6”) to “center”
Example: Setting a hyperlink
set the hyperlink of cell(3,5) of myWorksheet to “https://www.keysight.com”
Example: Using the mergeRange property
set the mergeRange of cell(“A2”) of sales to “A2:F2”
put the mergeRange of cell(“A2”) of sales —> [[2,1], [2,6]]
delete mySheet’s cell(5,9).mergeRange
Example: Setting the patternForegroundColor property
set the patternForegroundColor of cell(“A2”) of statusSheet to darkGreen
Example: Accessing the rowHeight property
put rowHeight of cell(5,1) of salesSheet into row5ht
Example: Setting the verticalAlignment property
set the verticalAlignment of salesSheet.cell(“A6”) to “Bottom”
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
9and12. 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, sincecellRangealways 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. - singleIdentifier is a property list specifying a rows: listOfRows property with a list of row numbers such as
rows: [4,15,9,12]. This will return the values for all columns of the specified rows (in the order given). This allows for a list of disjointed rows to be specified. - singleIdentifier is a property list specifying a columns: listOfColumns property with a list of column numbers or letters such as
columns: [A,C,Z,T]orcolumns: [1,5,7,10]. This will return the values for all rows of the specified columns (in the order given). This allows for a list of disjointed columns to be specified.
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.
InsertColumns Command
Behavior: Inserts one or more columns into a worksheet.
Command Syntax:
insertColumns worksheet, columnToInsert
insertColumns worksheet, columnRangeToInsert
insertColumns worksheet, firstColumn , lastColumn
columnToInsert is a number or letter indicating the column to be inserted. To insert a range of columns, either specify a range (columnRangeToInsert) or two numbers or letters indicating the first and last now separated by a comma (firstColumn and lastColumn). Columns can be referred to by number or letter.
Examples:
insertColumns greenSheet, 2 // insert a new column 2
insertColumns customerInfo, 1..3 // insert three new columns 1, 2, and 3
insertColumns myNewSheet, A,C // insert three new columns A, B, and C
DeleteColumns Command
Behavior: Deletes one or more columns from a worksheet.
Command Syntax:
deleteColumns worksheet, columnToDelete
deleteColumns worksheet, columnRangeToDelete
deleteColumns worksheet, firstColumn , lastColumn
columnToDelete is a number or letter indicating the column to be deleted. To delete a range of columns, either specify a range (columnRangeToDelete) or two numbers or letters indicating the first and last now separated by a comma (firstColumn and lastColumn). Columns can be referred to by number or letter.
Examples:
deleteColumns infoFileSheet, F // delete column F
deleteColumns myTestSheet, 6..9 // delete four columns 6, 7, 8, and 9
deleteColumns newSheet, H..J // delete three columns H, I, and J
InsertRows Command
Behavior: Inserts one or more rows into a worksheet.
Command Syntax:
insertRows worksheet, rowToInsert
insertRows worksheet, rowRangeToInsert
insertRows worksheet, firstRow , lastRow
rowToInsert is a number indicating the row to be inserted. To insert a range of rows, either specify a range (rowRangeToInsert) or two numbers indicating the first and last now separated by a comma (firstRow and lastRow). Rows are referred to by number.
Examples:
insertRows customerData, 10 // insert row 10
insertRows productSheet, 1500,1599 // inserts 100 new rows from 1500 to 1599
insertRows myTestSheet, 100..103 // inserts four rows 100, 101, 102, and 103
DeleteRows Command
Behavior: Deletes one or more rows from a worksheet.
Command Syntax:
deleteRows worksheet, rowToDelete
deleteRows worksheet, rowRangeToDelete
deleteRows worksheet, firstRow , lastRow
rowToInsert is a number indicating the row to be deleted. To delete a range of rows, either specify a range (rowRangeToDelete) or two numbers indicating the first and last now separated by a comma (firstRow and lastRow). Rows are referred to by number.
deleteRows productSheet, 100 // deletes row 100
deleteRows myWorksheet, 1..5 // deletes 5 rows from 1 to 5
deleteRows tooLongSheet, 2000,5500 // deletes rows 2000 through 5500
Additional Workbook Actions
In addition to the functions shown above for accessing the worksheets in a workbook, and accessing and modifying the contents of cells within a worksheet, the following actions can be performed on workbooks and worksheets.
NewWorksheet Function
Behavior: The NewWorksheet function creates a new worksheet within a workbook. The function returns a Worksheet object which refers to the new worksheet.
Syntax:
newWorksheet( options )
newWorksheet( workbook, sheetNameOrOptions )
workbook . newWorksheet( sheetNameOrOptions )
NewWorksheet Properties
When using the first syntax, the parameter options must include a workbook: property to specify the workbook. It may also include the options described under sheetNameOrOptions below.
The parameter sheetNameOrOptions may be either the name to be given to the new worksheet, or may be one or more of these properties:
name:the name of the new worksheet.sequenceNumber:the sequential position where the new worksheet should be inserted, with 1 making it the first sheet in the workbook. If not specified, the new worksheet will be added as the last worksheet in the workbook.copyFrom:an existing worksheet in the workbook that should be copied. If this is specified its value must be a worksheet object, and the new worksheet will be initialized as a copy of all of the values from the copyFrom worksheet.
Examples:
put newWorksheet(myWorkbook, “Flowers”) into flowerSheet
set myNewSheet to workbook’s newWorksheet(name:”Summary”, sequenceNumber:2, copyFrom: worksheet(1) of workbook)
set anotherSheet to newWorksheet(workbook:myWorkbook, name:“CustomerList”)