If your worksheet takes up more than one page when printed, then there are times when you may want to repeat rows at the bottom, and columns at the right of each page. For example, if you want to show the final grade scored by a student in a class inclusive of all the subject grades, then there is a need to repeat columns on right of each page. 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 we'll print to PDF, the worksheet will break into different pages.

image1

MS Excel does not have a direct way of repeating rows at bottom or repeating columns at right while printing. It has options to only repeat rows or columns at 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.

GrapeCity Documents for Excel (GcExcel) v2 sp2 introduces new features "Repeating Rows at Bottom" and "Repeating Columns on Right" on each printed page of a PDF file.

This new GcExcel 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.

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 API "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:

image2

image3

image4

Thanks for following along! If you have any questions about the new features, please leave them in the comments below. Happy Coding!

Get GcExcel .NET v2 sp2

Download the latest version of GrapeCity Documents for Excel .NET

Download Now!