JXLS Tutorial: Export Complex Excel Files with Template-Based Java Library

Introduction

JXLS is a lightweight Java library for exporting Excel files. It solves complex export requirements such as fixed styles, merged cells, and dynamic columns that would otherwise require extensive custom code with POI or EasyExcel.

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

Maven Dependencies

<!-- Version from official Release, using 2.11.0 -->
<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>

<!-- For JavaExcelAPI-based transformer -->
<dependency>
  <groupId>org.jxls</groupId>
  <artifactId>jxls-jexcel</artifactId>
  <version>${jxlsJexcelVersion}</version>
</dependency>

Backend Code

Utility Class: JxlsUtils

public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
    Context context = new Context();
    if (model != null) {
        for (String key : model.keySet()) {
            context.putVar(key, model.get(key));
        }
    }
    JxlsHelper jxlsHelper = JxlsHelper.getInstance();
    Transformer transformer = jxlsHelper.createTransformer(is, os);
    JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig()
            .getExpressionEvaluator();
    Map<String, Object> funcs = new HashMap<String, Object>();
    funcs.put("utils", new JxlsUtils()); // Add custom functions
    evaluator.setJexlEngine(new JexlBuilder().namespaces(funcs).create());
    jxlsHelper.processTemplate(context, transformer);
}

Controller Example

@PostMapping("/export/exportTradeCreditData")
@ResponseBody
public void exportTradeCreditData(HttpServletRequest request, HttpServletResponse response, Date countDate) {
    String templatePath = "template/excel/trade_credit_data.xlsx";
    URL templateResource = PathTools.findResource(templatePath);
    try (OutputStream out = response.getOutputStream();
         InputStream templateStream = templateResource.openStream()) {
        // Query business data
        List<CindaTradeCreditDto> list = countingReportService.queryTradeCreditData(countDate);
        
        // Assemble data for template
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("year", getYear(countDate));
        map.put("contracts", list);
        JxlsUtils.exportExcel(templateStream, out, map);
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
        log.error("导出excel异常, {}", JxlsUtils.executeException(e));
    }
}

Creating Templates

Create new Excel template in xlsx format (not xls) to avoid data conversion errors

Set table styles per export requirements

Limited to simple headers, row/column width/height

Write expressions by inserting comments (right-click → Insert Comment, shortcut Shift+F2)

Define areas, data rows, merged cells, dynamic columns

XLS Expressions

jx:area

jx:area(lastCell = "H3")

Defines the rectangular parsing area in the template from A1 to the specified last cell. Must be placed in cell A1 to speed up parsing.

jx:each (Most Common)

jx:each(items="contracts" var="contract" lastCell="H3")

items : Collection variable name in context

var : Variable name for each record during iteration

area : Parsing area for this command

direction : Fill direction, default DOWN (vertical)

select : Expression to filter data

For horizontal iteration (dynamic columns):

jx:each(items="countMonths" var="month" lastCell="C3" direction="RIGHT")

jx:mergeCells

jx:mergeCells(lastCell="合并单元格范围"
[, cols="合并的列数"]
[, rows="合并的行数"]
[, minCols="要合并的最小列数"]
[, minRows="要合并的最小行数"]
)

lastCell : Merge range

cols : Number of columns to merge

rows : Number of rows to merge

minCols : Minimum columns to merge

minRows : Minimum rows to merge

Note : Only works on cells that are not already merged.

Dynamic Columns - Combined Usage

Uses jx:each with direction="RIGHT" for horizontal expansion.

jx:each(items="countMonths" var="month" lastCell="C3" direction="RIGHT")

Key parameters in the example:

countMonths : Dynamic column collection; month is the loop entity, value: ${month} contracts : Row data collection; contract, colData are loop entities, values like ${contract.custName} colData.monthData.get(month) : Dynamic column data, matched by column name to entity field

${empty()} : Checks if dynamic column data is empty, handles null safely

Dynamic column data retrieval with null handling:

${empty(colData.monthData.get(month) ) ? 0 : colData.monthData.get(month)}

Summary

The article covers the most commonly used JXLS operations for complex Excel exports: defining parse areas, iterating collections vertically and horizontally, merging cells programmatically, and handling dynamic columns with safe data access patterns.