Introduction

This section specifies how to export collections with jXLS in a simplest way. Currently it is recommended to use <jx:forEach> tag to export collections. See Using tags section for details. Another option is to export every collection item into a separate excel worksheet. See Dynamic sheets section for details.

We assume that we have two dependent Java beans of type Department and Employee which are passed to XLSTransformer in a code like this

				        Department department;
				        ... //initialization
				        Map beans = new HashMap();
				        beans.put("department", department);
				        XLSTransformer transformer = new XLSTransformer();
				        transformer.transformXLS(xlsTemplateFileName, beans, outputFileName);
            

Collection Property Access

Collection properties can be specified in the same way as ordinary properties but will be processed differently. Usually we don' know the size of collection in the time we create XLS template and it should be determined during execution of application. jXLS engine will iterate specified collection and insert a new row for each collection item preserving original row style. All related collection properties in these rows will be set to corresponding collection item property values. All other cells will be processed as usual. For example, to access the names and the payment of all employees in the staff of some department we can write XLS template in the way

A B
1 Employee Name Employee Payment
2 ${department.staff.name} ${department.staff.payment}

We'll get an output like

A B
1 Employee Name Employee Payment
2 John 3000
2 Neil 2000
2 Oleg 2500

Fixed Size Collections

If you know the size of exported collection you may want not to create new row for every collection item but to export all collection to the rows already presented in XLS template. This can be done using fixed size collection feature.

In this case the template should be as follows (assuming employee is a collection with 3 items)

A B
1 Employee Name Employee Payment
2 ${employee.name} ${employee.payment}
3
4

To indicate that employee is a fixed size collection we have to invoke markAsFixedSizeCollection method of XLSTransformer.

			        List staff = new ArrayList();
			        staff.add(new Employee("Derek", 35, 3000, 0.30));
			        staff.add(new Employee("Elsa", 28, 1500, 0.15));
			        staff.add(new Employee("Oleg", 32, 2300, 0.25));
			        Map beans = new HashMap();
			        beans.put("employee", staff);
			        XLSTransformer transformer = new XLSTransformer();
			        transformer.markAsFixedSizeCollection("employee");
			        transformer.transformXLS(templateFileName, beans, destFileName);
            

For the code and template above we will get an output of employee collection into the existing template rows.

A B
1 Employee Name Employee Payment
2 Derek 3000.0
3 Elsa 1500.0
4 Oleg 2300.0

See also Chart sample.