Advanced Excel Reporting and Printing Features with Repeated Headers and Footers in C# and .NET 6

If you are working with a spreadsheet/worksheet that takes more than one page when printed, you may want to repeat rows at the bottom and columns at the right of each page.

For example, if you need to show the final grade scored by a student in a class (inclusive of all the subject grades), you need to repeat columns on each page's right.

Or there are times when you want to show a student's total monthly attendance in a class (at the right of each printed page along with a legend row at the bottom). You'll need to repeat the rows at the bottom and columns at the right of each printed page, and because it will print to PDF, the worksheet will break into different pages.

MS Excel does not have a direct way of repeating rows at the bottom or repeating columns at the right while printing. It has options to only repeat rows or columns at the top or left. One of the ways to repeat rows at the bottom of a page is to use a footer on every page. However, the use of a footer is limited specifically for large texts.

Document Solutions for Excel, .NET Edition (DsExcel, previously GcExcel) introduces new features "Repeating Rows at Bottom" and "Repeating Columns on Right" on each printed page of a PDF file.

DsExcel workbook's PageSetup API "PrintTailRows" & "PrintTailColumns" will help to provide a direct way for programmers to repeat rows at the bottom or repeat columns at the right of 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.

This article describes how to create a "Monthly Class Attendance" report with several rows and columns. The "Monthly Class Attendance" report will be printed into 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,P are used in every row, the user needs to see this abbreviation at the bottom of every printed page. We will accomplish this task with the help of PageSetup APIs "PrintTailRows" & "PrintTailColumns."

Repeating bottom and right rows and columns and converting Excel spreadsheets to PDFs in .NET Core

Step 1: Load the Excel file in a workbook and fill in the appropriate data

workbook.Open("MonthyClass6thAttendance.xlsx");  

Step 2: Set appropriate PrintTailColumns & PrintTailRows properties along with other properties

// 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";

Step 3: Export to PDF

workbook.Save(@"MonthyClass6thAttendance.pdf", SaveFileFormat.Pdf); 

Here are images showing the three-page output with the last row repeated at the bottom of each page and column totals repeated at the right side of each page:

Download the sample here to follow along. If you have any questions about the new features, please leave them in the comments below.

Happy Coding!