Java Read Excel File using Apache POI

Tags: Apache POI Excel

In this Java Apache POI tutorial we learn how to read Excel .xlsx files in Java program with step by step guide using the Apache POI library.

Table of contents

  1. Add Apache POI dependencies to Java project
  2. Example Excel File to Read
  3. Create Entity Class
  4. Implement Excel File Reader Java Class
  5. How to use the ExcelFileReader Class to Read Excel File

Add Apache POI dependencies to Java project

First step, we need to add the Apache POI dependencies to the Java project.

If you use Gradle build project, add the following dependencies to the build.gradle file. 

implementation group: 'org.apache.poi', name: 'poi', version: '5.2.2'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.2'

If you use Maven build project, add the following dependencies to the pom.xml file.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

Example Excel File to Read

For example, we have an Excel file located at D:\SimpleSolution\Data\contacts.xlsx with 3 data columns represent a list of contact information as the following screenshot.

Java Read Excel File using Apache POI

Create Entity Class

At this step we implement a new Java class named Contact to represent the contact information which need to read.

Contact.java 

public class Contact {
    private String firstName;
    private String lastName;
    private String email;

    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;
    }

    @Override
    public String toString() {
        return "Contact{" +
                "firstName='" + firstName + '\'' +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

Implement Excel File Reader Java Class

At this step we create a new Java class named ExcelFileReader to read the Excel file and return a list of Contact objects with the following steps.

  • Read the Excel file using Java FileInputStream class with given file name.
  • Read InputStream into a Workbook object which represents an Excel workbook.
  • From the Workbook, get the first Sheet object which represent an Excel worksheet.
  • Get row Iterator object using Sheet.rowIterator() method which to allow reading Excel sheet row by row.
  • Loop the row Iterator and get cell Iterator using Row.cellIterator() method which allow to read cell data of 3 columns.
  • With each row we create Contact object to store data and add it to the List of Contact as final result.

ExcelFileReader.java 

import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ExcelFileReader {

    private static final int FIRST_NAME_COLUMN_INDEX = 0;
    private static final int LAST_NAME_COLUMN_INDEX = 1;
    private static final int EMAIL_COLUMN_INDEX = 2;

    /***
     * Read the Excel file content and return a list of contact.
     * @param fileName the Excel file name
     * @return List of Contact objects
     */
    public List<Contact> readExcelFile(String fileName) {
        List<Contact> result = new ArrayList<>();
        try(InputStream fileInputStream = new FileInputStream(fileName)) {
            // Read InputStream into Workbook
            Workbook workbook = new XSSFWorkbook(fileInputStream);
            // Read the first Sheet
            Sheet sheet = workbook.getSheetAt(0);

            // Get row Iterator
            Iterator<Row> rowIterator = sheet.rowIterator();

            // Skip the first row because it is the header row
            if(rowIterator.hasNext()) {
                rowIterator.next();
            }

            // Read all data rows
            while(rowIterator.hasNext()) {
                Row row = rowIterator.next();
                // Get cell Iterator
                Iterator<Cell> cellIterator = row.cellIterator();
                Contact contact = new Contact();
                // Read cell data
                while(cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    int columnIndex = cell.getColumnIndex();
                    switch (columnIndex) {
                        case FIRST_NAME_COLUMN_INDEX:
                            contact.setFirstName(cell.getStringCellValue());
                            break;
                        case LAST_NAME_COLUMN_INDEX:
                            contact.setLastName(cell.getStringCellValue());
                            break;
                        case EMAIL_COLUMN_INDEX:
                            contact.setEmail(cell.getStringCellValue());
                            break;
                    }
                }
                result.add(contact);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return result;
    }
}

How to use the ExcelFileReader Class to Read Excel File

At this final step we implement a Main class and use the ExcelFileReader class above to read the Excel file content as the following Java code.

Main.java 

import java.util.List;

public class Main {
    public static void main(String... args) {
        ExcelFileReader excelFileReader = new ExcelFileReader();
        List<Contact> contacts = excelFileReader.readExcelFile("D:\\SimpleSolution\\Data\\contacts.xlsx");

        for (Contact contact : contacts) {
            System.out.println(contact.toString());
        }
    }
}
The output as below. 
Contact{firstName='Rayhan', lastName='Harmon', email='rayhan@simplesolution.dev'}
Contact{firstName='Paddy', lastName='Sheridan', email='paddy@simplesolution.dev'}
Contact{firstName='Clara', lastName='Callaghan', email='clara@simplesolution.dev'}
Contact{firstName='Josephine', lastName='Amos', email='josephine@simplesolution.dev'}
Contact{firstName='Sheikh', lastName='Tucker', email='sheikh@simplesolution.dev'}

Happy Coding 😊

Java Read Excel Workbook from File using Apache POI

Java Read Excel Workbook from InputStream using Apache POI

Java Create Excel File .xlsx using Apache POI

Java Apache POI Tutorial

Spring Boot Download Excel File Export from MySQL Database

Spring Boot Web Application Download Excel File

Java Read Password Protected Excel File using Apache POI

Java How to Iterate over Sheets Rows and Cells of Excel file using Apache POI

Java Add Rows to Existing Excel File using Apache POI

Java Add Sheet to Existing Excel File using Apache POI

Java Remove Sheet from Existing Excel File using Apache POI

Java Create Formula Excel Cells using Apache POI