Make Excel Like a Database

Excel is the most commonly used data source used in test automation frameworks. I have come across one really interesting API that is called Fillo. It allows users to pass SQL queries and fetch data from excel.

Add the below maven dependency in pom.xml

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

Refer the code below

 public class FilloExcelReader 
 {
 Fillo fillo;
 Connection connection;
 //Setup the fillo connection
 public void setup(String excelFilePath) 
 {
     try
     {
         fillo= new Fillo();
         connection= fillo.getConnection(excelFilePath);
     }
     catch(Exception e)
     {
         System.out.println("Exception occurred"+e.getMessage());
     }
 }
 //close the connection
 public void cleanup()
 {
     connection.close();
 }
 //Select all values from a column
 public void selectField(String sheetName, String fieldName)
 {
     String strQuery="Select * from "+sheetName;
     Recordset rs = null;
     try
     {
         rs= connection.executeQuery(strQuery);
         while(rs.next())
         {
             System.out.println(rs.getField(fieldName));
         }
     }
     catch(Exception e)
     {
         System.out.println("Exception occurred"+e.getMessage());
     }
 rs.close();
 }
 //Select all values from the sheet
 public void selectSheet(String sheetName)
 {
     String strQuery="Select * from "+sheetName;
     Recordset rs = null;
     String fieldName;
     try
     {
         rs= connection.executeQuery(strQuery);
         ArrayList fieldNames = rs.getFieldNames();
        while(rs.next())         
        {             
           for(int i=0;i<fieldNames.size();i++)             
            {                 
               fieldName=fieldNames.get(i);
               System.out.print(rs.getField(fieldName));       
               System.out.print("---");             
             }             
           System.out.println("");         
       }
    }
  catch(Exception e)
  {
    System.out.println("Exeption occurred"+e.getMessage());
  }
     rs.close();
}
 //Update the value of a column
 public void updateColumn(String SheetName,String columnToUpdate, 
                          String value, String condition)
 {
     String strQuery="Update "+SheetName+" Set "+columnToUpdate+"='"+value
                      +"' where "+condition;
     try
     {
         connection.executeUpdate(strQuery);
         System.out.println("Sheet is updated");
     }
     catch(Exception e)
     {
         System.out.println("Exception occurred"+e.getMessage());
     }
 }
 //Inserting a row in excel sheet
 public void insertRow()
 {
     String strQuery="INSERT INTO EmpData(TC_NAME,FirstName)      
                      VALUES('GEN01','David')";
     try
     {
         connection.executeUpdate(strQuery);
         System.out.println("Sheet is updated");
     }
     catch(Exception e)
     {
         System.out.println("Exception occurred"+e.getMessage());
     }
 }
 
//Fetching a row from excel using multiple conditions
 public void multipleWhere(String sheetName, String cond)
 {
     String strQuery="Select * from "+sheetName+" where "+cond;
     Recordset rs = null;
     try
     {
         rs= connection.executeQuery(strQuery);
         ArrayList fieldNames = rs.getFieldNames();
         while(rs.next())
         {
             for(String fld: fieldNames)
             System.out.println(rs.getField(fld));
         }
     }
     catch(Exception e)
     {
         System.out.println("Exception occurred"+e.getMessage());
     }
    rs.close();
 }
 

public static void main(String[] args)
 {
     String filePath="./data/EmployeeDetails.xlsx";
     String sheetName="EmpData";
     FilloExcelReader reader = new FilloExcelReader();
     
     reader.setup(filePath);
     reader.selectField(sheetName, "Date Of Birth");
     reader.selectSheet(sheetName);
     reader.updateColumn(sheetName, "FirstName", "Sonu", "TC_NAME='POL4'");
     reader.insertRow();
     reader.multipleWhere(sheetName,"TC_NAME='POL4' and LastName='Dowson'");
     reader.cleanup();
    } 
 }

Excel File – EmployeeDetails.xlsx

Sheet Name= EmpData

TC_NAME	FirstName	LastName	Employee ID	Salary	Date Of Birth
POL1	Mark I	Dowson	121345	2000	9/8/1986
POL4	Mark II	Dowson	121346	3000	9/8/1986
POL5	Mark III	Dowson	12155	4000	9/8/1986

Reference – https://codoid.com/fillo/

Thanks for reading. Hope you liked the post.