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