Using Grouping and Nesting with ExcelTemplate

Introduction

In order to help organize hierarchical data in a more easily readable format, ExcelTemplate now features grouping and nesting options. The basic feature of grouping and nesting is that ExcelTemplate can remove unnecessary repeated values from a field if it is given hierarchical data in a flat format. This basic functionality is explained in further detail below, while advanced features such as headers and footers are discussed on the Advanced Grouping and Nesting page.

Creating a Data Set for grouping

Though grouping and nesting is meant to show hierarchical data, the input data for a template with grouping and nesting must be in flat format. This means that a data set for nesting and grouping will have repeated values for fields that are higher in the hierarchy. For this tutorial, the data will look like this:

Grouping Code

Grouping code is no different from regular ExcelTemplate code. In this tutorial, we use the following ExcelTemplate code:

Grouping by a single group

The first element needed for a grouping template is a data row that contains all of the data markers for your data set, as shown below:

If you bound the data above to this template, it would display exactly as you see it above -- in flat form. You can group based on the first column -- Territory Name -- by adding a *%%group* tag to the cell above the data marker for that column. If you add any *%%group* tag, you must also add an *%%endgroup* tag below the data markers in order to mark the end of the grouping section:

The resulting spreadsheet should look like the one below, with all of the extra repeated values from the Territory Name field removed:

Notice that the rows with the *%%group* and *%%endgroup* tags are removed as well. Be sure not to place any content in these rows, as it will be automatically removed when data is bound to the template.

Nested grouping with multiple groups

You can group by multiple groups by placing extra *%%group* markers in more columns. Additional *%%endgroup* markers are unnecessary. Each of the new fields will be grouped in order of hierarchy, from left to right unless otherwise specified. For example, if you want to group first by Territory Name, then by Salesperson, place an additional *%%group* marker in the second column next to your first *%%group* marker:

The resulting spreadsheet will be grouped first by Territory Name, then by Salesperson:

You can group even further by adding additional *%%group* tags:

The resulting spreadsheet:

Additional Functionality

Additional functionality such as headers and footers, are explained in the Advanced Grouping and Nesting page.