Read Excel Data Using Apache POIJI

Introduction:

Poiji is developed on top of very popular Apache POI just like Spring Boot on top of Spring. It uses lots of annotations to make our tasks even easier.
Poiji is a teeny Java library that programmatically maps excel sheet to Java classes. It supports JDK 8 and later versions.

Java Dependencies:

<dependency>
  <groupId>com.github.ozlerhakan</groupId>
  <artifactId>poiji</artifactId>
  <version>3.1.1</version>
</dependency>

dependencies {
    compile 'com.github.ozlerhakan:poiji:3.1.1'
}

Poiji Feature1:

Create a student model class.

public class Student {

    @ExcelRow                    
    private int rowIndex;

    @ExcelCell(0)                
    private long studentId;     

    @ExcelCell(1)
    private String name;

    @ExcelCell(2)
    private String surname;

    @ExcelCell(3)
    private int age;

    @ExcelCellName("emails")     
    List<String> emails;

    @ExcelCell(5)
    List<String> courses;

}

A field must be annotated with @ExcelCell along with its property in order to get the value from the right coordinate in the target excel sheet.
An annotated field can be either protected, private or public modifier.
The field may be either of boolean, int, long, float, double, or their wrapper classes. You can add a field of java.util.Date, java.time.LocalDate, java.time.LocalDateTime and String as well.
If one column contains multiple value, you can get them using a List field. A List field can store items which is of type BigDecimal, Long, Double, Float, Integer, Boolean and String.

Below code snippet shows how to read excel data using poiji.

PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings().build();
List<Student> students = Poiji.fromExcel(new File("Students.xls"), Student.class, options);
students.size();

By default, Poiji selects the first sheet of an excel file and ignores the header row of the excel data. If we want to ignore the first row of data then follow below code snippet.

PoijiOptions options = PoijiOptionsBuilder.settings(1).build(); // we eliminate 1st row of data
List<Student> students = Poiji.fromExcel(new File("students.xls"), Student.class, options);
students.size();

Poiji Feature2:

By using @ExcelSheet annotation we can specify the sheetname. we are configuring the name of the sheet to read data from and the other sheets will be ignored.

Poiji Feature3:

If our excel file is protected with a password, we can define the password via PoijiOptionsBuilder to read excel data rows.

PoijiOptions options = PoijiOptionsBuilder.settings() .password("password").build();
List<Student> students = Poiji.fromExcel(new File("students.xls"), Student.class, options);

Poiji Feature4:

Using @ExcelCellName annotation we can read the excel data directly using column names.

public class Student {

    @ExcelCellName("Student Name")  (1)
    private String name;

    @ExcelCellName("Address")
    private String address;

    @ExcelCellName("Age")
    private int age;

    @ExcelCellName("Email")
    private  String email;

}

By default, @ExcelCellName is case-sensitive and the excel file should’t contain duplicated column names

Poiji Feature5:

We can map Map with @ExcelUnknownCells annotation to parse all excel entries. which are not mapped in any other way for example by index or by name using @ExcelCell or @ExcelCellName annotation.

Below is our object model.

public class StudentExcelModel {

    @ExcelUnknownCells
    private Map<String, String> unknownCells = new LinkedHashMap();

}

Below is our code snippet:

File file = new File("./Student.xlsx");
List<StudentExcelModel> excelUnknownColumns =  Poiji.fromExcel(file,StudentExcelModel.class);

excelUnknownColumns.size();

Output:

[
Student [rowIndex=1, studentId=1, name=test, surname=test, age=25, emails=test@gmail.com, courses=java],
Student [rowIndex=2, studentId=2, name=test1, surname=test1, age=28, emails=test1@gmail.com, courses=Python],
]

we will get List of HashMap objects. from that list we have to iterate over each Map object to get the excel rows data. By using key we can get the value of the particular excel column.

Poiji Feature6:

Poiji supports the Option namedHeaderMandatory. If set to true, Poiji will check that all field annotated with @ExcelCellName must have a corresponding column in the Excel sheet. If any column is missing a HeaderMissingException will be thrown.

learnjavatogether