Post
Ask Question

Adding Headers Or Footers To A Worksheet In C#

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.

  1. //Create an object of Workbook class   
  2. Workbook wb = new Workbook();  
  3. //Get the first sheet  
  4. Worksheet sheet = wb.Worksheets[0];  
  5. //Format a string with script commands and assign it to CenterFooter   
  6. sheet.PageSetup.CenterFooter = "&\"Arial\"&B&12&KFF0000Copyright © 2016 xxx. All Rights Reserved.";  
  7. //Save the file  
  8. 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.
  1. Image image = Image.FromFile(@"C:\Users\Administrator\Desktop\logo.png");  
  2. sheet.PageSetup.LeftHeaderImage = image;  
  3. 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- 
  1. //Set the value of DifferentOddEven as 1, which indicates that headers/footers for odd and even pages are different.  
  2. sheet.PageSetup.DifferentOddEven = 1;  
  3. //Set header and footer string for odd pages, and format the string.  
  4. sheet.PageSetup.OddHeaderString = "&\"Arial\"&12&B&KFFC000Odd_Header";  
  5. sheet.PageSetup.OddFooterString = "&\"Arial\"&12&B&KFFC000Odd_Footer";  
  6. //Set different header and footer string for even pages, and format the string with different color.  
  7. sheet.PageSetup.EvenHeaderString = "&\"Arial\"&12&B&KFF0000Even_Header";  
  8. sheet.PageSetup.EvenFooterString = "&\"Arial\"&12&B&KFF0000Even_Footer";  


Thanks for reading.