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
|
1 2 3 4 5 6 |
public class Car { private String brand; private BigDecimal price; // ... constructors // ... getters/setters } |
To use Jxls to output this object collection into an Excel we need to do the following
- Add required Jxls libraries to your project
- Create an Excel template using a special markup
- 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
|
1 2 3 4 5 |
<span class="tag"><dependency></span> <span class="tag"><groupId></span><span class="pln">org.jxls</span><span class="tag"></groupId></span> <span class="tag"><artifactId></span><span class="pln">jxls</span><span class="tag"></artifactId></span> <span class="tag"><version></span><span class="pln">2.4.1</span><span class="tag"></version></span> <span class="tag"></dependency></span> |
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
|
1 2 3 4 5 |
<span class="tag"><dependency></span> <span class="tag"><groupId></span><span class="pln">org.jxls</span><span class="tag"></groupId></span> <span class="tag"><artifactId></span><span class="pln">jxls-poi</span><span class="tag"></artifactId></span> <span class="tag"><version></span><span class="pln">1.0.13</span><span class="tag"></version></span> <span class="tag"></dependency></span> |
To use Java Excel API based transformer implementation add the following dependency
|
1 2 3 4 5 |
<dependency> <groupId>org.jxls</groupId> <artifactId>jxls-jexcel</artifactId> <version>1.0.6</version> </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.
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
|
1 2 3 4 5 6 7 8 |
List cars = generateTestCarData(); try (InputStream is = ObjectCollectionDemo.class.getResourceAsStream("excel_template.xls")) { try (OutputStream os = new FileOutputStream("output/excel.xls")) { Context context = new Context(); context.putVar("cars", cars); JxlsHelper.getInstance().processTemplate(is, os, context); } } |
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
|
1 |
JxlsHelper.getInstance().processTemplate(is, os, context); |
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
|
1 |
JxlsHelper.getInstance().processTemplateAtCell(is, os, context, "Result!A1"); |
Here the area will be processed at cell A1 of Result sheet.
The final report looks like this
Here you can download full source project – jxls-output
Recomended books:
Hello just wanted to give you a quick heads up.
The text in your content seem to be running off the screen in Opera.
I’m not sure if this is a formatting issue or something to do with web
browser compatibility but I thought I’d post to let you know.
The style and design look great though! Hope you get the issue resolved soon. Many
thanks
Hi! I will try to fix it. Thanks!