Excel headers and footers
The following example shows how to add headers and footers to the first page and all other pages of an Excel worksheet in C# and VB.NET, using the GemBox.Spreadsheet library.
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("HeadersFooters");
SheetHeaderFooter sheetHeadersFooters = worksheet.HeadersFooters;
HeaderFooterPage firstHeaderFooter = sheetHeadersFooters.FirstPage;
HeaderFooterPage defaultHeaderFooter = sheetHeadersFooters.DefaultPage;
// Set title text on the center of the first page header.
firstHeaderFooter.Header.CenterSection
.Append("Title on the first page",
new ExcelFont() { Name = "Arial Black", Size = 18 * 20 });
// Set image on the left of the first and default page headers.
firstHeaderFooter.Header.LeftSection
.AppendPicture("%#Dices.png%", 40, 30);
defaultHeaderFooter.Header.LeftSection = firstHeaderFooter.Header.LeftSection;
// Set page number on the right of the first and default page footer.
firstHeaderFooter.Footer.RightSection
.Append("Page ")
.Append(HeaderFooterFieldType.PageNumber)
.Append(" of ")
.Append(HeaderFooterFieldType.NumberOfPages);
defaultHeaderFooter.Footer = firstHeaderFooter.Footer;
worksheet.Cells[0, 0].Value = "First page";
worksheet.Cells[0, 5].Value = "Second page";
worksheet.Cells[0, 10].Value = "Third page";
worksheet.VerticalPageBreaks.Add(5);
worksheet.VerticalPageBreaks.Add(10);
workbook.Save("Headers and Footers.%OutputFileType%");
}
}
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook As New ExcelFile()
Dim worksheet = workbook.Worksheets.Add("HeadersFooters")
Dim sheetHeadersFooters As SheetHeaderFooter = worksheet.HeadersFooters
Dim firstHeaderFooter As HeaderFooterPage = sheetHeadersFooters.FirstPage
Dim defaultHeaderFooter As HeaderFooterPage = sheetHeadersFooters.DefaultPage
' Set title text on the center of the first page header.
firstHeaderFooter.Header.CenterSection _
.Append("Title on the first page",
New ExcelFont() With {.Name = "Arial Black", .Size = 18 * 20})
' Set image on the left of the first and default page headers.
firstHeaderFooter.Header.LeftSection _
.AppendPicture("%#Dices.png%", 40, 30)
defaultHeaderFooter.Header.LeftSection = firstHeaderFooter.Header.LeftSection
' Set page number on the right of the first and default page footer.
firstHeaderFooter.Footer.RightSection _
.Append("Page ") _
.Append(HeaderFooterFieldType.PageNumber) _
.Append(" of ") _
.Append(HeaderFooterFieldType.NumberOfPages)
defaultHeaderFooter.Footer = firstHeaderFooter.Footer
worksheet.Cells(0, 0).Value = "First page"
worksheet.Cells(0, 5).Value = "Second page"
worksheet.Cells(0, 10).Value = "Third page"
worksheet.VerticalPageBreaks.Add(5)
worksheet.VerticalPageBreaks.Add(10)
workbook.Save("Headers and Footers.%OutputFileType%")
End Sub
End Module
GemBox.Spreadsheet represents worksheet headers and footers with SheetHeaderFooter. Every worksheet can specify HeaderFooterPage that applies to first, default, or even pages. Every header and footer on the page can specify HeaderFooterSection that applies to the left, center, or right section.
Headers and footers can only be seen when the Excel file is exported to PDF, XPS, or image formats, when it's printed, or when View Options is set to ViewType.PageLayout.
