READING EXCEL FILES USNG APACHE POI API

With Apache POI API, you can access various Microsoft Documents like MS Excel, MS Powerpoint,  and MS Word.

In this post, I am going to discuss - How to access Excel File using Apache POI API?

To Access excel files with different versions like 2003(xls) and 2007(xlsx) - we use POI-HSSF and POI-XSSF APIs respectively. HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 (.xlsx) file format.

Note: You must include APACHE POI jar in you build path to access and manipulate excel files.This jar file contains all the required classes for various versions of excel files.

Download Link:  Apache POI jar

Important Classes(For Excel- 2007 & above):

XSSFWorkbook

XSSFSheet

XSSFRow

XSSFCell

 All these classes are in same package : org.apache.poi.xssf.usermodel


Important Classes(For Excel- 97-2003):

HSSFWorkbook

HSSFSheet

HSSFRow

HSSFCell

 All these classes are in same package : org.apache.poi.hssf.usermodel

Examples:

In examples, reading excel file -Sheet1 - row wise and printing on console

Sample Data- In Sheet1

Test Case Name Username Password Results IsExecute
APACHE_POI_TC testuser_1 Test@123 Pass Yes
APACHE_POI_TC testuser_2 Test@124 Pass No
APACHE_POI_TC testuser_3 Test@125 Pass Yes
APACHE_POI_TC testuser_4 Test@126 Pass Yes
APACHE_POI_TC testuser_5 Test@127 Pass No
APACHE_POI_TC testuser_6 Test@128 Pass Yes

Example : with Excel file (2007) - .xlsx

Use  org.apache.poi.xssf.usermodel package - all class names starts with XSSF

 Here,   

XSSFWorkbook represents the whole excel file

XSSFSheet represents the instance of a single sheet

XSSFRow represents a single row of  the current sheet you are working on

XSSFCell represents a tiny element cell in the sheet

 

package com.sample;

import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/*
 * For Excel - From 2007 version
 * File Extension - .xlsx
 * All class from Apache POI API -should start with XSSF
 * 
 */

public class ReadWriteWorkbook {
 private static XSSFWorkbook xlWBook;
 private static XSSFSheet xlSheet;
 private static XSSFRow xlRow; 
 private static String filePath = "/home/dinesh/";
 private static String fileName = "xtest.xlsx";

 public static void main(String[] args) {

  try {

   FileInputStream xlFile = new FileInputStream(filePath + fileName);

   // Access the required test data sheet
   xlWBook = new XSSFWorkbook(xlFile);

   // Assuming your data is in Sheet1
   xlSheet = xlWBook.getSheet("Sheet1");

   // gives row count in sheet
   int noOfRows = xlSheet.getPhysicalNumberOfRows();

   // gives number of rows
   xlRow = xlSheet.getRow(0);
   
   int noOfColumns = xlRow.getLastCellNum();
   
   // Storing all the excel data into an array excelData (Sheet1 only)
   String[][] excelData = new String[noOfRows][noOfColumns];

   // r - row c- column
   for (int r = 1; r < noOfRows; r++) {
    for (int c = 0; c < noOfColumns; c++) {
     xlRow = xlSheet.getRow(r);
     
     // Here we have complete excel data excelData
     // if there are any cells with no data - as treated as blanks
     Cell cell = xlRow.getCell(c, Row.CREATE_NULL_AS_BLANK);

     excelData[r][c] = cell.toString();

     System.out.println("row: " + r + " column: " + c
       + " cell data: " + excelData[r][c]);
    }
    System.out.println("*****************************************");
   }
  } catch (Exception e) {
   e.printStackTrace();

  }
 }
}

Example : with Excel file (2003) - .xls Use  org.apache.poi.hssf.usermodel package - all class names starts with HSSF

package com.sample;

import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;


/*
 * For Excel - Upto 2003 version
 * File Extension - .xls
 * All class from Apache POI -should start with HSSF
 */

public class ReadWriteExcelUpto2003 {
 
 private static HSSFWorkbook xlWBook;
 private static HSSFSheet xlSheet;
 private static HSSFRow xlRow;
 private static String filePath = "/home/dinesh/";
 // file extension - .xls
 private static String fileName = "xtest.xls";

 public static void main(String[] args) throws InterruptedException {

  try {

   FileInputStream xlFile 
   = new FileInputStream(filePath + fileName);

   // Access the required test data sheet
   xlWBook = new HSSFWorkbook(xlFile);

   // Assuming your data is in Sheet1
   xlSheet = xlWBook.getSheet("Sheet1");

   // gives row count in sheet
   int noOfRows = xlSheet.getPhysicalNumberOfRows();
   
   //gives number of rows 
   xlRow = xlSheet.getRow(0);
   int noOfColumns = xlRow.getLastCellNum();

   //Storing all the excel data into an array excelData (Sheet1 only)
   String[][] excelData = new String[noOfRows][noOfColumns];

   // r - row c- column
   for (int r = 1; r < noOfRows; r++) {
    for (int c = 0; c < noOfColumns; c++) {
  xlRow = xlSheet.getRow(r);
  
  // Here we have complete excel data excelData
  // if there are any cells with no data - as treated as blanks
  Cell cell = xlRow.getCell(c, Row.CREATE_NULL_AS_BLANK);

  excelData[r][c] = cell.toString();

     System.out.println("row: " + r + " column: " + c 
       + " cell data: "+excelData[r][c]);     
    }
    System.out.println("*****************************************");
   }
  } catch(Exception e){
   e.printStackTrace();
   
  }
 }
} 

Output:

row: 1 column: 0 cell data: APACHE_POI_TC
row: 1 column: 1 cell data: testuser_1
row: 1 column: 2 cell data: Test@123
row: 1 column: 3 cell data: Pass
row: 1 column: 4 cell data: Yes
*****************************************
row: 2 column: 0 cell data: APACHE_POI_TC
row: 2 column: 1 cell data: testuser_2
row: 2 column: 2 cell data: Test@124
row: 2 column: 3 cell data: Pass
row: 2 column: 4 cell data: No
*****************************************
row: 3 column: 0 cell data: APACHE_POI_TC
row: 3 column: 1 cell data: testuser_3
row: 3 column: 2 cell data: Test@125
row: 3 column: 3 cell data: Pass
row: 3 column: 4 cell data: Yes
*****************************************
row: 4 column: 0 cell data: APACHE_POI_TC
row: 4 column: 1 cell data: testuser_4
row: 4 column: 2 cell data: Test@126
row: 4 column: 3 cell data: Pass
row: 4 column: 4 cell data: Yes
*****************************************
row: 5 column: 0 cell data: APACHE_POI_TC
row: 5 column: 1 cell data: testuser_5
row: 5 column: 2 cell data: Test@127
row: 5 column: 3 cell data: Pass
row: 5 column: 4 cell data: No
*****************************************
row: 6 column: 0 cell data: APACHE_POI_TC
row: 6 column: 1 cell data: testuser_6
row: 6 column: 2 cell data: Test@128
row: 6 column: 3 cell data: Pass
row: 6 column: 4 cell data: Yes
*****************************************

Note: All examples are properly commented - please feel free to post your doubts/comments - Hope you like the content  please share this - thank u