Mastering JXLS: Generate Complex Excel Reports with Java

Overview

JXLS is a lightweight Java library for template‑based Excel report generation. It allows defining data output in Excel templates using JSP‑like tags, handling fixed styles, merged cells, dynamic columns, and other complex export requirements.

Dependency

<dependency>
  <groupId>org.jxls</groupId>
  <artifactId>jxls-poi</artifactId>
  <version>3.0.0</version>
</dependency>

New API (JXLS 3.x, JDK 17)

The previous builder API ( JxlsHelper, TransformerFactory, createTransformer()) has been removed. Data is supplied as a Map<String, Object> to a template filler.

Map<String, Object> data = new HashMap<>();
data.put("employees", employees);
JxlsPoiTemplateFillerBuilder.newInstance()
    .withTemplate("template.xlsx")
    .build()
    .fill(data, new File("report.xlsx"));

Area (Region)

An Area represents a rectangular block of cells to be processed. Commands can be placed inside the area, and areas may contain nested sub‑areas.

jx:area(lastCell="K4")

Looping (jx:each)

Iterate over a collection. Basic syntax: jx:each(items="weeks" var="week" lastCell="B4") Java model example:

public class Employee {
    private String name;
    private Date birthDate;
    private BigDecimal payment;
    private BigDecimal bonus;
    // getters/setters ...
    public String getSalaryGroup() {
        return payment.doubleValue() > 2000d ? "high" : "normal";
    }
}

List<Employee> employees = new ArrayList<>();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MMM-dd", Locale.US);
employees.add(new Employee("Elsa", df.parse("1970-Jul-10"), new BigDecimal("1500"), new BigDecimal("0.15")));

Indexing

Expose the zero‑based loop index with varIndex:

jx:each(items="employees" var="e" varIndex="index" lastCell="C2")

Filtering

Render only items that satisfy a condition using select:

jx:each(items="employees" var="e" select="e.payment > 2000" lastCell="C2")

Grouping and Sorting

Group rows by a property and sort within each group with groupBy and groupOrder:

jx:each(items="employees" var="g" groupBy="g.salaryGroup" groupOrder="ASC" lastCell="C3")

Conditional Rendering (jx:if)

Show or hide cells based on a boolean expression:

jx:if(condition="e.payment<2000" lastCell="C2" areas=["A2:C2","A3:C3"])

Dynamic Grid (jx:grid)

Generate a table with a header area and data rows. Example:

jx:grid(headers="headers" data="items"
        areas=["A3:A3","A4:A4"]
        formatCells="BigDecimal:C1,Date:D1"
        lastCell="A4")

Merging Cells (jx:mergeCells)

Merge a range of cells. Parameters control the number of rows/columns to merge:

jx:mergeCells(cols="" rows="" minCols="" minRows="" lastCell="C2")

cols : number of columns to merge

rows : number of rows to merge

minCols : minimum columns required for the merge

minRows : minimum rows required for the merge

lastCell : cell that marks the end of the merge area

Inserting Images (jx:image)

Add an image to a worksheet. The src expression must return a byte[] containing the image data.

jx:image(src="image" lastCell="A2")

Formula Handling

Parameterized formulas use the $[ ] syntax. Example to display a tax rate as a percentage: $[${item.taxRate}/100] Set the cell style to percentage so the result is rendered as 1%.

Reference

Official JXLS site: https://jxls.sourceforge.net/