This topic discusses various template properties and functions with respect to the two pagination approaches, page-size pagination and count-per-page pagination. The table below summarizes the properties and functions required in each case.
| Page-size Pagination | Count-per-page Pagination |
|---|---|
| Pagination Properties | Pagination Properties |
| RepeatOutput | CountPerPage |
| KeepTogether | RepeatType |
| AttachTo | RepeatWithGroup |
| RepeatWithGroup | NoRepeatAction |
| Pagination Functions | |
| PageCount | |
| PageNumber |
In page-size pagination approach, a new page is generated when data rows exceed page size specified for the template document. In this mode, headers and footers are not repeated on every page and appear only once in the beginning and end of the document. Hence, in some cases, they might appear alone on a page depending on the spread of records. Similarly, there are cases of merged cell data spanning across the pages in which the cell data appears only once on the first occurrence of a template cell. To handle these scenarios, DsExcel provides following properties:
The RepeatOutput property specifies whether the value of merged cells appears only on the first page or on each page of the report. The property is implemented in a paginated template based on page size.
Value: Boolean
Example: {{ds.Client(RepeatOutput=true)}}
The below image shows how to let the template cell have value on each page if the cell expands to a merged cell across pages. You can download the Excel template layout used in the example below.
The KeepTogether property ensures the cell, and its descendants appear on the same page. The property allows you to choose if you want to keep the cells together with horizontal pagination or vertical pagination.
Value: Enum
Example: {{ds.Company(KeepTogether = Vertical)}}
The below image shows how to keep the grouped data together as much as possible by using the KeepTogether property. You can download the Excel template layout used in the example below.
The AttachTo property enables you to bind a cell template with another cell so that the referred cell does not appear alone after pagination. The property takes reference of the cell to be attached to a specific cell.
Value: Cell location
Example: Subtotal{{(AttachTo=B6)}}
The below image shows the usage of AttachTo property wherein it depicts how the cell appears together with the specific cell. You can download the Excel template layout used in below example.
In case of Page-size pagination, the RepeatWithGroup property specifies the cell reference in the template that repeats with a cell in the generated report. For example, you can specify to repeat a group header with the details row on all pages.
Value: Cell location
Example
Client{{(RepeatWithGroup =B6)}}
The below image shows the implementation of the RepeatWithGroup property along with the RepeatOutput. You can download the Excel template layout used in the example below.
In Count per Page pagination, a new page (or sheet) is generated when data rows exceed a specific count for grouped data. In this case, every new page has the same layout as that specified by the template.
DsExcel provides paginated templates using CountPerPage template property which specifies the maximum number of template cell instances generated on a page on template expansion. When CountPerPage value is set to “*”, there is no limit on number of the tempate cell instances that are generated as long as there is space available on the paper.
The property automatically creates a new page or worksheet with the same template layout including headers and footers, when the generated record or group count in a report exceeds value of the property. The term ‘count’ in CountPerPage property refers to the number of records, only when Group property of the template is set to List. When this property is set to Normal, Merge or Repeat, CountPerPage property considers the count as number of groups. Note that the CountPerPage property is only supported when TemplateOptions.PaginationMode is set to true.
Value: Integer or '*'
Example
{{ds.Product(FM=O, G=L, CP=10)}}
The image below shows how to apply CountPerPage template property when grouped on List basis. You can download the Excel template layout used in the example below.
Note:
The RepeatType property determines how to repeat a cell value within a group when the RepeatWithGroup property is set.
Value: Enum
Example
F6: {{(R=A6:L15, RepeatType = FirstPage)}}
The NoRepeatAction property determines how to set the deletion mode of common content when it is not displayed on the current page.
Value: Enum
Example
F6: {{(R=A6:L15, RepeatType = FirstPage, RepeatWithGroup =A3, NoRepeatAction = DeleteRows)}}
In case of count-per-page pagination, the RepeatWithGroup property specifies cell reference of the group with which a particular cell or cell range must be repeated.
Value: Cell location
Example
F6: {{(R=A6:L15, RepeatWithGroup =A3)}}
Note:
The below image shows the implementation of the RepeatWithGroup property along with the RepeatType and NoRepeatAction properties discussed in the sections below. You can download the invoice template used in the example below.
PageNumber function is used to add the current page available in the scope of the group.
Syntax: {{=PageNumber(A3)}}
Where in: PageNumber(string cell = null): If cell is not passed, the result is index of the current sheet in the workbook.
PageCount functions is used to add total number of pages available in the current group scope of the group.
Syntax: {{=PageCount(A3)}}
Where in: PageCount (string cell = null): If cell is not passed, the result is sheet count of the workbook.
The below image shows the implementation of PageNumber and PageCount functions while generating an invoice. You can download the Excel template layout used in below example.
Limitation
The PageNumber and PageCount functions cannot be mixed with other methods such as Count or Sum etc.
