ozlerhakan / poiji

:candy: A library converting XLS and XLSX files to a list of Java objects based on Apache POI

Poiji

Poiji is a tiny thread-safe 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.

Table of Contents

• Getting Started
• Usage
• Documentation
  • Annotations
  • Prefer Default Value
  • Sheet Name Option
  • Protected Excels
  • Annotation ExcelCellName
  • Super Class Inheritance
  • Annotation ExcelCellsJoinedByName
  • ExcelCellRange Annotation
  • Support Consumer Interface
  • Custom Casting Implementation
  • Parse UnknownCells
  • Optional Mandatory Headers and Cells
  • Debug Cells Formats
  • Modify Cells Formats
  • Read Excel Properties
  • Disable Cells Formats
  • Create Custom Formatting
  • Poi Sheet Support
  • Update Default Locale
• License

Getting Started

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

maven

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

You can find the latest and earlier development versions including javadoc and source files on Sonatypes OSS repository.

Usage

Poiji.fromExcel Structure

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)

PoijiOptions.PoijiOptionsBuilder Structure

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)

Documentation

Here are the list of features with examples that the latest version of Poiji supports.

Annotations

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;

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

    @ExcelCell(5)
    List<BigDecimal> bills;

    //no need getters/setters to map excel cells to fields
}

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

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

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

ID	NAME	SURNAME	AGE	SINGLE	BILLS	EMAILS
123923	Joe	Doe	30	TRUE	123,10;99.99	[email protected];[email protected]
123123	Sophie	Derue	20	TRUE	1022	[email protected];[email protected]
135923	Paul	Raul	31	FALSE	73,25;70	[email protected];[email protected]

The snippet below shows how to obtain the excel data using Poiji.

PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings()
        .addListDelimiter(";") (1)
        .build();
List<Employee> employees = Poiji.fromExcel(new File("employees.xls"), Employee.class, options);
// 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, emails=[[email protected], [email protected]], biils=[123,10, 99.99]}

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 use PoijiOptions.

PoijiOptions options = PoijiOptionsBuilder.settings(1).build(); // we eliminate Joe Doe.
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, emails=[[email protected], [email protected]], biils=[1022]}

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();

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 return null rather than a default date, use PoijiOptionsBuilder with the preferNullOverDefault method as follows:

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.

Sheet Name Option

Poiji allows specifying the sheet name using annotation

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

Protected Excels

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

Annotation ExcelCellName

Using ExcelCellName, 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;

    @ExcelCellName(value = "", expression = "Surname|Second name")  (2)
    private String surname;

}

1. We need to specify the name 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 using PoijiOptionsBuilder#caseInsensitive(boolean) and you can ignore white spaces using PoijiOptionsBuilder#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 the expression leaving the column name empty.

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

Name	Address	Age	Email	Surname
Joe	San Francisco, CA	30	[email protected]	Doe
Sophie	Costa Mesa, CA	20	[email protected]	Doe

List<Person> people = Poiji.fromExcel(new File("person.xls"), Person.class);
people.size();
// 2
Person person = people.get(0);
// Joe
// San Francisco, CA
// 30
// [email protected]
// Doe

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;

}

Super Class Inheritance

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

Annotation ExcelCellsJoinedByName

Using ExcelCellsJoinedByName 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.

public class Album {

    @ExcelCellsJoinedByName(expression = "Artist")  (1)
    private MultiValuedMap<String, String> artists = new ArrayListValuedHashMap<>();

    @ExcelCellsJoinedByName(expression = "Track[0-9]+")  (2)
    private MultiValuedMap<String, String> tracks = new ArrayListValuedHashMap<>();

}

1. Here we map multiple columns with name Artist.

2. Here we map multiple columns with name Track1, Track2, Track3, etc.

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

Artist	Artist	Artist	Track1	Track2
Michael Jackson	Lionel Richie	Stevie Wonder	We are the World	We are the World (instrumental)
artist 1	artist 1	artist 1	track 1	track 1

List<Album> albums = Poiji.fromExcel(new File("album.xls"), Album.class);
albums.size();
// 2
Album album1 = albums.get(0);
// artists = { Artist = [Michael Jackson, Lionel Richie, Stevie Wonder] }
// tracks = { Track1 = [We are the World], Track2 = [We are the World (instrumental)] }
Album album2 = albums.get(1);
// artists = {Artist = [artist 1, artist 1, artist 1] }
// tracks = {Track2 = [track 1], Track1=[track 1] }

Json presentation for album1 will be as follows

{
  "artists": {
    "Artist": [
      "Michael Jackson",
      "Lionel Richie",
      "Stevie Wonder"
    ]
  },
  "tracks": {
    "Track1": [
      "We are the World"
    ],
    "Track2": [
      "We are the World (instrumental)"
    ]
  }
}

ExcelCellRange Annotation

Consider you have a table like below:

No.	Personal Information					Credit Card Information
	Name	Age	City	State	Zip Code	Card Type	Last 4 Digits	Expiration Date
1	John Doe	21	Vienna	Virginia	22349	VISA	1234	Jan-21
2	Jane Doe	28	Greenbelt	Maryland	20993	MasterCard	2345	Jun-22
3	Paul Ryan	19	Alexandria	Virginia	22312	JCB	4567	Oct-24

The ExcelCellRange annotation lets us aggregate a range of information in one object model. In this case, we collect the data in PersonCreditInfo plus details of the person in PersonInfo and for the credit card in CardInfo:

public class PersonCreditInfo {

    @ExcelCellName("No.")
    private Integer no;

    @ExcelCellRange
    private PersonInfo personInfo;

    @ExcelCellRange
    private CardInfo cardInfo;

    public static class PersonInfo {
        @ExcelCellName("Name")
        private String name;
        @ExcelCellName("Age")
        private Integer age;
        @ExcelCellName("City")
        private String city;
        @ExcelCellName("State")
        private String state;
        @ExcelCellName("Zip Code")
        private String zipCode;
    }

    public static class CardInfo {
        @ExcelCellName("Card Type")
        private String type;
        @ExcelCellName("Last 4 Digits")
        private String last4Digits;
        @ExcelCellName("Expiration Date")
        private String expirationDate;
    }
}

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

PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings().headerCount(2).build();
List<PersonCreditInfo> actualPersonalCredits = Poiji.fromExcel(new File(path), PersonCreditInfo.class, options);

PersonCreditInfo personCreditInfo1 = actualPersonalCredits.get(0);
PersonCreditInfo.PersonInfo expectedPerson1 = personCreditInfo1.getPersonInfo();
PersonCreditInfo.CardInfo expectedCard1 = personCreditInfo1.getCardInfo();

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:

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().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);
}

Custom Casting Implementation

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

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:

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

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

Parse UnknownCells

You can annotate a Map<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:

public class MusicTrack {

    @ExcelCellName("ID")
    private String employeeId;

    @ExcelCellName("AUTHOR")
    private String author;

    @ExcelCellName("NAME")
    private String name;

    @ExcelUnknownCells
    private Map<String, String> 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}.

Note that If you use the PoijiOptionsBuilder#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 provide mandatoryHeader and mandatoryCell 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, while mandatoryCell looks into the values of the mentioned header/column. If any of the options is active and your excel breaks the rule, either a HeaderMissingException or PoijiMultiRowException will be thrown accordingly.

// default behavior
@ExcelCellName(value = "COLUMN_NAME", mandatoryHeader = false, mandatoryCell = false)
String fieldName;

// default behavior
@ExcelCell(value = COLUMN_INDEX, mandatoryHeader = false, mandatoryCell = false)
String fieldName;

Note	The mandatoryHeader field is compatible with XLS and XLSX files.

Note	The mandatoryCell field works only with XLS files and Sheet instances. XLS workbooks are opened with RETURN_BLANK_AS_NULL missing cell policy. If passing a Sheet instance it is up for the caller to make sure the missing cell policy of the parent workbook is set accordingly.

Debug Cells Formats

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<Model> 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 since we get the date value as (12.00). Let’s see how internally the excel file is being parsed via PoijiLogCellFormat:

List<InternalCellFormat> 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.

Modify Cells Formats

We can change the default format of a cell using PoijiNumberFormat. Recall Debug 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.

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

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:

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.

Disable Cells Formats

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<TestInfo> result = Poiji.fromExcel(new File(path), TestInfo.class, options); (1)

result.get(1).amount
// -50

1. Add options, 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 the Formatting interface.

public class MyFormatting implements Formatting {
    @Override
    public String transform(PoijiOptions options, String value) {
        return value.toUpperCase().trim(); (1)
    }
}

public class Person {

    @ExcelCellName("ID")
    protected String employeeId;

    @ExcelCellName("NAME")
    protected String name;

    @ExcelCellName("SURNAME")
    protected String surname;

}

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 the withFormatting method:

PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings()
                .withFormatting(new MyFormatting())
                .build();
List<Person> people = Poiji.fromExcel(excel, Person.class, options);

Poi Sheet Support

Poiji accepts excel records via Poi Sheet object as well:

File file = new File("/tmp/file.xlsx");
FileInputStream fileInputStream = new FileInputStream(file);
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);

List<Model> result = Poiji.fromExcel(sheet, Model.class);

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 that you 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. See JEP-252 for more details.

PoijiOptions options = PoijiOptions.PoijiOptionsBuilder.settings()
                .setLocale(Locale.getDefault())
                .build();

License

poiji's Issues

Skip hidden sheets

Is it currently possible and I just cant see how, or would it be possible to introduce a settings option to ignore all hidden Excel sheets.
Could not figure out why a excel book I was given would not work and eventually figured out that there where an 4 additional sheets hidden before the one i was viewing and trying to import

Thanks

Blank numeric cells are zero rather than null

Hello!
I'm currently working on a project where we are using your library to parse an xls into an object and then doing some validation against that object.

We are running into an issue where when a numeric cell is blank, after parsing using Poiji, when we check the value of that field it is 0 -- this makes it hard for us to determine if the field actually contained zero or was blank/empty. We need to be able to treat zeros and empty fields differently.

We're wondering why Poiji sets a blank numeric field as zero rather than something like null that would indicate a missing value?

Thanks!

Parsing of Dates

Could we also add support for locales? I realized with the dates, it seems to work when I use the US format and it is precisely like this : "MM/dd/yy"
If I specify "MM/dd/yyyy" it will not work, if I use the UK format it will not work

Duplicate entry error

Hi, I added your library to my gradle and when I try to build my project it threw this error:

Error:Execution failed for task ':app:transformClassesWithJarMergingForDebug'.
com.android.build.api.transform.TransformException: java.util.zip.ZipException: duplicate entry: org/apache/xmlbeans/xml/stream/Location.class

I found it here that this issue is related to POI having same class names with javax or something like that.

Your last commit is pretty new though. I guess it's my mistake but I cannot find a solution, and I didn't see it mentioned anywhere in this repository.

Provider com.bea.xml.stream.EventFactory not found

Hello
I'm successfully using poiji to read XLS files in my Android app, however after I changed PoijiExcelType.XLS to PoijiExcelType.XLSX in my Poiji.fromExcel() call my app crashes with the following stacktrace

FATAL EXCEPTION: main Process: com.haistudios.taxman, PID: 7928 javax.xml.stream.FactoryConfigurationError: Provider com.bea.xml.stream.EventFactory not found at javax.xml.stream.FactoryFinder.newInstance(FactoryFinder.java:72) at javax.xml.stream.FactoryFinder.find(FactoryFinder.java:178) at javax.xml.stream.FactoryFinder.find(FactoryFinder.java:92) at javax.xml.stream.XMLEventFactory.newInstance(XMLEventFactory.java:30) at org.apache.poi.openxml4j.opc.internal.marshallers.PackagePropertiesMarshaller.<clinit>(PackagePropertiesMarshaller.java:41) at org.apache.poi.openxml4j.opc.OPCPackage.init(OPCPackage.java:161) at org.apache.poi.openxml4j.opc.OPCPackage.<init>(OPCPackage.java:141) at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:97) at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:324) at com.poiji.bind.mapping.XSSFUnmarshallerStream.unmarshal(XSSFUnmarshallerStream.java:28) at com.poiji.bind.Poiji.fromExcel(Poiji.java:128)

I have tried looking around but I only find very old stuff regarding Eclipse or some Proguard settings that supposedly should help, but I have tried the workarounds and they don't really do anything.
Am I supposed to do something in particular to work with XLSX files on Android?

Mechanism required to input file streams instead of files.

In the current mechanism. If the file is in memory as a byte stream there is no way around committing it to a file system. It'll be great if we can have a constructor that takes file stream as an input.

CastingTest incorrect

It appears that in the CastingTest.java, the "castValue" is passed Object types, instead of primitive types. This causes the "castValue" to not even hit the conditions for anything other than "java.util.Date". An easy work around would be to change to the primitive implementation, however, the tests will need to be re-written.

When any two rows has same data it only adds one java object in list of POJO

You can replicate it by reading a file which has 2 duplicate rows.

Problem with POI version java.lang.NoSuchFieldError: CREATE_NULL_AS_BLANK, xlsx files not working

I'm guetting this error when I try to import a xls file :

java.lang.NoSuchFieldError: CREATE_NULL_AS_BLANK
at com.poiji.bind.mapping.HSSFUnmarshaller.isRowEmpty(HSSFUnmarshaller.java:113)
at com.poiji.bind.mapping.HSSFUnmarshaller.unmarshal(HSSFUnmarshaller.java:52)
at com.poiji.bind.Poiji.fromExcel(Poiji.java:89)
..

My pom only imports poi only via poiji and when I go the the actual code, it seems to be okay....

private boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

Xlsx files give another error

java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:763)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:467)
at java.net.URLClassLoader.access$100(URLClassLoader.java:73)
at java.net.URLClassLoader$1.run(URLClassLoader.java:368)
at java.net.URLClassLoader$1.run(URLClassLoader.java:362)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:361)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:763)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:467)
at java.net.URLClassLoader.access$100(URLClassLoader.java:73)
at java.net.URLClassLoader$1.run(URLClassLoader.java:368)
at java.net.URLClassLoader$1.run(URLClassLoader.java:362)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:361)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:295)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:201)
at com.poiji.bind.mapping.XSSFUnmarshaller.unmarshal(XSSFUnmarshaller.java:43)
at com.poiji.bind.Poiji.fromExcel(Poiji.java:89)
at be.fgov.sigedis.db2p.support.core.utils.ExcelToPOJOTest.importExcelFile(ExcelToPOJOTest.java:20)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.lang.ClassNotFoundException: org.apache.poi.UnsupportedFileFormatException
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
... 52 more

It seems to be importing poi 3.10.1 (which differs with poi-xml 3.17)...

Get cell value, not the formula.

Can i get the cell value instead of the full formula cell as a String?

java.lang.NoSuchFieldError: CREATE_NULL_AS_BLANK

The excel is test.xlsx:
|campusid | campusname | campusnumber | campusarea | campusaddress
|ca111111 | campus1| 301 | 5000 | address1
|ca111112 | campus2 | 302 | 4000 | address2
|ca111113 | campus3 | 303 | 3000 | address3
|ca111114 | campus4 | 304 | 2000 | address4
|ca111115 | campus5 | 305 | 500 | address5
|ca111116 | campus6 | 306 | 1000 | address6
And the pojo code is also right.
@ExcelCell(0)
private String campusid;
@ExcelCell(1)
private String campusname;
@ExcelCell(2)
private Integer campusnumber;
@ExcelCell(3)
private Float campusarea;
@ExcelCell(4)
private String campusaddress;

I check it for many times but can find the reason.

Can I map only the first 10 rows?

I need to read only the first 10 rows from an excel of 1 million rows and this needs to be done fast. Can I map only the first 10 rows in a streaming fashion?

Unable to resolve dependency

when i add implementation 'com.github.ozlerhakan:poiji:1.18.0' to my build.gradle file and sync ,
i get this error:

Unable to resolve dependency for ':app@debug/compileClasspath': Could not resolve com.github.ozlerhakan:poiji:1.18.0.

Feature Request: Allow for mandatory ExcelCellName fields and throw exception if not present in the input Excel file

I recently used poiji for extracting the content from an excel file, but I found myself wanting it to do one extra thing. :)

When creating your object model using @ExcelCellName on fields, can we also have an annotation for fields to be required/mandatory. Then, when deserializing an excel file if the mandatory fields are not present in the Excel file being read, throw a data validation exception.

Thanks,

Jamie

Poiji.fromExcel mutates input file

Poiji's method fromExcel(final File file, final Class type) mutates the input file.
When I set the input file to be read only, this yields a PoijiException wrapping FileNotFoundException (permission denied)

Caused by: java.io.FileNotFoundException: <my-file-path> (Permission denied)
	at java.io.FileOutputStream.open0(Native Method)
	at java.io.FileOutputStream.open(FileOutputStream.java:270)
	at java.io.FileOutputStream.<init>(FileOutputStream.java:213)
	at java.io.FileOutputStream.<init>(FileOutputStream.java:162)
	at org.apache.poi.openxml4j.opc.internal.FileHelper.copyFile(FileHelper.java:68)
	at org.apache.poi.openxml4j.opc.ZipPackage.closeImpl(ZipPackage.java:456)
	at org.apache.poi.openxml4j.opc.OPCPackage.close(OPCPackage.java:479)
	at com.poiji.bind.mapping.XSSFUnmarshallerFile.returnFromExcelFile(XSSFUnmarshallerFile.java:43)
	... 27 more

How can we read by "ExcelCellName"

Hi,

First, thank you for your work. This part of the tutorial doesn't show how, it uses index as well. Any example would be helpful! Thanks!

Date pattern Regex

Hey, We are having a lot of excel documents being imported with strange date formats, while we can set the date format in the setting options, if the excel document cell value does not match this exact format we sometimes get a strange Date object created, no parse exception is thrown so we don't get null value, we get a strange date. Would rather have null date then an incorrect date.

Would it be possible to have the ability to specify a regex pattern (yyyy-MM-dd = \d{4}-\d{2}-\d{2}) in setting options so that the date string must match the pattern before it is parsed to a date object. This way if the document has a date in strange format it can be set to null (if null preferred, or current date).

Do you have any development like this in the pipe line or I can code it up.

Thanks

Date format

Please, help me out.

Am I using PoijiOptions correctly?

Excel

Name	Birthday
Oliver	1982-02-05

Entity

StaffBaseInfo {

   @ExcelCellName("Name") private String name;

   @ExcelCellName("Birthday") private java.sql.Date birthday;

}

Setting date pattern

PoijiOptions options = PoijiOptionsBuilder.settings().datePattern("yyyy-MM-dd").build();
List<StaffBaseInfo> baseInfoList = Poiji.fromExcel(file, StaffBaseInfo.class, options);

Exception

java.lang.IllegalArgumentException: Can not set java.sql.Date field com.xxx.hr.StaffBaseInfo.birthday to java.lang.String

Accessing data from different SpreadSheet

Hi,
How to access the data from different sheets in same excel ?

Is there a way to evaluate formulas using this library?

Exception in thread "main" java.lang.NoSuchMethodError

How can I fix this error because I dont know the reason of this error?

I am using the same example, thanks!

Exception in thread "main" java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellType()Lorg/apache/poi/ss/usermodel/CellType;
at com.poiji.bind.mapping.HSSFUnmarshaller.isRowEmpty(HSSFUnmarshaller.java:160)
at com.poiji.bind.mapping.HSSFUnmarshaller.unmarshal(HSSFUnmarshaller.java:56)
at com.poiji.bind.Poiji.fromExcel(Poiji.java:87)
at com.poiji.bind.Poiji.fromExcel(Poiji.java:66)

Cannot read doubles, int, dates from xls file

The library seems fine but it can only read values into a bean field which is a string

Dex cannot parse version 52 byte code.

Log:
Error:Error converting bytecode to dex:
Cause: Dex cannot parse version 52 byte code.
This is caused by library dependencies that have been compiled using Java 8 or above.
If you are using the 'java' gradle plugin in a library submodule add
targetCompatibility = '1.7'
sourceCompatibility = '1.7'
to that submodule's build.gradle file.

Column handling possibilities

Hi Hakan,

1. question: I have an excel sheet, but in some cases, I need to avoid not only the first row, I need to skip the first 3 row. PoijiOptions is ok with it? How can use it?

2. question: what if I have 5 column like: id|name|gender|age|address and I need to parse in one case only the age and address? How can I do that with cell position number? Or in this case, I need to use ExcelCellName? I didn't find the example for it.

My idea would be: I have a person xlsx, so if I use excelCelName and not position, I can use custom getters like, Person person = person.getId("id"), person.getName("Smith"), person.getGender("male")? Do I need to implement the custom getters? It would be easier to use getId simply.

3. Question: Can I use List and the cell contains several items with comma? I really need to have the ability to fetch everything from the cell and if I have 3 integer in the cell divided by , it should be handled as a list.

@ExcelName("whatever")
List whateverList;

Many thanks and keep up the good work!

Adding support for java.time.LocalDate and Enums

Hello

Love this package !

I'd love it even more if it could support java.time.LocalDate and Enums !

Do you think adding this could be of interest ? I'd be willing to do it.

Update README with version of apache poi required to run poiji

I was using apache poi 3.14 and poiji fails to give POJO's in case file is xls. I then have to come and look into your pom to understand Apache poi version supported. It's better to mention it in README file.

Trying to upload a xls file and create POJO:

Caused by: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellTypeEnum()Lorg/apache/poi/ss/usermodel/CellType;
at com.poiji.bind.mapping.HSSFUnmarshaller.isRowEmpty(HSSFUnmarshaller.java:156)
at com.poiji.bind.mapping.HSSFUnmarshaller.unmarshal(HSSFUnmarshaller.java:61)
at com.poiji.bind.Poiji.fromExcel(Poiji.java:64)

I need to get header information of excel sheet

Do we have mechanism to extract header info of an excel sheet.

Import unformatted cells

Hello,

Is it possible to add an Option to get the unformatted cells values ?

Would be perfect because some users add their own format and I can't parse an unknown format into a Double for example.

Thanks

Excel date import format error

Hi @ozlerhakan

I think I have found a little bug for the Excel Type excel.
If the column in the excel is type "Excel Date" and not "String" the tool import not correctly the date.

For example try to import the following excel (sheet1):
https://www.esma.europa.eu/sites/default/files/template_-_si_calculations.xlsx

for the first row I have:
If you use in the Pojo:
Column(0) String -> 1/1/18
or
Column(0) java.util.date -> 1 jan 0018

Maybe I tried in not correct way.

Thanks
Fabrizio

Returning null value for a String type using 1.5

Backthen when it's 1.4 it will return empty in case just string alone
After new update it's return null if the cell is empty
I mean when the column present is empty it will return me null value although it's type String
Thank you

Set row number in converted Excel POJO's

When we convert an Excel to it's corresponding POJO, I need provision to know the row number of Excel sheet the converted POJO belongs to. It will be a very useful feature when we will have validation failures in POJO and we want to track it back to row number in Excel sheet.

Converting Files and Casting to Singletons

Hi? Is it okay, if I do a PR with Files and Casting as singletons. This would enable me to test the 2 classes and maybe help raise the code coverage. Casting seems like a class that would really benefit if we could do something like the file attached.

Update:
The PR seems to have, (not expected) passed all tests, despite the conflict in HSSFUnmarshaller and PoijiHandler.

CastingTest.txt
FilesTest.txt

Parsing of dates not working

When i'm trying to parse date it turn out this format
I don't know why its not working with cell date types in my case
instead of dd/M/yyyy it should be
i tried to use cell.getDateCellValue() it will turn out the year will not missing



here is the sample file
Sample.zip

Not working on Android

Any time i try to write this library on android it always gives this error and the app crashes
java.lang.NoClassDefFoundError: Failed resolution of: Ljava/time/format/DateTimeFormatter;

Can we read encrypted excels using poiji?

Can we read encrypted excels using poiji?

We have excels which are password protected and I need to read them.

Skip useless lines

Hello,

I had some issues to deserialize beans using ExcelCellName.

My Excel file was the following:

In fact, it would be nice to have a similar attribute in loadColumnTitles in order to skip useless lines.

Thank you,

Philippe

Skip Blank rows between header and data

DummyData.xlsx
While parsing the excel, if there are blank rows between header and actual data, I need the ability to skip those rows, currently it fails with NullPointerException while trying to parse rows:

java.lang.NullPointerException: null
	at com.poiji.bind.mapping.PoijiHandler.endRow(PoijiHandler.java:103) ~[poiji-1.8.jar:na]
	at org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.endElement(XSSFSheetXMLHandler.java:389) ~[poi-ooxml-3.14.jar:3.14]
	at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.endElement(AbstractSAXParser.java:609) ~[na:1.8.0_45]

Can we read till a particular row only

Suppose I have 40 rows, and I want to read from row 1 only till row 10 , is it possible to do so?

Adding parse exception for java.time.LocalDate

Hello,
i would love it if it could have some empty check before parse a localdate or exception to return null 👍
since you have added it recently
Thanks you xD

Read empty rows

I have a xlsx file which contains only 100 filled rows. (2 columns). This file was created from a google spread sheet. When I try to convert those rows into an object using Poiji, it converts an empty rows as well.
By default sheet has 1000 rows. Out of those 1000 rows only 100 rows contains data. (in 2 columns). But after converting to objects I get 1000 objects.
Is there anyway that it can stop reading the rows once it found an empty row?

Add Required attribute on annotation ExcelCell and ExcelCellName

Hello,

It would be nice to have Required attribute on annotation.

Thank you,

Philippe

Not able to read by Column name if the header starts from row x

Not able to read by Column name if the header starts from row x

I have to start reading the rows from row 6, so I have specified my headers at row 5, but using version 1.11, I am not able to read such scenario by column name.

@ExcelCellName("Currency")
private String currency;

@ExcelCellName("No")
private String no;

Config Class:
@bean(name = "poijiOptionsSMBQ")
public PoijiOptions genericOptions() {
return settings(6).sheetIndex(0).build();
}

Mechanism is required to get column values by header names

Mechanism is required to get column values by header names.

For Example:
Expected header format is:
id name
Actual:
name id

We don't have mechanism to specify it is invalid format, we can only read by annotation as:
@ExcelCell(0)
int id;

@ExcelCell(1)
String name;

So if we have an excel in format:
name id

I think we should also have mechanism to read by header name:

@ExcelCellName("id")
int id;

@ExcelCellName("name")
String name;

Using poiji in android

I'm trying to use it in android. As Android doesn't allow duplicate classes withing jar files, I'm getting an error "duplicate entry: org/apache/xmlbeans/xml/stream/Location.class"
I know its not the problem in your project but in Apache POI.
I think its the same issue as this

So I've searched the issue got custom Apache POI jar file with no duplications but I don't know how to use it with this project.

I really need this project working.

Skip every second row support

Hi Hakan,

can we configure poiji in order to skip every second row during the parsing?

headerRow: using poiji options, skip it (It is ok)
valid dataRow: parse
some additional info in a new row: Do not parse it.
valid dataRow: Parse
additionalInfoRow: do not parse it
etc.

Thanks!

Coverage increase / decrease incorrect for pull request #14

I don't understand how those minor changes might have caused the coverage to decrease. If anything i removed a line of code somewhere. Anyway I will see if I could test cases covering the uncovered exceptions

Can we map a list field from a column?

I have:
public class ProductDTO {

String id;
String name;
List colors;
...
}
how can i map this object?

Thanks

Add bigDecimal to castValue

Would it be possible to support BigDecimal in Casting.java? I have made a suggestion for the solution here:
else if (fieldType.getName().equals("java.math.BigDecimal")) { o = bigDecimalValue(value, options); }
private BigDecimal bigDecimalValue(String value, PoijiOptions options) { try { String clean = value != null ? value.replace(",", ".") : ""; return new BigDecimal(clean); } catch (NumberFormatException nfe) { if (Boolean.TRUE.equals(options.preferNullOverDefault())) { return null; } return BigDecimal.ZERO; } }

Another question; any plan for merging #49 and making a new release? I made the changes locally myself and i can make a more update to date PR if wanted.

Object contains list of objects inside

For example if I want to read excel where I have objects inside one object
Like
Inside Employee
I have Address object with different fields
or for example if I have two address for one employee
In this case how we can handle this?
My model looks like this
Employee
{
List

addressList;
}

Address
{
String pincode;
}

Can you pls help how todo using poiji

getting cast exception in case if fields are wrapper calsses

@ozlerhakan hi is there a way to use wrapper classes for fields lets say Long or Double. I was trying with Long field and getting cast exception, when I changed field type to primitive it worked properly. Is there a way I can use Wrapper classes of primitive types