Poiji is a teeny Java library that provides one way mapping from Excel sheets to Java classes. In a way it lets us convert each row of the specified excel data into Java objects. Poiji uses Apache Poi (the Java API for Microsoft Documents) under the hood to fulfill the mapping process.
In your Maven/Gradle project, first add the corresponding dependency:
<dependency>
<groupId>com.github.ozlerhakan</groupId>
<artifactId>poiji</artifactId>
<version>1.16</version>
</dependency>
dependencies {
compile 'com.github.ozlerhakan:poiji:1.16'
}
You can find the latest and earlier development versions including javadoc and source files on Sonatypes OSS repository.
Create your object model:
public class Employee {
@ExcelRow (1)
private int rowIndex;
@ExcelCell(0) (2)
private long employeeId; (3)
@ExcelCell(1)
private String name;
@ExcelCell(2)
private String surname;
@ExcelCell(3)
private int age;
@ExcelCell(4)
private boolean single;
@ExcelCell(5)
private String birthday;
//no need getters/setters to map excel cells to fields
@Override
public String toString() {
return "Employee{" +
"rowIndex=" + rowIndex +
", employeeId=" + employeeId +
", name='" + name + '\'' +
", surname='" + surname + '\'' +
", age=" + age +
", single=" + single +
", birthday='" + birthday + '\'' +
'}';
}
}
-
As of 1.10, optionally we can access the index of each row item by using the
ExcelRow
annotation. Annotated variable should be of typeint
,double
,float
orlong
. -
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 ofjava.util.Date
,java.time.DateTime
andString
as well.
This is the excel file (employees.xlsx
) we want to map to a list of Employee
instance:
ID | NAME | SURNAME | AGE | SINGLE | BIRTHDAY |
---|---|---|---|---|---|
123923 |
Joe |
Doe |
30 |
TRUE |
4/9/1987 |
123123 |
Sophie |
Derue |
20 |
TRUE |
5/3/1997 |
135923 |
Paul |
Raul |
31 |
FALSE |
4/9/1986 |
The snippet below shows how to obtain the excel data using Poiji
.
List<Employee> employees = Poiji.fromExcel(new File("employees.xls"), Employee.class);
// alternatively
InputStream stream = new FileInputStream(new File("employees.xls"))
List<Employee> employees = Poiji.fromExcel(stream, PoijiExcelType.XLS, Employee.class, options);
employees.size();
// 3
Employee firstEmployee = employees.get(0);
// Employee{rowIndex=1, employeeId=123923, name='Joe', surname='Doe', age=30, single=true, birthday='4/9/1987'}
By default, Poiji ignores the first row of the excel data. You can override this behaviour by setting a PoijiOptions
.
PoijiOptions options = PoijiOptionsBuilder.settings(2).build();
List<Employee> employees = Poiji.fromExcel(new File("employees.xls"), Employee.class, options);
Employee firstEmployee = employees.get(0);
// Employee{rowIndex=2, employeeId=123123, name='Sophie', surname='Derue', age=20, single=true, birthday='5/3/1997'}
By default, Poiji selects the first sheet of an excel file. You can override this behaviour like below:
PoijiOptions options = PoijiOptionsBuilder.settings()
.sheetIndex(1) (1)
.build();
-
Poiji should look at the second (zero-based index) sheet of your excel file.
If you want a date field to return null
rather than a default date, use PoijiOptionsBuilder
with the preferNullOverDefault
method as follows:
PoijiOptions options = PoijiOptionsBuilder.settings()
.preferNullOverDefault(true) (1)
.build();
-
a field that is of type either
java.util.Date
,Float
,Double
,Integer
,Long
orString
will have anull
value.
Consider that your excel file is protected with a password, you can define the password via PoijiOptionsBuilder
to read rows:
PoijiOptions options = PoijiOptionsBuilder.settings() .password("1234") .build(); List<Employee> employees = Poiji.fromExcel(new File("employees.xls"), Employee.class, options);
The version 1.11
introduces a new annotation called ExcelCellName
so that we can read the values by column names directly.
public class Person {
@ExcelCellName("Name") (1)
protected String name;
@ExcelCellName("Address")
protected String address;
@ExcelCellName("Age")
protected int age;
@ExcelCellName("Email")
protected String email;
}
-
We need to specify the
name
of the column for which the corresponding value is looked.@ExcelCellName
is case-sensitive and the excel file should’t contain duplicated column names.
For example, here is the excel (person.xls
) file we want to use:
Name | Address | Age | |
---|---|---|---|
Joe |
San Francisco, CA |
30 |
|
Sophie |
Costa Mesa, CA |
20 |
List<Person> people = Poiji.fromExcel(new File("person.xls"), Person.class);
people.size();
// 2
Person person = people.get(0);
// Joe
// San Francisco, CA
// 30
// joe@doe.com
Given that the first column always stands for the names of people, you’re able to combine the ExcelCell
annotation with ExcelCellName
in your object model:
public class Person {
@ExcelCell(0)
protected String name;
@ExcelCellName("Address")
protected String address;
@ExcelCellName("Age")
protected int age;
@ExcelCellName("Email")
protected String email;
}
Your object model may be derived from a super class:
public abstract class Vehicle {
@ExcelCell(0)
protected String name;
@ExcelCell(1)
protected int year;
}
public class Car extends Vehicle {
@ExcelCell(2)
private int nOfSeats;
}
and you want to map the table (car.xlsx
) below to Car objects:
NAME | YEAR | SEATS |
---|---|---|
Honda Civic |
2017 |
4 |
Chevrolet Corvette |
2017 |
2 |
Using Poiji, you can map the annotated field(s) of super class(es) of the target class like so:
List<Car> cars = Poiji.fromExcel(new File("cars.xls"), Car.class);
cars.size();
// 2
Car car = cars.get(0);
// Honda Civic
// 2017
// 4
As of 1.14, Poiji supports Consumer Interface. As @fmarazita explained the usage, there are several benefits of having a Consumer:
-
Huge excel file ( without you have all in memory)
-
Run time processing/filtering data
-
DB batch insertion
For example, we have a Calculation entity class and want to insert each row into a database while retrieving:
class Calculation {
@ExcelCell(0)
String name
@ExcelCell(1)
int a
@ExcelCell(2)
int b
public int getA(){
return a;
}
public int getB(){
return b;
}
public int getName(){
return name;
}
}
File fileCalculation = new File(example.xlsx);
PoijiOptions options = PoijiOptionsBuilder.settings(1).sheetIndex(1).build();
Poiji.fromExcel(fileCalculation, Calculation.class, options, this::dbInsertion);
private void dbInsertion(Calculation siCalculation) {
int value= siCalculation.getA() + siCalculation.getB();
String name = siCalculation.getName();
insertDB(name , value);
}
Since we have a new pedagogic tool, Java 9 REPL, you can try Poiji in JShell. Clone the repo and follow the steps below. JShell should open up a new jshell session once loading the startup scripts and the specified jars that must be in the classpath. You must first import and create related packages and classes before using Poiji. We are able to use directly Poiji and Employee classes because they are already imported from jshell/snippets
with try-with-jshell.sh
.
$ cd poiji/ $ ./try-with-jshell.sh | Welcome to JShell -- Version 9 | For an introduction type: /help intro jshell> List<Employee> employees = Poiji.fromExcel(new File("src/test/resources/employees.xlsx"), Employee.class); jshell> employees.forEach(System.out::println) Employee{employeeId=123923, name='Joe', surname='Doe', age=30, single=true, birthday='4/9/1987'} Employee{employeeId=123123, name='Sophie', surname='Derue', age=20, single=false, birthday='5/3/1997'} Employee{employeeId=135923, name='Paul', surname='Raul', age=31, single=false, birthday='4/9/1986'}