TestNG Data Provider implementation using Fillo API – Read Excel files

Hello Readers,

In this blog, we are going to see how to implement TestNG framework Data provider using Fillo API for reading Excel files.

Fillo API is a java based API used for fetching, updating, creating and deleting data from Excel files. It is an open source API created by Codoid and makes Data-Driven Testing (DDT) easier for automation scripts. It uses SQL-like queries to process the data from Excel files.

Why Fillo API?

• Other ApI’s like JXL and Apache POI are also available for working with Excel. The reason for choosing Fillo API is less coding.

• With Apache POI or JXL , we need deep coding knowledge and must write larger code blocks to get test data from Excel.

• With Fillo API, we can get the test data from Excel with minimal coding using simple SQL queries.

• It supports full CURD operations.

Advantages of Fillo API

• Extract data from both .xls and .xlsx excel files using SQL like queries.

• Reduces overall code size and complexity in implementing data driven framework.

• Supports CURD operations

• Can handle small and large amount of Excel files.

• Easy to integrate with Selenium.

Data-Driven Testing (DDT) and Data Providers

Data-driven testing and data providers are closely linked concepts in automation testing.

• Data-Driven Testing is an approach where test data is separated from the test logic. Instead of hardcoding test data directly into the script, the data is stored in an external files such as excel, .CSV, databases, JSON or XML. This allows the same test script to execute multiple times with different set of test data without modifying the code.

• Data Provider is a mechanism in TestNG that enables data- driven testing. If a method is annotated with @DataProvider, responsible for passing the test data (usually in the form of an Object[][]) to the test method. When a test method is configured to use a DataProvider, the test framework automatically invokes the test method for each set of data provided by the data driven approach.

• ExcelReader is a utility or helper java class in test automation framework that used to fetch data from Excel files.

Steps to Implement

1.      Add Maven Dependency

Add the following dependency in your pom.xml file.

<dependency>

              <groupId>com.codoid.products</groupId>

              <artifactId>fillo</artifactId>

             <version>1.22</version>

</dependency>

2.      Create Test Data in Excel

Prepare a sample Excel file with test data. Here is the sample login sheet.

Save it under your Maven project folder.

/ExploreFilloAPI/src/test/resources/TestData.xlsx as shown below.

3. Create Excel Reader Class:

Create a java class under utilities folder as shown below.  Eg. ExcelReaderFillo

This class will handle reading data from Excel using Fillo API.

It reads the data once, stores it in cache, and reuses it for multiple test cases and avoiding multiple Excel reads.

Here I am not using any conditional query statement because I want to read all the data from the excel sheet. However, if you want, you can add conditional WHERE statement to filter the test data from excel.

private static Map<String, Object[][]> sheetDataCache = new HashMap<>();

You may have a question that Why I am using Map, HashMap and String, Object[][] ???

Here is the explanation for using Map:

In real time , there will be multiple sheets, each with its own name and data.

We need a way to store the sheet name which will be in String format and sheetdata which will be in 2D array format(Object[][]).

Map is perfect for this because it stores key- value pairs. In our case key is String which holds sheet name and value is Object[][] which holds data for the sheet.

Here is the explanation for using HashMap :

HashMap is a class that implements Map using hashing for fast lookups. HashMap has get and put methods which do fast fetching and adding data.

Closing the connection and recordset helps prevent from memory leak.

4.      Create Data Provider Class:

Create the java class under utilities as shown below, eg:ExcelDataProvider

Write the following code,

Each methods is annotated with @DataProvider(name=“providerName”).

The provider name must match the test method configuration to fetch the correct data.

5.      Test Class Creation:

Finally, create a test class as shown below,

Annotate test methods with

@Test(dataProvider = “providerName”, dataProviderClass = className)

Ensure that all column names in the excel sheet match the test method parameters(in both name and order)

If there is a mismatch, data will not map correctly and mismatch errors will occur.  

With this setup , you can read Excel data once, cache it, and reuse it across multiple test cases and making automation framework efficient and scalable.

Conclusion

By integrating Fillo API with TestNG providers, we can build a clean and efficient data-driven testing framework. Fillo simplifies Excel interactions using SQL-like queries, reducing code complexity compared to traditional libraries like Apache POI. With the help of a caching mechanism, test data is read from Excel only once and reused across multiple test case.

Thank you for reading blog.