Automation V3: Writing Data into Excel
How to Setup Data to Write in Excel
First, we need data in a variable/file that we want to write in Excel.
Then, drag the Write test step into your test case from the Test Palette. Enter the parameters as follows:
- Data: Data which we need to write in Excel. It can be a file or a variable.
- Data store URL: Select Excel workbook from the dropdown.
- Workbook: Enter the workbook path.
- Worksheet: Enter the worksheet name.
- Sheet Mode: Select from three options: Replace the sheet contents, Append to the sheet content, or Update the sheet content based on a condition.
After selecting one of the three Sheet Mode options, refer to the additional steps below for your chosen option.
Option 1: Replace the Sheet Contents
If you set Sheet Mode to Replace the sheet contents, the list of items in the Tokens variable will be replaced in the sheet.
After execution of the test case, check the sheet content.
Option 2: Append to the sheet content
If you set Sheet Mode to Append to the sheet content, the list of items in the Tokens variable will be added to the end of the existing content in the sheet.
Data to be appended
After execution of the test case, check the sheet content.
Option 3: Update the sheet content based on a condition
If you set Sheet Mode to Update the sheet content based on a condition, complete the following information:
- Match by: Select Match by Position, Match on Condition, or Match by Columns
- Value range: The range of cells that need to be updated. If nothing is specified, the entire sheet will be targeted.
- Column Names: This has two options:
- Across the first row: Automation will start writing from the first row.
- Down the first column: Automation will start writing from the first columns.
After selecting one of the Match by options, refer to the additional steps below for your chosen option.
Option 3.1: Match by Position
If you choose Match by Position, then in Match Locator, enter the Row Number from where you want to start writing:
Note: Match Locator makes changes on the first column only.
After execution of the test case, check the sheet content.
Option 3.2: Match On Condition
If you choose Match on Condition, enter the condition based on which you want to write data in Match Locator.
For example:
Here, we stored data in a variable named Var where Var.Name and Var.Time will refer to the columns of the sheet.
After execution of the test case, Automation will update Name as MP India and Time as IST against text India, based on the condition set above {Location=” India”}.
Option 3.3: Match By Columns
If you choose Match By Columns, the match locator requires the Column name and Value to be matched in the input and output. Based on this it updates the data in the sheet rows.
For Example:
We have an Excel sheet with the following data.
We use the Write API using the condition Match by Columns.
Here, we stored data in a variable named var where var.Accountname and ACC1 will refer to the columns of the sheet and the value that is matched for the particular column respectively.
After execution of the test case, Automation will update the Excel based on the condition set above.
