com.poiji.bind.Poiji#fromExcel(java.io.File, java.lang.Class<T>)com.poiji.bind.Poiji#fromExcel(java.io.File, java.lang.Class<T>, java.util.function.Consumer<? super T>)com.poiji.bind.Poiji#fromExcel(java.io.File, java.lang.Class<T>, com.poiji.option.PoijiOptions)com.poiji.bind.Poiji#fromExcel(java.io.File, java.lang.Class<T>, 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<T>)com.poiji.bind.Poiji#fromExcel(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class<T>, java.util.function.Consumer<? super T>)com.poiji.bind.Poiji#fromExcel(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class<T>, com.poiji.option.PoijiOptions)com.poiji.bind.Poiji#fromExcel(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class<T>, com.poiji.option.PoijiOptions, java.util.function.Consumer<? super T>)com.poiji.bind.Poiji#fromExcel(org.apache.poi.ss.usermodel.Sheet, java.lang.Class<T>)com.poiji.bind.Poiji#fromExcel(org.apache.poi.ss.usermodel.Sheet, java.lang.Class<T>, com.poiji.option.PoijiOptions)com.poiji.bind.Poiji#fromExcel(org.apache.poi.ss.usermodel.Sheet, java.lang.Class<T>, com.poiji.option.PoijiOptions, java.util.function.Consumer<? super T>)com.poiji.bind.Poiji#fromExcelProperties(java.io.File, java.lang.Class<T>)com.poiji.bind.Poiji#fromExcelProperties(java.io.File, java.lang.Class<T>, com.poiji.option.PoijiOptions)com.poiji.bind.Poiji#fromExcelProperties(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class<T>)com.poiji.bind.Poiji#fromExcelProperties(java.io.InputStream, com.poiji.exception.PoijiExcelType, java.lang.Class<T>, com.poiji.option.PoijiOptions)

com.poiji.option.PoijiOptions.PoijiOptionsBuilder    #settings()    #build()    #dateLenient(boolean)    #dateRegex(String)    #datePattern(String)    #dateTimeFormatter(java.time.format.DateTimeFormatter)    #ignoreHiddenSheets(boolean)    #password(String)    #preferNullOverDefault(boolean)    #settings(int)    #sheetIndex(int)    #skip(int)    #limit(int)    #trimCellValue(boolean)    #headerStart(int)    #withCasting(Casting)    #withFormatting(Formatting)    #caseInsensitive(boolean)    #ignoreWhitespaces(boolean)    #poijiNumberFormat(PoijiNumberFormat)    #poijiLogCellFormat(PoijiLogCellFormat)    #disableXLSXNumberCellFormat()    #addListDelimiter(String)    #setLocale(java.util.Locale)    #rawData(boolean)

Annotations

Create your object model:

1. Optionally, we can access the index of each row item by using theExcelRow annotation. Annotated variable should be of typeint,double,float orlong.

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 ofboolean,int,long,float,double, or their wrapper classes. You can add a field ofjava.util.Date,java.time.LocalDate,java.time.LocalDateTime andString as well.

4. If one column contains multiple value, you can get them using a List field. A List field can store items which is of typeBigDecimal,Long,Double,Float,Integer,Boolean andString.

This is the excel file (employees.xlsx) we want to map to a list ofEmployee instance:

The snippet below shows how to obtain the excel data usingPoiji.

1. By default the delimiter/separator is, to split items in a cell. There is an option to change this behavior. Since we use; between items, we need to tell Poiji to use; as a separator.

By default, Poiji ignores the header row of the excel data. If you want to ignore the first row of data, you need to usePoijiOptions.

By default, Poiji selects the first sheet of an excel file. You can override this behaviour like below:

1. Poiji should look at the second (zero-based index) sheet of your excel file.== Documentation

Prefer Default Value

If you want a date field to returnnull rather than a default date, usePoijiOptionsBuilder with thepreferNullOverDefault method as follows:

1. a field that is of type eitherjava.util.Date,Float,Double,Integer,Long orString will have anull value.

Sheet Name Option

Poiji allows specifying the sheet name using annotation

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

Protected Excels

Consider that your excel file is protected with a password, you can define the password viaPoijiOptionsBuilder to read rows:

Annotation ExcelCellName

UsingExcelCellName, we can read the values by column names directly.

1. We need to specify thename of the column for which the corresponding value is looked. By default,@ExcelCellName is case-sensitive and the excel file should’t contain duplicated column names. However, you can manipulate this feature usingPoijiOptionsBuilder#caseInsensitive(boolean) and you can ignore white spaces usingPoijiOptionsBuilder#ignoreWhitespaces(boolean).

2. In rare situations a column can have synonyms, especially when the column was renamed and backward compatibility is needed.Here we specify theexpression leaving the columnname empty.

For example, here is the excel (person.xls) file we want to use:

Given that the first column always stands for the names of people, you’re able to combine theExcelCell annotation withExcelCellName in your object model:

Super Class Inheritance

Your object model may be derived from a super class:

and you want to map the table (car.xlsx) below to Car objects:

Using Poiji, you can map the annotated field(s) of super class(es) of the target class like so:

Annotation ExcelCellsJoinedByName

UsingExcelCellsJoinedByName we can read columns which name meets same regular expression. Values will be combined as a multi valued map.

Please pay attention the variable must be initialized explicitly.

1. Here we map multiple columns withnameArtist.

2. Here we map multiple columns withnameTrack1,Track2,Track3, etc.

For example, here is the excel (album.xls) file we want to use:

Json presentation foralbum1 will be as follows

ExcelCellRange Annotation

Consider you have a table like below:

TheExcelCellRange annotation lets us aggregate a range of information in one object model. In this case, we collect the data inPersonCreditInfo plus details of the person inPersonInfo and for the credit card inCardInfo:

Using the conventional way, we can retrieve the data usingPoiji.fromExcel:

Support Consumer Interface

Poiji supports Consumer Interface. As@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:

Custom Casting Implementation

You can create your own casting implementation without relying on the default Poiji casting configuration using theCasting interface.

Then you can add your custom implementation with thewithCasting method:

Parse UnknownCells

You can annotate aMap<String, String> 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:

This is the excel file we want to parse:

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

Note that If you use thePoijiOptionsBuilder#caseInsensitive(true) option, the ExcelUnknownCells map will be parsed with lowercase.

Optional Mandatory Headers and Cells

As of 4.0.0,@ExcelCellName and@ExcelCell are given to providemandatoryHeader andmandatoryCell fields, meaning we can be more specific by declaring rules in headers or cells or both in excel files.mandatoryHeader expects the labeled excel column exists in a given excel file, whilemandatoryCell looks into the values of the mentioned header/column. If any of the options is active and your excel breaks the rule, either aHeaderMissingException orPoijiMultiRowException will be thrown accordingly.

Debug Cells Formats

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

We can get all the list of cell formats usingPoijiLogCellFormat withPoijiOptions:

Hmm, It looks like we did not achieve the correct date format since we get the date value as (12.00). Let’s see how internally the excel file is being parsed viaPoijiLogCellFormat:

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

Modify Cells Formats

We can change the default format of a cell usingPoijiNumberFormat. RecallDebug Cells Formats, we are unable to see the correct cell format, what’s more the excel file uses another format which we do not want to.

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

1. Voila!

We know that the index 47 uses the formatmm:ss.0 by default in the given excel file, thus we’re able to override its format withmm/dd/yyyy hh.mm aa using theputNumberFormat method.

Read Excel Properties

It is possible to read excel properties from xlsx files. To achieve that, create a class with fields annotated with@ExcelProperty.

Example:

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 apropertyName (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 classDefaultExcelProperties.

Poiji can only read Text properties from an Excel file, so you have to use aString to read them.This does not apply to "modified", "lastPrinted" and "created", which are deserialized into aDate.

Disable Cells Formats

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

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 withxls ordisableXLSXNumberCellFormat() for xlsx files usingPoijiOptions.

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

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

and let Poiji ignores the cell formats:

1. Addoptions, if your excel is xlsx file.

Create Custom Formatting

You can create your own formatting implementation without relying on the default Poiji formatting configuration using theFormatting interface.

1. Suppose that all the header names of an excel file have different formatting. Using custom formatting, we are able to look at headers with a custom format. All the headers will be uppercase and don’t have white spaces before and after.

Then you can add your custom implementation with thewithFormatting method:

Poi Sheet Support

Poiji accepts excel records via Poi Sheet object as well:

Update Default Locale

For parsing numbers and dates java.lang.Locale is used. Also Apache Poi uses the Locale for parsing.As default, Poij uses Locale.US irrespective of Locale used on the running system. If you want to change thatyou can use a option to pass the Locale to be used like shown below.

In this example the Jvm default locale is used. Beware that if your code run’s on a other Jvm with another Locale set as default parsing could give different results. Better is to use a fixed locale.Also be aware of differences how Locales behave between Java 8 and 9+. For example AM/PM in Locale.GERMANY is displayed as AM/PM in Java 8 but Vorn./Nam. in Java 9 or higher.This is due to the changes in Java 9. SeeJEP-252 for more details.