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.
Header and Footer Options
Use the properties of the XlHeaderFooter object to specify the following header/footer settings.
XlHeaderFooter.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 XlHeaderFooter.OddHeader and XlHeaderFooter.OddFooter properties.
-
Set this property to true to specify a unique header or footer for the first page of a worksheet.
-
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.
XlHeaderFooter.AlignWithMargins
Set this property to true to align the header and footer edges with page margins
Header and Footer Codes
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 | Turns bold on or off for the characters that follow. | |
&I | Turns italic on or off for the characters that follow. | |
&U | Turns underline on or off for the characters that follow. | |
&E | Turns double underline on or off for the characters that follow. | |
&S | Turns strikethrough on or off for the characters that follow. | |
&Y | Turns subscript on or off for the characters that follow. | |
&X | Turns superscript on or off for the characters that follow. | |
&L | Left aligns the characters that follow. | |
&C | Centers the characters that follow. | |
&R | Right aligns the characters that follow. | |
&P | Inserts the current page number. | |
&N | Inserts the total number of pages in a workbook. | |
&D | Inserts the current date. | |
&T | Inserts the current time. | |
&Z | Inserts the workbook file path. | |
&F | Inserts the name of a workbook file. | |
&A | 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);