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.

setting Sheet Mode to Replace the sheet contents in writing data in Excel

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.

setting Sheet Mode to Append to the sheet content in writing data in Excel

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”}.

sample of sheet content based on a condition when writing data in Excel

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.