Using JXLS for Complex Excel Export in Java

JXLS is a lightweight Java library that simplifies the generation of complex Excel files with features such as merged cells, dynamic columns, and custom styling, reducing the amount of code developers need to write.

Maven dependencies required are <dependency> <groupId>org.jxls</groupId> <artifactId>jxls</artifactId> <version>2.11.0</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-poi</artifactId> <version>2.11.0</version> </dependency> <!-- Add jxls-jexcel for JavaExcelAPI based transformer --> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-jexcel</artifactId> <version>${jxlsJexcelVersion}</version> </dependency> .

Backend utility code includes a JxlsUtils.exportExcel method that creates a Context , registers custom functions, and processes the template with JxlsHelper . The controller example shows how to load an Excel template, assemble data into a map, and invoke the utility to stream the generated file to the client.

Template creation guidelines: use an .xlsx workbook, set basic styles for headers and rows, add comments for JXLS expressions, and define areas for data rows, merged cells, and dynamic columns.

Common JXLS expressions :

jx:area(lastCell="H3") – defines the rectangular area to be processed.

jx:each(items="contracts" var="contract" lastCell="H3") – iterates over a collection, filling rows downward.

jx:each(items="countMonths" var="month" lastCell="C3" direction="RIGHT") – iterates horizontally for dynamic columns.

jx:mergeCells(lastCell="合并单元格范围" [, cols="..."] [, rows="..."] [, minCols="..."] [, minRows="..."]) – merges cells that have not been merged yet.

Dynamic column handling combines jx:each with the RIGHT direction and uses expressions like ${empty(colData.monthData.get(month)) ? 0 : colData.monthData.get(month)} to safely output values.

The article concludes with a brief recap of the most frequently used JXLS operations for exporting complex Excel reports.