TOP RSS LOGIN

Community

Product

Contact

Please send mail to takezoe@aa.bb-east.ne.jp.

SourceForge.jp

XLSBeans

Moved to GitHub: https://github.com/takezoe/xlsbeans

XLSBeans is a library for mapping Excel sheets to POJOs.

XLSBeans provides an advanced and abstract way to read Excel instead of low-level API such as POI and Java Excel API. You can map Excel sheets, cells and tables to JavaBean properties using annotations easily.

License

Apache License, Version 2.0.

Download

If you want to use XLSBeans with Maven, add the following dependency to your pom.xml.

<repositories>
  <repository>
    <id>amateras</id>
    <name>Project Amateras Maven2 Repository</name>
    <url>http://amateras.sourceforge.jp/mvn/</url>
  </repository>
</repositories>

<dependencies>
  <dependency>
    <groupId>jp.sf.amateras.xlsbeans</groupId>
    <artifactId>xlsbeans</artifactId>
    <version>1.2.1</version>
  </dependency>
</dependencies>

How to use

 Mapping by annotation

For example, here is one Excel sheet.

Map this Excel sheet to POJO using @HRecord and @LabelledCell.

@Sheet(name="Users")
public class UserList {
  
  @LabelledCell(label="Title", type=LabelledCellType.Right)
  public String title;

  @HorizontalRecords(tableLabel="User list", recordClass=User.class)
  public List<User> users;

}

And the following is the record class. Properties of the record class is mapped to columns by @Column.

public class User {
  
  @Column(columnName="ID")
  public int id;
  
  @Column(columnName="Name")
  public String name;
  
  @Column(columnName="Gender", merged=true)
  public String gender;
  
}

You can get the mapped POJO using XLSBeans#load() like following:

UserList userList = (UserList)XLSBeans.load(
  new FileInputStream("example.xls"), UserList.class);

 Mapping by external XML files

You can map the Excel sheet and POJO by the XML file, not only annotations.

<?xml version="1.0" encoding="utf-8"?>
<annotations>
  <class name="net.java.amateras.xlsbeans.example.SheetObject">
    <annotation name="net.java.amateras.xlsbeans.annotation.Sheet">
      <attribute name="name">'Users'</attribute>
    </annotation>
  </class>
</annotations>

Of course, you can declare annotations for methods and fields. Annotations which declared by the XML file overrides hard-coded annotations. To use XML annotations, you must specify InputStream of the Excel file and the XML file as arguments of XLSBeans#load().

SheetObject sheet = (SheetObject)XLSBeans.load(
  new FileInputStream("example.xls"), 
  new FileInputStream("example.xml"), SheetObject.class);

In addition, you can use this XML annotations in other applications, libraries or frameworks using AnnotationReader.

// Load from the XML file
XMLInfo info = XMLLoader.load(new FileInputStream("example.xml"));
// Create AnnotationReader instance
AnnotationReader reader = new AnnotationReader(info);
// Get @Sheet from SheetObject
Sheet sheet = reader.getAnnotation(SheetObject.class, Sheet.class);

AnnotationReader has more methods which get annotations from methods and fields.

History

 2011/07/05

  • Fixed an error for loading expression cell using POI.

 2010/07/03

  • Supports Apache POI / Excel2007.
  • Fixed charset problem in JavaExcel API.

 2010/03/14 - 1.1.0

  • Supports public fields.
  • When it's not specified ClassLoader to DynamicAnnotationBuilder, it uses the context ClassLoader.

 2008/12/23 - 1.0.7

  • You can specify ClassLoader which is used by OGNL to DynamicAnnotationBuilder.
  • Added skip attribute to @LabelledCell.
  • Added optional attribute to @IterateTables.

 2008/11/24 - 1.0.6

  • Performance tuning of @HorizontalRecords and @VerticalRecords processing.
  • Overriding annotation by XML uses proxy instead of Javassist.
  • Added headerLimit attribute to @HorizontalRecords and @VerticalRecords.

 2008/08/02 - 1.0.5

  • setXxxxPosition() method to set a cell position
  • Add @Documented to all annotations

 2008/06/07 - 1.0.4

  • Bug Fix

 2007/07/04 - 1.0.3

  • @IterateTable
  • @Column throws Exception if the target column is not found
  • Add some parameters to annotations

 2007/02/11 - 1.0.2

  • Initial Public Release

 2006/05/18 - 1.0 beta1

  • The first technical preview release.
  • Note: This is still buggy and contains only Japanese documentation.