If you prepare reports regularly, you’ve probably seen these problems. A printed sales report cuts off the two rightmost columns. A financial report loses its column headers after the first page. In another report, inconsistent margins leave some text hidden by the binding. Adjusting each workbook by hand takes time, and individual settings are easy to overlook. With Python, you can define the print layout once and apply it consistently across your reports.
Why Configure Excel Page Setup in Code?
Excel offers many print settings, including paper size, orientation, margins, print area, repeating titles, scaling, headers, and footers. Setting them by hand is manageable for one workbook. Keeping those settings consistent across dozens or hundreds of reports is harder.
- Consistent layouts. Every report can use the same paper size and orientation, such as A4 landscape.
- Complete settings in batch runs. Each workbook receives the full set of print settings defined in the script.
- Reusable code. When the data source changes, you can run the same page setup logic again.
This tutorial uses Free Spire.XLS for Python to cover everything from basic paper settings to headers, footers, and advanced print options.
1. Install the Library and Run a Minimal Example
pip install spire.xls.free
Start by creating a workbook and setting its first worksheet to A4 paper in landscape orientation.
from spire.xls import *
wb = Workbook()
ws = wb.Worksheets[0]
ws.Name = "Sample Sheet"
ps = ws.PageSetup
ps.PaperSize = PaperSizeType.PaperA4
ps.Orientation = PageOrientationType.Landscape
wb.SaveToFile("minimal.xlsx", ExcelVersion.Version2013)
wb.Dispose()
The key object is ws.PageSetup. It exposes the print settings for the worksheet, including paper size, orientation, margins, print area, repeating titles, scaling, headers, and footers. The following examples use the same object to configure those properties.
2. Configure Common Page Setup Options in One Script
This example creates a regional sales report. It uses a list of dictionaries for the sales records and configures the print layout in one pass.
from spire.xls import *
wb = Workbook()
ws = wb.Worksheets[0]
ws.Name = "Regional Sales Report"
# Write the column headers
col_titles = ["Region", "Category", "Sales (CNY 10,000s)", "Gross Margin"]
for c, title in enumerate(col_titles, 1):
ws.Range[1, c].Text = title
# Write the sales records
rows = [
{"Region": "East China", "Category": "Laptops", "Sales": 120, "Margin": "15%"},
{"Region": "East China", "Category": "Tablets", "Sales": 85, "Margin": "12%"},
{"Region": "North China", "Category": "Laptops", "Sales": 95, "Margin": "13%"},
{"Region": "North China", "Category": "Tablets", "Sales": 70, "Margin": "10%"},
{"Region": "South China", "Category": "Laptops", "Sales": 110, "Margin": "14%"},
{"Region": "South China", "Category": "Tablets", "Sales": 90, "Margin": "11%"},
]
for r, item in enumerate(rows, start=2):
ws.Range[r, 1].Value = item["Region"]
ws.Range[r, 2].Value = item["Category"]
ws.Range[r, 3].Value = str(item["Sales"])
ws.Range[r, 4].Value = item["Margin"]
ps = ws.PageSetup
# Set the paper size and orientation
ps.PaperSize = PaperSizeType.PaperA4
ps.Orientation = PageOrientationType.Landscape
# Set page, header, and footer margins in inches
ps.TopMargin = 1.2
ps.BottomMargin = 1.2
ps.LeftMargin = 0.8
ps.RightMargin = 0.8
ps.HeaderMarginInch = 0.4
ps.FooterMarginInch = 0.4
# Print only A1:D5
ps.PrintArea = "A1:D5"
# Repeat the first row on every printed page
ps.PrintTitleRows = "$1:$1"
# Center the content on the page
ps.CenterHorizontally = True
ps.CenterVertically = True
# Fit the print area to one page wide and one page tall
ps.FitToPagesWide = 1
ps.FitToPagesTall = 1
# Set the header and footer
ps.LeftHeader = '&"Arial"&12 Regional Sales Report'
ps.CenterFooter = "Page &P of &N"
ps.RightFooter = "Date: &D"
ws.Range.AutoFitColumns()
wb.SaveToFile("sales_pagesetup.xlsx", ExcelVersion.Version2013)
wb.Dispose()
Paper size and orientation.PaperSize sets the paper format, and Orientation selects portrait or landscape. Landscape gives wide tables more room across the page and can help keep the rightmost columns together.
Margins. The top, bottom, left, and right margins are measured in inches. HeaderMarginInch and FooterMarginInch set the header and footer offsets from the top and bottom edges of the page, respectively.
Print area.PrintArea uses standard Excel range notation, such as "A1:D5". In this example, the printout includes the header and the first four data rows. The last two data rows remain in the workbook outside the print area.
Repeating title rows.PrintTitleRows takes a reference such as "$1:$1", which repeats the first row on every printed page. This is useful when a long table spans multiple pages.
Scaling. Setting both FitToPagesWide and FitToPagesTall to 1 specifies a print layout one page wide and one page tall. For large tables, fitting everything onto one page can make the text too small to read comfortably.
Header and footer codes. Header and footer strings support codes for page numbers, dates, times, and text formatting.
| Code | Meaning |
|---|---|
&P |
Current page number |
&N |
Total number of pages |
&D |
Current date |
&T |
Current time |
&"Font Name"&Size |
Font and size, such as&"Arial"&12
|
&B/&I
|
Bold / italic |
3. Use Advanced Print Options
For an internal review, you might want to print gridlines, row numbers, and column letters so readers can refer to specific cells. Excel also offers black-and-white printing, draft quality, and options for printing comments and formula errors.
from spire.xls import *
wb = Workbook()
ws = wb.Worksheets[0]
ws.Name = "Metrics Review"
ws.Range[1, 1].Text = "Metric"
ws.Range[1, 2].Text = "Value"
ws.Range[1, 3].Text = "Unit"
metrics = [
("Total Sales", 150000, "CNY"),
("Average Order Value", 2500, "CNY"),
("Customer Count", 1200, "customers"),
("Conversion Rate", 15, "%"),
]
for r, (name, val, unit) in enumerate(metrics, start=2):
ws.Range[r, 1].Value = name
ws.Range[r, 2].NumberValue = val
ws.Range[r, 3].Value = unit
ps = ws.PageSetup
ps.IsPrintGridlines = True # Print gridlines
ps.IsPrintHeadings = True # Print row numbers and column letters
ps.BlackAndWhite = True # Print in black and white
ps.PrintComments = PrintCommentType.InPlace # Print comments where they appear
ps.PrintErrors = PrintErrorsType.NA # Print cell errors as #N/A
ws.Range.AutoFitColumns()
wb.SaveToFile("metrics_print.xlsx", ExcelVersion.Version2013)
wb.Dispose()
IsPrintGridlines controls whether gridlines appear on the printout, while IsPrintHeadings adds row numbers and column letters. BlackAndWhite enables monochrome printing, and Draft selects draft quality. PrintComments specifies where comments appear, and PrintErrors controls how Excel displays cell errors on the printed page.
4. Page Setup Workflow and Property Reference
-
Get a workbook. Create one with
Workbook()or open an existing file withLoadFromFile(). -
Select a worksheet. Access it through
wb.Worksheets[index]. -
Get the page setup object. Use
ws.PageSetupto access the worksheet’s print settings. - Apply the settings you need. Set the paper size, orientation, margins, print area, repeating titles, centering, scaling, headers, footers, and advanced options as needed.
-
Save the workbook. Call
SaveToFile().
| Property | Purpose |
|---|---|
PaperSize |
Paper format, such as A3, A4, A5, or Letter |
Orientation |
Portrait or landscape orientation |
TopMargin/BottomMargin/LeftMargin/RightMargin
|
Page margins in inches |
HeaderMarginInch/FooterMarginInch
|
Header and footer margins in inches |
PrintArea |
Range to print, such as"A1:D5"
|
PrintTitleRows/PrintTitleColumns
|
Rows or columns to repeat on each printed page, such as"$1:$1"or"$A:$A"
|
CenterHorizontally/CenterVertically
|
Horizontal or vertical centering on the printed page |
FitToPagesWide/FitToPagesTall
|
Target number of printed pages across or down |
LeftHeader/CenterHeader/RightHeader
|
Left, center, and right sections of the header |
LeftFooter/CenterFooter/RightFooter
|
Left, center, and right sections of the footer |
IsPrintGridlines/IsPrintHeadings
|
Printing gridlines, row numbers, and column letters |
BlackAndWhite/Draft
|
Black-and-white printing and draft quality |
PrintComments/PrintErrors
|
How comments and cell errors appear in print |
Conclusion
Defining Excel page setup in Python lets you apply the same print settings every time a report is generated. You can reuse the settings for paper size, orientation, margins, print area, repeating titles, scaling, headers, and footers across an entire batch of workbooks.
Free Spire.XLS can also write data, format worksheets, and add charts. You can combine those operations with page setup in a single script that builds the report, applies the print layout, and saves the finished Excel file.

Top comments (0)