Create a library ** xlbean ** to handle Excel table data intuitively and easily on a Java program. Published as open source.

Personally, I do the work that I often see in system development I think it has become a tool that can support it reasonably well.

If you don't mind, please use it I hope that system development in the world will be easier.

In a word of features

** "Define the data structure of the read destination in the Excel file itself" **. First of all, please see the usage image.

Excel reading

You can get Excel table data in List format in the following two steps.

STEP 1. Definition of Excel file

Define the sheet as shown below. (Download the actual Excel file here.)

TODO

Specify the column name of the table in the format of ** "table name # column name" **
** Specify the read start row in the format of "table name # ~ (tilde)" **
Specify the sheet to write ** "####" ** to A1

STEP 2. Java code creation

`Sample code`


InputStream in = new FileInputStream("example/presidents.xlsx");
XlBeanReader reader = new XlBeanReader();
XlBean bean = reader.read(in);

List<XlBean> list = bean.beans("presidents");
list.forEach(System.out::println);

Execution result

When you run it, it looks like this!

`Execution result`


{name=John F. Kennedy, inOfficeTo=1963-11-22T00:00:00.000, numberOfDaysInOffice=1036.0, dateOfBirth=1917-05-29T00:00:00.000, inOfficeFrom=1961-01-20T00:00:00.000, stateOfBirth=Massachusetts}
{name=Lyndon B. Johnson, inOfficeTo=1969-01-20T00:00:00.000, numberOfDaysInOffice=1886.0, dateOfBirth=1908-08-27T00:00:00.000, inOfficeFrom=1963-11-22T00:00:00.000, stateOfBirth=Texas}
{name=Richard M. Nixon, inOfficeTo=1974-08-09T00:00:00.000, numberOfDaysInOffice=2027.0, dateOfBirth=1913-01-09T00:00:00.000, inOfficeFrom=1969-01-20T00:00:00.000, stateOfBirth=California}
{name=Gerald R. Ford, inOfficeTo=1977-01-20T00:00:00.000, numberOfDaysInOffice=895.0, dateOfBirth=1913-07-14T00:00:00.000, inOfficeFrom=1974-08-09T00:00:00.000, stateOfBirth=Nebraska}
{name=Jimmy Carter, inOfficeTo=1981-01-20T00:00:00.000, numberOfDaysInOffice=1461.0, dateOfBirth=1924-10-01T00:00:00.000, inOfficeFrom=1977-01-20T00:00:00.000, stateOfBirth=Georgia}
{name=Ronald Reagan, inOfficeTo=1989-01-20T00:00:00.000, numberOfDaysInOffice=2922.0, dateOfBirth=1911-02-06T00:00:00.000, inOfficeFrom=1981-01-20T00:00:00.000, stateOfBirth=Illinois}
{name=George H. W. Bush, inOfficeTo=1993-01-20T00:00:00.000, numberOfDaysInOffice=1461.0, dateOfBirth=1924-06-12T00:00:00.000, inOfficeFrom=1989-01-20T00:00:00.000, stateOfBirth=Massachusetts}
{name=Bill Clinton, inOfficeTo=2001-01-20T00:00:00.000, numberOfDaysInOffice=2922.0, dateOfBirth=1946-08-19T00:00:00.000, inOfficeFrom=1993-01-20T00:00:00.000, stateOfBirth=Arkansas}
{name=George W. Bush, inOfficeTo=2009-01-20T00:00:00.000, numberOfDaysInOffice=2922.0, dateOfBirth=1946-07-06T00:00:00.000, inOfficeFrom=2001-01-20T00:00:00.000, stateOfBirth=Connecticut}
{name=Barack Obama, inOfficeTo=2017-01-20T00:00:00.000, numberOfDaysInOffice=2922.0, dateOfBirth=1961-08-04T00:00:00.000, inOfficeFrom=2009-01-20T00:00:00.000, stateOfBirth=Hawaii}
{name=Donald Trump, dateOfBirth=1946-06-14T00:00:00.000, inOfficeFrom=2017-01-20T00:00:00.000, stateOfBirth=New York}

point

--Table data can be obtained as List with each column as an element of Bean --It also interprets the spreadsheet function (as you can see in the sample Excel file, the * # of days in office * columns are calculated by the spreadsheet function)

Introducing a little more

For those who are interested here, I would like to introduce some more features. It is possible to express various structures including nesting, not just table data.

Various data structures

Extend the Excel you mentioned earlier as follows.

The Java code looks like this.

InputStream in = new FileInputStream("example/presidents.xlsx");
XlBeanReader reader = new XlBeanReader();
XlBean bean = reader.read(in);

System.out.println(bean.get("name"));// United States of America
System.out.println(bean.bean("stats").get("totalArea"));// 9833520.0
System.out.println(bean.bean("stats").get("gdp"));// 18558000000000000

point

--Not only tables but single items can be defined --Nested beans are also OK

I want to use it conveniently after loading

For this Excel, define the following DTO class.

public class Country {
    private String name;
    private Stats stats;
    private List<President> presidents;
    // Getter/Setter/toString omitted
}

public class President {
    private String name;
    private LocalDate dateOfBirth;
    private String stateOfBirth;
    private Date inOfficeFrom;
    private LocalDateTime inOfficeTo;
    private int numberOfDaysInOffice;
    // Getter/Setter/toString omitted
}

public class Stats {
    private Long totalArea;
    private BigDecimal gdp;
    // Getter/Setter/toString omitted
}

At this time, you can write this code.

InputStream in = new FileInputStream("example/presidents.xlsx");
XlBeanReader reader = new XlBeanReader();
XlBean bean = reader.read(in);

//Single bean conversion
Stats stats = bean.beanOf("stats", Stats.class);
System.out.println(stats);
// Stats [totalArea=9833520, gdp=18558000000000000]

//List unit conversion
List<President> presidents = bean.listOf("presidents", President.class);
System.out.println(presidents);
// [President [name=John F. Kennedy, dateOfBirth=1917-05-29, stateOfBirth=Massachusetts, inOfficeFrom=Fri Jan 20 00:00:00 JST 1961, inOfficeTo=1963-11-22T00:00, numberOfDaysInOffice=1036], President [name=Lyndon B. Johnson, dateOfBirth=1908-08-27, stateOfBirth=Texas, inOfficeFrom=Fri Nov 22 00:00:00 JST 1963, inOfficeTo=1969-01-20T00:00, numberOfDaysInOffice=1886], President [name=Richard M. Nixon, dateOfBirth=1913-01-09, stateOfBirth=California, inOfficeFrom=Mon Jan 20 00:00:00 JST 1969, inOfficeTo=1974-08-09T00:00, numberOfDaysInOffice=2027], President [name=Gerald R. Ford, dateOfBirth=1913-07-14, stateOfBirth=Nebraska, inOfficeFrom=Fri Aug 09 00:00:00 JST 1974, inOfficeTo=1977-01-20T00:00, numberOfDaysInOffice=895], President [name=Jimmy Carter, dateOfBirth=1924-10-01, stateOfBirth=Georgia, inOfficeFrom=Thu Jan 20 00:00:00 JST 1977, inOfficeTo=1981-01-20T00:00, numberOfDaysInOffice=1461], President [name=Ronald Reagan, dateOfBirth=1911-02-06, stateOfBirth=Illinois, inOfficeFrom=Tue Jan 20 00:00:00 JST 1981, inOfficeTo=1989-01-20T00:00, numberOfDaysInOffice=2922], President [name=George H. W. Bush, dateOfBirth=1924-06-12, stateOfBirth=Massachusetts, inOfficeFrom=Fri Jan 20 00:00:00 JST 1989, inOfficeTo=1993-01-20T00:00, numberOfDaysInOffice=1461], President [name=Bill Clinton, dateOfBirth=1946-08-19, stateOfBirth=Arkansas, inOfficeFrom=Wed Jan 20 00:00:00 JST 1993, inOfficeTo=2001-01-20T00:00, numberOfDaysInOffice=2922], President [name=George W. Bush, dateOfBirth=1946-07-06, stateOfBirth=Connecticut, inOfficeFrom=Sat Jan 20 00:00:00 JST 2001, inOfficeTo=2009-01-20T00:00, numberOfDaysInOffice=2922], President [name=Barack Obama, dateOfBirth=1961-08-04, stateOfBirth=Hawaii, inOfficeFrom=Tue Jan 20 00:00:00 JST 2009, inOfficeTo=2017-01-20T00:00, numberOfDaysInOffice=2922], President [name=Donald Trump, dateOfBirth=1946-06-14, stateOfBirth=New York, inOfficeFrom=Fri Jan 20 00:00:00 JST 2017, inOfficeTo=null, numberOfDaysInOffice=0]]

//Nested bean conversion
Country usa = bean.of(Country.class);
System.out.println(usa);
// Country [name=United States of America, stats=Stats [totalArea=9833520, gdp=18558000000000000], presidents=[President [name=John F. Kennedy, dateOfBirth=1917-05-29, stateOfBirth=Massachusetts, inOfficeFrom=Fri Jan 20 00:00:00 JST 1961, inOfficeTo=1963-11-22T00:00, numberOfDaysInOffice=1036], President [name=Lyndon B. Johnson, dateOfBirth=1908-08-27, stateOfBirth=Texas, inOfficeFrom=Fri Nov 22 00:00:00 JST 1963, inOfficeTo=1969-01-20T00:00, numberOfDaysInOffice=1886], President [name=Richard M. Nixon, dateOfBirth=1913-01-09, stateOfBirth=California, inOfficeFrom=Mon Jan 20 00:00:00 JST 1969, inOfficeTo=1974-08-09T00:00, numberOfDaysInOffice=2027], President [name=Gerald R. Ford, dateOfBirth=1913-07-14, stateOfBirth=Nebraska, inOfficeFrom=Fri Aug 09 00:00:00 JST 1974, inOfficeTo=1977-01-20T00:00, numberOfDaysInOffice=895], President [name=Jimmy Carter, dateOfBirth=1924-10-01, stateOfBirth=Georgia, inOfficeFrom=Thu Jan 20 00:00:00 JST 1977, inOfficeTo=1981-01-20T00:00, numberOfDaysInOffice=1461], President [name=Ronald Reagan, dateOfBirth=1911-02-06, stateOfBirth=Illinois, inOfficeFrom=Tue Jan 20 00:00:00 JST 1981, inOfficeTo=1989-01-20T00:00, numberOfDaysInOffice=2922], President [name=George H. W. Bush, dateOfBirth=1924-06-12, stateOfBirth=Massachusetts, inOfficeFrom=Fri Jan 20 00:00:00 JST 1989, inOfficeTo=1993-01-20T00:00, numberOfDaysInOffice=1461], President [name=Bill Clinton, dateOfBirth=1946-08-19, stateOfBirth=Arkansas, inOfficeFrom=Wed Jan 20 00:00:00 JST 1993, inOfficeTo=2001-01-20T00:00, numberOfDaysInOffice=2922], President [name=George W. Bush, dateOfBirth=1946-07-06, stateOfBirth=Connecticut, inOfficeFrom=Sat Jan 20 00:00:00 JST 2001, inOfficeTo=2009-01-20T00:00, numberOfDaysInOffice=2922], President [name=Barack Obama, dateOfBirth=1961-08-04, stateOfBirth=Hawaii, inOfficeFrom=Tue Jan 20 00:00:00 JST 2009, inOfficeTo=2017-01-20T00:00, numberOfDaysInOffice=2922], President [name=Donald Trump, dateOfBirth=1946-06-14, stateOfBirth=New York, inOfficeFrom=Fri Jan 20 00:00:00 JST 2017, inOfficeTo=null, numberOfDaysInOffice=0]]]

point

--Can be converted to a class with a similar nested structure

What are you happy about?

--You don't have to work hard to read each cell with Apache POI --There are no format restrictions other than using row 1 and column 1, and it is easy to use the table created in Excel for the time being. --Since the definition is written on the Excel sheet, it will not become unreadable even if you add rows or columns. ――If you find it difficult to use with just a map, you can quickly map it to a bean and use it.

document

There are many other functions as well. See the GitHub Wiki for more information.

Setting method

For now, I'll just leave a reference to Maven and Gradle.

Maven

<repositories>
    <repository>
        <id>xlbean</id>
        <url>http://jcenter.bintray.com</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>org.xlbean</groupId>
        <artifactId>xlbean</artifactId>
        <version>0.2.1</version>
    </dependency>
</dependencies>

Gradle

repositories {
     jcenter()
}

dependencies {
    compile group: 'org.xlbean', name: 'xlbean', version:'0.2.1'
}

at the end

In system development, there are many opportunities to fight against structured data, and I think Excel is the most convenient tool for expressing that structured data. ** xlbean ** is a tool that enables you to quickly incorporate ** structured data, which is often seen in such system development sites, into programs. ** **

I've been doing dog hooding as an creator, but I've noticed that there are many things that can be done because you can do it quickly. So, I would like to introduce various application examples of this tool in another article.

Library

For Maven

<repositories>
    <repository>
        <id>xlbean</id>
        <url>http://jcenter.bintray.com</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>org.xlbean</groupId>
        <artifactId>xlbean</artifactId>
        <version>0.3.0</version>
    </dependency>
</dependencies>

For Gradle

repositories {
     jcenter()
}
dependencies {
    compile group: 'org.xlbean', name: 'xlbean', version:'0.3.0'
}

Introducing the super-easy Excel read / write library xlbean

In a word of features

Excel reading

STEP 1. Definition of Excel file

STEP 2. Java code creation

Sample code

Execution result

Execution result

point

Introducing a little more

Various data structures

point

I want to use it conveniently after loading

point

What are you happy about?

document

Setting method

at the end

Library

`Sample code`

`Execution result`