Pagination Properties and Functions
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 |
Pagination Functions | |
Page-size Pagination
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, GcExcel provides following properties:
RepeatOutput
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.
KeepTogether
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
None: (Default value) Places the cell and its descendants according to the availability of space on a page during pagination.
Horizontal: Keeps the cell and its descendants on the same page as much as possible while paginating horizontally.
Vertical: Keeps the cell and its descendants on the same page as much as possible while paginating vertically.
Both: Keeps the cell and its descendants on the same page as much as possible while paginating horizontally or vertically.
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.
AttachTo
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.
RepeatWithGroup
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.
Count-per-page pagination
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.
CountPerPage
GcExcel 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 template 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:
CountPerPage property can only be set for one template cell in the template worksheet.
When CountPerPage is implemented, pagination by paper size is ignored.
When value for CountPerPage(CP) is set to ‘*’, then FillMode(FM) can only be set to Insert.
When FillMode property of a cell is set to Overwrite and CountPerPage property has a value, the FillRange property can be omitted as it is automatically calculated by the engine based on value of the CountPerPage property.
RepeatType
The RepeatType property determines how to repeat a cell value within a group when the RepeatWithGroup property is set.
Value: Enum
PerPage: (Default Value) The template cell is visible on every page.
FirstPage: The template cell is only visible on the first page of the group specified by the RepeatWithGroup property .
LastPage: The template cell is only visible on the last page of the group specified by the RepeatWithGroup property .
Example
F6: {{(R=A6:L15, RepeatType = FirstPage)}}
NoRepeatAction
The NoRepeatAction property determines how to set the deletion mode of common content when it is not displayed on the current page.
Value: Enum
ClearCells : (Default value) If a cell is not repeated, its value and format is deleted automatically.
DeleteRows: If a cell is not repeated, its rows are deleted.
DeleteColumns: If a cell is not repeated, its columns are deleted.
Example
F6: {{(R=A6:L15, RepeatType = FirstPage, RepeatWithGroup =A3, NoRepeatAction = DeleteRows)}}
RepeatWithGroup
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:
If RepeatType = PerPage, RepeatWithGroup setting is less visible because range of cell is repeated on all pages.
If RepeatType = FirstPage or LastPage and RepeatWithGroup is not specified then, range of cell gets displayed only on the first or last page of the entire workbook.
If RepeatType = FirstPage or LastPage, RepeatWithGroup is specified, then range of cell gets rendered on first or last page of each group.
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
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
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.
Template_Pagination_PageNumber.xlsx
Limitation
The PageNumber and PageCount functions cannot be mixed with other methods such as Count or Sum etc.