excel[orm]

The idea behind the project is to keep access to an excel spreadsheet in a more human-readable format and provide advanced DSL syntax, instant access, reading complex data structures in easiest way. Built on top of Apache POI this tiny library will bring into your project flexibility and robustness what you will see on the examples below.

Maven dependency

At first, let's add the dependency

<dependency>
  <groupId>com.sombrainc</groupId>
  <artifactId>excelorm</artifactId>
  <version>2.0.1</version>
</dependency>

Quick overview

PROBLEM: How would you read just a single cell from a spreadsheet [A1] on the screenshot using Apache POI? Your code might look something like:

try {
   XSSFWorkbook sheets = new XSSFWorkbook(file);
   XSSFSheet sheet = sheets.getSheet(DEFAULT_SHEET);
   XSSFFormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

   String value = new DataFormatter().formatCellValue(sheet.getRow(0).getCell(0), formulaEvaluator);
} catch (IOException | InvalidFormatException e) {
   e.printStackTrace();
}

SOLUTION: In fact, it’s almost fine, except that's too many lines of code for reading just a single value. Here is how it can be done with a single line:

String value = new EReader(file, DEFAULT_SHEET).single(String.class).pick("A1").go();

Sweat, right? Hold on, that’s just a little part of what we can do with this library. Would you like to see how we can use annotation on POJO object in this example, here it is:

public class Foo {
   @Cell("A1")
   private String value;

   // getters / setters
}

Foo foo = Excelorm.read(file, DEFAULT_SHEET, Foo.class);

excel[orm] supports both approaches annotation processing and instant processing based on runtime configurations. Runtime processing has tools to work with a different data structure as well as the annotation approach and even more.

Class EReader has several constructors to suites multiple scenarios (you can find java docs written for all of them):

  // Load specific {@code sheetName} sheet from the excel doc by path {@code path}
  public EReader(String path, String sheetName)

  // Load first sheet from the excel doc by path {@code path}
  public EReader(String path)

  // Load first sheet from the excel doc {@code inputStream}
  public EReader(InputStream inputStream)

  // Load specific {@code sheetName} sheet from the excel doc {@code inputStream} 
  public EReader(InputStream inputStream, String sheetName)

  // Load first sheet from the excel doc {@code file}
  public EReader(File file)

  // Load specific {@code sheetName} sheet from the excel doc {@code file}
  public EReader(File file, String sheetName)

  // Set the sheet to process
  public EReader(Sheet sheet)

After instantiating EReader class you get access to a few generalized methods which can define the data structure what is required for you:

  // this method is responsible for creating an only single object which can be:
  // int, long, bigDecimal, enum type, double, float, string, boolean or user-defined object
  public <T> SinglePick<T> single(Class<T> aClass)

  // this method provides you a list of passing objects. It supports all the types which are supported by “@single()” method
  public <E> ListOfRange<E> listOf(Class<E> aClass)

  // by this method you will be able to map and create Map<K, V>
  public <K, V> MapOfRanges<K, V> mapOf(Class<K> key, Class<V> value)

  // this method almost the same as “@mapOf()” but with the difference that it provides a map of lists (Map<K, List<V>>)
  public <K, V> MapOfLists<K, V> mapOfList(Class<K> key, Class<V> value)

Each of the methods above has its own API to solve specifically related issues based on the chosen data structure. Let’s take a look at a few more examples:

Defining the value formatter at runtime

String name = new EReader(file, DEFAULT_SHEET).single(String.class).pick("E2")
      .map(field -> field.toText().split(" ")[0]).go();

OUTPUT: Bill

Converting to another type

int result = new EReader(file, DEFAULT_SHEET).single(int.class).pick("E2")
       .map(field -> Integer.parseInt(field.toText().split("=")[0].trim())).go()

OUTPUT: 5

Read into a user-provided object

public static class Foo {
   private String name;
   // getters / setters
}

Foo foo = new EReader(file, DEFAULT_SHEET)
      .single(Foo.class).binds(new Bind("name", "E2")).go();

OUTPUT: Bill Gates

Here you can also add your own mapper

    Foo foo = new EReader(file, DEFAULT_SHEET).single(Foo.class)
           .binds(new Bind("name", "E2").map(BindField::toText))
           .go();

Read list into a user-provided object

public static class Foo {
  private String group;
  private List<String> students;

  // getters / setters
}

Foo foo = new EReader(file, DEFAULT_SHEET).single(Foo.class)
      .binds(
              new Bind("group", "E2"),
              new Bind("students", "E3:G3")
      ).go();

You are also able to specify “until()”, “filter()” and “map()” methods to any collection

Foo foo = new EReader(file, DEFAULT_SHEET).single(Foo.class)
       .binds(
               new Bind("group", "E2"),
               new Bind("students", "E3:G3")
                       .until(contains("Adrian"))
                       .filter(contains("Tom"))
                       .map(field -> "[" + field.toText() + "]")
       ).go();

OUTPUT: Foo(group=A, students=[[Tom]])

Here is how you can read only student names

List<String> names = new EReader(file, DEFAULT_SHEET)
       .listOf(String.class).pick("E3:G3").go();

OUTPUT: [Tom, Roddy, Adrian]

Reading a list with defined filter

List<String> names = new EReader(file, DEFAULT_SHEET)
       .listOf(String.class).pick("E3:K3").filter(BindField::isNotBlank).go()

OUTPUT: [Tom, Roddy, Adrian, Julia, Mishel]

List of user-provided objects

public class Foo {
  private long id;
  private String firstName;
  private String lastName;
  private int age;
  private Gender gender;
  private boolean driverLicense;

  // getters / setters

  public enum Gender {
      FEMALE,
      MALE
  }
}

List<Foo> table = new EReader(file, DEFAULT_SHEET)
      .listOf(Foo.class)
      .binds(
              new Bind("id", "A2"),
              new Bind("firstName", "B2"),
              new Bind("lastName", "C2"),
              new Bind("age", "D2"),
              new Bind("gender", "E2"),
              new Bind("driverLicense", "F2")
      ).pick("A2:A5").go();

OUTPUT:

[
  {
    "id": 1,
    "firstName": "John",
    "lastName": "Travolta",
    "age": 12,
    "gender": "MALE",
    "driverLicense": true
  },
  {
    "id": 2,
    "firstName": "Piter",
    "lastName": "Pen",
    "age": 8,
    "gender": "MALE",
    "driverLicense": false
  },
  {
    "id": 3,
    "firstName": "Olia",
    "lastName": "Ududiak",
    "age": 20,
    "gender": "FEMALE",
    "driverLicense": true
  },
  {
    "id": 4,
    "firstName": "Marta",
    "lastName": "Chorna",
    "age": 28,
    "gender": "FEMALE",
    "driverLicense": true
  }
]

Map of a user-provided object the same screenshot as for example 7. Let’s use the id column as a key. As usual, you have access to map, filter, and special conditions to which point it will be iterating.

Map<Long, Foo> map = new EReader(file, DEFAULT_SHEET)
       .mapOf(Long.class, Foo.class)
       .binds(
               new Bind("firstName", "B2"),
               new Bind("lastName", "C2"),
               new Bind("age", "D2"),
               new Bind("gender", "E2"),
               new Bind("driverLicense", "F2")
       )
       .pick("A2:A5").go()

OUTPUT:

{
 "1": {
   "firstName": "John",
   "lastName": "Travolta",
   "age": 12,
   "gender": "MALE",
   "driverLicense": true
 },
 "2": {
   "firstName": "Piter",
   "lastName": "Pen",
   "age": 8,
   "gender": "MALE",
   "driverLicense": false
 },
 "3": {
   "firstName": "Olia",
   "lastName": "Ududiak",
   "age": 20,
   "gender": "FEMALE",
   "driverLicense": true
 },
 "4": {
   "firstName": "Marta",
   "lastName": "Chorna",
   "age": 28,
   "gender": "FEMALE",
   "driverLicense": true
 }
}

Matrices

Map<Integer, Integer> map = new EReader(file, DEFAULT_SHEET)
       .mapOf(int.class, int.class)
       .pick("E2:G4", "I2:K4").go();

OUTPUT: {1=10, 2=11, 3=12, 4=13, 5=14, 6=15, 7=16, 8=17, 9=18}

Map of list the same screenshot as for example 8

Map<Integer, List<Integer>> map = new EReader(file, DEFAULT_SHEET)
      .mapOfList(int.class, int.class)
      .pick("E2:E4", "F2:K2")
      .filterValue(BindField::isNotBlank).go()

OUTPUT: {1=[2, 3, 10, 11, 12], 4=[5, 6, 13, 14, 15], 7=[8, 9, 16, 17, 18]}

More examples with annotation processing can be found at file annotations.md or go to the test folder to explore even more test examples.

If you like it, please give a star to the project and feel free to submit a pull request to any part of the project that can be improved.

Enjoy :)

License

MIT

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

excel[orm]

Maven dependency

Quick overview

License

Files

README.md

Latest commit

History

README.md

File metadata and controls

excel[orm]

Maven dependency

Quick overview

License