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();



Post a Comment

1 Comments

  1. nice info , how we can do same thing for having multiple values in csv

    ReplyDelete


What would you like to add in my list? I look forward to reading your comments below.