Using the Fillo API for Efficient Excel Data Handling in Selenium

open book lot

In the realm of automation testing, data-driven testing is a pivotal approach. Here, data is extracted from external sources such as Excel files, CSV files, or databases. Selenium, a renowned automation testing tool, often requires integration with other APIs to efficiently handle data-driven testing. One such API is the Fillo API, which offers a seamless experience when dealing with Excel data.

Introduction to Fillo API

Fillo is an open-source Java API, crafted by Codoid, that specializes in fetching data from Excel files. Its primary advantage is the ability to execute SQL-like queries for data retrieval and manipulation, making it a preferred choice for data-driven automated scripts.

Why Choose Fillo API?

Historically, Jxl API was the go-to solution for parameterization. However, with the advent of Apache POI and its subsequent popularity, the landscape changed. Both these APIs required extensive code to traverse rows and columns to fetch values from Excel sheets. Enter Fillo API, which eliminates the need to worry about the size of rows and columns. It internally manages these intricacies, offering a more streamlined approach. Unlike Apache POI, which demands a significant amount of Java programming for CRUD operations, Fillo API simplifies this with SQL queries.

Setting Up Fillo Maven Dependency

To integrate Fillo API into your project, you need to add its Maven dependency. Here's how you can do it:

XML
<dependency>
    <groupId>com.codoid.products</groupId>
    <artifactId>fillo</artifactId>
    <version>1.15</version>
</dependency>

For the latest version of the dependency, you can visit Maven Repository.

Reading Excel Data with Fillo

To fetch data from an Excel sheet using the Fillo API, follow these steps:

  1. Initialize Fillo: Create an instance of the Fillo class.
Java
Fillo fillo = new Fillo();

Establish Connection: Use the Fillo instance to establish a connection with the Excel file.

Java
Connection connection = fillo.getConnection("path_to_excel_file");

Execute Query: Write and execute the SQL-like query.

Java
String query = "SELECT * FROM SheetName";
Recordset recordset = connection.executeQuery(query);

Fetch Data: Iterate over the recordset to retrieve the data.

Java
while(recordset.next()) {
    // Fetch data using recordset.getField("ColumnName")
}

Close Connections: It's crucial to close the recordset and connection to prevent memory leaks.

Java
recordset.close();
connection.close();

Integrating Fillo with Selenium

To demonstrate the integration of Fillo API with Selenium, consider the following example:

Java
package HandleExcel;

import com.codoid.products.exception.FilloException;
import com.codoid.products.fillo.Connection;
import com.codoid.products.fillo.Fillo;
import com.codoid.products.fillo.Recordset;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class FilloWithSelenium {
    @Test(dataProvider = "getDataFromExcelUsingFillo")
    public void testUsingSelenium(String username) {
        System.setProperty("webdriver.chrome.driver", "path_to_chromedriver");
        WebDriver driver = new ChromeDriver();
        driver.get("https://www.seleniumeasy.com/test/basic-first-form-demo.html");
        driver.findElement(By.id("user-message")).sendKeys(username);
        driver.findElement(By.cssSelector("button[onclick='showInput();']")).click();
        driver.close();
    }

    @DataProvider
    public Object[] getDataFromExcelUsingFillo() {
        // Implementation to fetch data from Excel using Fillo
    }
}

Supported Queries by Fillo API

Fillo API supports a plethora of SQL-like queries, including:

  • SELECT: Fetch values from Excel and display them.
  • CREATE: Create a new sheet in the Excel file.
  • INSERT: Insert values into specific columns in an Excel sheet.
  • SELECT with WHERE Condition: Fetch specific data based on conditions.
  • UPDATE: Update values in the Excel sheet.

Benefits of Using Fillo API

  1. Automated Data Handling: Erase or add data in Excel sheets programmatically, eliminating manual interventions.
  2. Dynamic Data Updates: Update specific rows and columns in Excel sheets seamlessly.
  3. Simplified Data Retrieval: With SQL-like queries, fetching data becomes more intuitive and less code-intensive.

Additional Resources

For a hands-on experience and deeper understanding, you can refer to the Techhub Template and this comprehensive guide.

FAQs:

  • What is Fillo API? Fillo is an open-source Java API designed to fetch data from Excel files. It supports SQL-like queries for data retrieval and manipulation.
  • How does Fillo API differ from Apache POI? Unlike Apache POI, which requires extensive Java programming for CRUD operations, Fillo API offers a simplified approach with SQL queries.
  • Can I integrate Fillo API with Selenium? Yes, Fillo API can be seamlessly integrated with Selenium to enhance data-driven testing.
  • Where can I find more resources on Fillo API? You can refer to the Techhub Template and this guide for a comprehensive understanding.

Author