Documentation

Looking for something in particular?

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. 

After execution of the test case, Automation will update the Excel based on the condition set above.

For more information, check out this course on University of Provar.

Review Provar on G2
Documentation library

Trying to raise a case with our support team?

We use cookies to better understand how our website is used so we can tailor content for you. For more information about the different cookies we use please take a look at our Privacy Policy.

Scroll to Top