jXLS Integration for Mule 2
jXLS is small and easy-to-use Java library for generating Excel files using XLS templates. Also jXLS can be used to read XLS files and populate Java beans with spreadsheet data according to XML configuration file. The excel module provides two basic transformers in order to read and write excel files by using the jXLS library.
All mentioned examples use the following Mule header, with the new namespace for the Excel Module:
<mule xmlns="http:
xmlns:xsi="http:
xmlns:xls="http:
xsi:schemaLocation="
http: http: http:
BeanToXlsTransformer
The first transformer concentrates on writing XLS file by using a template and a map of java beans. A XLS template file can contain required formatting, formulas, macros etc using specific notation to indicate placement of data. When exporting data from your java application, using the template, the specific notation will be used to populate your data representing as java beans. A full documentation can be found on http://jxls.sourceforge.net
Let's consider a simple domain model consists of a Department and Employees.
public class Department implements java.io.Serializable{
private String name;
private Employee chief;
private List<Employee> staff = new ArrayList<Employee>();
...
}
public class Employee implements java.io.Serializable {
private String name;
private int age;
private Double payment;
private Double bonus;
private Date birthDate;
private Employee superior;
...
}
If you want to put the name of an employee in your excel template, just simple write the specific notations in your cell.
After defining a valid template the transformer can be set up. The input of the transformer must be a Map of beans containing the data, which should be populated in the XLS file. The transformer needs the template to be used to generate the final XLS file and returns a bate[].
<xls:bean-to-xls-transformer name="BeanToXlsTransformer" template="src/test/resources/template.xls"/>
XlsToBeanTransformer
The second transformer can be used to read XLS documents and map the document to one or more Java beans by using a mapping file. This transformer uses the XLS Reader from the jXLS libraray. In order to map data from the XLS document to Java Beans a mapping file is required. In this example we map a excel list containing a list of employees:
and the mapping file. The loop in the mapping file enables us to iterate over a given number of rows in the file. For each row a new instance of the Employee object will be created. For a full description about the mapping elements, please refer to XLS Reader documentation.
<workbook>
<worksheet name="Sheet1">
<section startRow="0" endRow="1" />
<loop startRow="2" endRow="2" items="employees" var="employee"
varType="org.mule.providers.xls.test.model.Employee">
<section startRow="2" endRow="2">
<mapping row="2" col="0">employee.name</mapping>
<mapping row="2" col="1">employee.age</mapping>
<mapping row="2" col="2">employee.payment</mapping>
<mapping row="2" col="3">employee.bonus</mapping>
</section>
<loopbreakcondition>
<rowcheck offset="0">
<cellcheck offset="0">
Employee Payment Totals:
</cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>
After defining the mapping file, we can set up our transformer. The transformer accepts a File, InputStream and a byte[]. The transformer to the mapping work for you. Therefore you must first specifiy the mapping file that should be used by the transformer. The result of the transformer is a map of mapped beans, defining by the property mappingBeans. In this property the key will be used to get the mapped bean from the map. The value defines the type. In our case we have a list of employees and map this list in a Array List.
<xls:xls-to-bean-transformer name="XlsToBeanTransformer" mappingFile="src/test/resources/xlsMapping.xml">
<xls:mapping-bean key="employees" value="java.util.ArrayList"/>
</xls:xls-to-bean-transformer>
This page
Other pages
Account
