Python: Add Headers and Footers to Excel

Headers and footers are areas located at the top and bottom of each page in a document, used to add additional information or elements. Headers typically include document titles, company names, dates, and other information, while footers often contain page numbers, file paths, copyright statements, and other details. By setting headers and footers in Excel, documents can be made more professional and organized. In this article, we will show you how to add headers and footers to Excel by using Spire.XLS for Python.

• Add Text to the Header or Footer in Excel
• Add Images to the Header or Footer in Excel

Spire.XLS for Python provides the PageSetup class to work with the page setup in Excel including headers and footers. Specifically, it contains LeftHeader property, CenterHeader property, RightHeader property, LeftFooter property, etc. to represent the left section, center section and right section of a header or footer. To add fields to headers or footers, or to apply formatting to text, you'll need to use the scripts listed in the following table.

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Add Text to the Header or Footer in Excel

Spire.XLS for Python allows you to add formatted text to the certain section of the header or footer. In this way, you can set different elements in Excel, such as file titles, page numbers or date. Here are the detailed steps.

• Create an object of Workbook class.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get the specific worksheet by Workbook.Worksheets[index] property.
• Add text to the left header by setting PageSetup.LeftHeader property as you like.
• Add page number to the center footer by setting PageSetup.CenterFooter property to &P.
• Add the current date to the right footer by setting PageSetup.RightFooter property to &D.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

inputFile = "sample.xlsx"
outputFile = "TextHeaderFooter.xlsx"

#Create an object of Workbook class
workbook = Workbook()

#Load a sample file from disk
workbook.LoadFromFile(inputFile)

#Get the first worksheet of this file
Worksheet = workbook.Worksheets[0]

#Add text to the left header
Worksheet.PageSetup.LeftHeader = "&\"Calibri\"&14 Sales Volume"

#Add page number to the center footer 
Worksheet.PageSetup.CenterFooter = "&P"

#Add the current date to the right footer
Worksheet.PageSetup.RightFooter = "&D"

#Set the view mode of the sheet
Worksheet.ViewMode = ViewMode.Layout

#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2010)
workbook.Dispose()

Add Images to the Header or Footer in Excel

What's more, Spire.XLS for Python also supports adding images to the header or footer. The following are detailed steps.

• Create an object of Workbook class.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get the specific worksheet by Workbook.Worksheets[index] property.
• Load an image and set it as the image source of the left header by PageSetup.LeftHeaderImage property.
• Display the image in the left header section by setting PageSetup.LeftHeader property to “&G”.
• Set it as the image source of the center footer by PageSetup.CenterFooterImage property.
• Display the image in the center footer section by setting PageSetup.CenterFooter property to “&G”.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.common import *

inputFile = "sample.xlsx"
inputImage = "Logo.png"
outputFile = "ImageHeaderFooter.xlsx"

#Create an object of workbook class
workbook = Workbook()

#Load a sample file from disk
workbook.LoadFromFile(inputFile)

#Get the first sheet of this file
sheet = workbook.Worksheets[0]

#Load an image from disk
image = Image.FromFile(inputImage)

#Add the image to the left header
sheet.PageSetup.LeftHeaderImage = image
sheet.PageSetup.LeftHeader = "&G"

#Add the image to the center footer
sheet.PageSetup.CenterFooterImage = image
sheet.PageSetup.CenterFooter = "&G"

#Set the view mode of the sheet
sheet.ViewMode = ViewMode.Layout

#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2010)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.