Writing excel sheet

Overview

XlBean can also be used for writing data out to excel files. However, Writing functions are supplementary compared to Reading functions.

Known limitations

Read excel file on memory then write the data to excel file will not ensure the same status as the initial excel. This asymmetry comes from the way definitions can be written.

STEP 1. Define Definition into Excel sheet

The way for the definition is exactly the same as reading, but of course, without data. (Data can be filled, however they will be simply ignored.)

STEP 2. Write Java Code

Unlike Reading, it requires 3 parameters for write method. That are:

• InputStream to template file
• XlBean of which actual data to write out
• OutputStream to write populated excel file

// Read data from Excel file
InputStream in = new FileInputStream("example/presidents.xlsx");
XlBeanReader reader = new XlBeanReader();
XlBean bean = reader.read(in);

// Convert XlBean to other class
List<President> presidents = bean.listOf("presidents", President.class);

// Make a small update
List<President> sortedPresidents = presidents
    .stream()
    .sorted(Comparator.comparing(President::getNumberOfDaysInOffice).reversed())
    .collect(Collectors.toList());

// XlBean to write out to Excel file
XlBean outBean = XlBeanFactory.getInstance().createBean();
// Since `sortedPresidents` is a list of non-XlBean, using `set(String key, Object value)` to set to XlBean.
outBean.set("presidents", sortedPresidents);

// Write `outBean` to `newPresidents.xlsx`
XlBeanWriter writer = new XlBeanWriter();
writer.write(
    new FileInputStream("example/presidents_blank.xlsx"),
    outBean,
    new FileOutputStream("newPresidents.xlsx"));

This program will create a file "newPresidents.xlsx" which looks like this:

3 fields at the top of the sheet name, stats.totalArea and stats.gdp are not populated because only "presidents" has been set in the above example code.