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:
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 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:
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 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
9
and12
. 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, sincecellRange
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. - 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.