Fillo - Read Excel files using Java Library

Fillo API

Fillo API is a Java-based API designed to fetch data from Excel files. It is an open-source API created by Codoid. One of its major advantages is that it facilitates reading Excel data using Fillo API in Selenium. Fillo API allows users to perform select, insert, and update operations with where conditions and supports multiple where conditions for data retrieval and manipulation. This makes it very convenient for data-driven automated scripts.

Why Fillo API?

Fillo API has several advantages over other APIs like Jxl API and Apache POI. These APIs require a lot of code to traverse rows and columns and fetch values stored in an Excel sheet. Fillo API, however, takes care of all this internally, so there is no need to worry about the size of rows and columns. Fillo API supports CRUD operations with SQL queries, while Apache POI requires a lot of Java programming code for the same.

Advantages of using Fillo API

• Erase the Excel values ​​through script instead of manually.
• Add the data in a particular row and column in an Excel sheet through the script.
• Update any particular row and column data through Fillo API.

Excel CRUD Operations with Fillo

Maven Dependency

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

Sample Excel file with data

Select

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.testng.annotations.Test;

public class CRUDWithFillo {

    String file = System.getProperty("user.dir") + "\\src\\main\\resources\\AutoTestData.xlsx";

    @Test
    public void select() throws FilloException {
        Fillo fillo = new Fillo();
        Connection connection = fillo.getConnection(file);
        String strQuery = "Select * from Sheet1 where full_name='Eloy Lesch'";
        Recordset recordset = connection.executeQuery(strQuery);

        while (recordset.next()) {
            System.out.println(recordset.getField("full_name"));
            System.out.println(recordset.getField("phone"));
            System.out.println(recordset.getField("email"));
            System.out.println(recordset.getField("region"));
            System.out.println(recordset.getField("postal"));
            System.out.println(recordset.getField("street"));
        }

        //Multiple Where conditions
        Recordset rs1 = connection.executeQuery("Select * from Sheet1 where full_name='Belle Emard' and region='Bellflower' and postal='47492-4003'");
        while (rs1.next()) {
            System.out.println(rs1.getField("STREET"));
        }

        //Where method
        Recordset rs2 = connection.executeQuery("Select * from Sheet1").where("full_name='Belle Emard'").where("region='Bellflower'");
        while (rs2.next()) {
            System.out.println(rs2.getField("STREET"));
        }
        recordset.close();
        connection.close();
    }
}

Insert

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.testng.annotations.Test;

public class CRUDWithFillo {

    String file = System.getProperty("user.dir") + "\\src\\main\\resources\\AutoTestData.xlsx";

    @Test
    public void insert() throws FilloException {
        Fillo fillo = new Fillo();
        Connection connection = fillo.getConnection(file);
        String strQuery = "INSERT INTO Sheet1(full_name,phone,postal,street) VALUES('Chathumal','1234',51000,'polonnaruwa')";

        connection.executeUpdate(strQuery);

        connection.close();
    }
}

Update

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.testng.annotations.Test;

public class CRUDWithFillo {

    String file = System.getProperty("user.dir") + "\\src\\main\\resources\\AutoTestData.xlsx";

    @Test
    public void update() throws FilloException {
        Fillo fillo = new Fillo();
        Connection connection = fillo.getConnection(file);
        String strQuery = "Update Sheet1 Set postal=1234 where region='Chesterfield' and full_name='Xavier Swift'";

        connection.executeUpdate(strQuery);

        connection.close();
    }

}

Delete

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.testng.annotations.Test;

public class CRUDWithFillo {

    String file = System.getProperty("user.dir") + "\\src\\main\\resources\\AutoTestData.xlsx";


    @Test
    public void delete() throws FilloException {
        Fillo fillo = new Fillo();
        Connection connection = fillo.getConnection(file);
        String strQuery = "DELETE FROM Sheet1 where full_name='Roel Hoeger'";

        connection.executeUpdate(strQuery);

        connection.close();
    }
}

Data Driven Testing with Fillo

As we know we can perform data driven testing with TestNG using DataProviders. Now we will be looking at how we can perform Data Driven Testing by reading the data from excel with Fillo.

Sample image of excel we used for our data driven testing.

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 {

    String file = System.getProperty("user.dir") + "\\src\\main\\resources\\sauceDemoCredentials.xlsx";

    @Test(dataProvider = "getDataFromExcelUsingFillo")
    public void testUsingSelenium(String username, String password) {
        WebDriver driver = new ChromeDriver();
        driver.get("https://www.saucedemo.com/");
        driver.findElement(By.id("user-name")).sendKeys(username);
        driver.findElement(By.id("password")).sendKeys(password);
        driver.findElement(By.id("login-button")).click();
        driver.quit();
    }

    @DataProvider
    public Object[][] getDataFromExcelUsingFillo() {
        try {
            Fillo fillo = new Fillo();
            Connection connection = fillo.getConnection(file);
            Recordset recordset = connection.executeQuery("SELECT * FROM Sheet1");
            int numberOfRows = recordset.getCount();
            Object[][] data = new Object[numberOfRows][2]; // 2 columns: username and password

            int row = 0;
            while (recordset.next()) {
                String username = recordset.getField("username");
                String password = recordset.getField("password");
                data[row][0] = username;
                data[row][1] = password;
                row++;
            }

            recordset.close();
            connection.close();
            return data;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

You can check below repository for more example

https://github.com/cozyloon/Fillo-JavaExcelLibrary