Skip to end of metadata
Go to start of metadata
Starting in version 7.1 of ExcelWriter, you can use [grouping and nesting markers|Creating Data Markers#grouping] to display flat data in a grouped and nested format using the ExcelTemplate object.

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:

Territory Name

Salesperson

Product Category

Product Subcategory

Color

Unit Price

Order Qty

Line Total

Central

Jillian Carson

Bikes

Mountain Bikes

Black

$2024.99

1

$2024.99

CentralJillian CarsonBikesMountain BikesBlack$2024.991$2024.99
CentralJillian CarsonBikesMountain BikesSilver$2039.991$2039.99
CentralJillian CarsonClothingSocksWhite

$5.70

3

$17.10

Northwest

David Campbell

Accessories

Helmets

Red

$20.19

2

$40.37

NorthwestDavid CampbellAccessories

Helmets

Blue

$20.19

1

$20.19

NorthwestDavid CampbellBikesMountain BikesBlack

$2024.99

2

$4049.99

NorthwestDavid CampbellBikesMountain BikesBlack

$2024.99

1

$2024.99

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.

 

  • No labels