Insert Header and Footer to Excel with C#, VB.NET in WPF

Generally we add predefined information or insert elements such as data, time and file name to Excel header or footer for printing purpose. Header or footer can be manually inserted or modified in Page Layout View or from Page Setup dialog box.

This article will present how to insert header and footer at runtime using Spire.XLS for WPF. Spire.XLS provides a class of PageSetup that contains all the page setup settings including header and footer, and a set of script commands that are used to set header and footer formatting.

Script Commands:

Commands Description
&G A picture
&D The current data
&T The current time
&A Worksheet name
&F File name
&B Make text bold
&I Italicize text
&<Font name> Font name. For example: &"Arial"
&K<Html color code> Font color. For example: &KFFFFF0

Code Snippets:

Step 1: Initialize a new instance of Workbook class and get the first sheet from workbook.

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

Step 2: Insert header text with formatting (specified font name, size and color) to the left part of Excel header.

sheet.PageSetup.LeftHeader ="&\"Showcard Gothic\"&14&K8B2252 Header and Footer Sample";

Step 3: Insert footer with formatting to the center part of Excel footer.

sheet.PageSetup.CenterFooter = "&B Copyright © 2016 e-iceblue. All Rights Reserved.";

Step 4: Save and launch the file.

wb.SaveToFile(@"..\Sample.xls");
System.Diagnostics.Process.Start(@"..\Sample.xls");

Output:

Header

Insert Header and Footer to Excel with C#, VB.NET in WPF

Footer

Insert Header and Footer to Excel with C#, VB.NET in WPF

Full Code:

[C#]
using System.Drawing;
using System.Windows;

namespace WpfApplication1
{
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, RoutedEventArgs e)
        {
            Workbook wb = new Workbook();
            Worksheet sheet = wb.Worksheets[0];

            sheet.PageSetup.LeftHeader = "&\"Showcard Gothic\"&14&K8B2252 Header and Footer Sample";
            sheet.PageSetup.CenterFooter = "&B Copyright © 2016 e-iceblue. All Rights Reserved.";

            wb.SaveToFile(@"..\Sample.xls");
            System.Diagnostics.Process.Start(@"..\Sample.xls");

        }
    }
}
[VB.NET]
Imports System.Drawing
Imports System.Windows

Namespace WpfApplication1
	Public Partial Class MainWindow
		Inherits Window
		Public Sub New()
			InitializeComponent()
		End Sub
		Private Sub button1_Click(sender As Object, e As RoutedEventArgs)
			Dim wb As New Workbook()
			Dim sheet As Worksheet = wb.Worksheets(0)

			sheet.PageSetup.LeftHeader = "&""Showcard Gothic""&14&K8B2252 Header and Footer Sample"
			sheet.PageSetup.CenterFooter = "&B Copyright © 2016 e-iceblue. All Rights Reserved."

			wb.SaveToFile("..\Sample.xls")
			System.Diagnostics.Process.Start("..\Sample.xls")

		End Sub
	End Class
End Namespace