Read excel file in Selenium using Fillo API
This article will present you with a complete idea about how to read and write test data from excel sheet using Fillo API .
- What is Fillo API?
- Maven Dependency of Fillo API
- Queries supported by Fillo API
What is Fillo API?
- Fillo is a Java API that is used for fetching data from Excel Files.
- Using Fillo API, we can consider excel sheet as data base and fetch data from excel [.xls OR .xlsx] by passing SQL query.
- This API can be used to trigger select, insert, and update operations with where conditions.
- Fillo API support CURD operation with just writing SQL queries but Apache POI support CURD operation with lot of java programming code.
Maven Dependency of Fillo API
 We can take latest maven fillo dependency using below link to “https://mvnrepository.com/artifact/com.codoid.products/fillo”Â
It could be in below format
com.codoid.products
fillo
1.21
If your project is not maven project then Fillo Libray can be downloaded from below URL and configured in the JAVA project to start using it.
 Queries supported by Fillo API
Below sql queries can be supported by Fillo API
- SELECT
- Select with Where Condition
- Create
- INSERT
- UPDATE
SELECT
- Select query fetch the values from a Excel sheet and display to the user.
package com.test;
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 class ReadExcelFillo {
public static void main (String args[]){
String query = "SELECT * FROM Sheet1";
String filePath = System.getProperty("user.dir") + "/src/main/java/com/testData/TestDataFillo.xlsx";
try {
Fillo fillo = new Fillo();
Connection connection = fillo.getConnection(filePath);
Recordset recordset = connection.executeQuery(query);
while (recordset.next()) {
System.out.println(recordset.getField("First Name") + " " + recordset.getField("Last Name") + " "+ recordset.getField("Age") + " " + recordset.getField("Place")
+ " " + recordset.getField("Gender"));
}
recordset.close();
connection.close();
} catch (FilloException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
SELECT With WHERE Condition
-
- we can fetch the data of particular row and column with the help of Select query with where Condition
package com.test;
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 class ReadExcelFillo {
public static void main (String args[]){
String query = "SELECT * FROM Sheet1 Where Gender='Male'";
String filePath = System.getProperty("user.dir") + "/src/main/java/com/testData/TestDataFillo.xlsx";
try {
Fillo fillo = new Fillo();
Connection connection = fillo.getConnection(filePath);
Recordset recordset = connection.executeQuery(query);
while (recordset.next()) {
System.out.println(recordset.getField("First Name") + " " + recordset.getField("Last Name") + " "+ recordset.getField("Age") + " " + recordset.getField("Place")
+ " " + recordset.getField("Gender"));
}
recordset.close();
connection.close();
} catch (FilloException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
CREATE
-
- CREATE query help to you create a sheet in Excel file.
package com.test;
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 class ReadExcelFillo {
public static void main (String args[]){
String filePath = System.getProperty("user.dir") + "/src/main/java/com/testData/TestDataFillo.xlsx";
try {
Fillo fillo = new Fillo();
Connection connection = fillo.getConnection(filePath);
connection.createTable("Sheet3", new String[] { "StudentID", "Student Name", "DepartmentID", "HOD name" });
connection.close();
} catch (FilloException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}}
INSERT
-
- INSERT query help to insert values in Excel sheet for particular column.
package com.test;
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 class ReadExcelFillo {
public static void main (String args[]){
String query = "INSERT INTO \"Sheet2\"(ID,\"First Name\",\"Last Name\",\"Age\") VALUES(2,'Rahul','Kumar',45)";
String filePath = System.getProperty("user.dir") + "/src/main/java/com/testData/TestDataFillo.xlsx";
try {
Fillo fillo = new Fillo();
Connection connection = fillo.getConnection(filePath);
connection.executeUpdate(query);
connection.close();
} catch (FilloException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}}
Result: 
UPDATE
-
- we can update any row and column values using update query
package com.test;
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 class ReadExcelFillo {
public static void main (String args[]){
String query = "UPDATE \"Sheet2\" SET Age='89' WHERE (ID=1 and \"First Name\"='Rahul' AND \"Last Name\"='Kumar')";
String filePath = System.getProperty("user.dir") + "/src/main/java/com/testData/TestDataFillo.xlsx";
try {
Fillo fillo = new Fillo();
Connection connection = fillo.getConnection(filePath);
connection.executeUpdate(query);
connection.close();
} catch (FilloException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}}
Result: 