Hello Everyone! Welcome back to MODTECHSTUFF.
In this blog, we will talk about how to read data from an Excel file Using Selenium WebDriver!
File Read and Write is a basic piece of any product or software process. We as often as possible make a document, open it, and update something or erase it in our Computers. The same is the situation with Selenium Automation. We need a procedure to control records with Selenium.
Java gives us various classes to File Manipulation with Selenium. In this article, we will figure out how might we Read Data on Excel documents with the assistance of the Java and Apache POI library.
Installing JAVA in your System.
TestNG requires Java which means you should download and install Java on your system. In my previous blog, I have shown how to install JAVA on your computer in detail. Here is the link https://modtechstuff.blogspot.com/2020/05/how-to-start-automation-using-selenium.html
Eclipse Installation
Eclipse IDE is one of most liked Java development, though it’s hard to deny it has been losing ground to IntelliJ recently, Eclipse composed for the most part in Java and its essential use is for creating Java applications, yet it might likewise be utilized to create applications in another programming language including C, C++, C#, COBOL, D, Erlang, Fortran, Groovy, Haskell, JavaScript, Julia, Perl, PHP, Prolog, Python, R, Ruby, Scala, and Scheme. It can likewise be utilized to create archives with LaTeX (by means of a TeXlipse module)
In my previous blog, I have shown how to install ECLIPSE on your computer in detail. Here is the link https://modtechstuff.blogspot.com/2020/05/how-to-start-automation-using-selenium.html
TestNG Plugin Installation.
After Succesful installation of Eclipse IDE, we have to install a TestNG plugin
When you Launch Eclipse IDE for the first time, you will see a welcome screen like shown below
The next step is to click on ‘Help’ and go to Eclipse Market Place and in search text box write TestNG and Press GO Button.
You will see TestNG for Eclipse, click on the install button, in my case, I have already installed TestNG plugin on my machine
After installing the TestNG Plugin, Eclipse is restarted, click on File-> New-> Java Project.
The next screen will display Java Settings. Go to Libraries and click on Add Library.
Select TestNG and click next. TestNG is now ready for use.
The next step is to create a TestNG File by Opening Eclipse and go to Package Explorer view. Select the test project you named earlier.
Right-click on src and choose new
->other
Now click on TestNG and it will display TestNG Class.
On the next screen, it will ask you to enter new TestNG class details.
After clicking on the finish button, you will see a screen like shown below
Congratulations Eclipse will now create your TestNG template automatically and You can start writing your code!
What is Selenium?
Selenium is an open-source automated testing tool used to automate web applications. You can utilize various programming languages like Ruby, Java, NodeJS, PHP, Perl, C#, Python, and so on to make Selenium Test Scripts. Testing done utilizing the Selenium tool is generally term as Selenium Testing.
Selenium Software isn't only a single tool however a set-up of different software, each piece taking into account distinctive testing needs of an association. To begin with Selenium's most recent adaptation (WebDriver-based usage), you'll just need a single selenium JAR file (selenium-independent server-'{'version'}'.jar) to run tests both locally and on remote systems.
Brief History of Selenium:
Selenium was developed by Jason Huggins in 2004. An architect at ThoughtWorks, he was taking a shot at a web application that necessary successive testing. Having understood that the tedious Manual Testing of their application was turning out to be increasingly wasteful, he made a JavaScript program that would consequently control the program's activities. He named this program JavaScriptTestRunner.
The Selenium name originated from a simple joke in which Jason broke one an opportunity to his group. Another automated testing structure was mainstream during Selenium's turn of events, and it was by the organization called Mercury Interactive (indeed, the organization that initially made QTP before it was procured by HP).
Components of Selenium Suite:
- Selenium (IDE)
- Selenium Remote Control (RC)
- Selenium WebDriver
- Selenium Grid
Selenium IDE:
Selenium Integrated Development Environment (IDE) is the most simple system in the Selenium suite and is the least demanding one to learn in the present scenario. Due to its simplicity, the Selenium IDE should just be utilized for only a prototyping tool. If you further develop advanced test cases, you should employ either Selenium RC or WebDriver.
Selenium RC:
Selenium RC was the lead testing system of the entire Selenium venture for quite a while. This is the primary automated web testing device that permitted clients to utilize a programming language they like. As of form 2.25.0, you can use Java, Ruby, Python, C#, Pearl in Selenium RC.
Selenium WebDriver:
The Selenium WebDriver demonstrates itself to be better than both Selenium IDE and Selenium RC in numerous perspectives. It actualizes a progressively current and stable methodology in the automation of the browser activities. WebDriver, not at all like Selenium RC, doesn't depend on JavaScript for Automation. It controls the browser by legitimately communicating with it. Selenium RC is compatible with Java, Ruby, Python, C#, Pearl.
Selenium Grid:
Selenium Grid is a tool utilized along with Selenium RC to run equal tests across various machines and various browsers simultaneously. Equal execution implies running different tests immediately.
Allows concurrent running of tests in numerous browsers and environments.
Save time effectively. Uses the nodes and-hubs approach. The hub works as a central source of Selenium command to every node associated with it.
Which Selenium YOU need?
1. Selenium IDE:
To find out about approaches on automated testing and Selenium, including:
Selenese commands, for example, type, open, clickAndWait, attest, confirm, and much more. Locators, for example, id, name, xpath, css selector. Executing customized JavaScript code utilizing runScript To develop tests with no earlier practical knowledge in programming. To make simple test cases and test suites that you can send out later to RC or WebDriver.
2. Selenium RC:
To structure a test utilizing a more expressive language than Selenese
To run your test against various programs (with the exception of HtmlUnit) on various working frameworks.
To send your tests over numerous situations utilizing Selenium Grid, the application against another program that underpins JavaScript and web applications with complex AJAX-based situations.
3. Selenium WebDriver:
To utilize a specific programming language in structuring your test cases. To test applications that are using more AJAX-based functionalities.
To execute tests on the HtmlUnit program.
4.Selenium Grid:
To run your Selenium RC contents in numerous test cases and working frameworks in parallel. To run a large number of the test suite, that can be finished as soon as possible.
Why do I need Selenium Automation Testing?
Let us suppose you are a manual tester who has this situation: Checking whether the Facebook sign up page approves input strings and registers a customer effectively in most recent versions of Chrome and Safari.
Accept that the sign-up page has these info fields—username, email address, and password. The tester will get a Windows 10 And Mac OS and follow these means, successively, on most recent variants of Chrome and Safar.
Enter the URL in the location bar (www.facebook.com/signup)
Enter an invalid string in each info field (email, username, and password)
Check whether the information was approved against comparing regexes and data in the database
Enter 'valid' strings in each information field; click Sign Up
Check whether "Welcome to Facebook, '{'username'}'" page appeared
Check whether the framework database made another userID for '{'username'}'
Depending upon the number of manual testers it might take a few hours to weeks to be certain that the web application is completely Tested.
Present-day engineers don't have that sort of time to allow for testing, however, they can't put aside comprehensive testing in a rush to discharge either. This is the reason Selenium comes under the picture.
With the help of Selenium Automation Testing, we can reduce labour time and get more accurate results in no time.
How to Install Selenium:
We can install Selenium in just three steps. The first step is installing JAVA SDK, the second step is installing the Eclipse IDE and finally some Selenium Driver Files.
Step.1 Installing JAVA on your System:
You can download and install JAVA Software Development Kit(SDK) JDK here
After clicking on JDK download, you have to choose Your Operating System
as shown below
This JDK version comes with an inbuilt Java Runtime Environment (JRE), so the advantage of JDK you do not need to download and install the JRE additionally.
Next step is
- Go to Start and search for ‘System’
- Click on ‘System’
- Click on ‘Advanced system settings’
- Click on ‘Environment Variables’ under the ‘Advanced’ tab as shown below:
- Under ‘Variable value’, at the end of the line, enter the following path – %JAVA_HOME%bin;
- Now, you can click ‘OK’ and you are done.
Once the installation is finished, open command prompt and type " java-version". If you see the following screen, then Congratulations you successfully installed JAVA on your System.
Step.2 Installing Eclipse IDE
Download the latest version of Eclipse IDE here
After downloading you will get a zip file, unzip it and install. Launch the Eclipse after a successful installation. When you launch eclipse IDE a popup window will open and asked to select for a workspace, just accept the default location.
The next step is to create a new project through the File > New > Java Project. Name the project as a "new project".
A new pop-up window will open enter details
- Project Name
- Location to save the project
- Select an execution JRE
- Select layout project option
- Click on Finish button
Step.3 Download Selenium jar File:
Download the Selenium jar file here
Extract the zip file and save it in your selenium folder. Next, you have to download Chrome Driver for Google Chrome and Gecko Driver if you are using Mozilla Firefox. In the same downloads page, scroll further down and you will find third-party drivers section.
To download driver click here
now next steps are
- Right-click on "new project" and select Properties.
- On the Properties dialog, click on "Java Build Path".
- Click on the Libraries tab, and then
- Click on "Add External JARs.."
\How to manage excel file using Apache POI library.
To read an Excel file, Apache gives a well-known library known as POI. This library is sufficiently competent to peruse and compose the two XLS and XLSX document configuration of Excel. To read XLS records, an HSSF usage is given by the POI library. To peruse XLSX, XSSF usage of the POI library will be the decision. How about we study these executions in detail.
In the event that you are utilizing Maven in your undertaking, the Maven dependence will be required,
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
You can basically download the most recent form POI containers from http://poi.apache.org/download.html and download the most recent compress record.
To read an Excel file, Apache gives a well-known library known as POI. This library is sufficiently competent to peruse and compose the two XLS and XLSX document configuration of Excel.
To read XLS records, an HSSF usage is given by the POI library.
To peruse XLSX, XSSF usage of the POI library will be the decision. How about we study these executions in detail.
In the event that you are utilizing Maven in your undertaking, the Maven dependence will be required,
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.1</version> </dependency>
You can basically download the most recent form POI containers from http://poi.apache.org/download.html and download the most recent compress record.
Read data from Excel file
Here is the complete code to read data from Excel file
package ExcelClasses;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.HashMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class XLReadConcepts {
public static void main(String[] args) throws IOException {
HashMap hmap = getDatafromExcel("TestData/Tcases.xls", "TestData", 2);
System.out.println(hmap.get("UserName"));
System.out.println(hmap.get("Password"));
System.out.println(hmap.get("ModuleName"));
System.out.println(hmap.get("SubModuleName"));
}
public static HashMap getDatafromExcel(String filepath, String sheetname, int rownumber) throws IOException {
HashMap hmobj = new HashMap<String, String>();
File file = new File(filepath);
FileInputStream fis = new FileInputStream(file);
Workbook workbook = getExcelWorkBook(filepath);
Sheet sheet = workbook.getSheet(sheetname);
Row firstRow = sheet.getRow(0);
Row row = sheet.getRow(rownumber);
int colCnt = row.getLastCellNum();
for(int j=0;j<colCnt;j++){
String colname = firstRow.getCell(j).getStringCellValue();
Cell cell = row.getCell(j);
CellType cellType = cell.getCellType();
String data = null;
if(cellType.equals(CellType.STRING)) {
data = cell.getStringCellValue();
}
else if(cellType.equals(CellType.NUMERIC)) {
Double dbldata = cell.getNumericCellValue();
Integer intdata = dbldata.intValue();
data = intdata.toString();
}
hmobj.put(colname, data);
}
return hmobj;
}
public static void ReadAllDataFromExcel() throws IOException {
String filepath = "TestData/Tcases.xls";
File file = new File(filepath);
FileInputStream fis = new FileInputStream(file);
//Workbook workbook = new HSSFWorkbook(fis);
Workbook workbook = getExcelWorkBook(filepath);
Sheet sheet = workbook.getSheet("Tcases");
int rowCount = sheet.getLastRowNum();
for(int i=0;i<=rowCount;i++){
Row row = sheet.getRow(i);
int colCnt = row.getLastCellNum();
for(int j=0;j<colCnt;j++){
Cell cell = row.getCell(j);
CellType cellType = cell.getCellType();
String data = null;
if(cellType.equals(CellType.STRING)) {
data = cell.getStringCellValue();
}
else if(cellType.equals(CellType.NUMERIC)) {
Double dbldata = cell.getNumericCellValue();
Integer intdata = dbldata.intValue();
data = intdata.toString();
}
System.out.println(data);
}
}
}
public static Workbook getExcelWorkBook(String FilePath) throws IOException {
String[] arr = FilePath.split("\\.");
FileInputStream fis = new FileInputStream(FilePath);
String ext = arr[1];
Workbook wbook = null;
if(ext.equalsIgnoreCase("xls")){
wbook = new HSSFWorkbook(fis);
}
else if(ext.equalsIgnoreCase("xlsx")){
wbook = new XSSFWorkbook(fis);
}
return wbook;
}
public static void ReadExcelData() throws IOException{
String filepath = "TestData/Tcases.xls";
File file = new File(filepath);
FileInputStream fis = new FileInputStream(file);
Workbook workbook = new HSSFWorkbook(fis);
Sheet sheet = workbook.getSheet("Tcases");
Row row = sheet.getRow(1);
Cell cell = row.getCell(1);
String data = cell.getStringCellValue();
System.out.println(data);
}
}
To Execute the Above Program, you have to make an Excel file with the name of Tcases.xls under the Folder TestData in your current Project Directory. As shown Below
Wrap-Up
An ever-increasing number of individuals are buying on the web, and everybody is requiring quality, security, and solace. To make your web-based business application serious, you should give the best consideration to these focuses, and be certain that you convey a reliable, excellent open door for your clients. From little things like burden times to enlisting or make a record before making a buy, everything about decides your future achievement. Try not to disregard QA and testing, a definitive thing that drives achievement in the digital age is the clients' recognition.
This article discussed how to read data from Excel file Using Selenium WebDriver, please let us know your feedback and also share your experiences and for any query send me a message here. I will ready to help(In FREE).
Great article. Couldn’t be write much better!
ReplyDeleteKeep it up!