How to Read Excel File Apache POI in Java Selenium Example Program?

How to Read Excel File Apache POI in Java Selenium Example Program?

package com.selenium.poi;
import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
public class _ExcelFileRead
{
   @SuppressWarnings("resource")
   public static void main(String[] args) throws Exception
   {
      WebDriver driver=new FirefoxDriver();
      driver.manage().window().maximize();
      driver.get("http://apps.qaplanet.in/qahrm/login.php");
      File ff=new File("G:\\Testdata\\orangehrm_login.xlsx");
      FileInputStream fi=new FileInputStream(ff);
      XSSFWorkbook wb=new XSSFWorkbook(fi);
      XSSFSheet sh=wb.getSheetAt(0);
      int roc=sh.getLastRowNum();
      System.out.println(roc); 
      for(int i=0;i<=roc;i++)
      {
         String uname=sh.getRow(i).getCell(0).getStringCellValue();
         String upsw=sh.getRow(i).getCell(1).getStringCellValue();
         System.out.println(uname+" "+ upsw);
         driver.findElement(By.name("txtUserName")).clear();
         driver.findElement(By.name("txtUserName")).sendKeys(uname);
         driver.findElement(By.name("txtPassword")).sendKeys(upsw);
         driver.findElement(By.name("Submit")).click();		   
      }
   }
}

Read Excel File In Selenium

package TestNGExcel;

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

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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 Login {
WebDriver driver;
   
   @Test(dataProvider = "inputdata")
   public static void testdata(String username ,String password){
   System.setProperty("webdriver.chrome.driver","C:\\selenium\\chromedriver.exe");

         WebDriver  driver= new ChromeDriver();
        driver.get("http://parabank.parasoft.com");
      
      // Maximize window 
      driver.manage().window().maximize();
      
      driver.findElement(By.xpath("//input[@name='username']")).sendKeys(username);
      driver.findElement(By.xpath("//input[@name='password']")).sendKeys(password);
       driver.findElement(By.xpath("//input[@class='button' and @type='submit']")).click();
   
}
   
   @DataProvider(name="inputdata")

       public Object[][] getcellData() throws IOException {
  //step1: Locate the path of excel file    	 
       FileInputStream file = new FileInputStream("C:\\ReadFile\\visionit\\TC_Login.xlsx");
  //step2: create workbook instance from excel sheet. 		
       XSSFWorkbook wb = new XSSFWorkbook(file);
  //step3: Get to the desired sheet.		
       XSSFSheet s = wb.getSheet("sheet1");
  //step4: getrow() specify which row we want to read and getcell() specify which column.		
       int rowcount = s.getLastRowNum()+1;
       int cellcount = s.getRow(0).getLastCellNum();
      
       Object data[][] = new Object[rowcount][cellcount];
      
            for(int i=0;i<rowcount;i++){
           Row r =s.getRow(i);
         
           for(int j = 0;j<cellcount;j++){
            Cell c = r.getCell(j);
            data[i][j] = c.getStringCellValue();
         }
      }
      wb.close();
      return data;
      
   }

}

package com.qa.DataRead;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.concurrent.TimeUnit;
import org.apache.poi.ss.usermodel.Cell;
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 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 Test_DataProvider 
{

   XSSFWorkbook excelWorkbook = null;
   XSSFSheet excelSheet = null;
   XSSFRow row = null;
   XSSFCell cell = null;
   WebDriver driver = null;

   @Test(dataProvider = "getData") //dataProvider value should be equal to @DataProvider method name
   public void doLogin(String text) throws InterruptedException 
   { //no. of parameter = no. of columns

      System.setProperty("webdriver.chrome.driver","C://Automation//chromedriver_win32//chromedriver.exe");
      driver=new ChromeDriver();

      driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
      driver.manage().window().maximize();
      driver.get("http://www.google.com");
      driver.findElement(By.xpath("//input[@title='Search']")).sendKeys(text);
      driver.findElement(By.name("btnK")).click();
      Thread.sleep(7000);
      driver.quit();
   }


   @DataProvider // supplying data for a test method.
   public Object[][] getData() throws IOException 
   {
      FileInputStream fis = new FileInputStream("C:\\Users\\ajain153\\Desktop\\Test Data 2.xlsx"); // Your .xlsx file name along with path
      excelWorkbook = new XSSFWorkbook(fis);
      // Read sheet inside the workbook by its name
      excelSheet = excelWorkbook.getSheet("Sheet1"); //Your sheet name
      // Find number of rows in excel file
      System.out.println("First Row Number/index:"+ excelSheet.getFirstRowNum() + " *** Last Row Number/index:"
            + excelSheet.getLastRowNum());
      int rowCount = excelSheet.getLastRowNum() - excelSheet.getFirstRowNum()+1;
      int colCount = excelSheet.getRow(0).getLastCellNum();
      System.out.println("Row Count is: " + rowCount
            + " *** Column count is: " + colCount);
      Object data[][] = new Object[rowCount-1][colCount];
      for (int rNum = 2; rNum <= rowCount; rNum++) 
      {
         for (int cNum = 0; cNum < colCount; cNum++) 
         {
            System.out.print(getCellData("Sheet1", cNum, rNum) + " "); // Your sheet name
            data[rNum - 2][cNum] = getCellData("Sheet1", cNum, rNum); //Your sheet name
         }
         System.out.println();
      }
      return data;
   }
   // Function will always used as below. It returns the data from a cell - No need to make any changes
   public String getCellData(String sheetName, int colNum, int rowNum) 
   {
      try
      {
         if (rowNum <= 0)
            return "";
         int index = excelWorkbook.getSheetIndex(sheetName);
         if (index == -1)
            return "";
         excelSheet = excelWorkbook.getSheetAt(index);
         row = excelSheet.getRow(rowNum - 1);
         if (row == null)
            return "";
         cell = row.getCell(colNum);
         if (cell == null)
            return "";
         if (cell.getCellType() == Cell.CELL_TYPE_STRING)
            return cell.getStringCellValue();
         else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
               || cell.getCellType() == Cell.CELL_TYPE_FORMULA)
         {
            String cellText = String.valueOf(cell.getNumericCellValue());
            return cellText;
         } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
            return "";
         else
            return String.valueOf(cell.getBooleanCellValue());
      } catch (Exception e)
      {
         e.printStackTrace();
         return "row " + rowNum + " or column " + colNum
               + " does not exist in xls";
      }
   }
}