Read excel file in Selenium using Apache POI

How to Read/Write Excel File?

• To Read and Write excel files in Selenium we have to take help of third party API like JXL and Apache POI.
• To read or write an Excel,Apache provides a very famous library POI. This library is capable enough to read and write both XLS and XLSX file format of Excel.

Difference between JXL and Apache POI

• Most significant difference is that Java JXL does not support ".xlsx" format; it only supports ".xls" format.
• JXL doesn't support Conditional Formatting, Apache POI does.
• JXL doesn't support rich text formatting, i.e. different formatting within a text string.

Different Classes and Interfaces in Apache POI:

Classes and Interfaces

Explanation of classes and interfaces POI for reading XLS and XLSX file

• Workbook: XSSFWorkbook and HSSFWorkbook classes implement this interface.
• XSSFWorkbook: Is a class representation of XLSX file.
• HSSFWorkbook: Is a class representation of XLS file.
• Sheet: XSSFSheet and HSSFSheet classes implement this interface.
• XSSFSheet: Is a class representing a sheet in an XLSX file.
• HSSFSheet: Is a class representing a sheet in an XLS file.
• Row: XSSFRow and HSSFRow classes implement this interface.
• XSSFRow: Is a class representing a row in the sheet of XLSX file.
• HSSFRow: Is a class representing a row in the sheet of XLS file.
• Cell: XSSFCell and HSSFCell classes implement this interface.
• XSSFCell: Is a class representing a cell in a row of XLSX file.
• HSSFCell: Is a class representing a cell in a row of XLS file.

Download Apache POI Jars and configure in your project to work with excel.

1. If you are using plain java project then download the jars from below link and configure in your project's build path.

http://poi.apache.org/download.html

2. And if you are using maven use below dependencies in your POM.xml file.

                        <dependency>

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

<artifactId>poi</artifactId>

<version>4.1.2</version>

</dependency>

<dependency>

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

<artifactId>poi-ooxml</artifactId>

<version>4.1.2</version>

</dependency>

We have a excel file in project directory and it contains below data:

Please refer below program to read above excel file in selenium:

import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;

public class ReadExcel {
 
 @Test
 public void readExcel() throws Exception {
  
  String excelPath="D:\\Workspace_Eclipse\\ReadExcel\\TestData\\TestData.xlsx";
  String fileName="TestData";
  String sheetName="Test";
  //Create the object of File Class to get the excel path
  File file= new File(excelPath);
  //To read the file
  FileInputStream fis= new FileInputStream(file);
  XSSFWorkbook wb= new XSSFWorkbook(fis);
  XSSFSheet sheet=wb.getSheet(sheetName);
  //Get Total Rows in Excel Sheet
  int rowCount=sheet.getLastRowNum();
  System.out.println("Total Rows: "+(rowCount+1));
  //Print a particular cell value
  String data=sheet.getRow(0).getCell(1).getStringCellValue();
  System.out.println("Particular cell value: "+data);
  
  //Loop to print all values of the excel sheet
  for(int i=0; i<=rowCount;i++) {
   Row row=sheet.getRow(i);
   for(int j=0; j<row.getLastCellNum();j++) {
    String data1=sheet.getRow(i).getCell(j).getStringCellValue();
    System.out.print(data1+" ");
   }
   System.out.println();
  }
  wb.close();
 }
}

Output:

[RemoteTestNG] detected TestNG version 6.14.3
Total Rows: 10
Particular cell value: LastName1
FirstName1 LastName1 
FirstName2 LastName2 
FirstName3 LastName3 
FirstName4 LastName4 
FirstName5 LastName5 
FirstName6 LastName6 
FirstName7 LastName7 
FirstName8 LastName8 
FirstName9 LastName9 
FirstName10 LastName10 
PASSED: readExcel

How to create Excel Library so that we can use it inside out test cases. For that please refer below program.

Excel Library class:

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

public class ExcelLibrary {
 
 XSSFWorkbook wb;
 XSSFSheet sheet;
 //Below Constructor is to load the excel configuration 
 public ExcelLibrary() throws Exception {
  String excelPath="D:\\Workspace_Eclipse\\ReadExcel\\TestData\\TestData.xlsx";
  File file= new File(excelPath);
  FileInputStream fis= new FileInputStream(file);
  wb= new XSSFWorkbook(fis);
 }
 public String readData(String sheetName, int row, int col) {
  sheet=wb.getSheet(sheetName);
  String data=sheet.getRow(row).getCell(col).getStringCellValue();
  return data;
 }
}

TestClass:

import org.testng.annotations.Test;
import com.readExcel.ExcelLibrary;

public class ReadExcelTest {
 
 @Test
 public void readExcelTest() throws Exception {
  ExcelLibrary obj= new ExcelLibrary();
  //Call readData method from ExcelLibrary class to get the value of Particular cell
  String datString=obj.readData("Test", 5, 1);
  System.out.println("The data is: "+datString);
 }
}

Please refer below YouTube video to get more details: