Introduction
Headers or footers are invisible on the worksheet in Normal view. They are only displayed in Page Layout view or the printed pages. We can add the headers or footers at the top or bottom of a printed worksheet to display page number, worksheet name, current data, text or a picture.
In this article, I am going to introduce how to programmatically add the headers or footers to an Excel sheet from the three aspects, given below-
- Add formatted text to the headers or footers.
- Add image to the headers or footers.
- Set different header or footer for odd and even pages.
Background
This solution requires a .NET Excel component, which provides a PageSetup class to deal with all page setup settings. Specifically, it contains LeftHeader, CenterHeader, LeftHeaderImage, OddHeaderString and similar properties to represent header/footer text, image, odd header/footer and even header/footer in Excel. Besides, this component provides some special script commands, which allows you to set header and footer formatting.
Script |
Description |
&P |
The current page number |
&N |
The total number of pages |
&D |
The current data |
&T |
The current time |
&G |
A picture |
&A |
The worksheet name |
&F |
The file name |
&B |
Make text bold |
&I |
Italicize text |
&U |
Underline text |
&”font name” |
Represents a font name, for example, &”Aril” |
&font size |
Represents font size, for example, &12 |
&K<HEX color> |
Represents font color, for example, &KFF0000 |
Using the code
Part 1 - Insert and format text in footer
It is easy to insert plain text in header or footer by assigning values to CenterHeader or CenterFooter. To change the look of the header/footer, we can use the script commends listed in the table, mentioned above to format the text.
-
- Workbook wb = new Workbook();
-
- Worksheet sheet = wb.Worksheets[0];
-
- sheet.PageSetup.CenterFooter = "&\"Arial\"&B&12&KFF0000Copyright © 2016 xxx. All Rights Reserved.";
-
- wb.SaveToFile("HeaderFooter.xlsx", ExcelVersion.Version2013);
Part 2 - Insert image in header
To add an image to the header or footer, we only need to specify an image to LeftHeaderImage or LeftFooterImage and change the display type of the picture.
- Image image = Image.FromFile(@"C:\Users\Administrator\Desktop\logo.png");
- sheet.PageSetup.LeftHeaderImage = image;
- sheet.PageSetup.LeftHeader = "&G";
Part 3 - Set different header or footer for odd and even pages
Sometimes, we may want the headers or footers on odd pages and even pages to be different. We can do it, using-
-
- sheet.PageSetup.DifferentOddEven = 1;
-
- sheet.PageSetup.OddHeaderString = "&\"Arial\"&12&B&KFFC000Odd_Header";
- sheet.PageSetup.OddFooterString = "&\"Arial\"&12&B&KFFC000Odd_Footer";
-
- sheet.PageSetup.EvenHeaderString = "&\"Arial\"&12&B&KFF0000Even_Header";
- sheet.PageSetup.EvenFooterString = "&\"Arial\"&12&B&KFF0000Even_Footer";
Thanks for reading.