How to use Excel for getting data in Selenium Webdriver Java | Read Excel File Using Apache POI Java

                    In this post, we will see How to use Excel for getting data in Selenium Webdriver Java | Read Excel File Using Apache POI Java 

Program Code (ExcelData.java):

package main;

import java.io.FileInputStream;

import java.io.IOException;

import java.util.Iterator;

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.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelData {

public static void main(String[] args) throws IOException {

//to print user id and password of Mayur Chavan

//1. create object of workbook

FileInputStream fis=new FileInputStream("C:\\selenium\\Study Material\\exceldata.xlsx");

XSSFWorkbook workbook=new XSSFWorkbook(fis);

//2. create object of sheet and allocate respective sheet

int numsheet=workbook.getNumberOfSheets();

XSSFSheet sheet = null;

for(int i=0;i<numsheet;i++)

{

if(workbook.getSheetName(i).equalsIgnoreCase("logindata"))

{

sheet=workbook.getSheetAt(i);

}

}

//3. Fetch data from sheet using two iterators

Iterator<Row> itrow=sheet.iterator();

while(itrow.hasNext())

{

Row row=itrow.next();

Iterator itcell=row.cellIterator();

Cell c=(Cell) itcell.next();

if(c.getStringCellValue().equals("Mayur Chavan"))

{

while(itcell.hasNext())

{

c=(Cell) itcell.next();

if(c.getCellType()==CellType.STRING)

{

System.out.println(c.getStringCellValue());

}

else if(c.getCellType()==CellType.NUMERIC)

{

System.out.println((int)c.getNumericCellValue());

}

}

}

}

}

}

Program Code (pom.xml):

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>Selenium8</groupId>

  <artifactId>Selenium8</artifactId>

  <version>0.0.1-SNAPSHOT</version>

  <dependencies>

  

  <!-- https://mvnrepository.com/artifact/org.seleniumhq.selenium/selenium-java -->

<dependency>

    <groupId>org.seleniumhq.selenium</groupId>

    <artifactId>selenium-java</artifactId>

    <version>4.1.3</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>5.2.2</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>5.2.2</version>

</dependency>

 

  

  </dependencies>

  <build>

    <sourceDirectory>src</sourceDirectory>

    <plugins>

      <plugin>

        <artifactId>maven-compiler-plugin</artifactId>

        <version>3.8.1</version>

        <configuration>

          <release>16</release>

        </configuration>

      </plugin>

    </plugins>

  </build>

</project> 

Watch following video:

Watch on YouTube: https://www.youtube.com/watch?v=BkZmqTIviP0