It's hard to use Apache POI as it is, so when I was looking for a good one, Jxls was good. What is good is that you can easily output data with a tree structure. However, when I searched for it, it seemed that there were few Japanese documents, so I translated it. I would appreciate it if you could point out any strange points.

This article is a translated article of here. We are translating the 2020/3/30 version of the document.

introduction

JXLS is a small Java library for easily generating Excel forms. The format of the form output by JXLS and the layout of the data are defined by writing the original syntax in the Excel template. Excel generation has become indispensable for Java applications such as the so-called form function.

Java has a very good library for Excel file output, both open source and commercial. Among the open source ones, Apache POI and Java Excel API are worth mentioning.

However, those libraries are very low level (primitive), and even if you want to output Excel with a simple structure, you have to write a lot of source code.

Normally, with those libraries, you have to manually format and data each cell. And as the layout of forms and the formatting of data become more complex, so does the source code, making debugging and maintenance more difficult. On top of that, not all Excel features are supported, so not all can be handled via the API. (For example, macros and graphs cannot be used.) For such unsupported functions, you can embed an object in an Excel template by yourself and then write data using the API.

Jxls, on the other hand, enables Excel form generation at a higher level. All you have to do to generate an Excel form with JXLS is to embed the form format and data formatting in a template and then flow the data into the template with the JXLS engine. In most cases of generating Excel forms, no source code writing is required other than declaring the JXLS engine and writing the appropriate settings.

Feature

--XML and binary format Excel form output (depends on low-level JAVA-Excel conversion API) --Convert Java collection class to row or column and output --Conditional result output --Markup language for defining forms --Multiple sheet output --Embed Excel formula --Formula with parameters --Supports grouping (* Data output for each group. Separate from grouping of Excel functions) --Supports cell merging --Area listener that supports Excel generation --Markup syntax to write in Excel comments for command definition --XML syntax for command definition --Custom command definition --Supports Streaming, which reduces memory consumption and enables faster output processing --It is possible to specify that only the selected sheet is handled by Streaming. --Supports table connection

Getting Started Guide

First, let's consider the case where you want to output the Employee object to an Excel form. The Employee class looks like this.

public class Employee {
    private String name;
    private Date birthDate;
    private BigDecimal payment;
    private BigDecimal bonus;
    // ... constructors
    // ... getters/setters
}

In order to output this object to Excel using JXLS, it is necessary to perform the following series of steps.

Add the required libraries for JXLS to your project
Create an Excel template using a special syntax
Perform the process of embedding Employee data in the template prepared using the JXLS API.

Let's take a closer look at each of these steps in the next chapter.

Add the required libraries for JXLS to your project

The easiest way is to use Maven to specify the required libraries in the config file. The Jxls jar is available in Maven's Central repository. The core module of Jxls depends on the following libraries.

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.8.0</version>
</dependency>

Another option is to download it from the SourceForge site and use the jar.

In addition to the Jxls core module, we need to add a dependency on the Jxls transformer engine, which is the basis of all the functionality to perform Java to Excel conversion operations.

As explained in the Transformer chapter (see main concept), Jxls core modules do not depend on any particular Java --exel library. However, these days Jxls provides independent interfaces for each of the Apache POI and Java Excel API libraries. ..

To use the Apache POI-based transducer, add the following dependency to pom.xml.

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

To use a converter based on the Java Excel API, add the following dependencies.

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-jexcel</artifactId>
    <version>${jxlsJexcelVersion}</version>
</dependency>

Create an Excel template

A template is an Excel file that uses its own syntax to specify how data is output.

Jxls has a built-in markup processing mechanism that can be interpreted from an Excel template to extract control commands.

Custom markup processing mechanisms can be created as needed. You can define your own syntax and embed it in an Excel template to create a Jxls command structure.

The following describes the built-in syntax handling mechanism.

By default, Jxls supports Apache JEXL. Apache JEXL is a language used in Excel templates to refer to the methods and properties of Java objects. Under a key, the object is available in the Jxls context. To print the Employee's name to the cell, just write $ {employee.name} in the cell. Basically, Jxls is also full of Jexl-like expressions $ { and }. By looking at the syntax, you can infer that the Employee object is behind the ʻemployee` key.

The property notation method can be set. For example, you can set it by using [[employee.name]] as the property notation. For more information, see Expression language (http://jxls.sourceforge.net/reference/expression_language.html).

The Sample Template for outputting the Employee object will be as follows in the end.

The fourth line of this template uses the JEXL syntax described earlier to reference the properties of the employee object.

Cell A1 contains an Excel comment such as "jx: area (lastCell =" D4 ")". This comment defines that the top-level template area of Jxls is in the range A1: D4.

In the A4 cell comment, Jxls Eaxh-Command is defined as "jx: each (items = "employees" var = "employee" lastCell = "D4")". This Each-Command retrieves a set of objects using ʻemployees as a key, and places individual data in the ʻemployee key (defined by the var attribute). The scope of each command is A4: D4, and it will be duplicated every time a new Employee object appears in the context.

This example uses the XlsCommentAreaBuilder class to build the Jxls area. This class can be defined by writing the Jxls command to an Excel cell comment. If it is preferable to define it in Java source code, you can use the cell without comments as a template.

Use Jxls API to process templates

Here is an example of using the Jxls API to process an Excel template.

...
    logger.info("Running Object Collection demo");
    List<Employee> employees = generateSampleEmployeeData();
    try(InputStream is = ObjectCollectionDemo.class.getResourceAsStream("object_collection_template.xls")) {
        try (OutputStream os = new FileOutputStream("target/object_collection_output.xls")) {
            Context context = new Context();
            context.putVar("employees", employees);
            JxlsHelper.getInstance().processTemplate(is, os, context);
        }
    }
...

In this example, the template (object_collection_template.xls) is specified and read from the classpath. And the generated Excel file is written to target / object_collection_output.xls.

The main process is represented by the following line.

JxlsHelper.getInstance().processTemplate(is, os, context);

By default JxlsHelper expects to overwrite data in the template sheet.

However, it is also possible to write new data to another sheet with the following method.

JxlsHelper.getInstance().processTemplateAtCell(is, os, context, "Result!A1");

In this example, the area is written to cell ʻA1 on the Result` sheet.

The final form generated in this example can be downloaded from here. It looks like this:

reference

Concept overview

Jxls is based on the following concept.

XlsArea
Command
Transformer

Let's take a closer look at each feature.

Xls area

The Xls area refers to the rectangular area drawn in the Excel file. Specifically, it is specified by specifying the cell range and the starting cell and size (number of rows and columns). May be done. That is, the Xls area contains all cells in the specified range.

Each XlsA area can have a set of commands that the Jxls engine will execute as it processes the area. And you can have child areas nested in the Xls area. And each child area, like its parent, can have commands and grandchild areas.

Xls areas can be defined in three ways: --How to write in the Excel template with a dedicated syntax: Jxls provides the syntax corresponding to XlsCommandAreaBuilder by default. You can also provide custom-defined syntax if you wish. --How to use XML definition file: Jxls provides XmlAreaBuilder class for XML syntax interpretation. --How to use Jxls Java API

Here is a sample of the definition of the Xls area.

Command

A command refers to a conversion action for one or more Xls areas. The corresponding Java interface is as follows.

public interface Command {
    String getName();
    List<Area> getAreaList();
    Command addArea(Area area);
    Size applyAt(CellRef cellRef, Context context);
    void reset();
}

The main method of the command is Size applyAt (CellRef cellRef, Context context). This method passes the data passed in the context variable to the cell whose command action is passed in the cellRef variable. The Context can be treated like a map and is used to pass data to commands.

Jxls provides the following built-in commands. --Each-Command: Command executed while iterating over list format data --If-Command: A command that executes processing under specific conditions --Image-Command: Command to render the image --MergeCells-Command --Merge cells

You can also easily define your own customized commands. The process of passing data to the command is realized by the Context object. The Context works like a Map. From the XLS template, it is referenced based on the key information, and the associated value is set as data.

Tramsformer The Transformer interface allows the Xls area to work with Excel independently of any particular framework. That is, you can use a conversion interface with a different Java-to-Excel framework. The interface is used in the following form.

public interface Transformer {
    void transform(CellRef srcCellRef, CellRef targetCellRef, Context context, boolean updateRowHeight);

    void setFormula(CellRef cellRef, String formulaString);

    Set<CellData> getFormulaCells();

    CellData getCellData(CellRef cellRef);

    List<CellRef> getTargetCellRef(CellRef cellRef);

    void resetTargetCellRefs();

    void resetArea(AreaRef areaRef);

    void clearCell(CellRef cellRef);

    List<CellData> getCommentedCells();

    void addImage(AreaRef areaRef, byte[] imageBytes, ImageType imageType);

    void write() throws IOException;

    TransformationConfig getTransformationConfig();

    void setTransformationConfig(TransformationConfig transformationConfig);

    boolean deleteSheet(String sheetName);

    void setHidden(String sheetName, boolean hidden);

    void updateRowHeight(String srcSheetName, int srcRowNum, String targetSheetName, int targetRowNum);

At first glance, it looks like a lot of methods have appeared, but most of them are already implemented in the underlying abstract class ʻAbstractTransformer`. Then, if necessary, you can inherit from it and implement a new java-to-excel.

Currently, Jxls provides the following two Transformers.

PoiTransformer
JexcelTransformer

PoiTransformer deals with the famous Apache POI Excel file output library. Note that JexcelTransformer is based on the old Java Excel API library.

POI Transformer PoiTransformer is an implementation of the Transformer interface based on Apache POI.

This is a built-in Excel transformer from the jxls-poi module.

It supports both POI-HSSF and POI-XSSF / SXSSF workbooks and has multiple constructors that allow you to create streaming or non-streaming transform instances from POI workbooks or from template input streams.

In addition, you can ignore the row height and column width of the source cell during cell conversion. This is achieved with the setIgnoreRowProps (boolean) and setIgnoreColumnProps (boolean) methods.

XLS Area Introduction The XLS area is the main concept of Jxls Plus. It basically represents a rectangular area in an Excel file that needs to be converted. Each XLS region can have a list of conversion commands associated with it and a set of nested child regions. Each child region is also an XLS region with its own command and a set of nested regions. A top-level XLS region is one that has no parent region (it is not nested in any other XLS region).

Create an XLS Area

There are three ways to define an XLS area:

--Markup to Excel --Defined by XML --Use Java API

Let's explain each method in detail.

Excel markup for building XLS areas

You can use special markup in the Excel template to build the XLS area. The markup should be placed in the Excel comment in the first cell of the area. The markup looks like this:

jx:area(lastCell = "<AREA_LAST_CELL>")

Where <AREA_LAST_CELL> is the last cell in the defined area.

This markup defines a top-level area that starts with the cell with the markup comment and ends with <AREA_LAST_CELL>.

To see an example, let's take a look at the Output Object Collection sample template.

In the comment in cell A1, the area is defined as follows:

jx:area(lastCell="D4")

So here we have an area that covers the A1: D4 cell range.

To parse the markup and create an XlsArea object, you need to use the XlsCommentAreaBuilder class as follows:

// getting input stream for our report template file from classpath
InputStream is = ObjectCollectionDemo.class.getResourceAsStream("object_collection_template.xls");
// creating POI Workbook
Workbook workbook = WorkbookFactory.create(is);
// creating JxlsPlus transformer for the workbook
PoiTransformer transformer = PoiTransformer.createTransformer(workbook);
// creating XlsCommentAreaBuilder instance
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
// using area builder to construct a list of processing areas
List<Area> xlsAreaList = areaBuilder.build();
// getting the main area from the list
Area xlsArea = xlsAreaList.get(0);

The following two lines of code perform the main process of creating a Jxls Area.

AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
List<Area> xlsAreaList = areaBuilder.build();

First, instantiate XlsCommentAreaBuilder to build an instance of AreaBuilder. Then call the ʻareaBuilder.build ()` method to create a list of Area objects from the template.

Once you have a list of top-level areas, you can use them for Excel conversion.

XML settings for building XLS regions

If you want to define an XLS region in XML markup, here are some ways to do it:

First, you need to create an XML setting that defines the region.

As an example, consider a simple XML configuration from the output of an object collection using XML Builder.

<xls>
    <area ref="Template!A1:D4">。
        <each items="employees" var="employees" ref="Template!
            <area ref="Template!A4:D4"/>
        </each>
    </area>
</xls>

The root element is xls. Next, list some of the area elements that define each top-level area.

Here, the Template sheet has one top-level area called A1: D4.

<area ref="Template!A1:D4"> Within this area, you use special elements for specific commands to define related commands. In this case, each command is defined in each xml element. The area associated with each command is indicated by the ref attribute.

<each items =" employees "var =" employees "ref =" template! A4: D4 ">. Within each command is a nested region parameter.

<area ref="template!

Build an XLS Area with Java API

To create an XLS area using the Java API, you can use one of the constructors of the XlsArea class. The following constructors are available.


 public XlsArea(AreaRef areaRef, Transformer transformer);

 public XlsArea(String areaRef, Transformer transformer);

 public XlsArea(CellRef startCell, CellRef endCell, Transformer transformer);

 public XlsArea(CellRef startCellRef, Size size, List<CommandData> commandDataList, Transformer transformer);

 public XlsArea(CellRef startCellRef, Size size);

 public XlsArea(CellRef startCellRef, Size size, Transformer transformer);

To build a top-level realm, you must provide a Transformer instance so that the realm can be transformed.

Then you have to define the cells in the area by using the cell range as a string or by creating a CellRef cell reference object and setting the area Size.

Below is part of the code that creates a set of nested XLS template regions with a command.


// create Transformer instance
// ...
// Create a top level area
XlsArea xlsArea = new XlsArea("Template!A1:G15", transformer);
// Create 'department' are
XlsArea departmentArea = new XlsArea("Template!A2:G13", transformer);
// create 'EachCommand' to iterate through departments
EachCommand departmentEachCommand = new EachCommand("department", "departments", departmentArea);
// create an area for employee 'each' command
XlsArea employeeArea = new XlsArea("Template!A9:F9", transformer);
// create an area for 'if' command
XlsArea ifArea = new XlsArea("Template!A18:F18", transformer);
// create 'if' command with the specified areas
IfCommand ifCommand = new IfCommand("employee.payment <= 2000",
        ifArea,
        new XlsArea("Template!A9:F9", transformer));
// adding 'if' command instance to employee area
employeeArea.addCommand(new AreaRef("Template!A9:F9"), ifCommand);
// create employee 'each' command and add it to department area
Command employeeEachCommand = new EachCommand( "employee", "department.staff", employeeArea);
departmentArea.addCommand(new AreaRef("Template!A9:F9"), employeeEachCommand);
// add department 'each' command to top-level area
xlsArea.addCommand(new AreaRef("Template!A2:F12"), departmentEachCommand);

SimpleExporter Introduction You can use the SimpleExporter class to export a list of objects to Excel with a single line of code.

This is achieved by using a special built-in template that includes a GridCommand.

how to use

Simply create a SimpleExporter instance and run its gridExport method.


new SimpleExporter().gridExport(headers, dataObjects, propertyNames, outputStream);

--headers --Collection of headers --dataObjects --Collection of data objects --propertyNames --A comma-separated list of object properties. --outputStream --Output stream for writing the final Excel.

See the example in SimpleExporter (http://jxls.sourceforge.net/samples/simple_exporter.html).

Custom Template To register the template used by SimpleExporter, use its registerGridTemplate method.


public void registerGridTemplate(InputStream templateInputStream)

The template must have [Grid Command] defined (http://jxls.sourceforge.net/reference/grid_command.html).

See the example in SimpleExporter for how to do this.

Transformers support notes

GridCommand is currently only supported by POI transformers, so you should use POI when working with SimpleExporter.

Excel mark-up

In Jxls, there are three types of markup syntax that can be used in Excel.

--Bean property markup --Area definition markup --Command definition markup

Jxls provides an XlsCommentAreaBuilder class that can read markup from Excel cell comments. XlsCommentAreaBuilder implements the general AreaBuilder interface.

public interface AreaBuilder {
    List<Area> build();
}

This is a simple interface with a single method that returns a list of area objects.

Therefore, if you want to define your own markup, you can create your own implementation of AreaBuilder to interpret the entered Excel template (or any other input) as you like.

Bean property markup

jxls processes using the Apache JEXL expression language.

In a future release, the formula language engine will be configurable so that JEXL can be replaced with other formula engines as needed.

The syntax of the JEXL expression language is described here.

Jxls expects to place the JEXL expression inside the $ {} of the XLS template file.

For example, the following cell content $ {department.chief.age} years tells JEXL to evaluate department.chief.age, assuming there is a department object in the context. For example, if the expression department.getChief (). GetAge () evaluates to 35, Jxls puts 35 years in the cell while processing the XlsArea.

XLS area definition markup

The Jxls area markup is used to define the root XlsArea that the Jxls engine processes. XlsCommentAreaBuilder supports the following syntax for defining areas as Excel cell commands.

jx:area(lastCell="<LAST_CELL>")

Here, <last_cell> defines the lower right cell of the rectangular area. The first cell is defined by the cell where the Excel comment is placed.

That is, assuming cell A1 has the following comment jx: area (lastCell = "G12"), the root area is read as A1: G12.

You must use XlsCommentAreaBuilder to load all the space from the template file. For example, the following code snippet loads all areas into the xlsAreaList and saves the first area in the xlsArea variable.

    AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
    List<Area> xlsAreaList = areaBuilder.build();
    Area xlsArea = xlsAreaList.get(0);

In most cases it is enough to define a single root XlsArea.

Command markup

The command must be defined within the XlsArea. XlsCommentAreaBuilder accepts the following command notation created as a comment in an Excel cell.

jx:<command_name>(attr1='val1' attr2='val2' ... attrN='valN' lastCell=<last_cell> areas=["<command_area1>", "<command_area2", ... "<command_areaN>"])

<command_name> is the command name pre-registered or manually registered with XlsCommentAreaBuilder. Currently, the following command names are pre-registered.

each
if
image

Custom commands can be registered manually using the static void addCommandMapping (String commandName, Class clazz) method of the XlsCommentAreaBuilder class.

ʻAttr1, attr2, ..., attrN` are command-specific attributes.

For example, If-Command has a conditional attribute for setting a conditional expression.

<last_cell> defines the lower right cell of the command body area. The upper left cell is determined by the cell with the command notation.

<command_area1>, <command_area2>, ... <command_areaN> --The XLS area to pass as a parameter to the command.

For example, If-Command expects to define the following areas:

ʻIfArea is a reference to the area to output when the ʻIf-command condition evaluates to true. ʻElseArea is an optional reference to the area to output when the ʻIf-command condition evaluates to false. That is, to define an area for the If command, its area attributes are:

areas=["A8:F8","A13:F13"]

You can define multiple commands in one cell comment. For example, the definitions of the "each" and "what if" commands are as follows:

jx:each(items="department.staff", var="employee", lastCell="F8")
jx:if(condition="employee.payment <= 2000", lastCell="F8", areas=["A8:F8","A13:F13"])

Expression Language

Overview

By default, Jxls uses the Apache JEXL expression language to evaluate property expressions specified in Excel template files.

See the JEXL syntax reference for what expressions you can use.

Customize the processing of Jexl

If you need to customize the Jexl process, you can get a reference from Transformer to JexlEngine and apply the required settings.

For example, the following code registers a custom Jexl function in the demo namespace.

     Transformer transformer = TransformerFactory.createTransformer(is, os);
     ...
     JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
     Map<String, Object> functionMap = new HashMap<>();
     functionMap.put("demo", new JexlCustomFunctionDemo());
     evaluator.getJexlEngine().setFunctions(functionMap);

Suppose you have a method in JexlCustomFunctionDemo like this:

public Integer mySum(Integer x, Integer y){
    return x + y;
}

Then, the following functions can be used on the template.

 ${demo:mySum(x,y)}

By the way, x and y here are the parameters passed from Context.

See JexlCustomFunctionDemo.java for an example implementation.

Expression engine changes

You may want to use another expression processing engine instead of Apache JEXL (eg Spring Expression Language (SpEL). Use reference / html / expressions.html), etc.).

Jxls allows you to substitute your preferred evaluation engine with your own.

To do this, simply implement one method of the ExpressionEvaluator interface and delegate the expression evaluation process to your favorite engine.

public interface ExpressionEvaluator {
    Object evaluate(String expression, Map<String,Object> context);
}

Then you need to pass the ExpressionEvaluator implementation to TransformationConfig, as shown in the code below.

 ExpressionEvaluator evaluator = new MyCustomEvaluator(); // your own implementation based for example on SpEL
  transformer.getTransformationConfig().setExpressionEvaluator(evaluator);

Each-Command

Introduction

Each-Command is used to iterate over the collection and clone the XLS area of the command. This is a Java analog of operator.

Command attributes

Each command has the following attributes:

--var is the variable name in the Jxls context to put each new collection item in the iteration. --items is the name of the context variable that contains the collection (Iterable <?>) Or array to iterate over. --area is a reference to the XLS area used as the body of each command. --direction is the value of the Direction enumeration, which can have a value of DOWN or RIGHT, and indicates how to repeat the command body (row or column). The default value is DOWN. --select is an expression selector for filtering collection items during iterations. --groupBy is a property for grouping. --groupOrder indicates the order of the groups ('desc' or'asc'). --orderBy contains comma-separated names and the optional prefix "ASC" (default) or "DESC" for sort order. --cellRefGenerator is a custom strategy for creating target cell references. --multisheet is the name of the context variable that contains the list of sheet names that output the collection. --lastCell is a common attribute of commands that points to the last cell in the command area.

The var attribute and item attribute are required, but other attributes can be omitted.

Check the Multiple Sheets section for more information on using cellRefGenerator and the multi-sheet attribute.

Command construction

As with any Jxls command, you can define each command using the Java API, Excel markup, and XML settings.

How to use Java API

Below is an example of creating Each-Command in the package org.jxls.examples.

// creating a transformer and departments collection
    ...
// creating department XlsArea
    XlsArea departmentArea = new XlsArea("Template!A2:G13", transformer);
// creating Each Command to iterate departments collection and attach to it "departmentArea"
    EachCommand departmentEachCommand = new EachCommand("department", "departments", departmentArea);

How to use Excel markup

To create each command in Excel markup, you must use a special syntax for commenting the start cell in the command body area.

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

The lastCell attribute defines the last cell in the body of the command XlsArea.

XML markup usage Use the following markup to create each command in the XML settings.

<each items="employees" var="employee" ref="Template!A4:D4">
    <area ref="Template!A4:D4"/>
</each>

Here, the ref attribute defines the area associated with each command. And the inner area defines the body of each command. Usually the same.

Loop direction

By default, the direction attribute of each command is set to DOWN, which means that the command body is duplicated down the Excel line.

If you need to duplicate the area for each column, you need to set the orientation attribute to the RIGHT value.

In the Java API:

//... creating EachCommand to iterate departments
// setting its direction to RIGHT
departmentEachCommand.setDirection(EachCommand.Direction.RIGHT);

Grouping data

Each command supports grouping using the groupBy property. The groupOrder property sets the order and can be desc or asc. If you write groupBy without specifying groupOrder, no sorting will be done.

The Excel markup looks like this:

jx:each(items="employees" var="myGroup" groupBy="name" groupOrder="asc" lastCell="D6")

In this example, each group can be referenced using the myGroup variable accessible from the Context.

The current group item can be referenced using myGroup.item. That is, to see the employee's name

For $ {myGroup.item.name} All items in the group can be accessed from the group's item properties.

jx:each(items="myGroup.items" var="employee" lastCell="D6") You can also skip the var attribute altogether, in which case the default group variable name will be _group.

See here for other examples.

Language overview

By default, Jxls uses the Apache JEXL expression language to evaluate property expressions specified in Excel template files.

See the JEXL syntax reference for what expressions you can use.

Customize the processing of Jexl

If you need to customize the Jexl process, you can get a reference from Transformer to JexlEngine and apply the required settings.

For example, the following code registers a custom Jexl function in the demo namespace.

     Transformer transformer = TransformerFactory.createTransformer(is, os).
     ...
     JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator().
     Map<String, Object> functionMap = new HashMap<>().
     functionMap.put("demo", new JexlCustomFunctionDemo()).
     evaluator.getJexlEngine().setFunctions(functionMap)。

Now for the JexlCustomFunctionDemo class

public Integer mySum(Integer x, Integer y){
    return x + y;
}

Therefore, you can use the following functions in the template.

${demo:mySum(x,y)} Where x and y point to the parameters from the Context.

See JexlCustomFunctionDemo.java for a full implementation sample.

Expression engine changes

You may want to use another expression processing engine instead of Apache JEXL (for example, using Spring Expression Language (SpEL)).

Jxls allows you to substitute your preferred evaluation engine with your own.

To do this, simply implement one method of the ExpressionEvaluator interface and delegate the expression evaluation process to your favorite engine.

public interface ExpressionEvaluator {
    Object evaluate(String expression, Map<String,Object> context);
}

Then you need to pass the ExpressionEvaluator implementation to TransformationConfig, as shown in the code below.

  ExpressionEvaluator evaluator = new MyCustomEvaluator(); // your own implementation based for example on SpEL
  transformer.getTransformationConfig().setExpressionEvaluator(evaluator);

Each-Command Each-Command is used to iterate over the collection and clone the XLS area of the command. This is a Java analog of operator.

Command Attribute

Each command has the following Atttibute:

The var and item attributes are required, but other attributes can be omitted.

Check the Multiple Sheets section for more information on using cellRefGenerator and the multi-sheet attribute.

Command construction

As with any Jxls command, you can define each command using the Java API, Excel markup, and XML settings. Below is an example of creating Each-Command in the package org.jxls.examples.

// creating a transformer and departments collection
    ...
// creating department XlsArea
    XlsArea departmentArea = new XlsArea("Template!A2:G13", transformer);
// creating Each Command to iterate departments collection and attach to it "departmentArea"
    EachCommand departmentEachCommand = new EachCommand("department", "departments", departmentArea);

How to use Excel markup

To create each command in Excel markup, you must use a special syntax for commenting the start cell in the command body area.

<each items="employees" var="employee" ref="Template!A4:D4">
    <area ref="Template!A4:D4"/>
</each>

XML markup usage

Use the following markup to create each command in the XML settings.

<each items = "employee" var = "employee" ref = "Template! <area ref = "template! Here, the ref attribute defines the area associated with each command. And the inner area defines the body of each command. Usually the same.

Oriented repeat

By default, the direction attribute of each command is set to DOWN, which means that the command body is duplicated above an Excel line. If you need to duplicate the area for each column, you need to set the orientation attribute to the RIGHT value. In the Java API:

//... creating EachCommand to iterate departments
// setting its direction to RIGHT
departmentEachCommand.setDirection(EachCommand.Direction.RIGHT);

Grouping data

Each command supports grouping using the groupBy property. The groupOrder property sets the order and can be desc or asc. If you write groupBy without specifying groupOrder, no sorting will be done.

The Excel markup looks like this:

jx:each(items="employees" var="myGroup" groupBy="name" groupOrder="asc" lastCell="D6")

In this example, each group can be referenced using the context-accessible myGroup variable.

The current group item can be referenced using myGroup.item. To refer to the employee name

For $ {myGroup.item.name} All items in the group can be accessed from the group's item properties.

jx:each(items="myGroup.items" var="employee" lastCell="D6")

You can also skip the var attribute altogether, in which case the default group variable name will be _group.

See Grouping Example (http://jxls.sourceforge.net/samples/grouping_example.html) for an example.

This is the Excel output in Java! "JXLS" official document translation

introduction

Feature

Getting Started Guide

Add the required libraries for JXLS to your project

Create an Excel template

Use Jxls API to process templates

reference

Concept overview

Xls area

Create an XLS Area

Excel markup for building XLS areas

XML settings for building XLS regions

Build an XLS Area with Java API

how to use

Transformers support notes

Bean property markup

XLS area definition markup

Command markup

Overview

Customize the processing of Jexl

Expression engine changes

Introduction

Command attributes

Command construction

How to use Java API

How to use Excel markup

Loop direction

Grouping data

Language overview

Customize the processing of Jexl

Expression engine changes

Command Attribute

Command construction

How to use Excel markup

XML markup usage

Oriented repeat

Grouping data