Overview

This part describes more complex cases when exporting collections.

Next sections 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);
            

Adjacent collections export

This section describes how to export adjacent collections. Using this feature you may specify more than one collection in the same row. Also it is possible to export a collection related cells side by side with fixed values cells. See Adjacent Lists sample.

The main thing to remember is that for each cell that should be duplicated with some collection (except those cells which contain some collection properties) you have to specify the name of the related collection after //. For example

A B
1 Department ${dep1.name} Name://dep1.staff ${dep1.staff.name} Payment://dep1.staff ${dep1.staff.payment} Department ${dep2.name} Name://dep2.staff ${dep2.staff.name} Payment://dep2.staff ${dep2.staff.payment}

Here we assumed that we have two Department beans passed to XLSTransformer under keys dep1 and dep2.

You see that Name: cell should be duplicated along with dep1.staff collection. It is indicated by specifying //dep1.staff or //dep2.staff in the cell. In the same time cell Department ${dep1.name} does not have such indication and will not be duplicated with any collection cells. In this way the output will be like following

A B C D
1 Department IT Name: John Payment: 3000.0 Department HR Name: Oleg Payment: 2500.0
2 Name: Neil Payment: 2000.0 Name: Yuri Payment: 2000.0
3 Name: Jim Payment: 1800.0 Name: Alex Payment: 1700.0

Several Excel rows per collection item

Let's assume that you need to output different properties of every item in the collection in several rows. With jXLS it is as easy as adding a number of additional rows in meta section of a cell. For example, if you want to output each employee name in the first row and employee payment in the second row you should specify your first collection property in the way

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

The number 2 in ${department.staff.name}//:2 indicates that next 2 rows are also related to the same collection and should be processed for every collection item. So we'll have 3 rows per collection item in this sample (3d row is empty)

A B
1 Employee Name: John
2 Employee Payment: 3000
3
4 Employee Name: Neil
5 Employee Payment: 2000
6

Inner Collections (grouping)

When you have a collection of items with inner collections the same syntax can be used to export grouped data. For example if you have a collection of departments you can export every department and all its employees as following:

A B
1 Department ${departments.name}//:4
2 Staff:
3 Name Payment
4 ${departments.staff.name} ${departments.staff.payment}
5

The number 4 in ${departments.name}//:4 specifies that next 4 rows are also related to the departments collection. For every department the collection of its employees will be processed in a usual way.

In this way you will get result XLS similar to

A B
1 Department Software
2 Staff:
3 Employee Name Employee Payment
4 John 3000
5 Neil 2000
6
7 Department Design
8 Staff:
9 Employee Name Employee Payment
10 Oleg 2500
11 Alex 1500
12