Coder Social home page Coder Social logo

poiji's Introduction

Poiji

Build Status Codacy code quality Coverage Status Gitter license MIT blue

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.

How it works

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

maven
<dependency>
  <groupId>com.github.ozlerhakan</groupId>
  <artifactId>poiji</artifactId>
  <version>1.11</version>
</dependency>
gradle
dependencies {
    compile 'com.github.ozlerhakan:poiji:1.11'
}

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

Example 1

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 + '\'' +
                '}';
    }
}
  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, java.util.Date or String.

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

PoijiOptions options = PoijiOptionsBuilder.settings()
                       .preferNullOverDefault(true) (1)
                       .build();
  1. Annotated date fields will have a null value, if the specified date pattern cannot be parsed.

Example 2

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;

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

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]

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;

}

Example 3

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

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

License

MIT

poiji's People

Contributors

ozlerhakan avatar abdulrafique avatar ghacupha avatar

Watchers

Matt Jackson avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.