Reading Data from Excel file.

While learning selenium or working on automation project we encounter lot of scenarios where we need to read data from excel sheet.A typical scenario can be where we are using test data & we want to validate run time data with our test data , so in order to validate we need to compare this value we got from any function from run time with value we have already stored in our excel sheet. 

For example we are working on a simple e-commerce application & we need to verify price of a product. Since you already know price of product you would have already saved that in test data file & you would like to test if price of product you are getting from your automated test is matching with what you have in stored in test data file.

Precondition for this test is that you should have apache poi jars in class path.

if not added please download latest jar from https://poi.apache.org/download.html.

or add maven dependency in pom.xml from https://mvnrepository.com

Basic steps to read excel file.

1. Create File object for path of your excel file.

FileInputStream fis = new FileInputStream(path);

2. Create object of workbook.

 XSSFWorkbook workbook = new XSSFWorkbook(fis);

3. Create object of Sheet.

XSSFSheet sheet = workbook.getSheet("userData");

4. Get values from Cell.

Code - 

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;


public class readExcel_2 {
    public static void main(String[] args) throws Exception{
        // Create object of file.        
  File path = new File(System.getProperty("user.dir")+"\\testdata\\testData.xlsx");
        // Create object of FileInput Stream.        
  FileInputStream fis = new FileInputStream(path);
        // Create object of workbook.        
  XSSFWorkbook workbook = new XSSFWorkbook(fis);
        // Create object of Sheet.        
  XSSFSheet sheet = workbook.getSheet("userData");
        // Get Cell Value of First Row & second column        
  String value = sheet.getRow(0).getCell(1).getStringCellValue();
        // Print Cell Value.        
  System.out.println(value);
        // Get Cell Value of Second Row & second column        
  sheet.getRow(1).getCell(1).getStringCellValue();
        // Get Row count using getLastRowNum.        
  System.out.println("Total Row is " + " " + sheet.getLastRowNum());
        // // Get Row count using getPhysicalNumberOfRows.        
  System.out.println("Total Row is " + " " + sheet.getPhysicalNumberOfRows());
        // Get Column Count.        
  System.out.println("Column count for defined row is "+sheet.getRow(1).getLastCellNum());
        workbook.close();

    }
}