Generating Excel documents with outline row groups from table components

When using a table component in a report, we often prefer to organise data by grouping it inside the table upon various criteria. Also, we can display summary data (such as totals, average value or number of records) using the group footer section.
Such tables with organised data and group footers can be easily converted into Excel documents with outlined row groups, with the help of the net.sf.jasperreports.export.xls.row.outline.level.<suffix> properties.
In order to obtain a consistent Excel document with outline row groups the following rules should be applied:

• The <suffix> fragment should be replaced with the outline level number (between 1 and 7). For instance: net.sf.jasperreports.export.xls.row.outline.level.1 for outline level 1, net.sf.jasperreports.export.xls.row.outline.level.3 for outline level 3.

The outline level 1 represents the outermost level, while level 7 represents the innermost level.

• Use the outline levels as follows: level 1 for elements in the first group declared in the report (which encloses all subsequent groups), level 2 for elements in the second group, and so on.
• For elements in group headers and detail cells use the "Body" value for the net.sf.jasperreports.export.xls.row.outline.level.<suffix> property. This ensure the row the element belongs to is part of an outline group.

For instance: <property name="net.sf.jasperreports.export.xls.row.outline.level.2" value="Body"/> ensures the row containing this element belongs to a level 2 row group.

• For elements in group footers use the "End" value for the net.sf.jasperreports.export.xls.row.outline.level.<suffix> property. This ensure the related outline group will end on that row and a new one will begin on the next row.

For instance: <property name="net.sf.jasperreports.export.xls.row.outline.level.2" value="End"/> ensures the level 2 row group ends on this line.

• There is no need to set this property for every element in a group header or in a detail row, or in a group footer. Using it for a single element per group header, per detail row and per group footer is enough to enable row outlining in Excel output.

Attached is an example report with 3 groups declared in a subdataset to be used in a table component:

<group name="product_class_id_group">
 <groupExpression><![CDATA[$F{product_class_id}]]></groupExpression>
</group>
<group name="brand_name_group">
 <groupExpression><![CDATA[$F{brand_name}]]></groupExpression>
</group>
<group name="recyclable_package_group">
 <groupExpression><![CDATA[$F{recyclable_package}]]></groupExpression>
</group>

 So the outermost group (level 1) is product_class_id_group and the innermost (level 3) is recyclable_package_group:

 There are no group headers in the report, so we have the following settings:

• For the first textfield in the detail row:

<property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
<property name="net.sf.jasperreports.export.xls.row.outline.level.2" value="Body"/>
<property name="net.sf.jasperreports.export.xls.row.outline.level.3" value="Body"/>

This ensures that every detail row belongs to all 3 outline level groups.

• For the first element in the recyclable_package_group (level 3) group footer:

<property name="net.sf.jasperreports.export.xls.row.outline.level.3" value="End"/>
<property name="net.sf.jasperreports.export.xls.row.outline.level.2" value="Body"/>
<property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>

This ensures that level 3 outline group ends in this line, while it is still contained in the level 2 and level 1 row groups.

• For the first element in the brand_name_group (level 2) group footer:

<property name="net.sf.jasperreports.export.xls.row.outline.level.2" value="End"/>
<property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>

This ensures that level 2 outline group ends in this line, while it is still contained in the level 1 row group. Automatically, any level 3 outline group will end in this row too.

• For the first element in the product_class_id_group (level 1) group footer:

<property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="End"/>

This ensures that level 1 outline group ends in this line. Automatically, any level 2 and 3 outline groups will end in this row too.

The report sample can be run using the Sample DB data adapter in Jaspersoft Studio.

outline_levels.jrxml