Write DTO List

To write DTO objects, we use DataTemplate<T>. A DataTemplate is a template of type <T> holding rules to extract fields and write as corresponding columns.

A Simple Usage

Column titles will be generated based on fields' names. Hence DTO classes should follow the camelCase convention properly.

public class Main {

    static 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);
    }
}

Customize Styles

Since Excel has some limitations regard to styling, ExcelUtil provides Style as an alternative wrapper to the original POI CellStyle. It allows you to style cells declaratively, while automatically handling caching underneath.

To assure the caching mechanism work properly, it is recommended to define all styles statically before doing the writing process.

Style BASED_STYLE = Style
    .builder()
    .fontName("Calibri")
    .fontSize((short) 12)
    .build();

// it is able to accumulate existing styles. It will not affect
// the caching mechanism as long as you define them statically.
Style HEADER_STYLE = Style
    .builder(BASED_STYLE) // accumulate from BASED_STYLE
    .fontColorInHex("#ffffff")
    .backgroundColorInHex("#191970")
    .border(BorderSide.FULL)
    .horizontalAlignment(HorizontalAlignment.LEFT)
    .build();

Below is a full example of styling.

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 ?  // conditional cell style
                                                    HIGH_RATE : null))
            .config(cf -> cf.startAtCell("A2")
                            .autoSizeColumns(true)
                            .headerStyle(HEADER_STYLE)
                            .dataStyle(DATA_STYLE) // background style
                            .conditionalRowStyle(book -> book.getTitle() // selective row style
                                            .contains("Harry Potter") ? FAVORITE_ONE : null));

    public static void main(String[] args) {
        InputStream stream = BOOK_TEMPLATE.writeData(books);

        FileUtil.writeToDisk(".../books.xlsx", stream, true);
    }
}

Here is the result.

Supported field types

ExcelUtil automatically writes data types that are specified in the original POI .setCellValue() method as below.

Category	Java Types
Basic (both primitive & wrapper)	int, long, float, double, boolean
Date	Date, LocalDateTime, LocalDate, Calendar
Text	String, RichTextString (from POI)

If the type of the field is not matched any of the above, ExcelUtil will convert the field to String by invoking method .toString().

Merge Rows

You can merge cells in a row based on similar values.

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 an example of how to configure merge rows.

Style VCENTER = Style.builder().verticalAlignment(VerticalAlignment.CENTER).build();

DataTemplate<Book> BOOK_TEMPLATE = DataTemplate
    .fromClass(Book.class)
    .includeFields("title")
    .column(c -> c.field("subCategory")
                    .style(VCENTER)
                    .mergeOnValue(true))  // merge cells having similar 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));

Here is the result.

Expand Rows

The reverse flow of merging rows can also be achieved - instead of merging cells, you can expand a field of collection on multiple rows, other fields will be merged automatically.

public class Category {
    private long id;
    private String name;
    private List<Book> books;
}

public class Book {
    private String isbn;
    private String title;
    private double rating;
    private String author;
    private String subCategory;
    private Category category;
    private List<String> words;
}

DataTemplate<Category> CATEGORY_TEMPLATE = DataTemplate
    .fromClass(Category.class)
    .includeFields("name")
    .column(c -> c.field("books")
                  .expandRows(Book.class, bookCols -> bookCols
                        .includeFields("isbn", "title")
                        .column(bc -> bc.field("words").expandRows())));

Here is the result.

Important Notes

With "Merge Rows" and "Expand Rows" sections, please note that the performance of the writing process will be badly impacted when the data set becomes sufficiently large.