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

Grouping and Nesting

In addition to data markers, ExcelTemplate uses Grouping and Nesting markers to format flat data as it is brought into a spreadsheet. The %%group, %%endgroup, %%header, and %%footer markers are used to signify the beginning and ending of the grouping formatting and the group header and footer sections; their rows will not be included in the worksheet. The %%value(data marker) marker allows you to include a data marker value in the header or footer of a group, while the (hide) modifier, described below, allow you to remove a field from the data marker rows if its values are repeated in the header and footer rows.

Icon

Grouping and Nesting Markers are only evaluated in Office Open XML (.xlsx) files. Grouping and Nesting Markers placed in BIFF8 (.xls) templates will not be evaluated.

Grouping and Nesting Marker Descriptions

%%group

Signifies the beginning of a grouping block. Place this marker in the column of the field by which you wish to group. Grouping and Nesting formatting will be applied to the rows from this marker to the %%endgroup marker. This row will not be displayed in the output spreadsheet. The group marker is required for grouping functionality to work.

%%header

Signifies the beginning of the header rows for a particular column in a grouping block. This marker should be placed in a row directly under the row containing the %%group markers or other header rows and above the data marker row in the column. All rows between this marker's row and the data marker row, or the next header marker, will be repeated for each new value in the grouping field. The row containing the %%header marker will not be displayed in the output spreadsheet. Header markers are optional.

%%value(data marker)

The %%value(data marker) marker is used to include the values of a data marker row in the header or footer of a group. The current value of the field whose data marker is placed as a modifier for this marker will be brought into the header or footer, replacing the %%value() marker. Value markers are optional.

%%footer

Placed below the data marker rows, this marker signifies the beginning of the footer rows for a particular column in a grouping block. All rows between this marker's row and the %%endgroup marker row or the next footer marker will be repeated once for each new value in the grouping field. The row containing the %%footer marker will not be displayed in the output spreadsheet. Footer markers are optional.

%%endgroup

Signifies the end of a grouping block. Place this marker in the same column as the %%group marker of the group you are ending. The row containing the %%footer marker will not be displayed in the output spreadsheet. Only one endgroup marker is required, no matter how many group markers are in the first row of the grouping block.

Example

Below is an example of a grouping block in a template spreadsheet.

%%group

 

 

 

 

%%header

 

 

 

 

%%value(data.#1)

 

 

 

 

%%=data.#1(hide)

%%=data.#2

%%=data.#3

%%=data.#4

%%=data.#5

%%footer

 

 

 

 

Subtotal row

 

 

 

 

%%endgroup

 

 

 

 

For the data to be grouped correctly, they must be in a flat format and sorted hierarchically by grouping categories. For the grouping block above, for example, you could use the following data table:

First category

first row

2

4

3

First category

second row

3

4

7

First category

third row

3

5

2

Second category

first row

5

2

9

Second category

second row

4

3

1

Second category

third row

8

5

5

ExcelTemplate's BindData method should bring the data in as displayed below:

First category

 

 

 

 

 

first row

2

4

3

 

second row

3

4

7

 

third row

3

5

2

Subtotal row

 

 

 

 

Second category

 

 

 

 

 

first row

5

2

9

 

second row

4

3

1

 

third row

8

5

5

Subtotal row

 

 

 

 

  • No labels