HOW TO READ DATA FROM EXCEL FILE (.XLSX) USING APACHE POI?

We can read data from excel file using Apache POI library.
Here is the very basic program to explain How to read data from excel file using Apache POI library.

Create a testdata.xlsx file in the C drive with following details:

Search Text

hello selenium

abhishek yadav qa

Java Source code:

package com.helloselenium.selenium.test;

import java.io.*;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class readDataFromExcelFile{

 public static void main(String[] args) { 

 try {
   
  FileInputStream file = new FileInputStream(new File("C:\\testdata.xlsx")); 
  XSSFWorkbook workbook = new XSSFWorkbook(file);
 
  XSSFSheet sheet = workbook.getSheetAt(0);
  
  String heading = sheet.getRow(0).getCell(0).getStringCellValue();
   
  String searchText1 = sheet.getRow(1).getCell(0).getStringCellValue();
   
  String searchText2 = sheet.getRow(2).getCell(0).getStringCellValue();
   
  System.out.println("Heading is:" + heading);
   
  System.out.println("Search Text 1 is:" + searchText1);
   
  System.out.println("Search Text 2 is:" + searchText2);

  file.close();

 } catch (FileNotFoundException fnfe) {
  fnfe.printStackTrace();
 } catch (IOException ioe) {
  ioe.printStackTrace();
 }
 }
}

Output:

Heading is:Search Text
Search Text 1 is:Hello Selenium
Search Text 2 is:abhishek yadav qa

Detailed explanation for the above program is as follows:

Following code is the required packages for JAVA IO to make integration with excel file.

import java.io.*;

Following code is the required packages for Apache POI library.

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Following code is to locate the path of excel file.

FileInputStream file = new FileInputStream(new File("C:\\testdata.xlsx"));

Following code is to initialize the excel file as a workbook.

XSSFWorkbook workbook = new XSSFWorkbook(file);

Following code is to initialize the excel sheet of the workbook. Here 0 (zero) refers to the first sheet of the workbook.

XSSFSheet sheet = workbook.getSheetAt(0);

Following code is to get the value from the worksheet. Here 0 (zero) refers to first row and first cell of the worksheet.

String heading = sheet.getRow(0).getCell(0).getStringCellValue();

Following code is to get the value from the worksheet. Here 1 refers to second row and and 0 (zero) refers to first cell of the worksheet.

String searchText1 = sheet.getRow(1).getCell(0).getStringCellValue();

Following code is to get the value from the worksheet. Here 2 refers to third row and 0 (zero) refers to first cell of the worksheet.

String searchText2 = sheet.getRow(2).getCell(0).getStringCellValue();

Following code is to print the stored string variable in console.

System.out.println("Heading is:" + heading);

Following code is to print the stored string variable in console.

System.out.println("Search Text 1 is:" + searchText1);

Following code is to print the stored string variable in console.

System.out.println("Search Text 2 is:" + searchText2);

You can also use the following code is to close the excel file.

file.close();