This is migrated to https://github.com/javaxcel/javaxcel.
Supporter for export and import of excel file
- What is Javaxcel?
- Getting started
- Examples
- No option
- Exclude field
- Name the header
- Set the default value
- Model without the targeted fields
- Model that extends class
- Format date/time
- Name a Sheet
- Decoration
- Expression
- Enum value constraint
- Limitation of reading rows
- Parallel reading
- Resolution of constructor and method
- Add a handler for custom type
- Support java.util.Map
Javaxcel is a supporter for exporting java.util.List
to spreadsheets and importing java.util.List
from spreadsheets using Apache POI.
<dependency>
<groupId>com.github.javaxcel</groupId>
<artifactId>javaxcel-core</artifactId>
<version>x.y.z</version>
</dependency>
<!-- Required dependency -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>a.b.c</version>
</dependency>
implementation 'com.github.javaxcel:javaxcel-core:x.y.z'
// Required dependency
implementation 'org.apache.poi:poi-ooxml:a.b.c'
// Creates an instance of Javaxcel.
Javaxcel javaxcel = Javaxcel.newInstance();
File src = new File("/data", "old-products.xls");
File dest = new File("/data", "new-products.xlsx");
try (InputStream in = new FileInputStream(src);
OutputStream out = new FileOutputStream(dest);
Workbook oldWorkbook = new HSSFWorkbook(in);
Workbook newWorkbook = new SXSSFWorkbook()) {
// Reads all the sheets and returns data as a list.
List<Product> products = javaxcel.reader(oldWorkbook, Product.class).read();
// Creates an Excel file and writes data to it.
javaxcel.writer(newWorkbook, Product.class).write(out, products);
} catch (IOException e) {
e.printStackTrace();
}
Code with simple usage.
class Product {
private long serialNumber;
private String name;
private String accessId;
private Double width;
private double depth;
private double height;
private Double weight;
}
// ...
Product product = Product.builder()
.serialNumber(10000)
.name("Choco cereal")
.accessId("2a60-4973-aec0-685e")
.height(20.5)
.weight(580.5)
.build();
List<Product> products = Collections.singletonList(product);
There is a list that contains a Product
.
File dest = new File("/data", "products.xlsx")
OutputStream out = new FileOutputStream(dest);
Workbook workbook = new SXSSFWorkbook();
Javaxcel.newInstance()
.writer(workbook, Product.class)
.write(out, products);
The result is
serialNumber | name | accessId | width | depth | height | weight |
---|---|---|---|---|---|---|
10000 | Choco cereal | 2a60-4973-aec0-685e | 0.0 | 20.5 | 580.5 |
The column order is the same as the declared field order.
If nothing is specified for the column, header name is the field name.
File src = new File("/data", "products.xlsx");
Workbook workbook = new XSSFWorkbook(src);
List<Product> products = Javaxcel.newInstance()
.reader(workbook, Product.class)
.read();
The result is
[
{
"serialNumber": 10000,
"name": "Choco cereal",
"accessId": "2a60-4973-aec0-685e",
"width": null,
"depth": 0.0,
"height": 20.5,
"weight": 580.5
}
]
The column order, also when read, is the same as the declared field order.
Model must has a constructor without parameters, so that ExcelReader
can instantiate.
@ExcelIgnore
private String accessId;
serialNumber | name | width | depth | height | weight |
---|---|---|---|---|---|
10000 | Choco cereal | 0.0 | 20.5 | 580.5 |
If you want to exclude several fields, annotate @ExcelIgnore
to them.
[
{
"serialNumber": 10000,
"name": "Choco cereal",
"accessId": null,
"width": null,
"depth": 0.0,
"height": 20.5,
"weight": 580.5
}
]
ExcelReader
will pass the fields that annotated @ExcelIgnore
by.
If column accessId
exists in Excel sheet and Product#accessId
is still annotated @ExcelIgnore
,
the exception will occur because of setting accessId
to width
(NumberFormatException).
@ExcelColumn(name = "PRODUCT_NO")
private long serialNumber;
@ExcelColumn
private String name;
PRODUCT_NO | name | accessId | width | depth | height | weight |
---|---|---|---|---|---|---|
10000 | Choco cereal | 2a60-4973-aec0-685e | 0.0 | 20.5 | 580.5 |
If you want to name the header, annotate @ExcelColumn
and assign name()
you want.
If you want to override ExcelColumn#name()
(use header names on this moment),
invoke ExcelWriter#options(ExcelWriteStrategy...)
with ExcelWriteStrategy.HeaderNames
.
List<String> headerNames = Arrays.asList("PRD_NO","NM","ACS_ID","WID","DEP","HEI","WEI"); // 7
// List<String> headerNames = Arrays.asList("PRD_NO","NM","ACS_ID","WID","DEP","HEI"); // 6: Occurs exception.
Javaxcel.newInstance()
.writer(workbook, Product.class)
.options(new HeaderNames(headerNames))
.write(out, products);
The result is
PRD_NO | NM | ASC_ID | WID | DEP | HEI | WEI |
---|---|---|---|---|---|---|
10000 | Choco cereal | 2a60-4973-aec0-685e | 0.0 | 20.5 | 580.5 |
If the number of arguments is not equal to the number of targeted fields, ExcelWriter
throws exception.
// Default value is effective except primitive type.
@ExcelColumn(name = "WIDTH", defaultValue = "0.0mm")
private Double width;
// Default value is ineffective to primitive type.
@ExcelColumn(name = "Depth", defaultValue = "(empty)")
private double depth;
@ExcelColumn(defaultValue = "0")
private double height;
serialNumber | name | accessId | WIDTH | Depth | height | weight |
---|---|---|---|---|---|---|
10000 | Choco cereal | 2a60-4973-aec0-685e | 0.0mm | 0.0 | 20.5 | 580.5 |
It's ineffective to assign default value to primitive type, because the field of primitive type is always initialized.
If you want to override ExcelColumn#defaultValue()
(use another default value on this moment),
invoke ExcelWriter#options(ExcelWriteStrategy...)
with option ExcelWriteStrategy.DefaultValue
.
Product product = new Product(); // Empty product.
List<Product> products = Collections.singletonList(product);
Javaxcel.newInstance()
.writer(workbook, Product.class)
.options(new DefaultValue("(empty)"))
.write(out, products);
The result is
serialNumber | name | accessId | WIDTH | Depth | height | weight |
---|---|---|---|---|---|---|
0 | (empty) | (empty) | (empty) | 0.0 | 0.0 | (empty) |
ExcelWriteStrategy.DefaultValue(String)
will be applied to all fields.
If cell value is null or empty string, ExcelReadHandlerConverter
parse ExcelColumn#defaultValue()
and set it to field.
Specify ExcelColumn#defaultValue()
with care.
It might not occur exception, when you write, though the default value doesn't match type of that field.
It must occur exception, when you read, if the default value doesn't.
If the field that is annotated by @ExcelReadExpression
, its default value doesn't work.
class NoFieldModel {}
class AllIgnoredModel {
@ExcelIgnore
private int number;
@ExcelIgnore
private Character character;
}
Javaxcel.newInstance()
.writer(workbook, NoFieldModel.class); // Occurs exception.
Javaxcel.newInstance()
.writer(workbook, AllIgnoredModel.class); // Occurs exception.
If you try to write with the class that has no targeted fields, ExcelWriter
will throw exception.
List<NoFieldModel> noFieldModels = Javaxcel.newInstance()
.reader(workbook, NoFieldModel.class); // Occurs exception.
List<AllIgnoredModel> allIgnoredModels = Javaxcel.newInstance()
.reader(workbook, AllIgnoredModel.class); // Occurs exception.
If you try to write with the class that has no targeted fields, ExcelReader
will throw exception.
class EducationalProduct extends Product {
private int[] targetAges;
private String goals;
private Product related; // Unknown type.
private LocalDate date;
private LocalTime time;
private LocalDateTime dateTime;
}
// ...
EducationalProduct eduProduct = EducationalProduct.builder()
.serialNumber(10001)
.name("Mathematics puzzle toys for kids")
.accessId("1a57-4055-a75b-98e4")
.width(18.0)
.depth(6)
.height(20)
.weight(340.07)
.targetAges(4,5,6,7,8,9)
.goals("Develop intelligence")
.related(new Product())
.date(LocalDate.now())
.time(LocalTime.now())
.dateTime(LocalDateTime.now())
.build();
List<EducationalProduct> eduProducts = Collections.singletonList(eduProduct);
There is a list that contains a EducationalProduct
.
Javaxcel.newInstance()
.writer(workbook, EducationalProduct.class)
.write(out, list);
targetAges | goals | related | date | time | dateTime |
---|---|---|---|---|---|
[4, 5, 6, 7, 8, 9] | Develop intelligence | com.github.javaxcel.Product@4bf558aa | 2020-09-13 | 11:54:26 | 2020-09-13 11:54:26 |
It writes the declared own fields, not including the inherited fields.
That's default.
@ExcelModel(includeSuper = true)
class EducationalProduct extends Product { /* ... */ }
If you annotate @ExcelModel
and assign true into includeSuper()
, it writes including the inherited fields.
The result is
serialNumber | name | accessId | width | depth | height | weight | targetAges | goals | related | date | time | dateTime |
---|---|---|---|---|---|---|---|---|---|---|---|---|
10001 | Mathematics puzzle toys for kids | 1a57-4055-a75b-98e4 | 18.0 | 6.0 | 20.0 | 340.07 | [4, 5, 6, 7, 8, 9] | Develop intelligence | com.github.javaxcel.Product@b1a58a3 | 2020-09-13 | 11:54:26 | 2020-09-13 11:54:26 |
List<EducationalProduct> eduProducts = Javaxcel.newInstance()
.reader(workbook, EducationalProduct.class)
.read();
[
{
"serialNumber": 10001,
"name": "Mathematics puzzle toys for kids",
"accessId": "1a57-4055-a75b-98e4",
"width": 18.0,
"depth": 6.0,
"height": 20.0,
"weight": 340.07,
"targetAges": [4, 5, 6, 7, 8, 9],
"goals": "Develop intelligence",
"related": null, // No handler for this type.
"date": "2020-09-13",
"time": "11:54:26",
"dateTime": "2020-09-13 11:54:26"
}
]
To know what types are basically supported, see DefaultExcelTypeHandlerRegistry
.
Others are not supported by default, so that the field value will be null (if primitive, will be initial value of that type). You can register handler for unknown types.
- Create an instance of
ExcelTypeHandlerRegistryImpl
. - Make implementations of
AbstractExcelTypeHandler
. - Add handlers to registry.
- Invoke
Javaxcel.newInstance(ExcelTypeHandlerRegistry)
with your registry.
@ExcelDateTimeFormat(pattern = "yyyyMMdd")
private LocalDate date = LocalDate.now();
@ExcelDateTimeFormat(pattern = "HH/mm/ss")
private LocalTime time = LocalTime.now();
// Default pattern: "yyyy-MM-dd HH:mm:ss"
private LocalDateTime dateTime = LocalDateTime.now();
date | time | dateTime |
---|---|---|
20200913 | 11/54/26 | 2020-09-13 11:54:26 |
If you want to write formatted value, annotate @ExcelDateTimeFormat
and assign pattern()
you want. These are
supported types by default.
java.util.Date
java.time.LocalTime
java.time.LocalDate
java.time.LocalDateTime
java.time.ZonedDateTime
java.time.OffsetTime
java.time.OffsetDateTime
{
"date": "2020-09-13",
"time": "11:54:26.0",
"dateTime": "2020-09-13T11:54:26.0"
}
ExcelReader
parses them with option ExcelDateTimeFormat#pattern()
.
Javaxcel.newInstance()
.writer(workbook, Product.class)
.options(new SheetName("Products"))
.write(out, products);
If you want to name a sheet, add option ExcelWriteStrategy.SheetName
.
The default of sheet name is Sheet
trailing sheet number according to rule of spreadsheet program.
Javaxcel.newInstance()
.writer(workbook, Product.class)
.options(
new AutoResizedColumns(),
new HiddenExtraRows(),
new HiddenExtraColumns(),
new HeaderStyles(new DefaultHeaderStyleConfig()),
new BodyStyles(new DefaultBodyStyleConfig())
)
.write(out, products);
You can adjust all sheets with option ExcelWriteStrategy.AutoResizedColumns
, ExcelWriteStrategy.HiddenExtraRows
and ExcelWriteStrategyHiddenExtraColumns
.
You can decorate the header with option ExcelWriteStrategy.HeaderStyles
and also decorate the body with
option ExcelWriteStrategy.BodyStyles
.
If the number of arguments is not equal to 1 nor the number of targeted fields, ExcelWriter
throws exception.
When you input single argument, ExcelWriter
applies it to all columns.
@ExcelModel(headerStyle = DefaultHeaderStyleConfig.class, bodyStyle = DefaultBodyStyleConfig.class)
class Product {
@ExcelColumn(headerStyle = RedColumnStyleConfig.class)
private long serialNumber;
@ExcelColumn(bodyStyle = GrayColumnStyleConfig.class)
private String name;
// ...
}
You can also decorate the header and body with annotations.
Look here for how to configure styles.
ExcelColumn#headerStyle()
takes precedence over ExcelModel#headerStyle()
.
ExcelColumn#bodyStyle()
takes precedence over ExcelModel#bodyStyle()
.
serialNumber
will be applied with RedColumnStyleConfig
. and DefaultBodyStyleConfig
.
name
will be applied with DefaultHeaderStyleConfig
and GrayColumnStyleConfig
.
class Product {
@ExcelWriteExpression("T(io.github.imsejin.common.util.StringUtils).formatComma(#serialNumber)")
private long serialNumber;
@ExcelWriteExpression("#name.toUpperCase()")
private String name;
@ExcelWriteExpression("#accessId.replaceAll('\\d+', '')")
private String accessId;
@ExcelWriteExpression("T(String).valueOf(#width).replaceAll('\\.0+$', '')")
private Double width;
@ExcelWriteExpression("#depth + 'cm'")
private double depth;
private double height;
@ExcelWriteExpression("T(Math).ceil(#weight)")
private Double weight;
}
@ExcelModel(includeSuper = true)
class EducationalProduct extends Product {
@ExcelWriteExpression("T(java.util.Arrays).stream(#targetAges)" +
".boxed()" +
".collect(T(java.util.stream.Collectors).toList())" +
".toString()" +
".replaceAll('[\\[\\]]', '')")
private int[] targetAges;
// Fixed value
@ExcelWriteExpression("'none'")
private String goals;
private Product related;
// Refers other field
@ExcelWriteExpression("T(java.time.LocalDateTime).of(#date, #time)")
private LocalDate date;
private LocalTime time;
private LocalDateTime dateTime;
}
serialNumber | name | accessId | width | depth | height | weight | targetAges | goals | related | date | time | dateTime |
---|---|---|---|---|---|---|---|---|---|---|---|---|
10,001 | MATHEMATICS PUZZLE TOYS FOR KIDS | a--ab-e | 18 | 6.0cm | 20.0 | 341.0 | 4, 5, 6, 7, 8, 9 | none | com.github.javaxcel.Product@b1a58a3 | 2020-09-13T11:54:26.176 | 11:54:26 | 2020-09-13 11:54:26 |
You can pre-process field value with @ExcelWriteExpression
before set value into a cell.
The expression language is SpEL(Spring Expression Language)
.
Look here for more details about this.
If you want to refer the field in an expression, write #
and the field name
. (e.g. #targetAges)
You can also refer the other fields. We call this as variable
.
Field you can refer is only targeted field.
It means you cannot refer the field that is annotated with @ExcelIgnore
(or ExcelModel#explicit()
).
If type of expression result is not String
, the converter will invoke Object#toString()
.
class Product {
@ExcelReadExpression("T(Long).parseLong(#serialNumber.replace(',', ''))")
private long serialNumber;
@ExcelReadExpression("#name.charAt(0) + #name.substring(1).toLowerCase()")
private String name;
@ExcelReadExpression("#accessId.replaceAll('-', '0')")
private String accessId;
private Double width;
// This string will be parsed as double.
@ExcelReadExpression("#depth.replace('cm', '')")
private double depth;
private double height;
@ExcelReadExpression("T(Double).parseDouble(#weight) - 0.93")
private Double weight;
}
@ExcelModel(includeSuper = true)
class EducationalProduct extends Product {
// Custom converter method
@ExcelReadExpression("T(com.github.javaxcel.Converter).toPowerIntArray(#targetAges.split(', ')")
private int[] targetAges;
// Fixed value
@ExcelReadExpression("'Develop intelligence'")
private String goals;
@ExcelReadExpression("new com.github.javaxcel.Product()")
private Product related;
@ExcelReadExpression("T(java.time.LocalDate).parse(#date.substring(0, 10))")
private LocalDate date;
private LocalTime time;
private LocalDateTime dateTime;
}
// com.github.javaxcel.Converter
public class Converter {
public static int[] toPowerIntArray(String[] str) {
return Arrays.stream(str).mapToInt(Integer::parseInt).map(i -> i + 1).toArray();
}
}
[
{
"serialNumber": 10001,
"name": "Mathematics puzzle toys for kids",
"accessId": "a00ab0e",
"width": 18.0,
"depth": 6.0,
"height": 20.0,
"weight": 340.07,
"targetAges": [16, 25, 36, 49, 64, 81],
"goals": "Develop intelligence",
"related": {
"serialNumber": 0,
"name": null,
"accessId": null,
"width": null,
"depth": 0.0,
"height": 0.0,
"weight": null
},
"date": "2020-09-13",
"time": "11:54:26.0",
"dateTime": "2020-09-13T11:54:26.0"
}
]
You can support not basic supported type with @ExcelReadExpression
.
The type of variable
is String
. It is value in cell.
@ExcelColumn(enumDropdown = true)
private java.nio.file.AccessMode accessMode;
@ExcelColumn(enumDropdown = true, dropdownItems = {"days", "hrs", "min", "sec", "ms", "us", "ns"})
private java.util.concurrent.TimeUnit timeUnit;
If you enable ExcelColumn#enumDropdown()
annotated to enum field, this column will have constraint. In other words,
this column will have dropdown items as Enum#name()
.
If you also specify ExcelColumn#dropdownItems()
to enum field, this column wll have dropdown items
as ExcelColumn#dropdownItems()
.
Javaxcel.newInstance()
.writer(workbook, Product.class)
.options(new EnumDropdown())
.write(out, products);
Even if enumDropdown()
is false
in ExcelModel
or ExcelColumn
, you can enable it on this moment with
option ExcelWriteStrategy.EnumDropdown
.
List<Product> products = Javaxcel.newInstance()
.reader(workbook, Product.class)
.options(new Limit(100))
.read();
assert products.size() <= 100;
You can read the Excel file, but not all the rows. Just read with option ExcelReadStrategy.Limit
.
List<Product> products = Javaxcel.newInstance()
.reader(workbook, Product.class)
.options(new Parallel())
.read();
With option ExcelReadStrategy.Parallel
, you can read the Excel file faster than without the option.
class Item {
private Long id;
private String title;
private LocalDateTime createdAt;
}
ModelReader
will resolve the constructor public Item()
.
class Item {
private Long id;
private String title;
private LocalDateTime createdAt;
@ExcelModelCreator
private Item(Long id, String title, LocalDateTime createdAt) {
this.id = id;
this.title = title;
this.createdAt = createdAt;
}
public Item(String title) {
this.id = new Random().nextLong();
this.title = title;
this.createdAt = LocalDateTime.now();
}
}
ModelReader
will resolve the constructor private Item(Long, String, LocalDateTime)
annotated
with @ExcelModelCreator
.
class Item {
private Long id;
private String title;
private LocalDateTime createdAt;
private Item(Long id, String title, LocalDateTime createdAt) {
this.id = id;
this.title = title;
this.createdAt = createdAt;
}
@ExcelModelCreator
public static Item from(String title) {
return new Item(new Random().nextLong(), title, LocalDateTime.now());
}
}
ModelReader
will resolve the method public static Item from(String)
annotated with @ExcelModelCreator
.
class Item {
private String id;
private String title;
private LocalDateTime createdAt;
public Item(String $title, LocalDateTime $createdAt) {
this.id = String.valueOf(new Random().nextLong());
this.title = $title;
this.createdAt = $createdAt;
}
}
If the class has multiple field types that are the same, resolver checks if field name and parameter name are the same. If not same, resolver fails to do. You can fix it with this solution when you can't change the parameter name(s).
import com.github.javaxcel.annotation.ExcelModelCreator.FieldName;
public Item(@FieldName("title") String $title, LocalDateTime $createdAt) {
this.id = String.valueOf(new Random().nextLong());
this.title = $title;
this.createdAt = $createdAt;
}
Both names of parameter and field whose type is LocalDateTime
are different, but their type is unique, so resolver can
handle the parameter. The rule of method is also like this.
If you want to know more rules, see AbstractExcelModelExecutableResolver
.
class Item {
private UUID uuid;
private String name;
private Item[] items;
}
Item
is custom class and not supported type by default. You can add a handler for the type.
public class ItemTypeHandler extends AbstractExcelTypeHandler<Item> {
public ItemTypeHandler() {
super(Item.class);
}
@Override
protected String writeInternal(Item value, Object... args) {
// Convert Item into String.
}
@Override
public Item read(String value, Object... args) {
// Convert String into Item.
}
}
// ...
ExcelTypeHandlerRegistry registry = new DefaultExcelTypeHandlerRegistry();
registry.add(new ItemTypeHandler());
Javaxcel javaxcel = Javaxcel.newInstance(registry);
If you want to override the supported type by default, just make a handler class for the type and add it to registry.
List<Map<String, Object>> maps = new ArrayList<>();
maps.add(Map.of("serialNumber", 10010, "productName", "Alpha"));
maps.add(Map.of("serialNumber", 10011, "productName", "Beta"));
Javaxcel.newInstance()
.writer(workbook)
.write(out, maps);
The result is
productName | serialNumber |
---|---|
Alpha | 10010 |
Beta | 10011 |
The column order is not guaranteed except for java.util.LinkedHashMap
. There is option for this issue.
List<String> keyOrders = Arrays.asList("serialNumber", "productName");
Javaxcel.newInstance()
.writer(workbook)
.options(new KeyNames(keyOrders))
.write(out, maps);
The result is
serialNumber | productName |
---|---|
10010 | Alpha |
10011 | Beta |
If you rearrange the column order of Map
, use option ExcelWriteStrategy.KeyNames
. If you want to rename the header
names, use that option like this.
List<String> keyOrders = Arrays.asList("serialNumber", "productName");
List<String> newKeyNames = Arrays.asList("SERIAL_NUMBER", "PRODUCT_NAME");
Javaxcel.newInstance()
.writer(workbook)
.options(new KeyNames(keyOrders, newKeyNames))
.write(out, maps);
The result is
SERIAL_NUMBER | PRODUCT_NAME |
---|---|
10010 | Alpha |
10011 | Beta |
Javaxcel.newInstance()
.reader(workbook)
.read();
[
{
"SERIAL_NUMBER": "10010",
"PRODUCT_NAME": "Alpha"
},
{
"SERIAL_NUMBER": "10011",
"PRODUCT_NAME": "Beta"
}
]
If you want to rename the keys of Map
, use option ExcelReadStrategy.KeyNames
.
List<String> newKeyNames = Arrays.asList("serialNumber", "productName");
Javaxcel.newInstance()
.reader(workbook)
.options(new KeyNames(newKeyNames))
.read();
The result is
[
{
"serialNumber": "10010",
"productName": "Alpha"
},
{
"serialNumber": "10011",
"productName": "Beta"
}
]