Reading Excel Data using Fillo API in Selenium

Reading or writing data is one of the most commonly used operations, either fetching values from database tables or fetching values from an excel sheet. it’s will help you to have a Reading Excel Data using Fillo API in Selenium.

Fillo API

• Fillo is a Java API that is used for fetching data from Excel Files. It’s an open source API(created by Codoid) and it’s also help to Reading the Excel data using Fillo API in selenium. This API can be used to trigger select, insert, and update operations with where conditions. It also supports multiple where conditions.This makes data retrieval and data manipulation much easy when dealing with data-driven automated scripts.

Why Fillo API?

• We used to use Jxl API earlier for doing parameterise, later Apache POI came into the market and in both of these API’s we need to write big code, to traverse the rows and columns and fetch the values stored in an excel sheet But now with this new Fillo API, there’s no need to worry about the size of rows and columns, everything will be taken care of by the API internally.
• Fillo API support CURD operation with SQL queries but Apache POI support CURD operation with lot of java programming code.

Fillo Maven Dependency

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


• We can use any version dependency for suitable code. Go to “https://mvnrepository.com/artifact/com.codoid.products/fillo” and select latest version dependency.

Execution Steps To Be Followed For Reading Excel sheet

• Create an Object of Fillo Class.
  Fillo fillo = new Fillo();
• Create an Object for Connection class to establish the connection between the excel sheet and Fillo API’s.
  Connection connection = fillo.getconnection();
• Write the Query.
  String query = “Select * From Sheet1”;
• execute the Select query and store the result in a Recordset class present in the Fillo API.
  Recordset recordset = connection.executequery(query);
• Use the Count method for returning all data from excel.
  recordset.getcount();
• Create a loop for fetching all data from Excelsheet.
• Close the recordset to avoid a memory leak.
  recordset.close();
• Close the connection to avoid memory leak.
  connection.close();

Selenium code, which fetches the value from excel sheet.

package HandleExcel;

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.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 {

	@Test(dataProvider = "getDataFromExcelUsingFillo")
	public void testUsingSelenium(String username) {
		System.setProperty("webdriver.chrome.driver", "/home/knoldus/Desktop/Handle-Excel_Selenium/src/Driver/chromedriver");
		WebDriver driver = new ChromeDriver();
		driver.get("https://www.seleniumeasy.com/test/basic-first-form-demo.html");
		driver.findElement(By.id("user-message")).sendKeys(username);
		driver.findElement(By.cssSelector("button[onclick='showInput();']")).click();
		 driver.close();
	}

	@DataProvider
	public Object[] getDataFromExcelUsingFillo() {
		try {
			Fillo fillo = new Fillo();
			Object[] object = null;
			Connection connection = fillo.getConnection("./files/SampleExcelFile2.xlsx");
			Recordset recordset = connection.executeQuery("SELECT * FROM Sheet1");
			int numberOfRows = recordset.getCount();
			System.out.println("Size: " + numberOfRows);
			int i = 0;
			object = new Object[numberOfRows];
			while (recordset.next()) {
				object[i] = recordset.getField("Username");
				System.out.println(object[i]);
				i++;
			}
			recordset.close();
			connection.close();
			return object;
		} catch (FilloException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

}


Fillo API supports below queries

• SELECT
• CREATE
• INSERT
• SELECT WITH WHERE CONDITION
• UPDATE

SELECT

• Select operation fetching the values from a Excel and display to the end-user.

public void test1() {
		query = "SELECT * FROM Sheet1";
		filePath = "./files/SampleExcelFile1.xlsx";
		try {
			Fillo fillo = new Fillo();
			Connection connection = fillo.getConnection(filePath);
			Recordset recordset = connection.executeQuery(query);
			while (recordset.next()) {
				System.out.println(recordset.getField("ID") + " " + recordset.getField("First Name") + " "+ recordset.getField("Last Name") +  " " + recordset.getField("Age"));
			}
			recordset.close();
			connection.close();
		} catch (FilloException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

CREATE

• CREATE operation help to you create a sheet in Excel file.

public void test1() {
		filePath = "./files/SampleExcelFile1.xlsx";
		try {
			Fillo fillo = new Fillo();
			Connection connection = fillo.getConnection(filePath);
			connection.createTable("Sheet4", new String[] { "ID", "First Name", "Last Name", "Age" });
			connection.close();
		} catch (FilloException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

INSERT

• INSERT operation help to you insert values in Excel sheet for particular column.

public void test1() {

		String query = "INSERT INTO \"Sheet3\"(ID,\"First Name\",\"Last Name\",\"Age\") VALUES(1,'Rohit','Sharma',18)";
		String filePath = "./files/SampleExcelFile1.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();
		}
	}

SELECT With WHERE Condition

• Use of the WHERE condition, we can fetch the data of particular row and column.

public void test3() {
		query = "SELECT * FROM Sheet1 WHERE Age='33'";
		filePath = "./files/SampleExcelFile1.xlsx";
		try {
			Fillo fillo = new Fillo();
			Connection connection = fillo.getConnection(filePath);
			Recordset recordset = connection.executeQuery(query);
			while (recordset.next()) {
				System.out.println(recordset.getField("ID") + " " + recordset.getField("First Name") + " "
						+ recordset.getField("Last Name") +  " " + recordset.getField("Age"));
			}
			recordset.close();
			connection.close();
		} catch (FilloException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

UPDATE

• Using of UPDATE operation, we can update any row and column values through script.

public void test1() {
		query = "UPDATE \"Sheet2\" SET Age='19' WHERE (ID=2 and \"First Name\"='Aditya' AND \"Last Name\"='Dubey')";
		filePath = "./files/SampleExcelFile1.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();
		}
	}

Advantage of using Fillo API

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

Techhub Template Reference

https://github.com/knoldus/Selenium-datadriven-with-FilloAPI

Reference

https://www.softwaretestinghelp.com/read-write-data-from-excel-sheet-in-selenium-web-driver/#Selenium_Code