Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

Introduced in build 7.0.0.1261

Starting in version 7.1 of ExcelWriter, you can use grouping and nesting markers 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

Central

Jillian Carson

Bikes

Mountain Bikes

Black

$2024.99

1

$2024.99

Central

Jillian Carson

Bikes

Mountain Bikes

Silver

$2039.99

1

$2024.99

Central

Jillian Carson

Clothing

Socks

White

$5.70

3

$17.10

Northwest

David Campbell

Accessories

Helmets

Red

$20.19

2

$40.37

Northwest

David Campbell

Accessories

Helmets

Blue

$20.19

1

$20.19

Northwest

David Campbell

Bikes

Mountain Bikes

Black

$2024.99

2

$4049.99

Northwest

David Campbell

Bikes

Mountain Bikes

Black

$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:

C#
VB.NET

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