Output data to excel file by template with jxls

Let’s assume we have a Java collection of Car objects that we want to output into Excel. The Car class may look like this

To use Jxls to output this object collection into an Excel we need to do the following

  1. Add required Jxls libraries to your project
  2. Create an Excel template using a special markup
  3. Use Jxls API to process the prepared template and fill it with the car data

Let’s look at each of these steps in detail.

Adding Jxls libraries to the project

The easiest way to add Jxls libraries to your project is to use Maven and specify the required libraries in your project build configuration file.

Jxls jars are available in the Central Maven repository.

We need to add the following dependency to core Jxls module

Alternatively you can download Jxls distribution from the Sourceforge site and use the jars from the distribution.

Besides the dependency to core Jxls module we need to add a dependency to an implementation of Jxls transformer engine which will execute all the underlying Java to Excel manipulations.

As it is explained in Transformers section (see Main Concepts)) Jxls core module does not depend on any specific Java-Excel library and works with Excel exclusively through a predefined interface. Currently Jxls supplies two implementations of this interface in separate modules based on the well-known Apache POI and Java Excel API libraries.

To use Apache POI API based transformer implementation add the following dependency

To use Java Excel API based transformer implementation add the following dependency

Creating Excel template

A template is an excel file which uses a special markup to specify how Jxls should output the data.

Jxls provides some built-in markup processors which can be used to parse an excel template and extract control commands.

A custom markup processor can be created if needed. So one define his own markup for an excel template and parse in a proper way to create Jxls Commands structure.

Let’s look at the built-in Jxls markup processors.

By default Jxls supports Apache JEXL as an expression language that can be used in an excel template to refer to java object properties and methods. The object must be available in Jxls context under a certain key. To output the car brand in a cell we can put the following text in the cell ${car.name} . Basically we just surrounded Jexl expression with ${  and } . We assume that in the context there is an Car object under the car key.

The property notation is configurable so you may decide to use for example [[car.brand]] as a property notation. See Expression Language for more details on how to do it.

Excel file template
Excel file template

In the template cells in row 4 we refer to the car object properties using JEXL expressions as described above.

Cell A1 contains an excel comment with the following text jx:area(lastCell="B4") . It defines the root area of our template to be A1:B4 .

A comment to A4 cell defines Jxls Each-Command with the following comment text jx:each(items="cars" var="car" lastCell="B4") . The Each-Command will iterate the collection of objects under cars  key in Jxls context and place each individual collection item into the context under car  key (defined by var  attribute). The body area of the Each-Command is A4:B4  (defined by the lastCell attribute) and it will be cloned and processed with each new Car object in the context.

This example assumes usage of XlsCommentAreaBuilder class to construct Jxls areas from the template. By using this class you can define Jxls commands in Excel cell comments. If you prefer to define the commands in Java code then the template will be the same except you have to remove the comments from the cells.

Use Jxls API to process the template

Here you can see how to use Jxls API to process the excel template

In this example we are loading the template from the classpath resource excel_template.xls. And the target excel file will be written to output/excel.xls.

All the main processing is performed in a single line

By default JxlsHelper assumes that you want to override the template sheet with the data.

But you may also choose to generate the data at another sheet by using the following method

Here the area will be processed at cell A1 of Result sheet.

The final report looks like this

Final result
Final result

Here you can download full source project – jxls-output

Related Post

2 thoughts on “Output data to excel file by template with jxls”

Leave a Reply

Your email address will not be published. Required fields are marked *