Use the Excel Export API to Add Headers and Footers to a Worksheet Printout

  • 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 IXlSheet.HeaderFooter property. It provides access to the XlHeaderFooter object, which allows you to define the header or footer for the first page (XlHeaderFooter.FirstHeader and XlHeaderFooter.FirstFooter), odd-numbered pages (XlHeaderFooter.OddHeader and XlHeaderFooter.OddFooter) and even-numbered pages (XlHeaderFooter.EvenHeader and XlHeaderFooter.EvenFooter) of the printed worksheet. Use the XlHeaderFooter.FromLCR method to specify the text that should be displayed in the left, center and right section of a header or footer, respectively.

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

The Excel Export Library supports specific codes that allows you to format the header/footer text and include 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 of the XlHeaderFooter class.

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

Code

XlHeaderFooter field

Description

&B

XlHeaderFooter.Bold

Turns bold on or off for the characters that follow.

&I

XlHeaderFooter.Italic

Turns italic on or off for the characters that follow.

&U

XlHeaderFooter.Underline

Turns underline on or off for the characters that follow.

&E

XlHeaderFooter.DoubleUnderline

Turns double underline on or off for the characters that follow.

&S

XlHeaderFooter.Strikethrough

Turns strikethrough on or off for the characters that follow.

&Y

XlHeaderFooter.Subscript

Turns subscript on or off for the characters that follow.

&X

XlHeaderFooter.Superscript

Turns superscript on or off for the characters that follow.

&L

XlHeaderFooter.Left

Left aligns the characters that follow.

&C

XlHeaderFooter.Center

Centers the characters that follow.

&R

XlHeaderFooter.Right

Right aligns the characters that follow.

&P

XlHeaderFooter.PageNumber

Inserts the current page number.

&N

XlHeaderFooter.PageTotal

Inserts the total number of pages in a workbook.

&D

XlHeaderFooter.Date

Inserts the current date.

&T

XlHeaderFooter.Time

Inserts the current time.

&Z

XlHeaderFooter.BookPath

Inserts the workbook file path.

&F

XlHeaderFooter.BookName

Inserts the name of a workbook file.

&A

XlHeaderFooter.SheetName

Inserts the name of a worksheet.

&”fontname”

Prints the characters that follow using the specified font.

Be sure to enclose the font name in double quotation marks.

&nn

Prints the characters that follow using the specified font size.

Use a two-digit number to specify the font size in points.

&P+number

Inserts the page number plus the specified number.

&P-number

Inserts the page number minus the specified number.

&&

Inserts an ampersand character.

Example

The example below demonstrates how to specify different headers and footers for the odd-numbered and even-numbered pages of the printed worksheet.

// Specify different headers and footers for the odd-numbered and even-numbered pages.
sheet.HeaderFooter.DifferentOddEven = true;
// Add the bold text to the header left section, 
// and insert the workbook name into the header right section.
sheet.HeaderFooter.OddHeader = XlHeaderFooter.FromLCR(XlHeaderFooter.Bold + "Sample report", null, XlHeaderFooter.BookName);
// Insert the current page number into the footer right section. 
sheet.HeaderFooter.OddFooter = XlHeaderFooter.FromLCR(null, null, XlHeaderFooter.PageNumber);
// Insert the workbook file path into the header left section, 
// and add the worksheet name to the header right section. 
sheet.HeaderFooter.EvenHeader = XlHeaderFooter.FromLCR(XlHeaderFooter.BookPath, null, XlHeaderFooter.SheetName);
// Insert the current page number into the footer left section 
// and add the current date to the footer right section. 
sheet.HeaderFooter.EvenFooter = XlHeaderFooter.FromLCR(XlHeaderFooter.PageNumber, null, XlHeaderFooter.Date);