How to Add Repeated Headers and Footers to Excel XLSX in .NET C# using a .NET Spreadsheet API

When dealing with multi-page printed worksheet/spreadsheet reports in Excel, you may want repeating rows at the bottom and columns at the right for better presentation. For instance, if displaying a student's final grade with subject scores on each page, repeating columns on the right is essential. Similarly, for showcasing monthly attendance, repeating rows at the bottom and columns at the right ensures clarity across the printed PDF pages:

Microsoft Excel lacks a direct way to repeat rows at the bottom or columns at the right while printing, limiting options to the top or left. Enter Document Solutions for Excel, .NET Edition (DsExcel, previously GcExcel), a server-side .NET Excel API that provides Page Setup options, including PrintTailRows andPrintTailColumns properties that provide a direct solution for repeating rows or columns on each printed page. For details, please see the help topics Configure Columns to Repeat at Left and Right and Configure Rows to Repeat at Top and Bottom.

During this tutorial, we will create a "Monthly Class Attendance" report with several rows and columns. The "Monthly Class Attendance" report will be printed onto a three-page PDF file. Since all of the columns won't get printed on a single PDF page, there is a need to repeat certain columns (Total attendance of a student in a month) at the right of each printed page. Similarly, since the abbreviated letters T, U, E, and P are used in every row, the user needs to see this abbreviation at the bottom of every printed page.

Download a Trial of Document Solutions for Excel, .NET Edition, Today!

Steps to Programmatically Invoke Repeating Headers and Footers in Printed Excel Files From a .NET Application

1. Create .NET 8 Console App with .NET Excel API NuGet Package Installed
2. Open the Excel File in the .NET Excel API Workbook
3. Set PrintTailColumns & PrintTialRows Properties
4. Export Excel Report to PDF

Download the .NET 8 sample app to follow along with the tutorial.

Create .NET 8 Console App with .NET Excel API NuGet Package Installed

In Visual Studio 2022, create a new project, and select “Console App (.NET Core)”. Add the DsExcel .NET references to the project. In the Solution Explorer, right-click Dependencies and select Manage NuGet Packages. In NuGet Package Manager, search for ‘DS.Documents.Excel’, select DS.Documents.Excel, and click Install. See our Quick Start documentation for more information on getting started.

Open the Excel File in the .NET Excel API Workbook

Next, in the “Program.cs” file, create a new DsExcel .NET workbook using the Workbook class and add a new worksheet. Note: ‘Document Solutions for Excel’ was previously named ‘GrapeCity Documents for Excel’; at this time, the older product name remains in areas of our API.

using GrapeCity.Documents.Excel;

// Create DsExcel workbook
Workbook workbook = new Workbook();

Open the existing “MonthlyClass6thAttendance.xlsx” file in the .NET Excel workbook using the Open method.

using GrapeCity.Documents.Excel;

// Create DsExcel workbook
Workbook workbook = new Workbook();

// Open an Excel file
workbook.Open("MonthlyClass6thAttendance.xlsx");

Set PrintTailColumns & PrintTialRows Properties

Fetch the worksheet, and set the appropriate PrintTailColumns & PrintTailRows properties along with some additional properties.

using GrapeCity.Documents.Excel;

// Create DsExcel workbook
Workbook workbook = new Workbook();

// Open an Excel file
workbook.Open("MonthlyClass6thAttendance.xlsx");

// Fetch default worksheet 
IWorksheet worksheet = workbook.Worksheets[0];

// Columns to be repeated on the left side of each page, while saving pdf
worksheet.PageSetup.PrintTitleColumns = "$A:$B";
// Rows to be repeated on the top of each page, while saving pdf
worksheet.PageSetup.PrintTitleRows = "$1:$6";
// Columns to be repeated on the right side of each page, while saving pdf
worksheet.PageSetup.PrintTailColumns = "$AH:$AK";
// Rows to be repeated on the bottom of each page, while saving pdf
worksheet.PageSetup.PrintTailRows = "$47:$47";

Export Excel Report to PDF

Save the DsExcel workbook as a PDF to observe the repeating headers and footers on the multi-paged file.

// Saving workbook to pdf
workbook.Save(@"MonthyClass6thAttendance.pdf");

Download the sample app for this example and try this for yourself!

Learn More About this .NET Excel API

This article only scratches the surface of the full capabilities of Document Solutions for Excel (DsExcel), .NET Edition, our server-side .NET/C# Excel API. Review our documentation to see the many available features, and check out our online demo explorer to see the features in action and interact with the sample code. To learn more about DsExcel .NET and the new features added in the latest release, check out our releases page.

Ready to Try it Out? Download Document Solutions for Excel Today!