In this post we’ll see how you can read Excel sheet in Java using Apache POI library.

• Refer How to Write Excel File in Java Using Apache POI to see how to write to excel sheet using Java program.

Apache POI is an open source library using which you can read and write Excel files from your Java program.

Maven Dependencies

You will need to include following maven dependencies for Apache POI in your pom.xml file.

org.apache.poi
  poi
4.0.0



org.apache.poi
  poi-ooxml
4.0.0

Here the first dependency is required for working with older excel format having .xls extension.

Second dependency is required for working with the OOXML based file format having .xlsx extension.

These dependencies add the following jars-

poi-4.0.0.jar
commons-codec-1.10.jar
commons-collections4-4.2.jar

poi-ooxml-4.0.0.jar
poi-ooxml-schemas-4.0.0.jar
xmlbeans-3.0.1.jar
commons-compress-1.18.jar
curvesapi-1.04.jar

Apache POI classes for working with Excel spreadsheets

Before getting into example for reading excel spreadsheet in Java using Apache POI first let’s get some idea about the classes that are used in the code.

With in Apache POI there are two implementations for two types of spread sheets-

• HSSF- It is the POI Project's pure Java implementation of the Excel '97(-2007) file format (.xls).
• XSSF- It is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

Note that there is a component module that attempts to provide a common high level Java API to both OLE2 and OOXML document formats which is SS for Excel workbooks. So it's better to use SS package as much as possible so that one implementation can be replaced by another seamlessly.

The following interfaces from the SS model will be used in the example to read excel file in Java using Apache POI-

• org.apache.poi.ss.usermodel.Workbook- High level representation of a Excel workbook. This is the first object most users will construct whether they are reading or writing a workbook. Implementing classes for the HSSF and XSSF respectively are HSSFWorkbook and XSSFWorkbook.
• org.apache.poi.ss.usermodel.Sheet- High level representation of a Excel worksheet. Implementing classes for the HSSF and XSSF respectively are HSSFSheet and XSSFSheet.
• org.apache.poi.ss.usermodel.Row- High level representation of a row of a spreadsheet. Implementing classes for the HSSF and XSSF respectively are HSSFRow and XSSFRow.
• org.apache.poi.ss.usermodel.Cell- High level representation of a cell in a row of a spreadsheet. Cells can be numeric, formula-based or string-based (text). Implementing classes for the HSSF and XSSF respectively are HSSFCell and XSSFCell.

Apart from these interfaces there is a class WorkbookFactory that is used to create the appropriate workbook.

WorkbookFactory- Factory for creating the appropriate kind of Workbook (be it HSSFWorkbook or XSSFWorkbook), by auto-detecting from the supplied input.

Reading excel file in Java using Apache POI example

In the example following excel spreadsheet is being read which has two sheets- Users and Books.

For the first sheet after reading each row in the excel sheet an object of type User is created and added to an ArrayList. For the second sheet cell values are displayed on the console.

Model class (User.java) whose objects are created by reading each row of the excel sheet.

public class User {

  private String firstName;
  private String lastName;
  private String email;
  private Date DOB;

  public User() {

  }
  public User(String firstName, String lastName, String email, Date DOB) {
   this.firstName = firstName;
   this.lastName = lastName;
   this.email = email;
   this.DOB = DOB;
  }

  public String getFirstName() {
   return firstName;
  }
  public void setFirstName(String firstName) {
   this.firstName = firstName;
  }
  public String getLastName() {
   return lastName;
  }
  public void setLastName(String lastName) {
   this.lastName = lastName;
  }
  public String getEmail() {
   return email;
  }
  public void setEmail(String email) {
   this.email = email;
  }
  public Date getDOB() {
   return DOB;
  }
  public void setDOB(Date dOB) {
   DOB = dOB;
  }
}

import java.io.FileInputStream;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.netjs.Model.User;

public class ReadExcel {
  private static final String PATH_TO_EXCEL="resources\\user.xlsx";
  public static void main(String[] args) {
    new ReadExcel().readExcel(PATH_TO_EXCEL);
  }

  private void readExcel(String pathToExcel) {
    try {
      Workbook workbook = WorkbookFactory.create(new FileInputStream(pathToExcel));
      // If you have only one sheet you can get it by index of the sheet 
      //Sheet sheet = workbook.getSheetAt(0);
      Iterator sheetItr = workbook.sheetIterator();
      while(sheetItr.hasNext()) {
        Sheet sheet = sheetItr.next();
        // For Users sheet create List of objects
        if(sheet.getSheetName().equals("Users")) {
            readExcelSheet(sheet);
        }else {
          // For other sheet just print the cell values
          printExcelSheet(sheet);
        }
      }                   
    } catch (EncryptedDocumentException | IOException | ParseException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }

  private void readExcelSheet(Sheet sheet) throws ParseException{
    System.out.println("Starting to read sheet- " + sheet.getSheetName());
    Iterator rowItr = sheet.iterator();
    List userList = new ArrayList();
    // Iterate each row in the sheet
    while(rowItr.hasNext()) {
      User user = new User();
      Row row = rowItr.next();
      // First row is header so skip it
      if(row.getRowNum() == 0) {
        continue;
      }
      Iterator cellItr = row.cellIterator();
      // Iterate each cell in a row
      while(cellItr.hasNext()) {               
        Cell cell = cellItr.next();
        int index = cell.getColumnIndex();
        switch(index) {
          case 0:
            user.setFirstName((String)getValueFromCell(cell));
            break;
          case 1:
            user.setLastName((String)getValueFromCell(cell));
            break;
          case 2:
            user.setEmail((String)getValueFromCell(cell));
            break;
          case 3:
            user.setDOB((Date)getValueFromCell(cell));
            break;
        }
      }
      userList.add(user);
    }
    for(User user : userList) {
      System.out.println(user.getFirstName() + " " + user.getLastName() + " " + user.getEmail() + " " +  user.getDOB());
    }        
  }

  // This method is used to print cell values
  private void printExcelSheet(Sheet sheet) throws ParseException{
    System.out.println("Starting to read sheet- " + sheet.getSheetName());
    Iterator rowItr = sheet.iterator();
    while(rowItr.hasNext()) {
      Row row = rowItr.next();
      if(row.getRowNum() == 0) {
          continue;
      }
      Iterator cellItr = row.cellIterator();
      while(cellItr.hasNext()) {                
        Cell cell = cellItr.next();
        System.out.println("Cell Type- " + cell.getCellType().toString() + " Value- " + getValueFromCell(cell));
      }        
    }
  }

  // Method to get cell value based on cell type
  private Object getValueFromCell(Cell cell) {
    switch(cell.getCellType()) {
      case STRING:
        return cell.getStringCellValue();
      case BOOLEAN:
        return cell.getBooleanCellValue();
      case NUMERIC:
        if(DateUtil.isCellDateFormatted(cell)) {
          return cell.getDateCellValue();
        }
        return cell.getNumericCellValue();
      case FORMULA:
        return cell.getCellFormula();
      case BLANK:
        return "";
      default:
        return "";                                
    }
  }
}

Output

Starting to read sheet- Users
Jack Reacher [email protected] Sat Jul 12 00:00:00 IST 1975
Remington Steele [email protected] Thu Apr 28 00:00:00 IST 2016
Jonathan Raven [email protected] Thu Jan 06 00:00:00 IST 1966
Starting to read sheet- Books
Cell Type- STRING Value- Five Little Pigs
Cell Type- NUMERIC Value- 12.56
Cell Type- STRING Value- And Then There Were None
Cell Type- NUMERIC Value- 15.89
Cell Type- STRING Value- Dumb Witness
Cell Type- NUMERIC Value- 22.0
Cell Type- STRING Value- Curtain
Cell Type- NUMERIC Value- 18.99

That's all for this topic How to Read Excel File in Java Using Apache POI. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Programs Page

Related Topics

1. Creating PDF in Java Using iText
2. Spring MVC Excel Generation Example
3. How to Read Properties File in Java
4. How to Create PDF From XML Using Apache FOP
5. How to Run a Shell Script From Java Program

You may also like-

1. How to Convert Date And Time Between Different Time-Zones in Java
2. Setting And Getting Thread Name And Thread ID in Java
3. Convert double to int in Java
4. Java Program to Get All The Tables in a DB Schema
5. Abstraction in Java
6. Private Methods in Java Interface
7. Difference Between Comparable and Comparator in Java
8. Dependency Injection in Spring Framework