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