3 way's to Read from excel......Apache POI- Selenium-MacOS

To work with excel  first we need to have below jars present on the system from Apache POI.

Download files from Binary distribution .tar.gz for Mac and zip from windows, link

once all jar files are downloaded

Go to Java project-> right click-> Build Path-> configure Build path

Add external Jars 

and click apply and OK

If you are using Maven add the Apache POI dependency in POM.XML

Go to Maven repository and search for Apache POI, first 2 dependency will be good to go with

Add the dependency in your POM.XML file

<dependency>

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

    <artifactId>poi</artifactId>

    <version>5.0.0</version>

</dependency>

<dependency>

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

    <artifactId>poi-ooxml</artifactId>

    <version>5.0.0</version>

</dependency>

And your are good to go with Excel read and write

Eg:-excel present in your system contains the data.

public class Exceldataread {

@Test

public void test1() throws IOException

{ 

//"/Users/priyankac/Desktop/testdata.xlsx" is the path of the file which can be taken in Mac system by clicking on file-> Get Info-> copy the the path mentioned under "where" and paste it here, below is the screenshot for reference

 //In windows while giving a path of the excel you need to convert forward slash into double backward slash

//In MAC you don't need to change anything

FileInputStream fis = new FileInputStream("/Users/priyankac/Desktop/testdata.xlsx");

XSSFWorkbook workbook = new XSSFWorkbook(fis);

 //To fetch sheet with Index number

        XSSFSheet sheet = workbook.getSheetAt(0);

 //To fetch sheet with sheet name

             XSSFSheet sheet = workbook.getSheet(Sheet1);

 //To fetch sheet name

System.out.println("sheet name is "+sheet.getSheetName());

//To fetch number of rows

int rows=sheet.getLastRowNum();

System.out.println(rows);

//To fetch number of coulmn

       int cols= sheet.getRow(0).getLastCellNum();

System.out.println(cols);

//This is simple way To fetch all rows and columns present in the sheet including header

//Fetching the first row data

// Header Name O row and O column

System.out.println(sheet.getRow(0).getCell(0).getStringCellValue());

//Header place at 0 row 1st column

System.out.println(sheet.getRow(0).getCell(1).getStringCellValue());

//Header EmpID at 0 row 2nd column

System.out.println(sheet.getRow(0).getCell(2).getStringCellValue());

//Header Rating at 0 row 3rd column

        System.out.println(sheet.getRow(0).getCell(3).getStringCellValue());

//Similarly we can fetch 2nd row data one by one

//Fetching data from 1st row and 0 column

System.out.println(sheet.getRow(1).getCell(0).getStringCellValue());

//Fetching data from 1st row and 1st column

System.out.println(sheet.getRow(1).getCell(1).getStringCellValue());

//Fetching data from 1st row and 2nd column

System.out.println(sheet.getRow(1).getCell(2).getRawValue());

//Fetching data from 1st row and 3rd column

System.out.println(sheet.getRow(1).getCell(3).getRawValue());

}}

//Similarly you can do it for the rest of rows.

Result

If you want Result to be in in a line remove "ln" from print

Result

 To Read all data in the sheet using "for loop"

public class Exceldataread {

@Test

public void test1() throws IOException

{ 

FileInputStream fis = new FileInputStream("/Users/priyankac/Desktop/testdata.xlsx");

XSSFWorkbook workbook = new XSSFWorkbook(fis);

        XSSFSheet sheet = workbook.getSheetAt(0);

int rows=sheet.getLastRowNum();

  System.out.println(rows);

int cols= sheet.getRow(0).getLastCellNum();

        System.out.println(cols);

//Traversing through rows

        for (int r=0;r<rows;r++)

        {

XSSFRow row=sheet.getRow(r);

//Traversing through columns

for(int c=0;c<cols;c++)

  {

XSSFCell cell=row.getCell(c);

//Switch is used to returns type of cell (string/numeric/boolean) 

switch(cell.getCellType())

{

//Print command os used to stop same rows going in next line

case STRING: System.out.print(cell.getStringCellValue()); break;

case NUMERIC: System.out.print(cell.getNumericCellValue());break;

case BOOLEAN: System.out.print(cell.getBooleanCellValue());break;

}

// Pipe is used to create distance between 2 fields within a row

System.out.print(" | ");

}

//one row to another data should be written in next line

System.out.println(); 

       }

}

}

Result

Reading the excel using Iterator method

public class Exceldataread {

@Test

public void test1() throws IOException

{ 

FileInputStream fis = new FileInputStream("/Users/priyankac/Desktop/testdata.xlsx");

XSSFWorkbook workbook = new XSSFWorkbook(fis);

        XSSFSheet sheet = workbook.getSheetAt(0);

//Iterator object is created for sheet

        Iterator it = sheet.iterator();

//Capture the next value in a row

           while(it.hasNext())

                {

//XSSF row typecasting should be used

           XSSFRow row =(XSSFRow) it.next();

//Iterator  should be applied on cell level

Iterator cellIt =row.cellIterator();

//To read all cells while loop is used

    while(cellIt.hasNext())

                 {

           XSSFCell cell=(XSSFCell) cellIt.next();

//Switch is used to returns type of cell (string/numeric/boolean) 

switch(cell.getCellType())

{

case STRING: System.out.print(cell.getStringCellValue()); break;

case NUMERIC: System.out.print(cell.getNumericCellValue());break;

case BOOLEAN: System.out.print(cell.getBooleanCellValue());break;

}

// Pipe is used to create distance between 2 fields within a row

System.out.print(" | ");

}

//one row to another data should be written in next line

System.out.println();

}

}}

Result

Check the final run video here