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).
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-ooxmlimplementation 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.
0 Comments