Best Practices: Use Editor

The fundamental idea of this library is the Editor class. It is a wrapper of the Apache POI Workbook to provide high-level functions that easily manipulate the Excel file.

Editor provides 3 types of methods:

1. Navigation methods: which allow you to iterate through sheets or go to any specific cell.

2. Writing methods: which allow you to apply styles and constraints, write data as a table or as a template of cells.

3. Reading methods: which allow you to read a specific cell's value, or read a whole table as list.

Open a new Editor

To create a blank Excel file, simply create an empty Editor. By default, Editor uses XSSFWorkbook.

// use try-with-resource to automatically release memory
try (Editor editor = new Editor()) {
    ...
}

To open an existing file, you can either read it as InputStream or as Workbook.

InputStream stream = FileUtil.readFromDisk("...");

// read directly from stream
try (Editor editor = new Editor(stream)) {
    ...
}

// open and read as Workbook
Workbook xls = new HSSFWorkbook(stream);
try (Editor editor = new Editor(xls)) {
    ...
}

Navigation in Editor

You can navigate to a sheet via its name or its index. If the sheet does not exist, Editor will create one for you.

editor.goToSheet(3); // If not found, editor will append new one
editor.goToSheet("My Sheet"); // If not found, editor will create new one with provided name

Editor implements Iterable<Sheet> (POI Sheet), so that you can iterate through existing sheets using the enhanced loop (you can use the traditional loop as well).

// traditional loop
for (int i = 0; i < editor.getTotalSheets(); i++) {
    editor.goToSheet(i);
    ...
}


// enhanced loop
for (Sheet sheet : editor) {
    // No need to call .goToSheet(),
    // because 'sheet' is currently selected in 'editor'
    ...
}

To go to a cell, you can either specify the coordinate in number (count from 0) or the string address.

editor.goToCell(3, 2); // row, col
// or
editor.goToCell("C4");

Editor also has the same set of navigation methods as Template that allows you to move next or down along the spreadsheet, which is appropriate for writing free-style template.

editor.goToCell("C4")...
      .down()...  // go to next down cell
      .down(3)... // skip 2 rows
      .next()...  // go to next right cell
      .enter()... // enter next row and go to first cell

Write data

You can use Editor to either write a list of DTO or a template of cells as we introduced in previous pages.

To write a list of DTO, you will need to specify a DataTemplate<T> (as we introduced in section Write DTO list).

DataTemplate<Book> template = ...
List<Book> books = ...

editor.goToCell("C4").writeData(template, books);

ByteArrayInputStream stream = editor.exportToFile();

With cells template, Editor has similar interfaces as Template as we mentioned in section Create Custom Template. To write a pre-defined template, simply do as below.

Template template = ...

editor.goToCell("C4").writeTemplate(template);

Read data

To read a table section as a list of DTO, you need to provide a ReaderConfig<T> (as we introduced in Read data as DTO list).

(Note that when using Editor, we can only read a single section at a time. We cannot read multiple sections or multiple sheets as we discussed in Read data as DTO list)

ReaderConfig<Book> config = ...

Result<Book> books = editor
    .goToCell("A2") // the starting position
    .readSection(config);

for (Book book : books) { // Result<Book> is a collection
    ...
}

The best part of Editor is that you can read any cell's value in the most flexible way.

String companyName = editor.goToCell("A3").readString();
LocalDateTime eta = editor.goToCell("A12").readLocalDateTime();
Integer batchs = editor.goToCell("C8").readInt(); // if there is no value, null will be returned