In This Tutorial, You will Learn to Read and Write Data from an Excel File in Selenium WebDriver Using FILLO API and SQL statements.
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 and using them for performing analytics.
In this article we talk about, fetching values from an excel file using SQL statements and FILLO API.
Table of Contents:
FILLO API Overview
FILLO is a Java API that is used for fetching data from Excel Files. With the use of FILLO API, parameterization becomes very easy, i.e. running your test cases in Selenium with a different set of data.
Prior to this, we used JXL API for doing parameterization, 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. You just need to call the classes defined in the API and declare your test data in the excel file. Refer the official site for documentation and downloading of the jar file- FILLO Java Library
If you are using Maven, then use the Maven dependency.
FILLO jar with Maven Dependency
Fillo
- It is an excel API for Java language.
- It supports .xls and .xlsx files.
- It supports SELECT, UPDATE, and INSERT queries.
- Use with or without the WHERE clause and LIKE clause.
Operation Allowed With Fillo
SELECT Operation: SELECT statement performs the same function, as it does in fetching the values from a table and display to the end-user, same way here the SELECT statement returns data from an excel sheet.
Syntax :
SELECT * From Sheet Name
UPDATE Operation: UPDATE statement modifies the existing records in the excel sheet.
Syntax:
UPDATE sheet1 Set Column Name= ‘Value’
INSERT Operation: INSERT statement inserts a new record in an excel sheet.
Syntax:
INSERT INTO Sheet Name (ColumnName1,ColumnName2) VALUES (‘Val1’,’Val2’)
Perform the same operations with the WHERE and LIKE operators:
- “SELECT * from Sheet Name where ID=1 and name=’Jesus'”
- “SELECT * from Sheet Name where column1=value1 and column2=value2 and column3=value3”
- “UPDATE Sheet Name Set Country=’UK’ where ID=10 and name=’Jesus'”
- “SELECT * from Sheet Name where Name like ‘Jes%'”
Execution Steps To Be Followed For SELECT/INSERT/UPDATE Operation:
#1) //Create an Object of Fillo Class.
Fillo fillo = new Fillo();
#2) //Create an Object for Connection class and use the getConnection() method defined inside Fillo class, to establish the connection between the excel sheet and Fillo API’s.
Connection connection = fillo.getConnection(”excelPath”);
#3) // Select all the values present in a sheet. Those present inside the excel and store its output in a string variable.
String strSelectQuerry = "Select * from SheetName";
#4) // execute the Select query and store the result in a Recordset class present in the Fillo API.
Recordset recordset =connection.executeQuery(strSelectQuerry);
#5) //use while loop to iterate through all the columns and rows available in the sheet present inside the excel file.
while(recordset.next()){
// through getfield() method retrieve the data present in a particular column
System.out.println(recordset.getField("Column1")); }
#6) //Use an update query to update the details in the excel file.
String strUpdateQuerry = "Update Data Set SiteTitle = 'SoftwareTestingHelp.com' "; connection.executeUpdate (strUpdateQuerry);
#7) //Use Insert query to insert data in the excel sheet.
String strInsertQuerry = "INSERT INTO Data (SiteTitle,SiteTopic) Values('Bharat','NewDelhi')";
connection.executeUpdate (strInsertQuerry);
#8) // close the recordset to avoid a memory leak.
recordset. Close();
#9) // close the connection to avoid memory leak.
connection. Close();
Now using the above steps, in the Selenium framework, we will fetch the values of rows and columns stored in an excel file viz “TestFile.xlsx”, and enter the value in a demo site: https://wordpress.com/start/about?ref=create-blog-lp
Excel sheet having Row values: “New Test Help” and “Testing_Related_Contents”
Excel file placed in the Project folder:
Selenium Code
The complete Selenium code, which fetches the value from excel sheet and inputs in the test site is given below:
package softwareTestingHelp.Com;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
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 ReadWriteExcel {
static WebDriver driver;
//demo site -https://wordpress.com/start/about?ref=create-blog-lp
//download jar file -
https://mvnrepository.com/artifact/com.codoid.products/fillo
publicstaticvoid main(String args[]) throwsInterruptedException,
FilloException{
//Calling up the GoogleChrome driver
System.setProperty("webdriver.chrome.driver", "D:\\Srinivas\\New
folder\\exe\\chromedriver.exe");
driver = newChromeDriver();
//Opening the demo site - wordpress.com
driver.get("https://wordpress.com/start/about?ref=create-blog-lp");
//Locating the Test data excel file
String excelPath = ".\\Data\\TestFile.xlsx";
System.out.println(excelPath);
//Create an Object of Fillo Class
Fillo fillo = newFillo();
//Create an Object for Connection class and use getConnection()
//method defined inside Fillo class, to establish connection between
excelsheet and Fillo API’s.
Connection connection = fillo.getConnection(excelPath);
//Select all the values present in a sheet, which is present inside the
excel and store its output in a String variable
String strSelectQuerry = "Select * from Data";
System.out.println(strSelectQuerry);
//Execute the Select query and store the result in a Recordset class
present in Fillo API.
Recordset recordset =null;
recordset = connection.executeQuery(strSelectQuerry);
//use while loop to iterate through all columns and rows available in
sheet present inside excel file
while(recordset.next()){
System.out.println("Column 1 = " +recordset.getField("SiteTitle"));
String siteTitle = recordset.getField("SiteTitle");
driver.findElement(By.xpath("//input[@name='siteTitle']")).clear();
driver.findElement(By.xpath("//input[@name='siteTitle']")).sendKeys(siteTitle)
;
System.out.println("Column 2 = " +recordset.getField("SiteTopic"));
String siteTopic = recordset.getField("SiteTopic");
driver.findElement(By.xpath("//input[@name='siteTopic']")).clear();
driver.findElement(By.xpath("//input[@name='siteTopic']")).sendKeys(siteTopic)
;
connection.close();
}
//Use update query to update the details in excel file
Connection connection1 = fillo.getConnection(excelPath);
System.out.println("Column 1 value before Update clause = "
+recordset.getField("SiteTitle"));
String strUpdateQuerry = "Update Data Set SiteTitle =
'SoftwareTestingHelp.com' ";
System.out.println(strUpdateQuerry);
connection1.executeUpdate(strUpdateQuerry);
System.out.println("Column 1 value after Update clause = "
+recordset.getField("SiteTitle"));
//Use Insert query to update the data in excel sheet
Connection connection2 = fillo.getConnection(excelPath);
System.out.println("Column 1 and column 2 value before insert clause =
" +recordset.getField("SiteTitle")
+recordset.getField("siteTopic"));
String strInsertQuerry = "INSERT INTO Data (SiteTitle,SiteTopic)
Values('Bharat','NewDelhi')";
System.out.println(strInsertQuerry);
connection2.executeUpdate(strInsertQuerry);
System.out.println("Column 1 and column 2 value after insert clause = "
+recordset.getField("SiteTitle")
+recordset.getField("siteTopic"));
}
}
Snapshot of the Running code
Code output which shows the SQL statement result:
Excel file after Update and Insert operation is performed:
Demo website where we fetched data from the excel and inserted into the site:
Conclusion
- Fillo is a very useful java API for extracting data from an excel sheet and it supports both .xls as well as .xlsx Excel files.
- It supports SELECT, UPDATE and INSERT statements.
- Be careful while editing the excel file. If any rows or columns not required, then delete them.
- If you just erase the values from the rows and columns without deleting the entire row, then the API will assume that the fields have the value and will try to fetch the value from the rows and columns and in return, we will get blank values.
- Finally, don’t forget to close the connection once you are done with fetching the values from the excel.
Happy Reading!!