Build Paginated Reports using Excel XLSX Templates in C# .NET
Tags: webdev, dotnet, tutorial, devops
Published: 2022-12-22 19:17:18

---
canonical_url: https://www.grapecity.com/blogs/build-paginated-reports-using-excel-xlsx-templates-in-c-sharp-net
description: Learn how to build paginated reports using Excel XLSX Templates in C# .NET. See more from GrapeCity Documents today.
---

No matter what industry you work in or what role you have, it's definite that you have been asked many times to create spreadsheets showing progress or performance, present analysis or research, and many other such businesses activity. They can have simple information to complex transactional info, sales reports, detailed analyses, or operational statistics.

Majorly these spreadsheet documents are designed to be viewed on digital mediums such as desktops or mobiles. You need not worry about its page size because the content can be easily zoomed, fitted to a screen, or scrolled. However, they can also need to be printed for sharing and keeping physical copies. The spreadsheets must be strictly generated for specific paper sizes in such situations.

### Pagination in Excel Spreadsheets

Excel spreadsheets are often used to create structured layouts such as Invoices, Blood Test Reports, Student Progress reports, Product Catalogs, Lists, and many others. These layouts are supposed to have specific page sizes. When populated using data from a data source, you want a way to paginate automatically into multiple sheets with the defined layout.

A typical page design consists of a header and footer for page, group, or detail sections. In non-paginated scenarios that flow continuously, generally, the header sections appear once at the start, and the footer sections appear at the end of the report. However, in paginated scenarios, these sections can appear depending on how the spreadsheets are expected to be generated. For example, the page header/footer can appear on all pages while the group headers can appear once at the start while the group footer appears once at the end, as depicted in the images below:

![](https://files.grapecity.com/gc-website-media/mhnjfy4o/paginated.png?width=500&height=334.9687778768956)

In the latest v6 release, **GrapeCity Documents for Excel (GcExcel)** [Templates](https://www.grapecity.com/documents-api-excel/docs/online/templates.html) introduces several [new properties and functions](https://www.grapecity.com/documents-api-excel/docs/online/pagination-properties-functions.html) that allow you to create paginated Excel spreadsheets programmatically. This blog will explore how pagination is achieved in Excel files using these new APIs for [**GcExcel .NET**](https://www.grapecity.com/documents-api-excel) and [**GcExcel Java**](https://www.grapecity.com/documents-api-excel-java).

Download the samples - [**BloodTest.zip**](https://files.grapecity.com/gc-website-media/zxyjuoja/bloodtest.zip) and [**ProductCatalog.zip**](https://files.grapecity.com/gc-website-media/w3ea1snt/productcatalog.zip) to get started.

### Pagination using GcExcel Templates

With **GcExcel Templates**, you can programmatically paginate spreadsheets to meet the two common business situations:

*   automatically move the content, beyond a page size limit, to the next page (or sheet)
*   control the number of records (or data) to be displayed on a page (or sheet)

In **GcExcel**, the former requirement can be achieved using the **Page-size pagination approach**, while the latter can be accomplished using the **Count per Page pagination approach**. The various properties and functions **GcExcel** offers are categorized as listed below, based on the approach to pagination.

<table border="0" width="759" style="font-weight: 400;">

<tbody>

<tr>

<td colspan="2" style="text-align: center;">

PaginationMode

</td>

</tr>

<tr>

<td>

Page-size pagination

</td>

<td>

Count per Page pagination

</td>

</tr>

<tr>

<td>

RepeatOutput

</td>

<td>

CountPerPage

</td>

</tr>

<tr>

<td>

KeepTogether

</td>

<td>

RepeatType

</td>

</tr>

<tr>

<td>

AttachTo

</td>

<td>

NoRepeatAction

</td>

</tr>

<tr>

<td colspan="2" style="text-align: center;">

RepeatWithGroup

</td>

</tr>

<tr>

<td>

-

</td>

<td>

PageNumber()

</td>

</tr>

<tr>

<td>

-

</td>

<td>

PageCount()

</td>

</tr>

</tbody>

</table>

These properties and functions work when you enable the global setting, **TemplateOptions.PaginationMode** as depicted in the following code snippet:

```
    // Set PaginationMode is true.
    workbook.Names.Add("TemplateOptions.PaginationMode", "true");
```

As you enable the global pagination mode, page-size pagination is enabled by default. The worksheet gets paginated into multiple sheets according to the paper size (width and height) of the Excel Template file. **This approach is recommended to paginate a list-type document such as Grocery List, Product Catalog, HR list, etc.**

We’ve used it with a **Product Catalog Template,** and the resultant document appears with multiple pages (or sheets), as shown below:

![](https://files.grapecity.com/gc-website-media/tfij0uu1/01-paginated.png?width=500&height=204.00943396226415)

Download the [**ProductCatalog**](https://files.grapecity.com/gc-website-media/w3ea1snt/productcatalog.zip) sample!

### Page-Size Pagination Properties

With page-size pagination, a new page (or sheet) is generated when the data rows exceed the page size of the **GcExcel Template** document. In this pagination mode, the detail rows flow from one page to the next, and the headers and footers are not repeated on each page (i.e., the header always appears first, and the footer appears last).

Sometimes a header or footer appears orphaned and alone on a page, or merged cell data appears only once on the first occurrence of a template cell that spans across more than one page. To manage such situations, **GcExcel Templates** provides four properties for specifying pagination options: **RepeatOutput**, **KeepTogether**, **AttachTo**, and **RepeatWithGroup**.

#### RepeatOutput

When a merged cell in your Excel Template (static or template field) expands across pages, you can use the **RepeatOutput** property to determine whether to show the value on the first page or all pages containing the part of the merged cell. It takes a Boolean value with _false_ as the default.

**Syntax:**

```
    {{ds.Field(RepeatOutput=True|False)}}  //with template field
    or 
    Text {{(RepeatOutput=True|False)}}  //with static text 

```

For example, in the **Product Catalog** document generated above, you can see that the **_Title_** appears merged across pages. And on pages 3, 4 and 5, the first cell appears empty. Thus, we used this setting with the **_Title_** field in the **Product Catalog Template** to ensure it repeats on every page it expands, as shown below:

![](https://files.grapecity.com/gc-website-media/dncek0tb/02-paginated.png?width=500&height=304.88204456094365)

See the _ProductCatalog-Template-RepeatOutput.xlsx_ with this setting in the [**ProductCatalog**](https://files.grapecity.com/gc-website-media/w3ea1snt/productcatalog.zip) sample.

#### KeepTogether

The **KeepTogether** property ensures that a template cell and its descendants appear on the same page as much as possible. You can choose to keep the cells together (specifically for grouped data) in case of horizontal pagination, vertical pagination, or both.

**Syntax:**  

```
  {{ds.TemplateField(R=<group range>, KeepTogether=Both|Vertical|Horizontal|None<default>)}}
```

We use this property in the **Product Catalog** sample above to keep the grouped data together as much as possible, and the output appears as shown below:

![](https://files.grapecity.com/gc-website-media/ie1nklps/03-paginated.png?width=500&height=325.9109311740891)

Check out the _ProductCatalog-Template-KeepTogether.xlsx_ file with this setting in the [**ProductCatalog**](https://files.grapecity.com/gc-website-media/w3ea1snt/productcatalog.zip) sample.

#### AttachTo

The **AttachTo** property allows binding a cell template with another cell to ensure that it does not appear alone on a page. This property can prevent orphaned headers/footers in the document.

It takes a cell reference as input which is typically the details cell to which you want to attach the header or footer.

**Syntax:**

```
    {{(AttachTo=<Cell reference>)}}
```

The **Product Catalog** is supposed to show the Item description, price, variants, and code information together. In the absence of the **AttachTo** property, the **_Variant_** and **_Code_** info appears orphaned for a product, as shown below:

![](https://files.grapecity.com/gc-website-media/00tm3pf4/04-paginated.png?width=500&height=388.48758465011286)

Check out the _ProductCatalog-Template-AttachTo.xlsx_ file with this setting in the [**ProductCatalog**](https://files.grapecity.com/gc-website-media/w3ea1snt/productcatalog.zip) sample.

#### RepeatWithGroup

The **RepeatWithGroup** property determines whether to repeat a cell or a range of cells. In a page-size-based pagination scenario, this property accepts a cell reference that you want to repeat this cell with. For example, a group header should repeat with the details row on all pages.

**Syntax**:

```
     {{(R=<cell range to repeat>, RepeatWithGroup=<cell reference to repeat with>)}}
```

We use this setting with the table header in the **Product Catalog** to ensure it appears on all pages with the detail records as shown below:

![](https://files.grapecity.com/gc-website-media/yawdafze/05-paginated.png?width=500&height=275.37593984962405)

See the _ProductCatalog-Template-RepeatGroup.xlsx_ with this setting in the **[ProductCatalog](https://files.grapecity.com/gc-website-media/w3ea1snt/productcatalog.zip) **sample.

### Count per Page Pagination Properties

With **Count per Page Pagination**, a new page (or sheet) is generated when the data rows exceed a specific count for grouped data. The newly generated pages have the same layout as the template page. This pagination approach can be used with a structured Excel file such as an **Invoice**, **Blood Test Report**, **Product Change Form**, and so on.

To manage how the layout should appear on the pages, **GcExcel Templates** provides four properties and two functions: **CountPerPage**), **RepeatType**, **NoRepeatAction**, **RepeatWithGroup**, **PageNumber()**, and **PageCount()**.

Download the [**BloodTest**](https://files.grapecity.com/gc-website-media/zxyjuoja/bloodtest.zip) sample to try these properties.

#### CountPerPage

**CountPerPage** property limits the number of instances (of a grouped value) for a cell template. It accepts an integer value or a special value *.

To use this property, add the abbreviation CP with a [template field](https://www.grapecity.com/documents-api-excel/docs/online/template-fields.html) as shown below:

```
    {{ds.FieldName(CP=5)}}
    or
    {{ds.FieldName(CP=*)}}
```

**CountPerPage <number> (CP=number)**

When **CP** is a number value, the number of grouped instances is strictly limited to the count of rows specified. A new page is generated when the count of records exceeds within a group instance or when the next group instance starts. Each generated page has an identical layout.

For example, we used this property to limit the maximum number of **Blood Test** records on a page, as shown below:

![](https://files.grapecity.com/gc-website-media/fzbokftr/06-paginated.png?width=500&height=272.85006753714543)

See the _BloodTestReport-Template-LimitedRows.xlsx_ with this setting in the [**BloodTest**](https://files.grapecity.com/gc-website-media/zxyjuoja/bloodtest.zip) sample.

**CountPerPage <*> (CP=*)**

When the **CP** value is a wildcard, there is no limit on the number of rows (for a grouped instance) to be present on a paginated sheet. However, the rows displayed per page are controlled by the paper height. That said, a new sheet is generated when the next row (for a grouped instance) cannot be accommodated within a page height. This property can be used in templates where the number of records for a group is not defined and may vary.

For example, we used it to show the maximum records for a **Blood Test** category on a page, as illustrated below:

![](https://files.grapecity.com/gc-website-media/5lkalxl2/07-paginated.png?width=500&height=470.5438066465257)

See the _BloodTestReport-Template-UnLimitedRows.xlsx_ with this setting in the [**BloodTest**](https://files.grapecity.com/gc-website-media/zxyjuoja/bloodtest.zip) sample.

#### RepeatType

When pagination happens based on the number of grouped instances, every page has the same layout. The **RepeatType**property helps determine how to repeat the cell value(s) within a group. For example, repeat page headers on all pages while group or details headers appear only on the first page.  
By default, this property is set to **_PerPage_**. You can set it to **_FirstPage_** or **_LastPage,_** depending on whether it is supposed to appear on the first or last page only.

**Syntax**:

```
    {{(R=<cell range>, RepeatType=PerPage|FirstPage|LastPage)}}
```

_Note: set this property in a cell contained in the specified cell range_

For example, we use this setting to show the **_Patient_** and **_Specimen_** information in the **Blood Test Report** only on the first page, as shown below:

![](https://files.grapecity.com/gc-website-media/yzbhs10b/08-paginated.png?width=500&height=288.7189292543021)

See the _BloodTestReport-Template-RepeatType.xlsx_ with this setting in the [**BloodTest**](https://files.grapecity.com/gc-website-media/zxyjuoja/bloodtest.zip) sample.

### RepeatWithGroup

The **RepeatWithGroup** property determines whether to repeat a cell or a range of cells. In a counter-based pagination scenario, this property accepts a cell reference that specifies a group in the scope of which the cells are repeated.

**Syntax:**

```
     {{(R=<cell range to repeat>, RepeatWithGroup=<group cell reference>)}}
```

In the previous template, when chosen to repeat the **_Patient_** and the **_Specimen_** information on the first page, you may have noticed that it did not appear on Page 5 even though the details are for another patient. This is because, in the absence of the **RepeatWithGroup** property, the group scope of the worksheet is used, and thereby the sections appear only on the first page among the generated pages.

To make the information section appear for each group, we use the **RepeatWithGroup** property as shown below:

![](https://files.grapecity.com/gc-website-media/xrrbz5yu/09-paginated.png?width=500&height=263.38582677165357)

See the _BloodTestReport-Template-RepeatGroup.xlsx_ in the [**BloodTest**](https://files.grapecity.com/gc-website-media/zxyjuoja/bloodtest.zip) sample.

### NoRepeatAction

When **RepeatType** is set to **_PerPage_**, you will see the cell (or range of cells) on all the pages. However, when set otherwise, i.e., some template cells repeated on the first or last page only, empty cells appear for the relative locations on the other pages.

Here the **NoRepeatAction** property can help to choose whether to clear the content or delete the corresponding row/column. By default, it is set to **_ClearCells;_ **thus, you see empty cells. Other supported values are **_DeleteRows_** _and **DeleteColumns,**_ which will remove the corresponding rows and columns, respectively.

**Syntax**:

```
    {{(R=<cell range>, RepeatType=FirstPage|LastPage, NoRepeatAction=ClearCells|DeleteRows|DeleteColumns)}}
```

![](https://files.grapecity.com/gc-website-media/dfunfhrv/10-paginated.png?width=500&height=296.0378208014408)

See the _BloodTestReport-Template-NoRepeatAction.xlsx_ with this setting in the [**BloodTest**](https://files.grapecity.com/gc-website-media/zxyjuoja/bloodtest.zip) sample.

### PageNumber()

This function returns the page number of the current page in the scope of the attached group. If a group is not specified, then the page number is returned in the scope of the workbook.

**Syntax:**

```
    {{=PageNumber()}}

    {{=PageNumber(<cell reference>)}}
```

### PageCount()

This function returns the number of pages generated for a group. If no group is specified, the page count is calculated in the scope of the workbook.

**Syntax:**

```
    {{=PageCount()}}

    {{=PageCount(<cell reference>)}}
```

Combine it with the **PageNumber()** function and texts using “&” as shown below:

```
    {{=PageNumber(<cell reference>) & "/" & PageCount(<cell reference>)}}
    or
    {{="Page:" & PageNumber() & "/" & PageCount()}}
```

![](https://files.grapecity.com/gc-website-media/oxibm3lc/11-paginated.png?width=500&height=338.5663507109005)

See the _BloodTestReport-Template-PageNumber.xlsx_ with these functions in the [**BloodTest**](https://files.grapecity.com/gc-website-media/zxyjuoja/bloodtest.zip) sample.

Check out the demos and documentation for **Template Pagination** [**.NET Help**](https://www.grapecity.com/documents-api-excel/docs/online/paginated-templates.html) | [**.NET Demo**](https://www.grapecity.com/documents-api-excel/demos/pagination) | [**Java Help**](https://www.grapecity.com/documents-api-excel-java/docs/online/paginated-templates.html) | [**Java Demo**](https://www.grapecity.com/documents-api-excel-java/demos/pagination)