Introduction

Headers & Footers are the lines of text that are displayed below the top margin or above the bottom margin respectively. It's possible to add headers and footers to the worksheets also. Headers & footers can be used to display any kind of useful information on the pages like page number, author name, topic name or date/time etc. Headers & footers are also managed using the page setup settings as shown below:

 

 

Figure: Header/Footer

 

Headers & Footers

Aspose.Cells allows to add headers and footer to the worksheets at runtime but it is recommended to set headers and footers manually in the pre-designed file for printing. You can use Microsoft Excel as a GUI tool to set headers and footers easily to reduce your efforts and development time. Aspose.Cells can import the file and reserve these settings.

To add headers and footers at runtime, Aspose.Cells provides special APIs and some Script Commands to control the formatting of headers and footers.

 

Script commands

Script commands are special commands provided by Aspose.Cells that allow developers to set the formatting of their headers and footers. These script commmands are listed below with their descriptions:

 

Script Commands	Description
&P	Represents current page number
&G	Represents picture
&N	Represents total number of pages
&D	Represents current date
&T	Represents current time
&A	Represents worksheet name
&F	Represents file name without its path
&"<FontName>"	Represents font name. For example: &"Arial"
&"<FontName>, <FontStyle>"	Represents font name with style. For example: &"Arial,Bold"
&<FontSize>	Represents font size. For example: &14. If this command is followed by a plain number to be printed in the header, it will be separated from the font size with a space character. For example: &14 123

 

Set headers and footers

PageSetup class provides two methods: SetHeader and SetFooter to add header and footer to a worksheet. These methods take only two parameters as follows:

• Section, represents the section where the header/footer should be placed. There are only three sections: Left, Center and Right that are represented by numeric values 0, 1 and 2 respectively.
• Script, represents the script to be used for header or footer. This script contains script commands to format headers or footers.

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook excel = new Workbook();

 

 

//Obtaining the reference of the PageSetup of the worksheet

Aspose.Cells.PageSetup pageSetup = excel.Worksheets[0].PageSetup;

 

 

//Setting worksheet name at the left section of the header

pageSetup.SetHeader(0, "&A");

 

 

//Setting current date and current time at the centeral section of the header

//and changing the font of the header

pageSetup.SetHeader(1, "&\"Times New Roman,Bold\"&D-&T");

 

 

//Setting current file name at the right section of the header and changing the

//font of the header

pageSetup.SetHeader(2, "&\"Times New Roman,Bold\"&12&F");

 

 

//Setting a string at the left section of the footer and changing the font

//of the footer

pageSetup.SetFooter(0, "Hello World! &\"Courier New\"&14 123");

 

 

//Setting the current page number at the central section of the footer

pageSetup.SetFooter(1, "&P");

 

 

//Setting page count at the right section of footer

pageSetup.SetFooter(2, "&N");

 

 

[VB.NET]

 

 

'Instantiating a Workbook object

Dim excel As Workbook =  New Workbook()

 

 

'Obtaining the reference of the PageSetup of the worksheet

Dim pageSetup As Aspose.Cells.PageSetup =  excel.Worksheets(0).PageSetup

 

 

'Setting worksheet name at the left section of the header

pageSetup.SetHeader(0, "&A")

 

 

'Setting current date and current time at the centeral section of the header

'and changing the font of the header

pageSetup.SetHeader(1, "&""Times New Roman,Bold""&D-&T")

 

 

'Setting current file name at the right section of the header and changing the

'font of the header

pageSetup.SetHeader(2, "&""Times New Roman,Bold""&12&F")

 

 

'Setting a string at the left section of the footer and changing the font

'of the footer

pageSetup.SetFooter(0,"Hello World! &""Courier New""&14 123")

 

 

'Setting the current page number at the central section of the footer

pageSetup.SetFooter(1, "&P")

 

 

'Setting page count at the right section of footer

pageSetup.SetFooter(2, "&N")

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Obtaining the reference of the PageSetup of the worksheet

PageSetup pageSetup = workbook.getWorksheets().getSheet(0).getPageSetup();

 

 

//Setting worksheet name at the left  header

pageSetup.setLeftHeader("&A");

 

 

//Setting current date and current time at the centeral header

//and changing the font of the header

pageSetup.setCenterHeader("&\"Times New Roman,Bold\"&D-&T");

 

 

//Setting current file name at the right header and changing the font of the header

pageSetup.setRightHeader("&\"Times New Roman,Bold\"&12&F");

 

 

//Setting a string at the left footer and changing the font of the footer

pageSetup.setLeftFooter("Hello World! &\"Courier New\"&14 123");

 

 

//Setting picture at the central footer

pageSetup.setCenterFooter( "&G");

pageSetup.addPicture(false,1,"C:\\footer.jpg");

 

 

//Setting the current page number and page count at the right footer

pageSetup.setRightFooter("&Pof&N");

 

Insert a Graphic in a Header or Footer

PageSetup class has two additional methods: SetHeaderPicture and SetFooterPicture to add pictures in the header and footer of a worksheet. These methods take two parameters as follows:

• Section, represents the header/footer section where the picture will be placed. There are only three sections: Left, Center and Right that are represented by numeric values 0, 1 and 2 respectively.
• Byte Array, represents the graphical data since the binary data should be written into the buffer of byte array.

Example:

[C#]

 

 

//Creating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a string variable to store the url of the logo/picture

string logo_url = "d:\\school.jpg";

 

 

//Declaring a FileStream object

FileStream inFile;

 

 

//Declaring a byte array

byte[] binaryData;

 

 

//Creating the instance of the FileStream object to open the logo/picture in the stream

inFile = new System.IO.FileStream(logo_url, System.IO.FileMode.Open, System.IO.FileAccess.Read);

 

 

//Instantiating the byte array of FileStream object's size

binaryData = new Byte[inFile.Length];

 

 

//Reads a block of bytes from the stream and writes data in a given buffer of byte array.

long bytesRead = inFile.Read(binaryData, 0, (int)inFile.Length);

 

 

//Creating a PageSetup object to get the page settings of the first worksheet of the workbook

PageSetup pageSetup = workbook.Worksheets[0].PageSetup;

 

 

//Setting the logo/picture in the central section of the page header

pageSetup.SetHeaderPicture(1, binaryData);

 

 

//Setting the script for the logo/picture

pageSetup.SetHeader(1, "&G");

 

 

//Setting the Sheet's name in the right section of the page header with the script

pageSetup.SetHeader(2,"&A");

 

 

//Saving the workbook

workbook.Save("d:\\headerpic.xls");

 

 

//Closing the FileStream object

inFile.Close();

 

 

[VB.NET]

 

 

'Creating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Creating a String variable to store the url of the logo/picture

Dim logo_url As String = "d:\school.jpg"

 

 

'Declaring a FileStream object

Dim inFile As FileStream

 

 

'Creating the instance of the FileStream object to open the logo/picture in the stream

inFile = New System.IO.FileStream(logo_url, System.IO.FileMode.Open, System.IO.FileAccess.Read)

 

 

'Declaring a Byte array object and creating it to FileStream object's size

Dim binaryData(inFile.Length) As Byte

 

 

'Reads a block of bytes from the stream and writes data in a given buffer of Byte array.

Dim bytesRead As Long = inFile.Read(binaryData, 0, CType(inFile.Length, Integer))

 

 

'Creating a PageSetup object to get the page settings of the first worksheet of the workbook

Dim pageSetup As PageSetup = workbook.Worksheets(0).PageSetup

 

 

'Setting the logo/picture in the central section of the page header

pageSetup.SetHeaderPicture(1, binaryData)

 

 

'Setting the script for the logo/picture

pageSetup.SetHeader(1, "&G")

 

 

'Setting the Sheet's name in the right section of the page header with the help of script

pageSetup.SetHeader(2, "&A")

 

 

'Saving the workbook

workbook.Save("d:\headerpic.xls")

 

 

'Closing the FileStream object

inFile.Close()

 

 

[JAVA]

 

 

//Creating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a string variable to store the url of the logo/picture

String logo_url = "d:\\school.jpg";

 

 

//Creating the instance of the FileInputStream object to open the logo/picture in the stream

FileInputStream inFile = new FileInputStream(logo_url);

 

 

//Creating a PageSetup object to get the page settings of the first worksheet of the workbook

PageSetup pageSetup = workbook.getWorksheets().getSheet(0).getPageSetup();

 

 

//Setting the logo/picture in the central section of the page header