Reading Excel Data using Fillo API in Selenium

Fillo is an excel API for Java and is used to query both xls and xlsx files.

It supports SELECT, UPDATE, INSERT, and DELETE queries with or without clause. 

Maven Dependency

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

Select:

Fillo fillo = new Fillo();
Connection con = new fillo.getConnection("c:\test.xlsx");
sQuery = "Select * from Sheet1 where ID=111 and Department = Finance";
Recordset rs = con.executeQuery(sQuery);
While(rs.next()){
System.out.println(rs.getField("Name"))
}
rs.close();
connection.close();

Update:

Fillo fillo = new Fillo();

Connection con = new fillo.getConnection("c:\test.xlsx");
sQuery = "Update Sheet1 Set Salary = 120000 where ID=111 and Department = Finance";
Recordset rs = con.executeQuery(sQuery);
While(rs.next()){
System.out.println(rs.getField("Name"))
}
rs.close();
connection.close();

Insert:

Fillo fillo = new Fillo();
Connection con = new fillo.getConnection("c:\test.xlsx");
sQuery = "Insert into Sheet1(Name, Department, Salary) Values('Bala','Finance', 120000)";
Recordset rs = con.executeQuery(sQuery);
While(rs.next()){
System.out.println(rs.getField("Name"))
}
rs.close();
connection.close();

Multiple Where conditions:

Recordset recordset=connection.executeQuery("Select * from Sheet1 where column1=value1 and column2=value2 and column3=value3");

Where method:

Recordset recordset=connection.executeQuery("Select * from Sheet1").where("ID=100").where("name='John'");

LIKE Operator:

Recordset recordset=connection.executeQuery("Select * from Sheet1 where Name like 'Cod%'");

Set table starting row and column:

System.setProperty("ROW", "5");//Table start row
System.setProperty("COLUMN", "3");//Table start column
Fillo fillo=new Fillo();
Connection connection=fillo.getConnection(strFile);

Get all column names:

rs.getFieldNames()

Get row count:

rs.getCount()