In the subsequent chapters, we will be dealing with a lot Excel data. In order to make data manipulation simpler using Excel, Codoid, a software firm, introduced the Fillo API. This API can be used for both XLS and XLSX files. It's an open source API. Fillo can be used to trigger select, insert, and update operations with where conditions. It also supports like and multiple where conditions. Let's understand how to make use of this useful API. First of all, let's add the Maven dependency for Fillo. Put the dependency shown here in pom.xml:

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

 To start using Fillo in the code, create a class file in Eclipse and type the following code in a main method. This code first creates a Fillo object using the Fillo() no-argument constructor. After that it creates a Connection object. The value assigned to this object is obtained from the getConnection() method, which takes the full file path of the .xls or .xlsx file as an argument:

package org.packt.selenium;

import com.codoid.products.exception.FilloException;
import com.codoid.products.fillo.Connection;
import com.codoid.products.fillo.Fillo;
import com.codoid.products.fillo.Recordset;

  public static void main(String[] args) throws FilloException {
    Fillo fillo = new Fillo();
    Connection conn = null;
    try {
      conn = fillo
          .getConnection("D:\workspace\hdfc1\src\test\testAutomation\resources\Framework.xls");
    } catch (FilloException e) {
      throw new FilloException("File not found");

    }
    String query = "Select * from TestConfig where Execute_Flag='Y'";
    Recordset rcrdset = null;
    try {
      rcrdset = conn.executeQuery(query);
    } catch (FilloException e) {
      throw new FilloException("Error executing query");

    }
    try {
      while (rcrdset.next()) {
        System.out.println(rcrdset.getField("TestCaseID"));
      }
    } catch (FilloException e) {
      throw new FilloException("No records found");

    } finally {
      rcrdset.close();
      conn.close();
    }
  }

This piece of code will print out
TestCase1
TestCase3
TestCase4

The Framework.xls has the following content. As can be seen here, in the TestConfig tab, there are four test cases, out of which one has 'N' set in the Execute_Flag field. Hence, only three test case IDs get printed in the console:

Notice that in the preceding code, we have hardcoded the path to the Framework.xls file. In Chapter 7, The Command Pattern and Creating Components, we will be creating a config.properties file, which will hold config level details, such as the URL and framework file path. The idea of having property files is to have config or object repository-level data at a central location. This helps when tests have to be done in different environments from time to time.

Update is another common function that can be done using Fillo. We will look at update when we start creating the framework.