Set Excel Headers and Footers Using Python

Headers and footers are important supporting information when printing spreadsheets. They do not affect cell data, but they can show a document title, company name, logo, or date at the top or bottom of every page, making printed reports more consistent and professional. When a batch of workbooks needs the same header and footer, setting them by hand one by one is not practical.

Python can set Excel headers and footers in batch and with precision, including text content, font styles, odd/even page differences, and inserted images. This article demonstrates these operations using Spire.XLS for Python.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

Header and footer settings are all centered on a worksheet's PageSetup property. It divides each header and footer into left, center, and right sections, accessed through LeftHeader / CenterHeader / RightHeader and LeftFooter / CenterFooter / RightFooter.

Setting a Basic Header and Footer

The simplest usage is to assign text directly to a section:

workbook = Workbook()
workbook.LoadFromFile("report.xlsx")
sheet = workbook.Worksheets[0]

# Set the left header
sheet.PageSetup.LeftHeader = "Sample Report by Spire.XLS"
# Set the center footer
sheet.PageSetup.CenterFooter = "Footer Text"

workbook.SaveToFile("header_footer.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

LeftHeader maps to the left section of the header, and CenterFooter to the center section of the footer. The six sections are independent, and you can use any subset of them as needed.

Controlling Font and Style with Format Codes

Header and footer text supports a set of format codes that take effect when written into the string. The most common are font name and size:

# &"font name"&size specifies the font and size
sheet.PageSetup.LeftHeader = "&\"Arial Unicode MS\"&18 Header Footer Sample"
sheet.PageSetup.RightFooter = "&\"Arial Unicode MS\"&18 Header Footer Sample"

The code pattern is &"font name"&size immediately followed by the text. Besides font and size, several other codes are commonly used:

• &B: bold, for example &"Arial"&12&B Header;
• &K + color code: sets the font color, for example &KFFC000;
• &G: the image placeholder, marking where an image is inserted.

Combining these codes produces a styled header or footer, for example:

sheet.PageSetup.OddHeaderString = "&\"Arial\"&12&B&KFFC000 Odd_Header"

This line renders the header in Arial, size 12, bold, in the specified color.

Using Different Headers and Footers for Odd and Even Pages

Some documents want different headers and footers on odd and even pages (for example, a title on odd pages and a chapter name on even ones). Set DifferentOddEven to 1 to enable the feature, then set the odd and even strings separately:

sheet.PageSetup.DifferentOddEven = 1

# Odd page header and footer
sheet.PageSetup.OddHeaderString = "&\"Arial\"&12&B&KFFC000 Odd_Header"
sheet.PageSetup.OddFooterString = "&\"Arial\"&12&B&KFFC000 Odd_Footer"

# Even page header and footer
sheet.PageSetup.EvenHeaderString = "&\"Arial\"&12&B&KFF0000 Even_Header"
sheet.PageSetup.EvenFooterString = "&\"Arial\"&12&B&KFF0000 Even_Footer"

Note that once DifferentOddEven is enabled, you should use properties such as OddHeaderString / EvenHeaderString rather than the plain LeftHeader, otherwise the settings will not be distinguished by parity.

Using a Different Header and Footer on the First Page

Covers or first pages usually should not show the same header and footer as the body. Set DifferentFirst to 1, then set the first page's content separately:

sheet.PageSetup.DifferentFirst = 1

# First page header and footer
sheet.PageSetup.FirstHeaderString = "Different First page"
sheet.PageSetup.FirstFooterString = "Different First footer"

# Header and footer for the remaining pages
sheet.PageSetup.LeftHeader = "Demo of Spire.XLS"
sheet.PageSetup.CenterFooter = "Footer by Spire.XLS"

FirstHeaderString / FirstFooterString apply only to the first page, while LeftHeader, CenterFooter, and the like apply to the remaining pages. The two sets do not interfere with each other.

Inserting Images into Headers and Footers

Placing a company logo in the header or footer is also common. Provide the image through a property such as LeftHeaderImage, then set the corresponding section's text to the &G placeholder:

image = Stream("logo.png")

# Image header: provide the image, then use the &G placeholder
sheet.PageSetup.LeftHeaderImage = image
sheet.PageSetup.LeftHeader = "&G"

# Image footer
sheet.PageSetup.CenterFooterImage = image
sheet.PageSetup.CenterFooter = "&G"

&G is the fixed image placeholder; the image itself is supplied by properties such as LeftHeaderImage. The two work together to complete the setup.

Setting an image header on the first page has dedicated methods. For the first page's left section:

sheet.PageSetup.DifferentFirst = 1

imageStream = Stream("logo.png")
sheet.PageSetup.SetFirstLeftHeaderImage(imageStream)
sheet.PageSetup.SetFirstLeftFooterImage(imageStream)

sheet.PageSetup.LeftHeader = "Demo of Spire.XLS"
sheet.PageSetup.LeftFooter = "Footer by Spire.XLS"

SetFirstLeftHeaderImage() and SetFirstLeftFooterImage() are dedicated to the first page's image header and footer.

Practical Tips

• Headers and footers are only visible in print preview or Page Layout view. Switch to Page Layout view with sheet.ViewMode = ViewMode.Layout to check the result.
• The six sections (header left/center/right and footer left/center/right) are independent, so a report can populate only the sections it needs.
• In the format code &"font name"&size, the font name must be quoted, with the size after the quotes; the order cannot be reversed.
• For batch processing, loop over workbook.Worksheets and apply the same header and footer settings to every sheet to keep the workbook consistent.
• Call Dispose() when finished to release resources.

Conclusion

This article covered several ways to set Excel headers and footers with Python: writing text into the six PageSetup section properties; controlling font and style with format codes such as &"font"&size, &B, and &K; distinguishing odd/even pages and the first page with DifferentOddEven and DifferentFirst; and inserting images through LeftHeaderImage together with the &G placeholder. With these techniques, consistent layout for printed reports can be handed off to a script.