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.
At first, let's add the dependency
<dependency>
<groupId>com.sombrainc</groupId>
<artifactId>excelorm</artifactId>
<version>2.0.1</version>
</dependency>
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 8Map<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 :)
MIT