Tutorials Hut

  • Selenium WebDriver

       Selenium Introduction
       Benefits of Selenium
       Four components of Selenium
       Difference b/w Selenium IDE, RC & WebDriver
       Selenium WebDriver Architecture
       Background when user execute selenium code
       Download and Install Java
       Download and Install Eclipse
       Download Selenium WebDriver
       Selenium WebDriver Locators
       Selenium - Launch Browser
       Selenium WebDriver Waits
       Selenium- Implicit wait
       Selenium- Explicit wait
       Selenium- Fluent wait
       Selenium- Commonly used commands
       Selenium- findElement & findElements
       Selenium- Selenium-Handling check Box
       Selenium- Handling Radio button
       Selenium- Handling drop down
       Selenium- Take Screenshot
       Selenium- Handle Web Alerts
       Selenium- Multiple Windows Handling
       Selenium- Handle iframes
       Selenium- Upload a file
       Selenium- Download a file
       Selenium- Actions Class Utilities
       Selenium- Mouse Actions
       Selenium- Keyboards Events
       Selenium- Handle mouse hover Actions
       Selenium- Drag and Drop
       Selenium- Scroll a WebPage
       Selenium- Context Click / Right Click
       Selenium- Double Click
       Selenium- Desired Capabilities
       Selenium- Assertions
       Selenium- Exceptions and Exception Handling
       Selenium- Difference b/w driver.close() & driver.quit()
       Selenium- difference b/w driver.get() & driver.navigate()
       Selenium- JavascriptExecutor
       Selenium- Read excel file using Fillo API
       Selenium- Database Testing using Selenium
       Selenium- Read & write excel file using Apache POI
       Selenium- Read and Write csv file in Selenium
       Selenium- Dynamic Web Table Handling
       Selenium- Maven Integration with Selenium
       Selenium- Set up Logging using Log4j
       Selenium-Implement Extent Report



  • Read and write excel file in Selenium using Apache POI

    In  this tutorial, we will learn how to Read and Write excel files in Selenium.

    We will cover below topics in this tutorial

    Read and write excel file in Selenium using Apache POI_1
    1. Introduction to Apache POI
    2. Java Interfaces in Apache POI
    3. Classes in Apache POI (for .xls extension)
    4. Classes in Apache POI (for .xlsx extension)
    5. How to read a specific cell value?
    6. How to read the entire Excel sheet?
    7. Write Data into Excel File in Selenium

    Introduction to Apache POI

    Selenium support only web UI automation so for reading and wring excel file , third party tool or API is required for e.g. Apache POI , JExcel etc.

    Apache provides a famous library POI to read or write an Excel. This library is used to read and write both XLS and XLSX file format of Excel.

    An HSSF implementation is provided by the POI library to read XLS files.

    And XSSF implementation is provided by the POI library to read XLSX files.

    Below Is the maven dependency if you are using Maven project and needs to be added in pom.xml



    org.apache.poi
    poi
    5.0.0

    And if it is a generic project then POI jar file can be download from the http://poi.apache.org/download.html  and you can add jar file in the project.

    Below are few classes and interfaces if you are using .xls and .xlsx file

    Java Interfaces in Apache POI

    1. Workbook– XSSFWorkbook and HSSFWorkbook classes implement this interface.
    2. Sheet– This interface is implemented to read the sheet inside the workbook
    3. Row– This interface is used to identify the row inside the sheet of the different types of excel sheet
    4. Cell– This interface is implemented to identify the corresponding cell of the given row of the different types of excel sheet

    Classes in Apache POI (for .xls extension)

    1. HSSFWorkbook: Is a class representation of XLS file.
    2. HSSFSheet: representing a sheet in an XLS file.
    3. HSSFRow  :representing a row in the sheet of XLS file.
    4. HSSFCell:  representing a cell in a row of XLS file.

     Classes in Apache POI (for .xlsx extension)

    1. XSSFWorkbook: representation of XLSX file.
    2. XSSFSheet: representing a sheet in an XLSX file.
    3. XSSFRow: representing a row in the sheet of XLSX file.
    4. XSSFCell: representing a cell in a row of XLSX file.

     How to read a specific cell value?

    Let’s take an example to show how to read any specific cell , so in below table we have to read 5th cell value of third row .

    Read and write excel file in Selenium using Apache POI_2
    //Code Snippet
    package com.test;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    
    public class ReadExcel {
    public static void main(String args[]) throws IOException {
    
    //Create an object of File class to open xlsx file
    File file = new File("E:\\Selenium\\ExcelData\\TestData.xlsx");
    
    //Create an object of FileInputStream class to read excel file
    FileInputStream inputStream = new FileInputStream(file);
    
    //Creating workbook instance that refers to .xls file
    XSSFWorkbook wb=new XSSFWorkbook(inputStream);
    
    //Creating a Sheet object using the sheet Name
    XSSFSheet sheet=wb.getSheet("TestSheet1");
    
    //Create a row object to retrieve third row
    XSSFRow row2=sheet.getRow(3);
    
    //Create a cell object to retreive 5th cell for second row
    XSSFCell cell=row2.getCell(5);
    
    //Get the address in a variable
    String address= cell.getStringCellValue();
    
    //Printing the address
    System.out.println("Address is :"+ address);
    }
    }
    

    How to read the entire Excel sheet?

    For reading complete excel data ,  you can iterate over each cell of the row, present in the sheet. 

    So for this we have to count total no of rows in the sheet and total number of columns .

    We will use below logic/code for total row count.

    Total no of Rows  = Last Row Number -First Row Number
    int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();

    Let’s try to print the entire data present in the sheet using the below code:

    package com.test;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;

    public class ReadExcel {
    public static void main(String args[]) throws IOException {
    //Create an object of File class to open xlsx file
    File file = new File("E:\\Selenium\\ExcelData\\TestData.xls");

    //Create an object of FileInputStream class to read excel file
    FileInputStream inputStream = new FileInputStream(file);

    //creating workbook instance that refers to .xlsx file
    XSSFWorkbook wb=new XSSFWorkbook(inputStream);

    //creating a Sheet object
    XSSFSheet sheet=wb.getSheet("Sheet1");

    //get all rows in the sheet
    int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();

    //iterate over all the row to print the data present in each cell.
    for(int i=0;i<=rowCount;i++){

    //get cell count in a row
    int cellcount=sheet.getRow(i).getLastCellNum();

    //iterate over each cell to print its value
    System.out.println("Row"+ i+" data is :");

    for(int j=0;j<cellcount;j++){
    System.out.print(sheet.getRow(i).getCell(j).getStringCellValue() +",");
    }
    System.out.println();
    }
    }
    }
    Read and write excel file in Selenium using Apache POI_3

    Write Data into Excel File in Selenium

    The code below is used to write data into an Excel file using Selenium.

    package com.test;
    
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    import org.apache.poi.ss.usermodel.Cell;
    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 WriteExcel {
    public static void main(String[] args) throws IOException {
    String path = System.getProperty("user.dir") + "/src/main/java/com/testData/TestData.xlsx";
    FileInputStream fis = new FileInputStream(path);
    
    Workbook workbook = new XSSFWorkbook(fis);
    
    //creating a Sheet object
    
    Sheet sheet=workbook.getSheet("Demo");
    
    int lastRow = sheet.getLastRowNum();
    
    //iterate through each row and set 5th cell value as 'Pass'
    for (int i = 1; i &lt;= lastRow; i++) {
    Row row = sheet.getRow(i);
    //set cell value of 5th cell for each row
    Cell cell = row.createCell(5);
    cell.setCellValue("Pass");
    }
    FileOutputStream fos = new FileOutputStream(path);
    workbook.write(fos);
    fos.close();
    }
    }
    



  • Selenium WebDriver Tutorials

       Selenium Introduction
       Benefits of Selenium
       Four components of Selenium
       Difference b/w Selenium IDE, RC & WebDriver
       Selenium WebDriver Architecture
       Background when user execute selenium code
       Download and Install Java
       Download and Install Eclipse
       Download Selenium WebDriver
       Selenium WebDriver Locators
       Selenium - Launch Browser
       Selenium WebDriver Waits
       Selenium- Implicit wait
       Selenium- Explicit wait
       Selenium- Fluent wait
       Selenium- Commonly used commands
       Selenium- findElement & findElements
       Selenium- Selenium-Handling check Box
       Selenium- Handling Radio button
       Selenium- Handling drop down
       Selenium- Take Screenshot
       Selenium- Handle Web Alerts
       Selenium- Multiple Windows Handling
       Selenium- Handle iframes
       Selenium- Upload a file
       Selenium- Download a file
       Selenium- Actions Class Utilities
       Selenium- Mouse Actions
       Selenium- Keyboards Events
       Selenium- Handle mouse hover Actions
       Selenium- Drag and Drop
       Selenium- Scroll a WebPage
       Selenium- Context Click / Right Click
       Selenium- Double Click
       Selenium- Desired Capabilities
       Selenium- Assertions
       Selenium- Exceptions and Exception Handling
       Selenium- Difference b/w driver.close() & driver.quit()
       Selenium- difference b/w driver.get() & driver.navigate()
       Selenium- JavascriptExecutor
       Selenium- Read excel file using Fillo API
       Selenium- Database Testing using Selenium
       Selenium- Read & write excel file using Apache POI
       Selenium- Read and Write csv file in Selenium
       Selenium- Dynamic Web Table Handling
       Selenium- Maven Integration with Selenium
       Selenium- Set up Logging using Log4j
       Selenium-Implement Extent Report

















  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Scroll to Top