Data Excel Exporter
A Java wrapper using Apache POI to read and write Excel file in declarative fashion.
Installation
ExcelUtil is using Apache POI version 5.0.0
<dependency>
<groupId>io.github.nambach</groupId>
<artifactId>ExcelUtil</artifactId>
<version>2.3.0</version>
</dependency>
Quick Guides
Write Excel
A very simple usage
The core building block to write data is DataTemplate<T>. It holds mapping rules of the DTO class you need to export Excel.
public class Main {
static final DataTemplate<Book> BOOK_TEMPLATE = DataTemplate
.fromClass(Book.class)
.includeAllFields();
public static void main(String[] args) {
InputStream stream = BOOK_TEMPLATE.writeData(books);
FileUtil.writeToDisk(".../books.xlsx", stream, true);
}
}
Your DTO class should follow camelCase convention, so that the generated titles would be correct.
Customize Styles
The next building block is Style, which is pretty much the same as what you can configure with normal Excel.
static final Style BASED_STYLE = Style
.builder()
.fontName("Calibri")
.fontSize((short) 12)
.build();
static final Style HEADER_STYLE = Style
.builder(BASED_STYLE) // it is able to accumulate previous style
.fontColorInHex("#ffffff")
.backgroundColorInHex("#191970")
.border(BorderSide.FULL)
.horizontalAlignment(HorizontalAlignment.LEFT)
.build();
Since Apache POI has some limitations regarding to stylings, it is recommended to pre-define your styles as static constant for optimization and further reuse.
Below is an example to apply styles conditionally.
public class Main {
static final Style DATA_STYLE = ...
static final Style HIGH_RATE = ...
static final Style FAVORITE_ONE = ...
static final DataTemplate<Book> BOOK_TEMPLATE = DataTemplate
.fromClass(Book.class)
.column(c -> c.field("isbn").title("ISBN")) // customize column title
.includeFields("title", "author")
.column(c -> c.title("Category")
.transform(book -> book.getCategory().getName())) // derive new column
.column(c -> c.field("rating")
.conditionalStyle(book -> book.getRating() > 4 ? // styles with conditions
HIGH_RATE : null))
.config(cf -> cf.startAtCell("A2")
.autoSizeColumns(true)
.headerStyle(HEADER_STYLE)
.dataStyle(DATA_STYLE)
.conditionalRowStyle(book -> book.getTitle() // selective styling
.contains("Harry Potter") ? FAVORITE_ONE : null));
public static void main(String[] args) {
InputStream stream = BOOK_TEMPLATE.writeData(books);
FileUtil.writeToDisk(".../books.xlsx", stream, true);
}
}
Result
Merge rows
You can merge your data rows, either based on same cell values or a particular value that you specify.
Before doing so, you might want to sort your data so that the merging process can perform correctly.
books.sort(Comparator
.comparing((Book book) -> book.getCategory().getId())
.thenComparing(comparing(Book::getSubCategory).reversed())
.thenComparing(Book::getTitle));
Here is example of how to configure merge rows.
static final Style VCENTER = Style.builder().verticalAlignment(VerticalAlignment.CENTER).build();
static final DataTemplate<Book> BOOK_TEMPLATE = DataTemplate
.fromClass(Book.class)
.includeFields("title")
.column(c -> c.field("subCategory")
.style(VCENTER)
.mergeOnValue(true)) // merge cells with the same value consecutively
.column(c -> c.title("Category")
.style(VCENTER)
.transform(book -> book.getCategory().getName())
.mergeOnId(book -> book.getCategory().getId())) // merge on derived value
.config(cf -> cf.startAtCell("A2")
.autoSizeColumns(true));
Result
Read Excel
The building block to read data is ReaderConfig<T>.
ReaderConfig<Book> BOOK_READER = ReaderConfig
.fromClass(Book.class)
.titleAtRow(0)
.dataFromRow(1)
.column(0, "ibsn")
.column(1, "title")
.column(2, "author")
.column(3, "category");
You can directly retrieve the config from your already defined DataTemplate<T>.
ReaderConfig<Book> BOOK_READER = BOOK_TEMPLATE.getReaderConfig();
InputStream stream = FileUtil.readFromDisk(".../book.xlsx");
List<Book> books = BOOK_READER.readSheet(stream);
For more flexible process while reading data, use built-in callback handler as below.
ReaderConfig<Book> READER_CONFIG = ReaderConfig
.fromClass(Book.class)
.titleAtRow(0)
.dataFromRow(1)
.column(0, "ibsn")
.column(1, "title")
.handler(set -> set.atColumn(2)
.handle((book, cell) -> {
String value = cell.readString();
book.getCategory().setName(value);
}))
.handler(set -> set.fromColumn(3)
.handle((book, cell) -> {
String title = cell.getColumnTitle();
if (title.contains("Rating in")) {
String year = title.substring(10);
Double rating = cell.readDouble();
book.getRatingMap().put(year, rating);
}
}));
Documentation
Documentation can be found here.
Notes
- Minimum JDK version: 1.8
- Support Excel version:
- 97-2003 (.xls)
- 2007+ (.xlsx)
License
Released under Apache-2.0 License.
![[Snyk] Upgrade org.apache.poi:poi-ooxml from 5.1.0 to 5.2.0](https://avatars.githubusercontent.com/u/19733683?v=4)
![[Snyk] Upgrade org.projectlombok:lombok from 1.18.22 to 1.18.24](https://avatars.githubusercontent.com/u/29589836?v=4)
1.9k Jan 6, 2023
97 Jan 7, 2023
1.9k Jan 2, 2023
1 Feb 12, 2022
42 Dec 20, 2022
2.5k Jan 7, 2023
14 Dec 20, 2022
3 Sep 7, 2021
58 May 3, 2022
2 Sep 4, 2022
3 Dec 11, 2022
6.5k Jan 4, 2023
865 Jan 2, 2023
6 May 25, 2022
3 Oct 2, 2021
24 Dec 3, 2022
3 Sep 26, 2022
1 Jan 13, 2022
1 Jan 16, 2022