How to get data from excel sheet based on scenario name in Katalon

How to get data from an Excel sheet based on the Cucumber feature file scenario name in Katalon Studio  

For my project, I created some methods to read data from an Excel sheet based on my cucumber scenario. It is 100% working. By default, Katalon Studio doesn't have any method to get data from an Excel sheet based on the scenario name. So, in this post, I am going to explain how I did it.

 Let's get straight into the coding!

Create Excel file:

       Create an Excel sheet with the column name "Scenario name." Keep this Excel sheet inside your project. Then add it to the data files.

           

For ex:

Create Property file:

        Create a property file. In that property file, keep your Excel sheet name like you did in the below image. This is one of the best ways to reduce maintenance work by keeping everything in one place. 

Create Base Class and Annotations class:

        Create one base class to hold all the common methods and global variables. Here, create a variable with the name "rowNum." In this "rowNum" variable, we are going to store the current scenario's row number, and this will pass across the project wherever it is required.

class BaseClass {
	public static int rowNum = 0;
    }

            In annotations class, we will do two things. The first is @Before, and the second is @After.

The Before method will get all the scenario names from the Excel sheet and compare them with the current scenario name. If the scenario name is matched with an Excel sheet, it will take that row number and store it in the rowNum variable.

@Before
	public void before(Scenario scenario) {
		try {
			int totRows = findTestData(getPropertyValue("Global", "ExcelFileName")).getRowNumbers();
			for (int i = 1; i <= totRows; i++) {
				String scenarioName = findTestData(getPropertyValue("Global", "ExcelFileName"))
						.getValue("Scenario name", i);
				if (scenarioName.equals(scenario.getName())) {
					rowNum = i;
					break;
				}
			}
			if (rowNum == 0) {
				throw new Exception(scenario.getName() + " -- This Scenario is not available -- ");
			}
			System.out.println("Scenario's test data row number is : " + rowNum);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

    We must set the "rowNum" variable to 0 in the After method. so that it will be reset after every scenario execution.

@After
public void after() {
     rowNum = 0;
} 

    Now we have to create a method to get the data from the Excel sheet based on our column name and matched row number.

public static String getData(String dataName) {
		String value = null;
		try {
			String fileName = getPropertyValue("Global", "ExcelFileName");
			value = findTestData(fileName).getValue(dataName, GlobalAnnotations.rowNum);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Error("Error while getting data value from excel sheet");
		}
		return value;
	}

Sample Feature File:

Feature: Cucumber feature file

  Scenario: Sample scenario one
    Given Launch browser and application
    And Login to the application

  Scenario: Sample scenario two
    Given Launch browser and application
    And Login to the application

  Scenario: Sample scenario three
    Given Launch browser and application
    And Login to the application

Sample Step definition:

class main extends BaseClass{

	@Given("Launch browser and application")
	public void launchBrowser() {
		WebUI.openBrowser('https://en-gb.facebook.com/login.php')
		WebUI.maximizeWindow()
		KeywordUtil.logInfo("Browser launched..!")
	}

	@And("Login to the application")
	public void fbLogin() {
		WebUI.setText(findTestObject('Object Repository/email'), getData("Email"))
		KeywordUtil.logInfo("Email id is -- "+getData("Email"))
		WebUI.setText(findTestObject('Object Repository/password'), getData("Password"))
		KeywordUtil.logInfo("Password is -- "+getData("Password"))
		WebUI.closeBrowser()
	}
}

Output:

2022-12-09 16:24:45.739 INFO  com.kms.katalon.core.util.KeywordUtil    - Browser launched..!
2022-12-09 16:24:45.740 DEBUG c.runtime.formatter.CucumberReporter     - ✓ Launch browser and application
2022-12-09 16:24:45.757 DEBUG c.runtime.formatter.CucumberReporter     - STEP Login to the application
2022-12-09 16:24:47.624 INFO  com.kms.katalon.core.util.KeywordUtil    - Email id is -- sampleone@gmail.com
2022-12-09 16:24:48.605 INFO  com.kms.katalon.core.util.KeywordUtil    - Password is -- sampleone
2022-12-09 16:24:49.353 DEBUG c.runtime.formatter.CucumberReporter     - ✓ Login to the application
2022-12-09 16:24:49.358 DEBUG c.runtime.formatter.CucumberReporter     - ✓ SCENARIO Sample scenario one
2022-12-09 16:24:49.359 INFO  c.runtime.formatter.CucumberReporter     - END SCENARIO Sample scenario one

2022-12-09 16:24:56.819 INFO  com.kms.katalon.core.util.KeywordUtil    - Browser launched..!
2022-12-09 16:24:56.821 DEBUG c.runtime.formatter.CucumberReporter     - ✓ Launch browser and application
2022-12-09 16:24:56.824 DEBUG c.runtime.formatter.CucumberReporter     - STEP Login to the application
2022-12-09 16:24:58.137 INFO  com.kms.katalon.core.util.KeywordUtil    - Email id is -- sampletwo@gmail.com
2022-12-09 16:24:58.880 INFO  com.kms.katalon.core.util.KeywordUtil    - Password is -- sampletwo
2022-12-09 16:24:59.498 DEBUG c.runtime.formatter.CucumberReporter     - ✓ Login to the application
2022-12-09 16:24:59.499 DEBUG c.runtime.formatter.CucumberReporter     - ✓ SCENARIO Sample scenario two
2022-12-09 16:24:59.500 INFO  c.runtime.formatter.CucumberReporter     - END SCENARIO Sample scenario two

        The third scenario, as you can see, failed. because we have only two scenarios with different scenario names. This scenario failed because the scenario name was unavailable. The exception is given below.

=============== ROOT CAUSE =====================


For trouble shooting, please visit: https://docs.katalon.com/katalon-studio/docs/troubleshooting.html
================================================

12-09-2022 04:25:05 PM Login to the application

Elapsed time: 0.074s

Login to the application FAILED.
Reason:
com.kms.katalon.core.exception.StepFailedException: Error while getting data value from excel sheet
	at com.kms.katalon.core.util.KeywordUtil.markFailedAndStop(KeywordUtil.java:29)
	at com.kms.katalon.core.util.KeywordUtil$markFailedAndStop$0.call(Unknown Source)
	at BaseClass.getData(BaseClass.groovy:56)
	at main.fbLogin(main.groovy:57)
	at ✽.Login to the application(C:/Users/xxx/Katalon Studio/My First Web UI Project/Include/features/Blog.feature:13)

I hope you understand the concept.
 
Thanks!