Multiple sheet output

Introduction

jXLS allows to output a collection into multiple worksheets. This can be done with Jxls Each-Command using a multisheet attribute or by passing a custom cell generator.

Usage of multisheet attribute with Each-command

Since v2.3.0 Jxls introduced multisheet attribute for Each-Command that allows to set a sheet names context variable in an Excel template markup.

jx:each(items="employees" var="employee" lastCell="D4" multisheet="sheetNames")

This markup assumes you have sheetNames variable in the context containing a list of worksheet names to output the collection of employees.

See Multi sheet markup demo for a code and a template example.

Usage of CellRefGenerator

You can use a CellRefGenerator to generate target cell ref for each element of the collection processed with Each-Command.

Let’s take a look at an example for multi-sheet output found in Multi sheet demo example

// create transformer and defining command areas
...
// creating each command providing custom cell reference generator instance
EachCommand departmentEachCommand = new EachCommand("department", "departments", departmentArea, new SimpleCellRefGenerator());
// define other commands and areas
...
// adding command to an area, setting up the bean context and transforming the template
xlsArea.addCommand(new AreaRef("Template!A2:F12"), departmentEachCommand);
Context context = new Context();
context.putVar("departments", departments);
logger.info("Applying at cell Sheet!A1");
xlsArea.applyAt(new CellRef("Sheet!A1"), context);
// finishing the processing
...

As can be seen we provided an instance of SimpleCellRefGenerator to Each-Command. The code for SimpleCellRefGenerator is very simple. We just have to implement a single method to return a cell reference where to start output a Department data for each iteration.

public class SimpleCellRefGenerator implements CellRefGenerator {
    public CellRef generateCellRef(int index, Context context) {
        return new CellRef("sheet" + index + "!B2");
    }
}

Our implementation just returns a new sheet reference for each iteration so the first department will go into sheet0!B2, second into sheet1!B2 and so on.