.
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.
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 |
|
|
|
|