Overview

Very often it is required to modify bean properties before placing them into excel cell. For example you may want to scale or summarize some properties without using Excel formulas. jXLS supports expression language syntax to perform achieve this.

Expression Language

Using expressions with your bean or collection properties is very easy. Instead of any bean property you may put an expression. For example if we want to calculate total chief's payment in a Department we can use an expression:

                ${department.chief.payment * ( department.chief.bonus + 1 )}
            

Applying expressions to every collection item is the same easy

                ${employee.payment * ( employee.bonus + 1 )}
            

In every Excel cell you may use as many expressions as you want. For example

    Payment:${employee.payment/10}, Total:${employee.payment * ( employee.bonus + 1 )}
            

Map Properties Access

It is easy to access properties which are placed in a Map object. For example assuming a Map is placed into bean context under 'map' key we can access individual objects using String-valued key

                ${map.get("object1").property1} + ${map.get('object2').property3}
            

JEXL Expressions Support

jXLS uses Jakarta Commons JEXL library to evaluate expressions. It means that besides standard arithmetic and boolean expressions you may also use arrays, collection references, perform object method calls and do many other things supported by JEXL. See reference on JEXL Syntax for more details. Still there are a couple of limitations imposed by jXLS on JEXL that needs to be mentioned.

• Use of the JEXL built-in "size" function does not produce the expected result because JXLS handles collections in a special way
• Functions on collections cannot be called because they are hidden by the way that JXLS handles collections

Also due to a special jXLS inner collections handling mechanism JEXL expressions for inner collections should be explicitly enabled. See the next section for details about accessing inner collections with JEXL.

To give you an example of some JEXL expressions let's assume the following code

                ...
        Map beans = new HashMap();
        SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
        beans.put("dateFormat", dateFormat);
        SampleBean obj = new SampleBean();
        beans.put("obj", obj);
        XLSTransformer transformer = new XLSTransformer();
        HSSFWorkbook resultWorkbook = transformer.transformXLS(inputStream, beans);
                ...
            

has been applied to the Bean.

Next JEXL expressions can be put in XLS template and evaluated

                ${obj.name}
                ${"Hello, World"}
                ${obj.flag == true}
                ${obj.name == null}
                ${empty(obj.collection)}
                ${obj.name.size()}
                ${!empty(obj.collection) && obj.id > 0}
                ${empty(obj.collection) || obj.id == 1}
                ${not empty(obj.collection)}
                ${obj.id > 1}
                ${obj.id == 1}
                ${obj.id eq 1}
                ${obj.id % 2}
                ${obj.myArray[0]} and ${obj.myArray[1]}
                ${dateFormat.format(obj.date)}
                ${obj.printIt()}
                ${obj.getName()}
                ${obj.echo("Hello")}
            

Using JEXL to access inner collections

Direct JEXL access to the items of inner collections is disabled by default because jXLS has its own inner collections handling mechanism. If you need this feature you have to turn it on explicitly by using a special method in XLSTransformer:

                    XLSTransformer transformer = new XLSTransformer();
                    transformer.setJexlInnerCollectionsAccess( true );
                

In this case built-in jXLS mechanism for handling inner collections will be disabled so JEXL expressions could be used to access inner collections in the following way:

obj.collection.innerCollection.get(1)

This expression would display the first item from all inner collections, each in a cell of its own. For example for an object graph that is structured like this... Sample Bean and placed into the context using

            beans.put("obj", new Bean());
            

...the expression would populate 3 cells on 3 different rows...

            |1|
            |a|
            |i|
            

Using aggregate functions

You can use aggregate functions to operate on collections. The syntax is like this

                ${Function_Name(field):collection}
            

Here Function_Name is the name of the aggregate function to apply to the field property for all the collection items.

Currently the next aggregate functions are supported:

• sum
• min
• max
• avg
• count

For example if we need to count the number of employees for a department we should use following syntax

${count(name):department.staff}

In the same way we can calculate the maximum employee payment:

                ${max(payment):department.staff}