Option
xlbean has a feature called Option which is metadata added to definition in order to change behavior of xlbean.
As you know, Excel has feature to change format of cell, such as General, Number, Currency, Date, etc. By default, basically xlbean reads value of cell without format. For instance, if you input "1" to General cell, then the value loaded to xlbean will be "1.0", because this is the data model inside Excel. Exceptionally Date and Time formats are converted automatically on read. These values in Excel is expressed by number as differential from 1900/1/0. For instance, 2019/01/01 is 43466. Because this number is not meaningful when it comes out from Excel, xlbean converts those numbers to String value in "yyyy-MM-ddTHH:mm:ss.SSS" format.
But sometimes you simply want a value of a cell as it is displayed. readAs option will do it.
Table shown in the example below shows how readAs option works.
Column B and C are simply showing the difference of default behavior between General cell and Text cell.
Value of General cell is number hence for "0", "0.0" is set. On the other hand, Text cell is String hence "0" is "0".
And Column D and E shows how this behavior changes by this option.
InputStream in = new FileInputStream("example/optionExample.xlsx");
XlBeanReader reader = new XlBeanReader();
XlBean bean = reader.read(in);
List<XlBean> readAsExample = bean.beans("readAs");
// no option
System.out.println(readAsExample.get(0).get("defType")); // 0.0
System.out.println(readAsExample.get(0).get("strType")); // 0
System.out.println(readAsExample.get(1).get("defType")); // 1.0
System.out.println(readAsExample.get(1).get("strType")); // 1
// readAs=text
System.out.println(readAsExample.get(0).get("defWithOption")); // 0 <- Changed from 0.0 by readAs=text
System.out.println(readAsExample.get(0).get("strWithOption")); // 0
System.out.println(readAsExample.get(1).get("defWithOption")); // 1 <- Changed from 0.0 by readAs=text
System.out.println(readAsExample.get(1).get("strWithOption")); // 1
- text - Force to load value as presented on the screen.
By default, xlbean reads table from ~ until blank row appears.
XlList limitedTable = bean.beans("limitedTable");
System.out.println(limitedTable.size());// 5 <= Number of rows loaded is limited to 5
System.out.println(limitedTable.get(0).string("value"));// 0.0 <= The list starts from row 1
System.out.println(limitedTable.get(1).string("value"));// 1.0- Integer
Number of rows from ~ to the row to start reading from.
A cell with no value is treated as null. When null appears for cells with xlbean definition, it is ignored by default. It means that a key for that cell will not be set to a map. But in some case, you want those keys to appear in the map with null value.
ignoreNull option is to change this behavior.
ignores=[
{id=1.0, ignoreNullFalse=aaa}, // "ignoreNullTrue"
{id=2.0, ignoredNullTrue=aaa, ignoreNullFalse=null},
{id=3.0, ignoreNullFalse=null}
]
- Integer
ignoreBlankMap=[
{id=1.0, ignoreBlankMapFalse={field=aaa}}, // "ignoreBlankMapTrue" field doesn't appear
{id=2.0, ignoreBlankMapTrue={field=aaa}, ignoreBlankMapFalse={}}, // "ignoreBlankMapFalse" field appears with blank map
{id=3.0, ignoreBlankMapFalse={}}
]
- true
- false
For nested list in list, you can define it by "list#listInList[0]", "list#listInList[1]" in columns of the parent list. If there are any values exist for listInList then the result will look straight forward, however if there are no values at all, there are 2 cases which you should be aware of. By default, this kind of blank list is ignored. It means that listInList doesn't appear in the element of the parent list. If you want blank list object in the parent list, use ignoreBlankList=false option.
ignoreBlankListTrue=[
{id=1.0, list=[aaa, bbb]},
{id=2.0, list=[null, ccc]},
{id=3.0}, // "list" doesn't appear
{id=4.0}] // "list" doesn't appear
ignoreBlankListFalse=[
{id=1.0, list=[aaa, bbb]},
{id=2.0, list=[null, ccc]},
{id=3.0, list=[]}, // "list" appears with blank list
{id=4.0, list=[]}] // "list" appears with blank list
- true
- false
- ignoreBlank: Shortcut for ignoreBlankMap and ignoreBlankList
- ignoreNullBlank: Shortcut for ignoreNull, ignoreBlankMap and ignoreBlankList
- true
- false
xlbean supports loading not only tables to list but also each cells to map. To read cells to map, you need to input definition in Row-1 and Column-1 of target cell. If there are multiple cells to read in one column, you need to join definitions in Column-1 of the column. You can do it this way if the number of cells are not very big, but not for bigger number of cells.
toMap option will sort out this case.
Let's assume you want a map "toMapExample2" with many key-values. In that case, define a list under toMapExample2 and for one of the column add toMap=key and for another column add toMap=value.
toMapExample1={
aaa=Hello toMapExample1.aaa,
bbb=Hello toMapExample1.bbb
ccc=Hello toMapExample1.ccc,
}
toMapExample2={
aaa=Hello aaa,
bbb=Hello bbb,
ccc=Hello ccc,
ddd=Hello ddd,
eee=Hello eee,
list=[{field1=aaa, field2=Hello aaa}, {field1=bbb, field2=Hello bbb}, {field1=ccc, field2=Hello ccc}, {field1=ddd, field2=Hello ddd}, {field1=eee, field2=Hello eee}]
}
- key
- value
xlbean by default set values of Excel to XlBean instance as String.
By using fieldType option, you can change type of value to something else.
[
{v1=Hello fieldType, v4=H, v5=false},
{v1=123.0, v2=123, v3=123, v4=1, v5=false, v6=123, v7=123, v8=123.0, v9=123.0, v10=123.0, v11=123},
{v1=0.123456789, v2=0, v3=0, v4=0, v5=false, v6=0, v7=0, v8=0.12345679, v9=0.123456789, v10=0.123456789, v11=0},
{v1=99999999999, v3=99999999999, v4=9, v5=false, v8=9.9999998E10, v9=9.9999999999E10, v10=99999999999, v11=99999999999},
{v1=true, v4=t, v5=true},
{v1=2019-01-01T00:00:00.000, v4=2, v5=false, v12=Tue Jan 01 00:00:00 JST 2019, v13=2019-01-01T00:00, v14=2019-01-01},
{v1=2019-01-01T12:34:56.000, v4=2, v5=false, v12=Tue Jan 01 12:34:56 JST 2019, v13=2019-01-01T12:34:56, v14=2019-01-01},
{v1=12:34:56.000, v4=1, v5=false, v15=12:34:56}
]
- string
- int
- long
- char
- boolean
- byte
- short
- float
- double
- bigdecimal
- biginteger
- date
- localdatetime
- localdate
- localtime
There is another way to define Options. Instead of define options in definition cell, you can define options like values.
Syntax is fieldName?optionName. Don't put = after the option name.
[
{id=1, name=Aaa Aaaa, dateOfBirth=1990-01-01},
{id=2, name=Bbb Bbbb, dateOfBirth=1980-01-01},
{id=3, name=Ccc Cccc, dateOfBirth=null },
{id=4, name=Ddd Dddd, dateOfBirth=2000-01-01},
{id=5, name=Eee Eeee, dateOfBirth=null }
]