How to: Add Headers and Footers to a Worksheet Printout

• Oct 10, 2020
• 5 minutes to read

Use the Worksheet.HeaderFooterOptions property to add headers and footers to printed pages.

Properties	Description
WorksheetHeaderFooterOptions.FirstHeader, WorksheetHeaderFooterOptions.FirstFooter	Define a header and footer for the first page.
WorksheetHeaderFooterOptions.OddHeader, WorksheetHeaderFooterOptions.OddFooter	Define headers and footers for odd pages.
WorksheetHeaderFooterOptions.EvenHeader, WorksheetHeaderFooterOptions.EvenFooter	Define headers and footers for even pages.

Header and Footer Options

Property	Description
WorksheetHeaderFooterOptions.DifferentOddEven	Specifies whether odd and even pages have different headers and footers.
WorksheetHeaderFooterOptions.DifferentFirst	Specifies whether the first page has a unique header and footer.
WorksheetHeaderFooterOptions.ScaleWithDoc	Specifies whether headers and footers should resize when you scale a worksheet.
WorksheetHeaderFooterOptions.AlignWithMargins	Specifies whether to align header and footer margins with the left and right page margins.

Header and Footer Codes

The Spreadsheet supports special codes you can use to add dynamic data to a header or footer, and format its content. These codes are also available as constant fields and static methods of the HeaderFooterCode class.

Note

The ampersand symbol (&) is used in headers and footers to specify format codes. Use two ampersands (&&) to add a single ampersand to the header or footer text.

Code	HeaderFooterCode Member	Description
&L	HeaderFooterCode.LeftSection	Aligns text to the left.
&C	HeaderFooterCode.CenterSection	Aligns text to the center.
&R	HeaderFooterCode.RightSection	Aligns text to the right.
&P	HeaderFooterCode.PageNumber	Inserts the current page number.
&P+number	HeaderFooterCode.CustomPageNumber	Inserts the current page number plus the specified number.
&P-number	HeaderFooterCode.CustomPageNumber	Inserts the current page number minus the specified number.
&N	HeaderFooterCode.PageTotal	Inserts the total number of pages in a workbook.
&N+number	HeaderFooterCode.CustomPageTotal	Inserts the total number of pages plus the specified number.
&N-number	HeaderFooterCode.CustomPageTotal	Inserts the total number of pages minus the specified number.
&D	HeaderFooterCode.Date	Inserts the current date.
&T	HeaderFooterCode.Time	Inserts the current time.
&Z	HeaderFooterCode.WorkbookFilePath	Inserts the path to the document.
&F	HeaderFooterCode.WorkbookFileName	Inserts the document’s name.
&A	HeaderFooterCode.WorksheetName	Inserts the current worksheet’s name.
&G	HeaderFooterCode.Picture	Inserts an image.
&&	HeaderFooterCode.Ampersand	Inserts an ampersand.
&B	HeaderFooterCode.Bold	Turns bold formatting on or off.
&I	HeaderFooterCode.Italic	Turns italic formatting on or off.
&U	HeaderFooterCode.Underline	Turns underline formatting on or off.
&E	HeaderFooterCode.DoubleUnderline	Turns double underline formatting on or off.
&S	HeaderFooterCode.Strikethrough	Turns strikethrough formatting on or off.
&Y	HeaderFooterCode.Subscript	Turns subscript formatting on or off.
&X	HeaderFooterCode.Superscript	Turns superscript formatting on or off.
&”font name,font type“	HeaderFooterCode.Font, HeaderFooterCode.BodyFont, HeaderFooterCode.HeadingsFont	Specifies the text font.
&nn	HeaderFooterCode.FontSize	Sets the font size. Use a two-digit number to specify the font size in points.
&KRRGGBB	HeaderFooterCode.FontColor	Sets the font color. Use hexadecimal numbers to specify color components.
&KTTSNNN	HeaderFooterCode.FontColor	Applies the theme color to the characters. TT is the theme color Id, S is either “+” or “-“ of the tint/shade value, and NNN is the tint/shade value.

Add Headers and Footers

The following example shows how to specify a header and footer for the first page.

C#

WorksheetHeaderFooterOptions options = worksheet.HeaderFooterOptions;
// Specify that the first page has a unique header and footer.
options.DifferentFirst = true;
// Insert rich formatted text into the header's left section.
options.FirstHeader.Left = string.Format("{0}&BDev{1}AV", HeaderFooterCode.FontColor(4, -50), HeaderFooterCode.FontColor(4,10));
// Insert the sheet name into the header's right section.
options.FirstHeader.Right = "&A";
// Insert the current date into the footer's left section.
options.FirstFooter.Left = "&D";
// Insert the current page number into the footer's 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 has a unique header and footer. 
options.DifferentFirst = True
' Insert rich formatted text into the header's left section.
options.FirstHeader.Left = String.Format("{0}&BDev{1}AV", HeaderFooterCode.FontColor(4, -50), HeaderFooterCode.FontColor(4,10))
' Insert the sheet name into the header's right section.
options.FirstHeader.Right = "&A"
' Insert the current date into the footer's left section.
options.FirstFooter.Left = "&D"
' Insert the current page number into the footer's right section.
options.FirstFooter.Right = String.Format("Page {0} of {1}", "&P", "&N")

Insert Pictures into Headers and Footers

Use the WorksheetHeaderFooter.AddPicture method to insert a picture into a header or footer.

C#

// Set measurement unit to inches.
workbook.Unit = DevExpress.Office.DocumentUnit.Inch;
// Access header and footer options.
WorksheetHeaderFooterOptions options = worksheet.HeaderFooterOptions;
// Specify that the first page has a unique header and footer.
options.DifferentFirst = true;

// Insert a picture in the center of the footer.
options.FirstFooter.AddPicture("DxLogo.png", HeaderFooterSection.Center);

// Add an inline picture to the header's left section.
HeaderFooterPicture picture = options.FirstHeader.AddPicture(SpreadsheetImageSource.FromFile("DevAvLogo.png"), HeaderFooterSection.Left);
// Use the &G code to specify the picture position within text.
options.FirstHeader.Left = string.Format("{0}&BDev{1}AV &G", 
    HeaderFooterCode.FontColor(Color.FromArgb(0x05, 0x6f, 0xCE)), HeaderFooterCode.FontColor(Color.FromArgb(0x39, 0xA6, 0xF7)));
// Specify the picture size in inches.
picture.Height = 0.3f;
picture.Width = 0.3f;

VB.NET

' Set measurement unit to inches.
workbook.Unit = DevExpress.Office.DocumentUnit.Inch
' Access header and footer options.
Dim options As WorksheetHeaderFooterOptions = worksheet.HeaderFooterOptions
' Specify that the first page has a unique header and footer.
options.DifferentFirst = True

' Insert a picture in the center of the footer.
options.FirstFooter.AddPicture("DxLogo.png", HeaderFooterSection.Center)

' Add an inline picture to the header's left section.
Dim picture As HeaderFooterPicture = options.FirstHeader.AddPicture(SpreadsheetImageSource.FromFile("DevAvLogo.png"), HeaderFooterSection.Left)
' Use the &G code to specify the picture position within text.
options.FirstHeader.Left = String.Format("{0}&BDev{1}AV &G",
                                         HeaderFooterCode.FontColor(Color.FromArgb(&H5, &H6F, &HCE)),
                                         HeaderFooterCode.FontColor(Color.FromArgb(&H39, &HA6, &HF7)))
' Specify the picture size in inches.
picture.Height = 0.3F
picture.Width = 0.3F

Note

Call the WorksheetHeaderFooter.AddPicture method before you use the &G code to insert an inline picture into a header or footer. System.InvalidOperationException occurs when the Spreadsheet control cannot find the picture.