Excel Operations
Last updated
Avo Assure
Avo Assure - User Guide v24.1
Last updated
Description: This keyword or action instructs Avo Assure Client to set the Excel sheet location or the CSV file location for the next keywords. This step is the mandatory first step for all @Excel keywords.
Keyword
Input Syntax
Output Syntax
Supported Input Formats
setExcelPath
<File Path>; <Sheet Name (optional)>
(Optional)
Text
Static values
Dynamic variables
Input file can be: .xls, .xlsx and .csv
Find the snippet of the keyword below:
In the above example, after the test case debug, it sets the Excel sheet location for the next keywords, and the execution status “True”/ “False” will be stored in variable {setExcelPath}.
Description: This keyword or action instructs Avo Assure Client to clear the Excel or CSV file path already set.
Keyword
Input Syntax
Output Syntax
Supported Input Formats
clearExcelPath
NA
(Optional)
NA
Note: This keyword clears the Excel path already set using the “setExcelPath” keyword. Hence, this keyword should be provided only after all Excel action keywords.
Find the snippet of the keyword below:
In the above example, after the test case debug, it clears the Excel path that was already set, and the execution status “True”/ “False” will be stored in variable {clearExcelPath}.
Description: This keyword or action instructs Avo Assure Client to fetch the count of the total number of columns present in the specified Excel or CSV file, which have values, and save the result in the output variable.
Keyword
Input Syntax
Output Syntax
Supported Input Formats
getColumnCount
<Row number> (optional)
{Variable}
NA
Note:
The count will exclude the trailing empty cells.
If the input value is not specified, then this keyword fetches the count of all text columns.
If the input value specified is the row number, this keyword fetches the column count for the specified row number.
Find the snippet of the keyword below:
In the above example, after the test case debug, it fetches the count of the total number of rows present in the Excel file with value and stores the result in variable {Excel_col}.
Description: This keyword or action instructs ICE to fetch the count of the total number of rows present in the specified Excel or CSV file, which have values, and save the result in the output variable.
Keyword
Input Syntax
Output Syntax
Supported Input Formats
getRowCount
<Column Number (optional)>
{Variable}
NA
Note:
The count will exclude the trailing empty cells.
If the input value is not specified, then this keyword fetches the count of all rows that contain text.
If the input value specified is the column number, this keyword fetches the row count for the specified column number.
Find the snippet of the keyword below:
In the above example, after the test case debug, it fetches the count of the total number of rows present in the Excel file with value and stores the result in variable {Excel_row}.
Description: This keyword or action instructs Avo Assure Client to read the content from the input column to the specified cell of the Excel or CSV file.
Keyword
Input Syntax
Output Syntax
Supported Input Formats
readCell
<rowNum>; <colNum>
{Variable}
Text
Static Values
Dynamic Variables
Supported column name as alphabets/ integers
Find the snippet of the keyword below:
In the above example, after the test case debug, it reads the content from the input column to the specified cell of the Excel file and stores the value in the variable {17_1}, and the execution status “True”/ “False” will be stored in variable {readCell}.
Description: This keyword or action instructs Avo Assure Client to write the content from the input column to the specified cell of the Excel or CSV file.
Keyword
Input Syntax
Output Syntax
Supported Input Formats
writeToCell
<rowNum>; <colNum>; <Value>; <Type>
(Optional)
Text
Static Values
Dynamic Variables
Supported column name as alphabets/integers
Supported Types are:
Number
Formula
Boolean
String
Find the snippet of the keyword below:
In the above example, after the test case debug, it writes the content from the input column to the specified cell of the Excel file, and the execution status “True”/ “False” will be stored in variable {writeToCell}.
Description: This keyword or action instructs Avo Assure Client to clear the content of the Excel or CSV file cell given in the input.
Keyword
Input Syntax
Output Syntax
Supported Input Formats
clearCell
<rowNum>; <colNum>;
(Optional)
Text
Static Values
Dynamic Variables
Supported column name as alphabets/ integers
Find the snippet of the keyword below:
In the above example, after the test case debug, it clears the content of the Excel cell given in the input, and the execution status “True”/ “False” will be stored in variable {clearCell}.
Description: This keyword or action instructs Avo Assure Client to delete a particular row in the Excel or CSV file.
Keyword
Input Syntax
Output Syntax
Supported Input Formats
deleteRow
<rowNum>
(Optional)
Text
Static Values
Dynamic Variables
Find the snippet of the keyword below:
In the above example, after the test case debug, it deletes the 23rd row in the Excel file, and the execution status “True”/ “False” will be stored in variable {deleteRow}.
Description: This keyword or action instructs Avo Assure Client to copy one Excel file’s content to another.
Keyword
Input Syntax
Output Syntax
Supported Input Formats
copyWorkBook
<FilePath1>;<FiePath2>;<Option>(Optional);<text/special>(Optional)
(Optional)
Text
Static Values
Dynamic Variables
Note:
The keyword supports copying the workbooks of file formats xlsx to xlsx and xls to xls only.
The workbook specified in FilePath1 (source) is copied to the workbook specified in FilePath2 (destination).
The copying of the workbooks can be changed based on the options provided below:
0 or empty (default): This option will append the unique workbook sheets to the destination workbook and replace the workbook sheets with common names.
For example,
WorkBook1 consists of Sheet1, Sheet2 and Custom_Sheet3.
WorkBook2 consists of Custom_Sheet1, Custom_Sheet2 and Custom_Sheet3.
After this option is used for the keyword, WorkBook2 will consist of Custom_Sheet1, Custom_Sheet2, Sheet1, Sheet2, and Custom_Sheet3 (overwritten from WorkBook1).
1: This option will append all sheets, irrespective of common names, by incrementing the sheet names.
For example,
WorkBook1 consists of Sheet1, Sheet2 and Custom_Sheet3.
WorkBook2 consists of Custom_Sheet1, Custom_Sheet2 and Custom_Sheet3.
After this option is used for the keyword, WorkBook2 will consist of Custom_Sheet1, Custom_Sheet2, Custom_Sheet3, Sheet1, Sheet2, and Custom_Sheet3 (1).
2: This option will copy all the sheets in the source workbook and paste them into the destination workbook. It will remove all previously existing data from the destination workbook.
For example,
WorkBook1 consists of Sheet1 and Sheet2.
WorkBook2 consists of Custom_Sheet1, Custom_Sheet2 and Custom_Sheet3.
After this option is used for the keyword, WorkBook2 will consist of Sheet1 and Sheet2.
The copy method type (text/special) works as follows:
The ‘text’ option only copies the data. This method is not dependent on the system where the ICE is running.
The ‘special’ option copies all the data along with the formatting. This method depends on the system and will work only on the Windows OS installed on the Excel application.
This argument is case insensitive.
Its default value depends on the system's OS.
If the ‘copyWorkBook’ keyword is run on a Windows OS with an Excel application and the user provides no value for this argument, then ‘special’ will be the default copy method type.
If the ‘copyWorkBook’ keyword is run on any OS, NOT Windows, and the user provides no value for this argument, then ‘text’ will be the default copy method type.
Find the snippet of the keyword below:
In the above example, after the test case debug, the source workbook is copied to the destination workbook based on the option provided, and the execution status “True” / “False” will be stored in the output variable (if provided).
