Message-ID: <86248974.7877.1711624667456.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_7876_1013374133.1711624667456" ------=_Part_7876_1013374133.1711624667456 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Grouping And Nesting

Grouping And Nesting

Grouping and Nesti= ng

=20

In addition to data markers, ExcelTemplate uses Grouping and Nesting mar= kers to format flat data as it is brought into a spreadsheet. The %= %group, %%endgroup, %%header, an= d %%footer markers are used to signify the beginning and e= nding 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 he= ader or footer of a group, while the (hide) modifier, desc= ribed below, allow you to remove a field from the data marker rows if its v= alues are repeated in the header and footer rows.

=20
=20 Icon=20
=20

Grouping and Nesting Markers are only evaluated in Office Open XML (.xls= x) files. Grouping and Nesting Markers placed in BIFF8 (.xls) templates wil= l not be evaluated.

=20
=20
=20

Grouping= and Nesting Marker Descriptions

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20

%%group

Signifies the beginning of a grouping block.= Place this marker in the column of the field by which you wish to group. G= rouping 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 functi= onality to work.

%%header

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

%%value(data marker)

The %%value(data marker) ma= rker is used to include the values of a data marker row in the header or fo= oter 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, r= eplacing the %%value() marker. Value markers are optional.=

%%footer

Placed below the data marker rows, this mark= er signifies the beginning of the footer rows for a particular column in a = grouping block. All rows between this marker's row and the %%endgro= up marker row or the next footer marker will be repeated once for = each new value in the grouping field. The row containing the %%foot= er 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 t= he group you are ending. The row containing the %%footer m= arker will not be displayed in the output spreadsheet. Only one endgroup ma= rker is required, no matter how many group markers are in the first row of = the grouping block.

=20

Example

=20

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

= =20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20

%%group

 

 

 

 

%%header

 

 

 

 

%%value(data.#1)

 

 

 

 

%%=3Ddata.#1(hide)

%%=3Ddata.#2

%%=3Ddata.#3

%%=3Ddata.#4

%%=3Ddata.#5

%%footer

 

 

 

 

Subtotal row

 

 

 

 

%%endgroup

 

 

 

 

=20

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:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20

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

=20

ExcelTemplate's BindData method should bring the data in as displayed be= low:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20

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

 

 

 

 

------=_Part_7876_1013374133.1711624667456--