How to Read Excel File Using Apache Poi and Java

Apache POI

Apache POI is a open source library developed by the Apache Software Foundation for creating or reading the Microsoft Excel Files. The Apache POI library helps to create, modify, read or delete values from the Microsoft excel using Java. Java or jdk doesn't support the direct use of excel's in Java. Apachea POI is easy to integrate into the maven or gradle project and it supports all the excel file formats(xls and xlsx).

Table of Contents

1. Components of Apache POI Used
2. Dependency
3. Program Input (SpreadSheet)
4. Program to Read the Excel File(xls and xlsx format) in the Java Program
5. Console Output
6. Conclusion

1. Components of Apache POI Used

HSSF (Horrible Spreadsheet Format) - This is used to read or write the xls format Microsoft excel files.

XSSF (XML Spreadsheet Format) - This is used to read or write the xlsx format Microsoft excel files.

Workbook - The workbook is a complete representation of SpreadSheet with Sheet, Row, and Cell details.

Sheet - SpreadSheet can have more than one sheet and this Sheet attribute means one Sheet in the SpreadSheet.

Row - Row means a row in excel.

Cell - Cell means a cell in the excel row.

2. Dependency 

Below are the dependencies for the Apache poi ooxml we have used in our project, according to your project download the jar in the application.

Maven

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

Gradle

// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.2'

3. Program Input (SpreadSheet)

File in the XLS format, SpreadSheet Name - student-doc

File in the XLSX format, SpreadSheet Name - student-list

4. Program to Read the Excel File(xls and xlsx format) in the Java Program

ReadExcelFile.java

package com.javaglobe.excelreading;

import java.io.File;

import java.io.IOException;

import java.util.List;

import java.util.Objects;

import java.util.Spliterator;

import java.util.Spliterators;

import java.util.stream.Collectors;

import java.util.stream.StreamSupport;

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 com.javaglobe.model.Student;

public class ReadExcelFile {

    private static final int REGISTRATION_NUMBER_INDEX = 0;

    private static final int NAME_INDEX = 1;

    private static final int MARK_INDEX = 2;

    private static final int CLASS_INDEX = 3;

    private List<Student> readExcelValues(File fileInputStream) throws IOException {

        System.out.println("SpreadSheet Path & Name : " + fileInputStream.getPath());

        //Create a workbook high-level representation of the Spreadsheet file

        Workbook wb = WorkbookFactory.create(fileInputStream);

        System.out.println("Sheet Name : " + wb.getSheetName(0));

        // Taking the first Sheet from the SpreadSheet directly

        Sheet sheet1 = wb.getSheetAt(0);

        System.out.println("SpreadSheet Row Count : " + sheet1.getPhysicalNumberOfRows());

        //Create a SplitIterator from the row Iterator and Stream the row values in the Excel.

        //Skipping the first value from the row because that is heading of the column.

        //Calling createStudent() for creating the Student Object from the row and collect all student Object to a list and return.

        return StreamSupport

                .stream(Spliterators.spliteratorUnknownSize(sheet1.rowIterator(), Spliterator.ORDERED), false).skip(1)

                .map(this::createStudent).collect(Collectors.toList());

    }

    private Student createStudent(Row row) {

        // Read each row and return as Student Object.

        Student student = null;

        if (Objects.nonNull(row)) {

            student = new Student();

            student.setStudentRegistrationNumber(row.getCell(REGISTRATION_NUMBER_INDEX).getStringCellValue());

            student.setStudentName(row.getCell(NAME_INDEX).getStringCellValue());

            student.setStudentMark((int) row.getCell(MARK_INDEX).getNumericCellValue());

            student.setStudentClass((int) row.getCell(CLASS_INDEX).getNumericCellValue());

        }

        return student;

    }

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

        ReadExcelFile readFile = new ReadExcelFile();

        // Read the file

        File xlsxFile = new File("./src/main/resources/student-list.xlsx");

        File xlsFile = new File("./src/main/resources/student-doc.xls");

        // Reading the xlsxFile now, If you want to read the xlsFile change the below code to- readFile.readExcelValues(xlsFile);

        List<Student> students = readFile.readExcelValues(xlsxFile);

        // Printing the created Student List Values

        students.forEach(v -> System.out.println(v.toString()));

    }

}

Student.java

package com.javaglobe.model;

public class Student {

    private String studentRegistrationNumber;

    private String studentName;

    private Integer studentMark;

    private Integer studentClass;

    public Student() {}

    public String getStudentRegistrationNumber() {

        return studentRegistrationNumber;

    }

    public void setStudentRegistrationNumber(String studentRegistrationNumber) {

        this.studentRegistrationNumber = studentRegistrationNumber;

    }

    public String getStudentName() {

        return studentName;

    }

    public void setStudentName(String studentName) {

        this.studentName = studentName;

    }

    public Integer getStudentMark() {

        return studentMark;

    }

    public void setStudentMark(Integer studentMark) {

        this.studentMark = studentMark;

    }

    public Integer getStudentClass() {

        return studentClass;

    }

    public void setStudentClass(Integer studentClass) {

        this.studentClass = studentClass;

    }

    @Override

    public String toString() {

        return "Student [studentRegistrationNumber=" + studentRegistrationNumber + ", studentName=" + studentName

                + ", studentMark=" + studentMark + ", studentClass=" + studentClass + "]";

    }

}

5. Console Output

SpreadSheet Name - student-doc.xls Output

SpreadSheet Name - student-list.xlsx Output

6. Conclusion

Hope you are able to read the excel file using java, the complete source code is available in the Github Repository. If this article helps, please provide a star on Github.