How to: Add Headers and Footers to a Worksheet Printout

• Apr 23, 2019
• 4 minutes to read

The example below demonstrates how to insert headers and footers at the top and bottom of a worksheet printout.

To define the header and footer options, use the Worksheet.HeaderFooterOptions property. It provides access to the WorksheetHeaderFooterOptions object, which allows you to specify the header or footer for the first page (WorksheetHeaderFooterOptions.FirstHeader and WorksheetHeaderFooterOptions.FirstFooter), odd-numbered pages (WorksheetHeaderFooterOptions.OddHeader and WorksheetHeaderFooterOptions.OddFooter) and even-numbered pages (WorksheetHeaderFooterOptions.EvenHeader and WorksheetHeaderFooterOptions.EvenFooter) of the printed worksheet.

Header and Footer Options

Use the properties of the WorksheetHeaderFooterOptions object to specify the following header/footer settings.

• WorksheetHeaderFooterOptions.DifferentOddEven

  Set this property to true to specify different headers and footers for the odd-numbered and even-numbered pages. If this property is false, all pages in a worksheet have the same headers or footers as specified by the WorksheetHeaderFooterOptions.OddHeader and WorksheetHeaderFooterOptions.OddFooter properties.

• WorksheetHeaderFooterOptions.DifferentFirst

  Set this property to true to specify a unique header or footer for the first page of a worksheet.

• WorksheetHeaderFooterOptions.ScaleWithDoc

  Set this property to true to scale headers and footers proportionately when you scale a worksheet to fit your information on the specified number of pages.

• WorksheetHeaderFooterOptions.AlignWithMargins

  Set this property to true to align the header and footer edges with page margins

Header and Footer Codes

The SpreadsheetControl supports specific codes that enable including dynamic information into a header or footer, such as a page number, current date and time, filename, worksheet name, and so on. Note that these codes can also be obtained as constant fields or methods of the HeaderFooterCode class.

The supported header/footer codes are listed in the table below.

Code	HeaderFooterCode member	Description
&L	HeaderFooterCode.LeftSection	Left aligns the characters that follow.
&C	HeaderFooterCode.CenterSection	Centers the characters that follow.
&R	HeaderFooterCode.RightSection	Right aligns the characters that follow.
&P	HeaderFooterCode.PageNumber	Inserts the current page number.
&N	HeaderFooterCode.PageTotal	Inserts the total number of pages in a workbook.
&D	HeaderFooterCode.Date	Inserts the current date.
&T	HeaderFooterCode.Time	Inserts the current time.
&Z	HeaderFooterCode.WorkbookFilePath	Inserts the workbook file path.
&F	HeaderFooterCode.WorkbookFileName	Inserts the name of a workbook file.
&A	HeaderFooterCode.WorksheetName	Inserts the name of a worksheet.
&G	HeaderFooterCode.Picture	Inserts an image.
&&	HeaderFooterCode.Ampersand	Inserts the ampersand character.
&B	HeaderFooterCode.Bold	Turns bold on and off for the characters that follow.
&I	HeaderFooterCode.Italic	Turns italic on and off for the characters that follow.
&U	HeaderFooterCode.Underline	Turns underline on or off for the characters that follow.
&E	HeaderFooterCode.DoubleUnderline	Turns double underline on and off for the characters that follow.
&S	HeaderFooterCode.Strikethrough	Turns strikethrough on or off for the characters that follow.
&Y	HeaderFooterCode.Subscript	Turns subscript on or off for the characters that follow.
&X	HeaderFooterCode.Superscript	Turns superscript on or off for the characters that follow.
&”font name,font type“	HeaderFooterCode.Font	Prints the characters that follow using the specified font.
&font size	HeaderFooterCode.FontSize	Prints the characters that follow using the specified font size in points.
&K_RRGGBB_	HeaderFooterCode.FontColor	Prints the characters that follow using the specified color. Use the hexadecimal numbers to specify the color components.
&K_TTSNN_	HeaderFooterCode.FontColor	Prints the characters that follow using the specified color from the current theme. TT is the theme color Id, S is either “+” or “-“ of the tint/shade value, and NN is the tint/shade value.

Example

The following example prints the workbook file name in the first page header, and inserts the current date and the page indicator in the first page footer.

C#

WorksheetHeaderFooterOptions options = worksheet.HeaderFooterOptions;
// Specify that the first page of the worksheet has unique headers and footers. 
options.DifferentFirst = true;
// Set headers and footers for the first page.
// Insert the workbook name into the header left section.
options.FirstHeader.Left = "&F";
// Insert the current date into the footer left section.
options.FirstFooter.Left = "&D";
// Insert the current page number into the footer right section.
options.FirstFooter.Right = string.Format("Page {0} of {1}", "&P", "&N");

VB.NET

Dim options As WorksheetHeaderFooterOptions = worksheet.HeaderFooterOptions
' Specify that the first page of the worksheet has unique headers and footers. 
options.DifferentFirst = True
' Set headers and footers for the first page.
' Insert the workbook name into the header left section.
options.FirstHeader.Left = "&F"
' Insert the current date into the footer left section.
options.FirstFooter.Left = "&D"
' Insert the current page number into the footer right section.
options.FirstFooter.Right = String.Format("Page {0} of {1}", "&P", "&N")

The image below shows the result.