Search code examples
javaxmljxls

JXLS grouping with xml markup

Is groupBy supported via xml configuration for JXLS 2.x?

Reason being I have a very basic grouping example that works with excel comments but simply refuses to work when I use the same configuration via xml markup. Output excel is grouped, but there is no data under each group.

I could not find any working examples online as well so either something is wrong with my syntax (see below) or groupBy is NOT supported in JXLS 2.x via xml markup.

excel comments (this works!)

enter image description here

detail_config.xml xml configuration (does not work!)

<xls>
    <area ref="Template!A1:D4">
        <each items="clockinouts" groupBy="employeeName" groupOrder="asc" ref="Template!A1:D3">
            <area ref="Template!A1:D3">
                <each items="_group.items" var="clockinout" ref="Template!A2:D2">
                    <area ref="Template!A2:D2"/>
                </each>
            </area>
        </each>
    </area>
</xls>

Java code to load xml configuration

public static void main(String args[]) throws IOException {
    List<ClockInOut> clockInOuts;

    clockInOuts = loadCSV();
    InputStream is = GroupingDemo.class.getResourceAsStream("detail_template.xlsx");

    try (OutputStream os = new FileOutputStream("target/detail.xlsx")) {
        Transformer transformer = TransformerFactory.createTransformer(is, os);
        try (InputStream configInputStream = GroupingDemo.class.getResourceAsStream("detail_config.xml")) {
            AreaBuilder areaBuilder = new XmlAreaBuilder(configInputStream, transformer);
            List<Area> xlsAreaList = areaBuilder.build();
            Area xlsArea = xlsAreaList.get(0);
            Context context = new Context();
            context.putVar("clockinouts", clockInOuts);
            xlsArea.applyAt(new CellRef("Template!A1"), context);
            transformer.write();
        }
    }
}
Solution

  • Based on the source code, it looks like XML markup only supports item, var, dir, and ref attributes, hence the above behavior is expected. Raised an issue in the JXLS repository, so hopefully, this will get fixed in a future version.

    Right now Excel markup seems to be the way forward.