How to Read data from Exel using Apache POI

How to Read data from Exel using Apache POI

 Reading data from excel is very important in the Automation because most of the data which we are using in the automation we can not hard code because some time we need to test same test case with different set of test data , in that case it is better to read data from external file like excel.

 From the below data, i need to read username and password.
-------------------------------

| username     | password   |

-------------------------------

| user1           | pass1        |

-------------------------------

first download the Apache POI jars from this site : https://poi.apache.org/download.html

and put into your project build path.

 after that create one loginTestdata.xlsx file and add the above data into the sheet "TestData"

import java.io.FileInputStream;

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;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class ExcelToDataProvider {

 String xlFilePath = "D:\\loginTestdata.xlsx";
 String sheetName = "TestData";
 Workbook myWorkbook = null;

 @Test(dataProvider = "userData")
 public void fillUserForm(String userName, String passWord) {
  System.out.println("UserName: " + userName);
  System.out.println("PassWord: " + passWord);
  System.out.println("*********************");
 }

 @DataProvider(name = "userData")
 public Object[][] userFormData() throws Exception {
  Object[][] data = testData(xlFilePath, sheetName);
  return data;
 }

 public Object[][] testData(String xlFilePath, String sheetName)
   throws Exception {

  Object[][] excelData = null;
  FileInputStream inputStream = new FileInputStream(xlFilePath);
  myWorkbook = new XSSFWorkbook(inputStream);
  Sheet mySheet = myWorkbook.getSheet(sheetName);
  int rowCount = mySheet.getLastRowNum() - mySheet.getFirstRowNum();
  excelData = new Object[rowCount][2];

  for (int i = 1; i <= rowCount; i++) {
   Row row = mySheet.getRow(i);
   for (int j = 0; j < 2; j++) {
    excelData[i - 1][j] = row.getCell(j).getStringCellValue();
    System.out.print(row.getCell(j).getStringCellValue() + "|| ");
   }
   System.out.println();
  }
  return excelData;
 }
}

once you execute the above code, you will get below output:

*********************

UserName: user1

PassWord: pass1

*********************