Excel Reader and Data Provider (How to read all data from excel sheet and How to implement data provider in testNG using Excel)

Hello everyone, I would like to share my knowledge on how to perform data driven testing using excel reader, access data from data provider class and implement it in testNG framework.

What is Data Driven testing?

Data-Driven Testing (DDT) is a software testing methodology in which test data is separated from test logic, allowing you to run the same test multiple times with different inputs and expected results.

Instead of writing separate test cases for different input combinations, you write one test script and feed it with multiple rows of data, usually from:

• Excel files

• CSV files

• JSON/XML

• Databases

What is Excel Reader?

An Excel Reader is a utility or helper class in test automation that allows your test code to read data from Excel files (.xls or .xlsx). It's commonly used in data-driven testing to fetch test inputs and expected results. It is easy to update inputs without changing test code using excel reader.

What is Data Provider?

Data provider is primarily used TestNG framework. It is a powerful feature that enables data-driven testing in Selenium. Data Provider in TestNG is a special annotation (@DataProvider) that allows you to create methods that supply data to test methods. This method returns an array of objects, enabling you to run the same test with multiple data sets.

Benefits of Using Data Provider:

• Reduces code duplication

• Improves test coverage

• Works well with Excel, JSON, DB, and in-memory data

• Supports parallel execution if needed

• Helpful with Parameterization

• Ease of Maintenance

How to do it?

Here we will follow a simple step by step process to implement Excel with TestNG Data Provider.

Step 1: Add below dependencies to your pom.xml file to read data from excel sheet.

Step 2: Create Excel sheet as below format.

Excel files are presented as a table. This lets you modify them easily. If you need to add more data, you simply modify the file either in any text editor or in Microsoft Excel (in case of hard-coded values, you should modify both data and code).

Step 3: Copy that sheet to your maven project under "src/test/resources/testCode" folder as shown below.

Step 4: Create a new java class named ExcelReader.java under utilities package in maven project as shown below.

Step 5: You can write a java code as below to read all data from excel sheet in ExcelReader.java class.

Step 6: Create a new java class named TestDataProvider.java under utilities package in maven project as shown below.

Step 7: You can write java code as below in TestDataProvider.java class with the help of @DataProvider annotation.

-> Here we create Excel Reader class to retrieve all data from login sheet and store them in sheetDataCache variable. So that we don't need to access excelsheet again and again in TestDataProvider. And whenever required, we can access data from TestDataProvider class rather than excel sheet in test methods.

->  We setup Data Provider with the help of @DataProvider annotation. We can use any name for @DataProvider(name = "dataProviderName") which makes sense.

-> @DataProvider annotation method should return an Object [][], Where each inner array represents a set of parameters for the test methods.

Step 8: Create a TestNg test case under src/test/java folder for accepting data using Data Provider and annotate your test method with @Test specify the dataProvider and dataProviderClass as below.

-> Here we should provide dataProviderClass name, so it will provider data from TestDataProvider class. (dataProviderClass = TestDataProvider.class)

-> We used [dataProvider = "InvalidUserLoginData"] in invalidValidField test method, so TestDataProvider class will read all data from login sheet and return respective username, password and message in Object [][] format which is row no 3 & row no 4 in login sheet.

Conclusion:

Using @DataProvider in TestNG is a powerful way to implement data-driven testing. It allows you to reuse test methods by running them with single/ multiple sets of input data, improving test coverage and reducing code duplication.

Happy Learning :)

Thank you for reading and please give your valuable comments.