poiji

by ozlerhakan

ozlerhakan / poiji

:candy: A tiny library converting excel rows to a list of Java objects based on Apache POI

228 Stars 87 Forks Last release: 3 months ago (v3.0.0) MIT License 292 Commits 35 Releases

Available items

No Items, yet!

The developer of this repository has not created any items for sale yet. Need a bug fixed? Help with integration? A different license? Create a request here:

:toc: macro :toclevels: 1

= Poiji :version: v3.0.0

image:https://travis-ci.org/ozlerhakan/poiji.svg?branch=master["Build Status", link="https://travis-ci.org/ozlerhakan/poiji"] image:https://api.codacy.com/project/badge/Grade/6587e90886184da29a1b7c5634695c9d["Codacy code quality", link="https://www.codacy.com/app/ozlerhakan/poiji?utmsource=github.com&utmmedium=referral&utmcontent=ozlerhakan/poiji&utmcampaign=BadgeGrade"] image:https://coveralls.io/repos/github/ozlerhakan/poiji/badge.svg?branch=master["Coverage Status", link="https://coveralls.io/github/ozlerhakan/poiji?branch=master"] image:https://img.shields.io/badge/apache.poi-4.1.2-brightgreen.svg[] image:https://app.fossa.com/api/projects/git%2Bgithub.com%2Fozlerhakan%2Fpoiji.svg?type=shield["FOSSA Status", link="https://app.fossa.com/projects/git%2Bgithub.com%2Fozlerhakan%2Fpoiji?ref=badgeshield"] image:https://img.shields.io/badge/license-MIT-blue.svg[]

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 https://poi.apache.org/Apache Poi under the hood to fulfill the mapping process.

[%collapsible] toc::[]

== How it works

In your Maven/Gradle project, first add the corresponding dependency:

.maven

[source,xml]

com.github.ozlerhakan poiji 3.0.0

.gradle

[source,groovy]

dependencies { compile 'com.github.ozlerhakan:poiji:3.0.0'

}

You can find the latest and earlier development versions including javadoc and source files on https://oss.sonatype.org/content/groups/public/com/github/ozlerhakan/poiji/[Sonatypes OSS repository].

.
Poiji.fromExcel
Structure

com.poiji.bind.Poiji#fromExcel(java.io.File, java.lang.Class) com.poiji.bind.Poiji#fromExcel(java.io.File, java.lang.Class, java.util.function.Consumer<? super T>) com.poiji.bind.Poiji#fromExcel(java.io.File, java.lang.Class, com.poiji.option.PoijiOptions) com.poiji.bind.Poiji#fromExcel(java.io.File, java.lang.Class, com.poiji.option.PoijiOptions, java.util.function.Consumer<? super T>) com.poiji.bind.Poiji#fromExcel(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class) com.poiji.bind.Poiji#fromExcel(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class, java.util.function.Consumer<? super T>) com.poiji.bind.Poiji#fromExcel(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class, com.poiji.option.PoijiOptions) com.poiji.bind.Poiji#fromExcel(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class, com.poiji.option.PoijiOptions, java.util.function.Consumer<? super T>)

com.poiji.bind.Poiji#fromExcelProperties(java.io.File, java.lang.Class) com.poiji.bind.Poiji#fromExcelProperties(java.io.File, java.lang.Class, com.poiji.option.PoijiOptions) com.poiji.bind.Poiji#fromExcelProperties(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class)

com.poiji.bind.Poiji#fromExcelProperties(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class, com.poiji.option.PoijiOptions)

.
PoijiOptions.PoijiOptionsBuilder
Structure

com.poiji.option.PoijiOptions.PoijiOptionsBuilder#settings() com.poiji.option.PoijiOptions.PoijiOptionsBuilder#build() com.poiji.option.PoijiOptions.PoijiOptionsBuilder#dateLenient(boolean) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#dateRegex(String) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#datePattern(String) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#dateTimeFormatter(java.time.format.DateTimeFormatter) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#ignoreHiddenSheets(boolean) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#password(String) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#preferNullOverDefault(boolean) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#settings(int) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#sheetIndex(int) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#skip(int) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#limit(int) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#trimCellValue(boolean) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#headerStart(int) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#withCasting(Casting) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#caseInsensitive(boolean) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#namedHeaderMandatory(boolean) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#poijiNumberFormat(PoijiNumberFormat) com.poiji.option.PoijiOptions.PoijiOptionsBuilder#poijiLogCellFormat(PoijiLogCellFormat)

com.poiji.option.PoijiOptions.PoijiOptionsBuilder#disableXLSXNumberCellFormat()

=== Feature 1

Create your object model:

[source,java]

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

}

<1> As of 1.10, optionally we can access the index of each row item by using the

ExcelRow
annotation. Annotated variable should be of type
int
,
double
,
float
or
long
. <2> 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. <3> 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.

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
.

[source,java]

List employees = Poiji.fromExcel(new File("employees.xls"), Employee.class); // alternatively InputStream stream = new FileInputStream(new File("employees.xls")) List 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 header row of the excel data. If you want to ignore the first row of data, you need to use

PoijiOptions
.

[source,java]

PoijiOptions options = PoijiOptionsBuilder.settings(1).build(); // we eliminate Joe Doe. List 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:

[source,java]

PoijiOptions options = PoijiOptionsBuilder.settings() .sheetIndex(1) <1>

.build();

  1. 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:

[source,java]

PoijiOptions options = PoijiOptionsBuilder.settings() .preferNullOverDefault(true) <1>

.build();

  1. a field that is of type either
    java.util.Date
    ,
    Float
    ,
    Double
    ,
    Integer
    ,
    Long
    or
    String
    will have a
    null
    value.

=== Feature 2 Poiji allows specifying the sheet name using annotation

[source,java]

@ExcelSheet("Sheet2") (1) public class Student {

@ExcelCell(0)
private String name;

@ExcelCell(1) private String id;

@ExcelCell(2) private String phone;

@Override public String toString() { return "Student {" + " name=" + name + ", id=" + id + "'" + ", phone='" + phone + "'" + '}'; }

}

<1> With the

ExcelSheet
annotation we are configuring the name of the sheet to read data from. The other sheets will be ignored.

=== Feature 3

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 employees = Poiji.fromExcel(new File("employees.xls"), Employee.class, options);

=== Feature 4

The version

1.11
introduces a new annotation called
ExcelCellName
so that we can read the values by column names directly.

[source,java]

public class Person {

@ExcelCellName("Name")  <1>
protected String name;

@ExcelCellName("Address") protected String address;

@ExcelCellName("Age") protected int age;

@ExcelCellName("Email") protected String email;

}

  1. 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 |Email

|Joe |San Francisco, CA |30 |[email protected]

|Sophie |Costa Mesa, CA |20 |[email protected]

|===

[source,java]

List people = Poiji.fromExcel(new File("person.xls"), Person.class); people.size(); // 2 Person person = people.get(0); // Joe // San Francisco, CA // 30

// [email protected]

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:

[source,java]

public class Person {

@ExcelCell(0)
protected String name;

@ExcelCellName("Address") protected String address;

@ExcelCellName("Age") protected int age;

@ExcelCellName("Email") protected String email;

}

=== Feature 5

Your object model may be derived from a super class:

[source,java]

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:

[source,java]

List cars = Poiji.fromExcel(new File("cars.xls"), Car.class); cars.size(); // 2 Car car = cars.get(0); // Honda Civic // 2017

// 4

=== Feature 6

Consider you have a table like below:

|=== 3+|Group A 3+| Group B |NameA | AgeA | CityA | NameB | AgeB | CityB

|John Doe |21 |Vienna |Smith Michael |32 |McLean

|Jane Doe |28 |Greenbelt |Sean Paul |25 |Los Angeles

|Paul Ryan |19 |Alexandria |John Peter |25 |Vienna

|Peter Pan |23 |Alexandria |Arnold Regan |35 |Seattle

|===

The new

ExcelCellRange
annotation (as of 1.19) lets us aggregate a range of information in one object model. In this case, we collect the details of the first person in
classA
and for second person in
classB
:

[source,java]

public class Groups {

@ExcelCellRange private GroupA groupA;

@ExcelCellRange private GroupB groupB;

}

[source, java]

public class GroupA {

@ExcelCellName("NameA")
private String name;

@ExcelCellName("AgeA") private Integer age;

@ExcelCellName("CityA") private String city;

}

public class GroupB {

@ExcelCellName("NameB")
private String name;

@ExcelCellName("AgeB") private Integer age;

@ExcelCellName("CityB") private String city;

}

Using the conventional way, we can retrieve the data using

Poiji.fromExcel
:

[source,java]

PoijiOptions options = PoijiOptionsBuilder.settings().headerStart(1).build(); // header starts at 1 (zero-based). List groups = Poiji.fromExcel(new File(excel), Groups.class, options);

Groups firstRowGroups = actualGroups.get(0);

GroupA firstRowPerson1 = firstRowGroups.getGroupA();

GroupB secondRowPerson2 = firstRowGroups.getGroupB();

=== Feature 7

As of 1.14, Poiji supports Consumer Interface. As https://github.com/ozlerhakan/poiji/pull/39#issuecomment-409521808[@fmarazita] explained the usage, there are several benefits of having a Consumer:

  1. Huge excel file ( without you have all in memory)
  2. Run time processing/filtering data
  3. DB batch insertion

For example, we have a Calculation entity class and want to insert each row into a database while retrieving:

[source, java]

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

}

[source, java]

File fileCalculation = new File(example.xlsx);

PoijiOptions options = PoijiOptionsBuilder.settings().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);

}

=== Feature 8

Since Poiji 1.19.1, you can create your own casting implementation without relying on the default Poiji casting configuration using the

Casting
interface.

[source,java]

public class MyCasting implements Casting { @Override public Object castValue(Class<?> fieldType, String value, PoijiOptions options) { return value.trim(); } }

public class Person {

@ExcelCell(0)
protected String employeeId;

@ExcelCell(1) protected String name;

@ExcelCell(2) protected String surname;

}

Then you can add your custom implementation with the

withCasting
method:

[source,java]

PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings() .withCasting(new MyCasting()) .build();

List people = Poiji.fromExcel(excel, Person.class, options);

=== Feature 9

Since Poiji 2.3.0, you can annotate a

Map
with
@ExcelUnknownCells
to parse all entries, which are not mapped in any other way (for example by index or by name).

This is our object model:

[source,java]

public class MusicTrack {

@ExcelCellName("ID")
private String employeeId;

@ExcelCellName("AUTHOR") private String author;

@ExcelCellName("NAME") private String name;

@ExcelUnknownCells private Map unknownCells;

}

This is the excel file we want to parse:

|=== |ID | AUTHOR |NAME |ENCODING |BITRATE

|123923 |Joe Doe |The example song |mp3 |256

|56437 |Jane Doe |The random song |flac |1500 |===

The object corresponding to the first row of the excel sheet then has a map with

{ENCODING=mp3, BITRATE=256}
and the one for the second row has
{ENCODING=flac, BITRATE=1500}
.

=== Feature 10

Poiji 2.7.0 introduced 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.

[source,java]

public class MusicTrack {

@ExcelCellName("ID")
private String employeeId;

@ExcelCellName("AUTHOR") private String author;

}

This is the excel file we want to parse:

|=== |ID | Artist

|123923 |Joe Doe

|56437 |Jane Doe |===

In the default setting of Poiji (

namedHeaderMandatory=false
), the author field will be null for both objects. With
namedHeaderMandatory=true
, a
HeaderMissingException
will be thrown.

=== Feature 11

As of 2.7.0, we can observe each cell format of a given excel file. Assume that we have an excel file like below:

|=== |Date |12/31/2020 12.00 AM |===

We can get all the list of cell formats using

PoijiLogCellFormat
with
PoijiOptions
:

PoijiLogCellFormat log = new PoijiLogCellFormat(); PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings() .poijiCellFormat(log) .build(); List dates = Poiji.fromExcel(stream, poijiExcelType, Model.class, options);

Model model = rows.get(0) model.getDate();

// 12.00

Hmm, It looks like we did not achieve the correct date format when we get the date value (

12.00
). Let's see how internally the excel file parses the value of the cell via
PoijiLogCellFormat
:

List formats = log.formats(); InternalCellFormat cell10 = formats.get(1);

cell10.getFormatString() // mm:ss.0 cell10.getFormatIndex()

// 47

Now that we know the reason of why we don't see the expected date value, it's because the default format of the date cell is the

mm:ss.0
format with a given index 47, we need to change the default format of index (i.e.
47
). This format was automatically assigned to the cell having a number, but almost certainly with a special style or format. Note that this option should be used for debugging purpose only.

=== Feature 12

Using 2.7.0, we can change the default format of a cell using

PoijiNumberFormat
. Recall
feature 10
, we are unable to see the correct cell format what's more the excel file uses another format which we do not want to.

|=== |Date |12/31/2020 12.00 AM |===

Using

PoijiNumberFormat
option, we are able to change the behavior of the format of a specific index:

PoijiNumberFormat numberFormat = new PoijiNumberFormat(); numberFormat.putNumberFormat((short) 47, "mm/dd/yyyy hh.mm aa");

PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings() .poijiNumberFormat(numberFormat) .build();

List rows = Poiji.fromExcel(stream, poijiExcelType, Model.class, options);

Model model = rows.get(0) model.getDate();

// 12/31/2020 12.00 AM <1>

  1. Voila!

We know that the index 47 uses the format

mm:ss.0
by default in the given excel file, thus we're able to override its format with
mm/dd/yyyy hh.mm aa
using the
putNumberFormat
method.

=== Feature 13

Since Poiji 2.8.0, it is possible to read excel properties from xlsx files. To achieve that, create a class with fields annotated with

@ExcelProperty
.

Example:

[source,java]

public class ExcelProperties { @ExcelProperty private String title;

@ExcelProperty
private String customProperty;

}

The field name corresponds to the name of the property inside the Excel file. To use a different one than the field name, you can specify a

propertyName
(e.g.
@ExcelProperty(propertyName = "customPropertyName")
)

The list of built-in (e.g. non-custom) properties in an Excel file, which can be read by Poiji can be found in the class

DefaultExcelProperties
.

Poiji can only read Text properties from an Excel file, so you have to use a

String
to read them. This does not apply to "modified", "lastPrinted" and "created", which are deserialized into a
Date
.

=== Feature 14

Consider we have a xls or xlsx excel file like below:

|=== |Amount |25,00 |(50,00) |(65,00) |===

Since we use a cell format on line 4 and 5 (i.e.

(50,00)
and
(65,00)
), we don't want to see the formatted value of each cell after processing. In order to do that, we can use
@DisableCellFormatXLS
on a field if the file ends with
xls
or
disableXLSXNumberCellFormat()
for xlsx files using
PoijiOptions
.

.xls files

public class TestInfo { @ExcelCell(0) @DisableCellFormatXLS <1> public BigDecimal amount;

}

  1. we only disable cell formats on the specified column.

.xlsx files

public class TestInfo { @ExcelCell(0) private BigDecimal amount; }

PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings() .disableXLSXNumberCellFormat() <1>

.build();

  1. when disabling number cell format, we disable it in the entire cells for xlsx files.

and let Poiji ignores the cell formats:


List result = Poiji.fromExcel(new File(path), TestInfo.class, options); <1>

result.get(1).amount

// -50

  1. Add
    options
    , if your excel is xlsx file.

== Try with JShell

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

== Stargazers over time

image:https://starcharts.herokuapp.com/ozlerhakan/poiji.svg["Stargazers over time", link="https://starcharts.herokuapp.com/ozlerhakan/poiji"]

== License

image:https://app.fossa.com/api/projects/git%2Bgithub.com%2Fozlerhakan%2Fpoiji.svg?type=large["FOSSA Status", link="https://app.fossa.com/projects/git%2Bgithub.com%2Fozlerhakan%2Fpoiji?ref=badge_large"]

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.