Build Paginated Reports using Excel XLSX Templates in C# .NET

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:

In the latest v6 release, GrapeCity Documents for Excel (GcExcel) Templates introduces several new properties and functions 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 and GcExcel Java.

Download the samples - BloodTest.zip and ProductCatalog.zip to get started.

Download Your Free Trial for GrapeCity Documents for Excel .NETor Java Today!

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.

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:

Download the ProductCatalog 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 Templatesprovides 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:

See the ProductCatalog-Template-RepeatOutput.xlsx with this setting in the ProductCatalog 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:

Check out the ProductCatalog-Template-KeepTogether.xlsx file with this setting in the ProductCatalog 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:

Check out the ProductCatalog-Template-AttachTo.xlsx file with this setting in the ProductCatalog 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:

See the ProductCatalog-Template-RepeatGroup.xlsx with this setting in the ProductCatalog 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 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 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:

See the BloodTestReport-Template-LimitedRows.xlsx with this setting in the BloodTest 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:

See the BloodTestReport-Template-UnLimitedRows.xlsx with this setting in the BloodTest sample.

RepeatType

When pagination happens based on the number of grouped instances, every page has the same layout. The RepeatTypeproperty 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:

See the BloodTestReport-Template-RepeatType.xlsx with this setting in the BloodTest 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:

See the BloodTestReport-Template-RepeatGroup.xlsx in the BloodTest 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)}}

See the BloodTestReport-Template-NoRepeatAction.xlsx with this setting in the BloodTest 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()}}

See the BloodTestReport-Template-PageNumber.xlsx with these functions in the BloodTest sample.

Check out the demos and documentation for Template Pagination .NET Help | .NET Demo | Java Help | Java Demo

Download Your Free Trial for GrapeCity Documents for Excel .NETor Java Today!