DEV Community: Allen Yang

How to Efficiently Combine Multiple Excel Files with Python

Allen Yang — Fri, 31 Jul 2026 07:45:26 +0000

In real-world work, data is often scattered across multiple Excel files — sales reports split by month, employee information organized by department, or order data tallied by region. When it comes time to consolidate and analyze, the first step is merging these files into a single workbook. Copying and pasting dozens of worksheets by hand is both time-consuming and error-prone; a single slip can lose formatting or miss data.

This article shows how to use Python to merge multiple Excel files (.xlsx and .xls) into one workbook. After merging, each source file's worksheets are preserved under their original names and in their original order, with formatting and data fully intact — ready for sorting, filtering, or data analysis.

Why Merge Excel Files with Code

Replacing manual merging with code brings three main benefits:

Batch processing - Whether there are 3 files or 30, the process is identical, and everything completes in a single run
Format preservation - Worksheets are copied along with cell styles, formulas, and other content, avoiding the formatting chaos caused by manual pasting
Reusable workflow - The merging logic can be wrapped into a script and run on a weekly or monthly schedule, forming a stable data consolidation routine

Environment Setup

This article uses the Spire.XLS for Python library, which can be installed with:

pip install Spire.XLS

Merging Multiple Excel Files

The core idea is simple: create a new workbook as the target, then load each source file in a loop and copy its worksheets one by one into the target workbook. The AddCopy method handles worksheet copying, and its second parameter, WorksheetCopyType.CopyAll, ensures that everything — data, formatting, formulas, and more — is copied along.

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

# Files to merge
files = ["Sales_January.xlsx", "Sales_February.xlsx", "Sales_March.xls"]

# Create the target workbook and specify its version
newbook = Workbook()
newbook.Version = ExcelVersion.Version2013

# Clear the default worksheet to avoid a blank sheet in the result
newbook.Worksheets.Clear()

# Temporary workbook for loading source files one at a time
tempbook = Workbook()

for file in files:
    # Load the source file
    tempbook.LoadFromFile(file)
    # Copy each worksheet from the source file into the target workbook
    for sheet in tempbook.Worksheets:
        newbook.Worksheets.AddCopy(sheet, WorksheetCopyType.CopyAll)

# Save the merged result
newbook.SaveToFile("Merged.xlsx", ExcelVersion.Version2013)

newbook.Dispose()
tempbook.Dispose()

A few key points:

A new Workbook() comes with an empty worksheet named "Sheet1"; calling Worksheets.Clear() removes it so the final file contains only the data actually merged in
LoadFromFile automatically detects the .xlsx and .xls formats from the file content, so mixed-format files can be processed together
Each loop iteration reloads a file and overwrites tempbook, so no matter how many files are merged, only one copy of source data stays in memory at a time
Merged worksheets keep their original names and order, making it easy to tell where the data came from

Merging Selected Worksheets

Sometimes you only need part of each file's worksheets rather than all of them. Since the first argument of AddCopy accepts a worksheet object, you can grab the sheets you need by index and decide which ones to copy:

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

newbook = Workbook()
newbook.Worksheets.Clear()
tempbook = Workbook()

# Load the first source file
tempbook.LoadFromFile("Sales_January.xlsx")

# Copy only the first two worksheets
newbook.Worksheets.AddCopy(tempbook.Worksheets[0], WorksheetCopyType.CopyAll)
newbook.Worksheets.AddCopy(tempbook.Worksheets[1], WorksheetCopyType.CopyAll)

newbook.SaveToFile("MergedSelected.xlsx", ExcelVersion.Version2013)
newbook.Dispose()
tempbook.Dispose()

This index-based approach suits scenarios where the file structure is fixed. If there are many source files with a uniform structure, you can also combine it with conditional logic to copy only the worksheets that meet certain criteria — for example, only sheets containing a specific keyword — enabling selective merging.

Practical Tips

Automatically Collecting Files with glob

Hard-coding the file list becomes cumbersome when there are many files. Instead, you can use the glob module from Python's standard library to match files by pattern:

import glob

files = glob.glob("./reports/*.xlsx") + glob.glob("./reports/*.xls")

As long as you drop files into the specified directory, the script automatically collects all of them for merging — and you don't need to touch the code when new files are added.

Controlling the Output Version

The ExcelVersion enumeration controls the output format when saving: Version2013 produces .xlsx files, while Version2010 is compatible with Excel 2010 and later. If the merged result is going to colleagues who use older versions of Excel, choosing a lower version is the safer bet.

Releasing Resources Promptly

Workbook objects consume system resources. In batch-processing scenarios, calling Dispose() after the work is done prevents memory from growing continuously during long-running processes.

Conclusion

This article covered how to merge multiple Excel files with Python: create a target workbook and clear its default worksheet, load each source file in a loop, copy the worksheets into the target workbook with AddCopy, and finally save the result. This workflow fits common scenarios such as report consolidation and data archiving, bringing scattered data together into a single file.

Building on this foundation, you can also sort, filter, or format the worksheets before or after merging to create a more complete Excel automation pipeline.

How to Add Text Headers and Footers to PDF Using Python

Allen Yang — Fri, 31 Jul 2026 07:05:01 +0000

PDF is the standard format for document distribution and archiving, and headers and footers are an indispensable part of formal documents—elements such as company names, document titles, section information, and page numbers all rely on them. For developers, manually adding headers and footers to a PDF that is dozens or even hundreds of pages long is clearly impractical. When documents need to be generated in batches on a regular basis, a programmatic approach is almost the only viable option.

This article explains how to add headers and footers to PDF documents using Python, covering plain-text headers and footers, image headers, different headers for different pages, and automatic page numbers generated through page templates.

Why Add Headers and Footers Programmatically

Handling PDF headers and footers in code offers several practical advantages over manual editing:

Batch Processing — Iterate through all pages with a loop and apply uniform headers and footers to the entire document in a single run
Dynamic Content — Elements like page numbers and section numbers can be generated automatically based on the document state, with no manual maintenance required
Consistent Styling — Fonts, colors, and alignment are controlled uniformly by code, avoiding the inconsistencies of manual work

Environment Setup

This article uses the Spire.PDF library to process PDF documents. Install it as follows:

pip install Spire.PDF

The library provides complete PDF creation and editing capabilities. Headers and footers are implemented by drawing text, images, and lines on the page canvas. Note that the PDF coordinate system has its origin at the bottom-left corner of the page, and the unit is points (1 point = 1/72 inch).

Adding Text Headers and Footers

To add uniform text headers and footers to an entire document, the most common approach is to iterate through all pages and draw the content on each page's canvas. The following code adds right-aligned header text at the top of every page, and a separator line with page numbers at the bottom:

from spire.pdf.common import *
from spire.pdf import *

doc = PdfDocument()
doc.LoadFromFile("Input.pdf")
margin = doc.PageSettings.Margins

# Define the pen, font, and alignment
pen = PdfPen(PdfBrushes.get_Black(), 0.75)
font = PdfTrueTypeFont("Arial", 12.0, PdfFontStyle.Italic, True)
brush = PdfBrushes.get_Black()
format = PdfStringFormat(PdfTextAlignment.Right)
format.MeasureTrailingSpaces = True
space = font.Height * 0.75

for i in range(doc.Pages.Count):
    page = doc.Pages.get_Item(i)
    x = margin.Left
    width = page.Canvas.ClientSize.Width - margin.Left - margin.Right

    # Header: draw a separator line + text
    y = margin.Top - space
    page.Canvas.DrawLine(pen, x, y, x + width, y)
    page.Canvas.DrawString("Internal Use", font, brush, x + width, y - font.Height, format)

    # Footer: draw a separator line + page number
    y = page.Canvas.ClientSize.Height - margin.Bottom + space
    page.Canvas.DrawLine(pen, x, y, x + width, y)
    label = "{0} / {1}".format(i + 1, doc.Pages.Count)
    page.Canvas.DrawString(label, font, brush, x + width, y + 1, format)

doc.SaveToFile("HeaderAndFooter.pdf")
doc.Close()

Output preview:

A few key points to note:

PdfTrueTypeFont specifies the font name, size, and style; PdfFontStyle.Italic means italic, and the last Boolean parameter controls whether the font is embedded
PdfStringFormat controls text alignment; setting MeasureTrailingSpaces = True makes right-aligned text account for trailing spaces, ensuring precise positioning
The header sits above the top margin and the footer below the bottom margin; font.Height * 0.75 reserves a reasonable gap between the text and the separator line

Inserting Images in Headers

Many documents place a company logo in the header. Images are also drawn on the canvas—load the image with PdfImage.FromFile(), then call DrawImage() to specify where it should be drawn:

headerImage = PdfImage.FromFile("Logo.png")
page.Canvas.DrawImage(headerImage, PointF(0.0, 0.0))

The second parameter of DrawImage() is the coordinate of the image's top-left corner. If you need to control the image size, you can pass additional width and height parameters (in points)—for example, to shrink the logo by half:

page.Canvas.DrawImage(headerImage, 50.0, 20.0,
    headerImage.Width / 2.0, headerImage.Height / 2.0)

Setting Different Headers for Different Pages

Documents with many sections often require a different header for each chapter. Since headers are implemented by drawing on specific pages, you can simply draw different content per page index:

rect = RectangleF(PointF(0.0, 20.0), SizeF(doc.PageSettings.Size.Width, 50.0))

# Page 1: red, bold, center-aligned
font1 = PdfTrueTypeFont("Arial", 15.0, PdfFontStyle.Bold, True)
format1 = PdfStringFormat()
format1.Alignment = PdfTextAlignment.Center
doc.Pages[0].Canvas.DrawString("Chapter 1 Overview", font1, PdfBrushes.get_Red(), rect, format1)

# Page 2: black, regular, left-aligned
font2 = PdfTrueTypeFont("Arial", 15.0, PdfFontStyle.Regular, True)
format2 = PdfStringFormat()
format2.Alignment = PdfTextAlignment.Left
doc.Pages[1].Canvas.DrawString("Chapter 2 Implementation", font2, PdfBrushes.get_Black(), rect, format2)

Here, doc.Pages[index] is used to access a specific page directly, and each page can then be given its own font, color, and alignment. If a document has many sections, you can first calculate the page ranges for each chapter and then combine that with a loop for batch drawing.

Managing Headers and Footers with Page Templates

When headers and footers need to apply to all pages of a document and take effect automatically, using a page template (PdfPageTemplateElement) is a more elegant solution. Templates are decoupled from page content and are applied automatically to any pages added later:

from spire.pdf.common import *
from spire.pdf import *

# Create a document and set the page size and margins
doc = PdfDocument()
doc.PageSettings.Size = PdfPageSize.A4()
doc.PageSettings.Margins = PdfMargins(50.0, 50.0, 50.0, 50.0)
pageSize = doc.PageSettings.Size

# Create a header template: draw the logo and a separator line
headerSpace = PdfPageTemplateElement(pageSize.Width, 50.0)
headerSpace.Foreground = False
image = PdfImage.FromFile("Logo.png")
headerSpace.Graphics.DrawImage(image, 50.0, 20.0, image.Width / 2.0, image.Height / 2.0)
pen = PdfPen(PdfBrushes.get_LightGray(), 1.0)
headerSpace.Graphics.DrawLine(pen, 50.0, 45.0, pageSize.Width - 50.0, 45.0)

# Create a footer template: use automatic fields to draw "Page x of y"
footerSpace = PdfPageTemplateElement(pageSize.Width, 50.0)
footerSpace.Foreground = False
footerSpace.Graphics.DrawLine(pen, 50.0, 0.0, pageSize.Width - 50.0, 0.0)
number = PdfPageNumberField()
count = PdfPageCountField()
compositeField = PdfCompositeField(
    PdfTrueTypeFont("Arial", 10.0, PdfFontStyle.Regular, True),
    PdfBrushes.get_Black(), "Page {0} of {1}", [number, count])
compositeField.StringFormat = PdfStringFormat(PdfTextAlignment.Left, PdfVerticalAlignment.Top)
compositeField.Bounds = RectangleF(PointF(50.0, 5.0), SizeF(200.0, 20.0))
widget = PdfGraphicsWidget(compositeField.Ptr)
widget.Draw(footerSpace.Graphics)

# Apply the templates to the top and bottom of the document
doc.Template.Top = headerSpace
doc.Template.Bottom = footerSpace

# Add a page and save
doc.Pages.Add()
doc.SaveToFile("TemplateHeaderFooter.pdf", FileFormat.PDF)
doc.Close()

The key here is the two automatic fields, PdfPageNumberField and PdfPageCountField: they don't store concrete values but are replaced automatically with the current page number and total page count at render time, so there's no need to iterate through pages manually. PdfCompositeField combines multiple fields into a single formatted text string. Setting the template's Foreground property to False places the template content behind the page content, so it never obscures the body text.

Practical Tips

Preserve existing page content: Drawing headers and footers directly onto existing pages can overwrite the body text. You can first save the original page as a template with page.CreateTemplate(), draw the headers and footers on the new page, and then render the original content to the bottom layer via PdfGraphicsWidget, adding headers and footers without damaging the content.
Semi-transparent effects: Calling page.Canvas.SetTransparency(0.5) lowers the opacity of subsequent drawing, letting headers and footers appear in a watermark-like style that is visually softer.
Remember font embedding: The last Boolean parameter of the PdfTrueTypeFont constructor controls whether the font is embedded in the document. Setting it to True ensures fonts render consistently when the document is opened on other devices.

Conclusion

This article covered several ways to add headers and footers to PDF documents using Python: drawing text and images on the canvas for custom headers and footers, setting different content per page by page index, and using page templates with automatic fields to manage page numbers uniformly. In practice, you can choose the approach that fits your document structure—simple uniform headers and footers can be done with a drawing loop, while page templates are the more convenient choice when you need automatic page numbers or batch generation.

Once you've mastered these techniques, you can combine them with find-and-replace, watermarks, layers, and other operations to build a complete document automation pipeline.

How to Draw Lines, Rectangles, and Shapes in PDF Using Python

Allen Yang — Wed, 29 Jul 2026 09:47:19 +0000

In everyday document processing, PDF has become the go-to format for distribution and archiving thanks to its cross-platform consistency. However, when generating PDFs, text content alone is often not enough—many scenarios require adding visual elements to the document, such as drawing dividers, highlighting rectangular areas, or adding decorative shapes. Whether you're dividing sections in a report or sketching prototype elements in a design draft, programmatic drawing is a fundamental and practical skill.

This article introduces how to draw common graphics in PDF documents using Python, including lines, rectangles, ellipses, and more complex custom paths.

Why Draw Graphics Programmatically

Drawing graphics in PDF through code offers several advantages over manual operations:

Precise Control — The coordinates, dimensions, colors, and transparency of graphics can be defined precisely through parameters, eliminating the deviations caused by manual dragging
Batch Generation — With loops and conditional logic, you can generate large volumes of documents containing graphical elements at once
Dynamic Content — Automatically adjust the position and style of graphics based on data sources, making it ideal for reports and templates

Environment Setup

This article uses the Spire.PDF library to create PDF documents and draw graphics. Install it as follows:

pip install Spire.PDF

This library provides comprehensive PDF manipulation capabilities, including page management, graphics drawing, and text rendering.

Drawing Basic Shapes

Spire.PDF provides a series of drawing methods through the PdfCanvas object. All drawing operations are performed on the page's Canvas. The canvas uses a coordinate system with the origin at the bottom-left corner of the page, and the unit is points (1 point = 1/72 inch).

Drawing Lines

Drawing a line is the most basic graphics operation. Use the DrawLine() method by specifying the start and end coordinates.

from spire.pdf.common import *
from spire.pdf import *

# Create a PDF document
doc = PdfDocument()
page = doc.Pages.Add()

# Save the canvas state
state = page.Canvas.Save()

# Create a pen (black, line width 1 point)
pen = PdfPen(PdfRGBColor(Color.get_Black()), 1.0)

# Draw a horizontal line from (50, 100) to (400, 100)
page.Canvas.DrawLine(pen, 50.0, 100.0, 400.0, 100.0)

# Draw a diagonal line from (50, 100) to (225, 300)
pen2 = PdfPen(PdfRGBColor(Color.get_Red()), 1.0)
page.Canvas.DrawLine(pen2, 50.0, 100.0, 225.0, 300.0)

# Restore the canvas state
page.Canvas.Restore(state)

# Save the document
doc.SaveToFile("DrawLines.pdf")
doc.Close()

PdfPen is the core tool for drawing lines—it determines the color and width of the line. The first two parameters of DrawLine() are the start coordinates, and the last two are the end coordinates. It's a good practice to call Save() and Restore() around each drawing operation to avoid affecting previous drawing settings with subsequent operations.

Drawing Dashed Lines

In real-world development, dashed lines are often used to separate sections or mark boundaries. This can be achieved by setting the pen's DashStyle and DashPattern properties.

# Create a red pen with a line width of 3 points
pen = PdfPen(PdfRGBColor(Color.get_Red()), 3.0)

# Set the dash style
pen.DashStyle = PdfDashStyle.Dash
pen.DashPattern = [1, 4, 1]  # Dash pattern: 1pt solid, 4pt gap, 1pt solid

# Draw the dashed line
page.Canvas.DrawLine(pen, 50.0, 200.0, 400.0, 200.0)

The PdfDashStyle enum provides predefined dash styles (Dash, Dot, DashDot, etc.), while DashPattern allows you to define a more precise custom dash pattern—the numbers in the array alternately represent the lengths of solid segments and gaps.

Drawing Rectangles

Rectangles are among the most commonly used shapes for document annotation. You can draw either a rectangular border or a filled rectangle.

# Draw a rectangular border
pen = PdfPen(PdfRGBColor(Color.get_Black()), 0.5)
# Parameters: pen, x, y, width, height
page.Canvas.DrawRectangle(pen, 100.0, 150.0, 200.0, 120.0)

# Draw a filled rectangle
pen2 = PdfPen(PdfRGBColor(Color.get_Black()), 1.0)
brush = PdfSolidBrush(PdfRGBColor(Color.get_OrangeRed()))
rect = RectangleF(PointF(100.0, 300.0), SizeF(200.0, 120.0))
page.Canvas.DrawRectangle(pen2, brush, rect)

DrawRectangle() has two common overloads: one draws only the border and accepts a pen along with position parameters; the other draws a fill as well and takes an additional PdfBrush object. PdfSolidBrush is the most commonly used fill brush, filling the interior of a shape with a single color.

Drawing Ellipses

Ellipses and circles are common in diagram annotations and can be drawn using the DrawEllipse() method.

# Draw an elliptical border (defined by a bounding rectangle)
pen = PdfPen(PdfRGBColor(Color.get_CadetBlue()), 1.0)
# The parameters define the bounding rectangle; the ellipse is drawn inside it
page.Canvas.DrawEllipse(pen, 220.0, 320.0, 100.0, 90.0)

# Draw a filled ellipse
brush = PdfSolidBrush(PdfRGBColor(Color.get_CadetBlue()))
page.Canvas.DrawEllipse(brush, 380.0, 325.0, 80.0, 80.0)

# The third and fourth parameters control the horizontal and vertical radii
# When both are equal, the ellipse becomes a perfect circle
page.Canvas.DrawEllipse(brush, 100.0, 450.0, 50.0, 50.0)  # This draws a perfect circle

An ellipse is essentially defined by its bounding rectangle: the closer the rectangle is to a square, the closer the ellipse is to a perfect circle. This approach is conceptually similar to the way ellipses are drawn on an HTML Canvas.

Drawing Custom Paths

For complex shapes like stars or polygons, you can combine multiple line segments using the PdfPath object and then draw them all at once with the DrawPath() method.

import math

# Calculate the five vertices of a pentagon
points = [None] * 5
for i in range(5):
    x = float(math.cos(i * 2 * math.pi / 5))
    y = float(math.sin(i * 2 * math.pi / 5))
    points[i] = PointF(x, y)

# Create a path and add line segments
path = PdfPath()
path.AddLine(points[2], points[0])
path.AddLine(points[0], points[3])
path.AddLine(points[3], points[1])
path.AddLine(points[1], points[4])
path.AddLine(points[4], points[2])

# Draw the path outline
pen = PdfPen(PdfRGBColor(Color.get_DeepSkyBlue()), 0.02)
page.Canvas.ScaleTransform(50.0, 50.0)
page.Canvas.TranslateTransform(5.0, 5.0)
page.Canvas.DrawPath(pen, path)

# Draw the filled path
brush = PdfSolidBrush(PdfRGBColor(Color.get_DeepSkyBlue()))
page.Canvas.DrawPath(pen, brush, path)

PdfPath supports building shapes segment by segment using methods like AddLine(), AddArc(), and AddCurve(). ScaleTransform() and TranslateTransform() are used to scale and translate the coordinate system, making it easy to draw the same shape at different positions.

Controlling Shape Transparency

Transparency control creates richer visual effects when shapes overlap. Use the SetTransparency() method to set the transparency level for subsequent drawing operations.

# Set transparency (range 0.0~1.0, where 0.0 is fully transparent and 1.0 is fully opaque)
page.Canvas.SetTransparency(0.5, 0.5)

# Shapes drawn after this will be rendered at 50% opacity
pen = PdfPen(PdfRGBColor(Color.get_Black()), 1.0)
brush = PdfSolidBrush(PdfRGBColor(Color.get_Red()))
page.Canvas.DrawRectangle(pen, brush, RectangleF(PointF(200.0, 300.0), SizeF(200.0, 100.0)))

# Set a different transparency level
page.Canvas.SetTransparency(0.2, 0.2)
page.Canvas.DrawRectangle(pen, brush, RectangleF(PointF(300.0, 250.0), SizeF(200.0, 100.0)))

Transparency is especially useful when working with multi-layered shapes, such as distinguishing different levels of annotations in a product design draft.

Complete Example: Drawing Multiple Shapes Together

The following code combines all the drawing operations introduced above into a single document, producing a comprehensive example that includes lines, rectangles, ellipses, and custom paths.

from spire.pdf.common import *
from spire.pdf import *
import math

# Create a document
doc = PdfDocument()
page = doc.Pages.Add()

# Save the canvas state
state = page.Canvas.Save()

# 1. Draw a line
pen = PdfPen(PdfRGBColor(Color.get_Black()), 1.0)
page.Canvas.DrawLine(pen, 50.0, 50.0, 200.0, 50.0)

# 2. Draw a dashed line
pen_dash = PdfPen(PdfRGBColor(Color.get_Red()), 2.0)
pen_dash.DashStyle = PdfDashStyle.Dash
page.Canvas.DrawLine(pen_dash, 50.0, 80.0, 200.0, 80.0)

# 3. Draw a rectangular border
pen_rect = PdfPen(PdfRGBColor(Color.get_Blue()), 1.0)
page.Canvas.DrawRectangle(pen_rect, 50.0, 120.0, 150.0, 100.0)

# 4. Draw a filled rectangle
pen_fill = PdfPen(PdfRGBColor(Color.get_Black()), 0.5)
brush_fill = PdfSolidBrush(PdfRGBColor(Color.get_OrangeRed()))
page.Canvas.DrawRectangle(pen_fill, brush_fill, 
    RectangleF(PointF(50.0, 250.0), SizeF(150.0, 100.0)))

# 5. Draw an ellipse
brush_ellipse = PdfSolidBrush(PdfRGBColor(Color.get_CadetBlue()))
page.Canvas.DrawEllipse(brush_ellipse, 300.0, 120.0, 100.0, 80.0)

# 6. Draw a perfect circle
page.Canvas.DrawEllipse(brush_ellipse, 300.0, 250.0, 60.0, 60.0)

# Restore the canvas state
page.Canvas.Restore(state)

# Save
doc.SaveToFile("ShapesDemo.pdf")
doc.Close()

Output preview:

Conclusion

This article introduced the basic methods for drawing graphics in PDF documents using Python, covering common elements such as lines, dashed lines, rectangles, ellipses, and custom paths. In real-world development, these fundamental drawing capabilities can be combined and applied to many scenarios—for instance, drawing table borders in reports, adding annotation frames in design drafts, or creating decorative lines in certificate templates.

Once you've mastered these basic graphics techniques, you can explore more advanced drawing capabilities such as gradient fills, shape transformations, and layer management to meet more complex document processing needs.

Configuring Excel Page Layout and Print Settings Using Python

Allen Yang — Fri, 24 Jul 2026 10:15:54 +0000

In everyday work, Excel files are frequently printed — reports, inventories, invoices, and more. However, without proper page layout settings, printouts often suffer from truncated content, uneven margins, missing header rows, and other issues that undermine the document's professionalism and readability.

By programmatically configuring Excel page layout parameters with Python, you can apply print-ready settings at the moment the file is generated, eliminating tedious manual adjustments. This article walks through how to set margins, paper size, page orientation, print area, print title rows/columns, and fit-to-page scaling using Spire.XLS for Python.

Prerequisites

Install the required Python package:

pip install Spire.XLS

Setting Page Margins

Page margins define the distance between the printed content and the edges of the paper. Proper margins make printouts look cleaner and more readable.

You can set the top, bottom, left, and right margins independently through the TopMargin, BottomMargin, LeftMargin, and RightMargin properties of the PageSetup object. Values are specified in inches.

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

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

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

# Set page margins (in inches)
pageSetup.TopMargin = 3
pageSetup.BottomMargin = 2
pageSetup.LeftMargin = 1
pageSetup.RightMargin = 1

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

The code above sets the top margin to 3 inches, the bottom margin to 2 inches, and both the left and right margins to 1 inch. Adjust these values as needed to suit your print requirements.

Setting Page Orientation

When a worksheet contains many columns, landscape orientation is usually a better choice than portrait, as it prevents content from exceeding the paper width.

You can switch the page orientation via the PageSetup.Orientation property:

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

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

sheet = workbook.Worksheets[0]

# Set page orientation to landscape
sheet.PageSetup.Orientation = PageOrientationType.Landscape

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

The PageOrientationType enumeration provides two options: Portrait (vertical) and Landscape (horizontal). By default, Excel uses portrait orientation.

Setting Paper Size

Different printing scenarios may call for different paper sizes. For instance, everyday office documents typically use Letter or A4, while large reports may require A3.

You can specify the paper size through the PageSetup.PaperSize property:

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

workbook = Workbook()

sheet = workbook.Worksheets[0]

# Set paper size to A4
sheet.PageSetup.PaperSize = PaperSizeType.PaperA4

workbook.SaveToFile("SetPaperSize.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The PaperSizeType enumeration supports a wide range of standard paper sizes, including PaperA3, PaperA4, PaperA5, PaperLetter, PaperLegal, and more. Choose the one that matches your printer's supported paper types.

Setting the Print Area

When a worksheet contains a large amount of data but only a portion of it needs to be printed, you can define a print area to specify exactly which cells should appear on the printout.

Use the PageSetup.PrintArea property to specify the print range as a cell address string:

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

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

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

# Set the print area to A1:E5
pageSetup.PrintArea = "A1:E5"

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

Once a print area is defined, Excel will only print the content within that range, ignoring everything outside it. This is especially useful when working with large datasets.

Setting Print Title Rows and Title Columns

When data spans multiple printed pages, header rows typically appear only on the first page, leaving subsequent pages without column headings and making the content harder to follow. By setting Print Title Rows, you can have specific rows automatically repeated at the top of every printed page.

Similarly, when a worksheet has many columns and prints across multiple pages horizontally, you can set Print Title Columns to repeat specific columns on every page.

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

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

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

# Set rows 1 and 2 as print title rows (repeated on every page)
pageSetup.PrintTitleRows = "$1:$2"

# Set columns A and B as print title columns (repeated on every page)
pageSetup.PrintTitleColumns = "$A:$B"

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

Note that title row and column references use the $ notation — for example, $1:$2 refers to rows 1 through 2, and $A:$B refers to columns A through B. This is consistent with Excel's absolute reference syntax.

Scaling to Fit the Page

When content exceeds a single page, you can use fit-to-page settings to automatically scale it across a specified number of pages. This approach is more convenient than manually adjusting the zoom percentage.

Use the FitToPagesTall and FitToPagesWide properties to control how many pages the content should span vertically and horizontally:

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

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

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

# Fit all content to 1 page tall and 1 page wide
pageSetup.FitToPagesTall = 1
pageSetup.FitToPagesWide = 1

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

Setting both properties to 1 shrinks all content onto a single page. If the worksheet has many rows but few columns, you can set FitToPagesTall to a larger value or to 0 (which removes the vertical page limit), while keeping FitToPagesWide at 1 to ensure the content width stays within one page.

Complete Example: Comprehensive Page Layout Configuration

The following example combines all of the settings above, configuring margins, orientation, paper size, print area, and print title rows for a single worksheet:

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

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

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

# Page margins
pageSetup.TopMargin = 2.5
pageSetup.BottomMargin = 2
pageSetup.LeftMargin = 1
pageSetup.RightMargin = 1

# Page orientation and paper size
pageSetup.Orientation = PageOrientationType.Landscape
pageSetup.PaperSize = PaperSizeType.PaperA4

# Print area
pageSetup.PrintArea = "A1:H50"

# Print title rows (repeat row 1 on every page)
pageSetup.PrintTitleRows = "$1:$1"

# Scale to fit page width
pageSetup.FitToPagesTall = 0
pageSetup.FitToPagesWide = 1

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

This code is well-suited for a typical landscape A4 report scenario: it applies sensible margins, restricts the print range, ensures the header row repeats on every page, and scales the content to fit the page width automatically.

Summary

This article covered several common techniques for configuring Excel page layout with Python, including page margins, orientation, paper size, print area, print title rows/columns, and fit-to-page scaling. These settings are invaluable in everyday office workflows and automated report generation, allowing developers to finalize print parameters programmatically and reduce manual intervention.

Beyond the features discussed here, Spire.XLS also supports additional page setup properties such as print quality, first page number, and header/footer margins — all of which can be explored further based on your specific requirements.

Creating and Managing Named Ranges in Excel Using Python

Allen Yang — Fri, 24 Jul 2026 09:45:43 +0000

When working with complex Excel worksheets, referencing data regions by raw cell addresses (such as A1:D10) is a common yet error-prone practice. As formulas and references multiply, keeping track of what each region represents becomes nearly impossible. Named ranges are designed to solve exactly this problem — they let you assign a meaningful name to a cell range, making formulas, data references, and code logic significantly clearer.

This article demonstrates how to create, query, modify, format, use in formulas, and delete named ranges in Excel using Python.

Prerequisites

This article uses the Spire.XLS for Python library to work with Excel files. Install it via pip:

pip install Spire.XLS

Then import the required modules in your Python script:

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

Creating a Named Range

The core idea behind a named range is to bind a cell range to a descriptive name. To create one, call the NameRanges.Add() method to add a new named range object, then point its RefersToRange property to the desired cell range.

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

# Create a named range and bind it to a specific cell range
named_range = workbook.NameRanges.Add("SalesData")
named_range.RefersToRange = sheet.Range["A1:E20"]

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

Here, NameRanges.Add() returns an INamedRange object. By setting its RefersToRange property, the association between the name and the range is established. From that point on, you can use the name SalesData in Excel formulas or code instead of the raw address A1:E20.

Workbook-Level and Worksheet-Level Named Ranges

Excel named ranges have two possible scopes: global (workbook-level) and local (worksheet-level). A workbook-level named range is accessible throughout the entire workbook, while a worksheet-level named range is only valid within the sheet where it is defined.

Workbook-level named ranges are created via workbook.NameRanges:

named_range = workbook.NameRanges.Add("GlobalRange")
named_range.RefersToRange = sheet.Range["A1:D10"]

Worksheet-level named ranges are created via sheet.Names:

named_range = sheet.Names.Add("LocalRange")
named_range.RefersToRange = sheet.Range["A1:D19"]

Worksheet-level named ranges are especially useful when multiple sheets each need their own independent range with the same name.

Querying Named Ranges

Retrieving All Named Ranges

Iterate through the NameRanges collection to list every named range in the workbook:

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

for name_range in workbook.NameRanges:
    print(name_range.Name)

workbook.Dispose()

Retrieving a Specific Named Range

You can access a specific named range by index or by name:

# Retrieve by index
range_by_index = workbook.NameRanges[1]

# Retrieve by name
range_by_name = workbook.NameRanges["SalesData"]

Getting the Address of a Named Range

Use the RefersToRange property to obtain the cell range and its address:

named_range = workbook.NameRanges[0]
cell_range = named_range.RefersToRange
address = cell_range.RangeAddress

print(f"The address of named range '{named_range.Name}' is {address}")

Looking Up a Named Range by Cell Range

If you have a cell range and want to check whether it has been defined as a named range, use the GetNamedRange() method:

sheet = workbook.Worksheets[0]
cell_range = sheet.Range["A2:D2"]
result = cell_range.GetNamedRange()

if result is not None:
    print(f"Named range for this range: {result.Name}")

This is particularly handy for verifying whether a given range already has a named range binding.

Renaming a Named Range

You can rename an existing named range by simply updating its Name property:

workbook.NameRanges[0].Name = "UpdatedRangeName"

After renaming, all formulas that reference the old name are automatically updated — consistent with Excel's native behavior.

Formatting a Named Range

Once you retrieve a named range, you can apply formatting to its underlying cell range. This is convenient when you need to highlight a specific data region:

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

named_range = workbook.NameRanges[0]
cell_range = named_range.RefersToRange

# Set the background color
cell_range.Style.Color = Color.get_Yellow()

# Set bold font
cell_range.Style.Font.IsBold = True

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

The range object obtained through RefersToRange behaves exactly like any regular cell range, so you can apply any standard style settings to it.

Merging Cells in a Named Range

The cell range covered by a named range also supports merge operations:

named_range = workbook.NameRanges[0]
cell_range = named_range.RefersToRange

# Merge cells
cell_range.Merge()

The merge operation combines all cells in the range into a single cell, commonly used to create header rows that span multiple columns.

Using Named Ranges in Formulas

One of the most valuable uses of named ranges is replacing hard-coded cell addresses in formulas. The following example shows how to define a named range for a region and then reference it directly in a SUM formula:

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

# Create a named range
named_range = workbook.NameRanges.Add("ScoreRange")
named_range.RefersToRange = sheet.Range["B10:B12"]

# Reference the named range in a formula
sheet.Range["B13"].Formula = "=SUM(ScoreRange)"

# Populate data
sheet.Range["B10"].Value2 = Int32(10)
sheet.Range["B11"].Value2 = Int32(20)
sheet.Range["B12"].Value2 = Int32(30)

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

Using named ranges significantly improves formula readability. When the data region changes, you only need to update the RefersToRange of the named range — there is no need to modify each formula's address references one by one.

Deleting Named Ranges

Named ranges can be removed by index or by name:

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

# Remove by index
workbook.NameRanges.RemoveAt(0)

# Remove by name
workbook.NameRanges.Remove("SalesData")

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

Keep in mind that after deleting a named range, any formulas referencing that name will return errors. Before removing a named range, it is a good practice to check whether any formulas depend on it.

Practical Tips

Naming conventions: Named range names cannot contain spaces, cannot start with a digit, and cannot conflict with cell addresses (such as A1). Use camelCase or underscore-separated names, such as SalesData or sales_data.
Scope selection: If a named range is only used within a single worksheet, prefer creating it as a worksheet-level named range to avoid name conflicts.
Dynamic updates: When the number of rows in a data region changes frequently, you can dynamically update the named range's coverage by reassigning its RefersToRange property in code.

Conclusion

Named ranges are a fundamental Excel feature that enhances formula readability and data management consistency. With Python, you can automate the creation, querying, modification, and cleanup of named ranges — making them ideal for batch-processing Excel files or building automated reporting systems.

This article covered the key operations: creating workbook-level and worksheet-level named ranges, querying by index and by name, retrieving address information, renaming, formatting, merging cells, referencing in formulas, and deleting. In real-world projects, you can combine these operations as needed to build more flexible and maintainable Excel data processing workflows.

How to Add, Edit, and Remove Hyperlinks in Excel Using Python

Allen Yang — Thu, 23 Jul 2026 10:19:13 +0000

When creating data reports, project tracking sheets, or resource indexes, you often need to embed clickable links in Excel cells — such as links to reference websites, email addresses, external files, or other worksheets within the same workbook. Manually adding hyperlinks one by one is not only tedious but also difficult to keep consistent when dealing with large datasets. Automating this process with Python scripts enables you to quickly create, read, modify, and delete hyperlinks in bulk, significantly boosting productivity.

This article demonstrates how to perform various hyperlink operations in Excel worksheets using Spire.XLS for Python, including adding web links, email links, image links, cross-sheet links, and external file links, as well as reading, modifying, and removing existing hyperlinks.

Prerequisites

Before getting started, install Spire.XLS for Python via pip:

pip install Spire.Xls

Then import the required modules in your script:

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

Adding Web and Email Hyperlinks

The most common use of Excel hyperlinks is to associate a URL or an email address with a cell. You can add a hyperlink to a specified cell using the sheet.HyperLinks.Add() method, set the link type via the Type property, and specify the target address through the Address property.

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

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

# Add a web hyperlink
url_link = sheet.HyperLinks.Add(sheet.Range["D10"])
url_link.TextToDisplay = sheet.Range["D10"].Text
url_link.Type = HyperLinkType.Url
url_link.Address = "http://en.wikipedia.org/wiki/Chicago"

# Add an email hyperlink
mail_link = sheet.HyperLinks.Add(sheet.Range["E10"])
mail_link.TextToDisplay = sheet.Range["E10"].Text
mail_link.Type = HyperLinkType.Url
mail_link.Address = "mailto:contact@example.com"

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

The key point here is the TextToDisplay property — it determines the text actually displayed in the cell. Typically, you set it to the cell's existing text so that the hyperlink overlays the cell without altering the original content. Although email links use the HyperLinkType.Url type, the address must be prefixed with the mailto: protocol so that clicking the link automatically opens the default email client.

Adding Image Hyperlinks

In addition to linking text cells, you can also bind hyperlinks to images in a worksheet, enabling effects such as clicking a logo to navigate to a website. Image hyperlinks are set using the SetHyperLink method, where the second parameter set to True indicates an absolute link.

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Insert an image and add a hyperlink
picture = sheet.Pictures.Add(2, 1, "logo.png")
picture.SetHyperLink("https://www.example.com", True)

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

The first two parameters of Pictures.Add(row, column, imagePath) specify the cell position where the top-left corner of the image is anchored, and the third parameter is the image file path. The second parameter of SetHyperLink, True, indicates that the URL is absolute; if the link points to a relative path resource, you can pass False instead.

Creating Cross-Sheet Links

In Excel files with multiple worksheets, you often need hyperlinks to navigate quickly between different sheets. This can be achieved by setting the link type to HyperLinkType.Workbook and specifying the address in the SheetName!CellAddress format.

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

workbook = Workbook()
sheet = workbook.Worksheets[0]
range = sheet.Range["A1"]

hyperlink = sheet.HyperLinks.Add(range)
hyperlink.Type = HyperLinkType.Workbook
hyperlink.TextToDisplay = "Go to cell C5 in Sheet2"
hyperlink.Address = "Sheet2!C5"

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

Note that the Address format is Sheet2!C5 — the sheet name comes before the exclamation mark, and the target cell reference comes after it. This is consistent with the address format used when manually creating cross-sheet hyperlinks in Excel.

Creating External File Links

When you need to reference other files on the local disk (such as reference documents or data source files), set the link type to HyperLinkType.File and point the address to the file path.

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

workbook = Workbook()
sheet = workbook.Worksheets[0]
range = sheet.Range["A1"]

hyperlink = sheet.HyperLinks.Add(range)
hyperlink.Type = HyperLinkType.File
hyperlink.TextToDisplay = "Open External Reference File"
hyperlink.Address = "C:/Documents/reference_data.xlsx"

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

When clicked, an external file link opens the target file using the default application associated with its file type in the operating system, making it ideal for linking to PDFs, Excel workbooks, Word documents, and other file types.

Adding Multiple Hyperlinks in Batch

In real-world scenarios, you often need to add multiple hyperlinks to a single worksheet. The following example demonstrates how to add multiple URL and email links in a loop:

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

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

# Add a homepage link
sheet.Range["B9"].Text = "Homepage"
link1 = sheet.HyperLinks.Add(sheet.Range["B10"])
link1.Type = HyperLinkType.Url
link1.Address = "http://www.example.com"

# Add an email link
sheet.Range["B11"].Text = "Contact Support"
link2 = sheet.HyperLinks.Add(sheet.Range["B12"])
link2.Type = HyperLinkType.Url
link2.Address = "mailto:support@example.com"

# Add a forum link
sheet.Range["B13"].Text = "Forum"
link3 = sheet.HyperLinks.Add(sheet.Range["B14"])
link3.Type = HyperLinkType.Url
link3.Address = "https://www.example.com/forum/"

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

Each hyperlink is bound to its own independent cell range and does not interfere with others. In practice, you can read link data from a database or configuration file and write them in a loop for fully automated hyperlink management.

Reading and Iterating Over Hyperlinks

To retrieve the address and type information of existing hyperlinks in a worksheet, iterate through the sheet.HyperLinks collection to read each link.

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

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

for link in sheet.HyperLinks:
    address = link.Address
    link_type = link.Type
    print(f"Address: {address}, Type: {link_type}")

workbook.Dispose()

The Type property returns an enumeration value (such as HyperLinkType.Url, HyperLinkType.File, or HyperLinkType.Workbook), which can be used to differentiate between link types and apply the appropriate processing logic. This is particularly useful in data auditing and link validation scenarios — for example, batch-checking the validity of all URL links.

Modifying Hyperlinks

To modify an existing hyperlink, simply retrieve the link object from the HyperLinks collection by index and update the TextToDisplay and Address properties directly.

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

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

links = sheet.HyperLinks
links[0].TextToDisplay = "Updated Display Text"
links[0].Address = "http://www.new-address.com"

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

The HyperLinks collection is indexed in the order hyperlinks were added. To locate a hyperlink for a specific cell, iterate through the collection and compare the Address or the associated cell range to find the target link.

Deleting Hyperlinks

There are two scenarios for deleting hyperlinks: removing only the hyperlink while keeping the cell's display text, or clearing the cell content entirely along with the hyperlink.

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

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

# Remove the hyperlink only, keeping the text
sheet.HyperLinks.RemoveAt(0)

# Clear all cell content (including text and hyperlinks)
sheet.Range["B1"].ClearAll()

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

RemoveAt(index) deletes only the hyperlink object itself, leaving the cell text intact. To clear the cell when removing a hyperlink, call the ClearAll() method additionally. Note that when calling RemoveAt repeatedly, the collection index shifts after each deletion. Therefore, when deleting multiple hyperlinks, you should work from the end of the collection toward the beginning, or re-fetch the index after each removal.

Best Practices

Choose the correct link type: Use HyperLinkType.Url for web and email links, HyperLinkType.Workbook for cross-sheet links, and HyperLinkType.File for external file links. Selecting the wrong type will cause the link to malfunction.
Watch for index shifts during batch deletion: When calling RemoveAt in a loop to delete multiple hyperlinks, iterate from the end of the collection toward the beginning to avoid index misalignment.
Email protocol prefix: Email addresses must start with mailto:; otherwise, Excel will not recognize them as email links.
Release resources promptly: Call Dispose() after finishing work with a workbook to release file handles and prevent resource leaks when processing multiple files in batch.

Summary

This article covered the full range of hyperlink operations in Excel worksheets using Python: adding web links, email links, image links, cross-sheet links, and external file links, as well as batch insertion, reading, modification, and deletion. The core entry point for all operations is the sheet.HyperLinks collection — Add() for creating, RemoveAt() for deleting, and indexing or iteration for reading and modifying existing hyperlink properties.

Building on these capabilities, you can combine hyperlink operations with other Excel features — for example, batch-adding reference links to auto-generated reports, creating cross-sheet navigation menus for dashboards, or integrating data validation and conditional formatting to build smarter, interactive spreadsheets.

Add and Customize Text Boxes in Excel Using Python

Allen Yang — Fri, 17 Jul 2026 10:12:43 +0000

When creating instructional spreadsheets, dashboards, or annotated reports, cells alone are often insufficient for accommodating lengthy explanatory text or freely positioned notes. Excel's Text Box provides a container independent of the cell grid: it can be placed anywhere, hold multiple lines of text, have its own font and background settings, and leave the data and formulas in surrounding cells untouched.

When you need to programmatically generate such annotated spreadsheets in bulk, manipulating text boxes through code is far more efficient and consistent than drawing them by hand. This article walks through how to insert text boxes into Excel worksheets using Python, configure common properties such as text content, alignment, font, background, padding, and word wrap, as well as read and replace text within text boxes.

Why Use Text Boxes Instead of Regular Cells

Compared to placing explanatory text directly in cells, text boxes offer several practical advantages:

Free positioning: They can float anywhere on the sheet, independent of the row-and-column structure.
Independent styling: Font, color, background, and border can each be configured separately, decoupled from the main table style.
Long-text support: They are ideal for displaying descriptions, annotations, legends, and other multi-line content, with word wrap to maintain layout.
Easy find-and-replace: Text boxes can serve as template placeholder carriers, enabling bulk text population or replacement.

Prerequisites

Working with Excel text boxes can be done using Spire.XLS for Python, which can be installed via pip:

pip install Spire.Xls

Import the main and common modules in your script as follows:

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

Inserting a Text Box and Setting Text and Alignment

To insert a text box into a worksheet, use sheet.TextBoxes.AddTextBox(), passing the row, column, height, and width (all in Excel's internal units). Once inserted, you can write content via the Text property and control horizontal and vertical alignment through HAlignment and VAlignment.

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

workbook = Workbook()
workbook.Version = ExcelVersion.Version2013
sheet = workbook.Worksheets[0]

# Insert a text box at row 2, column 2, with height 18 and width 65 units
textbox = sheet.TextBoxes.AddTextBox(2, 2, 18, 65)
textbox.Text = "Spire.XLS for Python is a professional Excel Python API..."
textbox.HAlignment = CommentHAlignType.Center
textbox.VAlignment = CommentVAlignType.Center

workbook.SaveToFile("TextBox.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Note that the first two parameters of AddTextBox (row and column) determine which cell position the top-left corner of the text box is anchored to, while the last two parameters define the text box's own dimensions. These units differ from the cell's width and height units, so it's best to verify the visual result when setting these values.

Setting Font and Background

The text appearance of a text box is controlled through the RichText mechanism, which allows precise font control over a specific character range. The background is filled with a solid color via the Fill property.

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

workbook = Workbook()
workbook.LoadFromFile("Template_Xls_5.xlsx")
sheet = workbook.Worksheets[0]
shape = sheet.TextBoxes[0]

# Create a font and set its properties
font = workbook.CreateFont()
font.FontName = "Century Gothic"
font.Size = 10
font.IsBold = True
font.Color = Color.get_Blue()

# Apply the font to the entire text box text
rt = RichText(shape.RichText)
rt.SetFont(0, len(shape.Text) - 1, font)

# Set a solid color background
shape.Fill.FillType = ShapeFillType.SolidColor
shape.Fill.ForeKnownColor = ExcelColors.BlueGray

workbook.SaveToFile("SetFontAndBackground.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The three parameters of RichText.SetFont(start, end, font) are the start index, end index, and font object, respectively. Indexing starts at 0 and is inclusive on both ends. To apply a uniform style to the entire text, simply use 0 to len(text) - 1 to cover all characters. For partial highlighting, pass the corresponding sub-range instead.

Setting Padding and Word Wrap

When a text box contains a large amount of text, you can control the spacing between the text and the border using inner margin properties, and enable word wrap via IsWrapText to prevent text overflow or visual crowding.

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

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

textbox = sheet.TextBoxes.AddTextBox(4, 2, 100, 300)
textbox.Text = "Inserting a Text Box in Excel with Internal Margins"
textbox.HAlignment = CommentHAlignType.Center
textbox.VAlignment = CommentVAlignType.Center

# Set internal margins of the text box
textbox.InnerLeftMargin = 1
textbox.InnerRightMargin = 3
textbox.InnerTopMargin = 1
textbox.InnerBottomMargin = 1

workbook.SaveToFile("SetInternalMarginOfTextbox.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

For an existing text box with lengthy content, simply enable word wrap directly:

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

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

shape = sheet.TextBoxes[0]
shape.IsWrapText = True

workbook.SaveToFile("TextBoxWithWrapText.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Inner margin values are measured in points — the larger the value, the farther the text sits from the border. Word wrap causes the text box to reflow content within its set dimensions, and combining both features significantly improves the readability of long text.

Reading and Replacing Text in Text Boxes

Text boxes are accessible objects within the document. By iterating over sheet.TextBoxes, you can read or bulk-replace the text they contain, making this ideal for template placeholder population.

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

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

# Iterate through all text boxes and replace the specified text
for tb in sheet.TextBoxes:
    if tb.Text != "" and "<TAG_1>" in tb.Text:
        tb.Text = tb.Text.replace("<TAG_1>", "Spire.XLS for .NET")

workbook.SaveToFile("ReplaceTextInTextBox.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

If the document contains many text boxes, you can also assign names to them and locate specific boxes directly by name rather than relying on index positions:

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

textbox = sheet.TextBoxes.AddTextBox(2, 2, 18, 65)
textbox.Name = "FirstTextBox"
textbox.Text = "Descriptive text to be retrieved later"

# Retrieve the text box by name
found = sheet.TextBoxes["FirstTextBox"]
print(found.Text)

workbook.Dispose()

Naming is especially useful when generating a large number of labeled text boxes. For example, you might name each annotation box "Note_CustomerInfo" or "Note_CostDescription" so that subsequent maintenance can target them precisely.

Practical Tips

Distinguish anchoring from sizing: In AddTextBox(row, col, height, width), the row and column define the anchor point while height and width define the text box's own size. The two use different units, so start with small test values when first working with them.
Check for empty text before replacing: When iterating and replacing, include a tb.Text != "" check to avoid unexpected behavior from empty text boxes.
Coordinate font size and background: Pair light backgrounds with dark text and dark backgrounds with light text to improve text box readability in dense reports.
Release resources promptly: After batch-processing multiple workbooks, call Dispose() to avoid file handle leaks.

Summary

This article covered common operations on Excel text boxes: inserting text boxes with text alignment, setting fonts and backgrounds, adjusting padding and word wrap, and reading and replacing text. The unified entry point for all these capabilities is the sheet.TextBoxes collection on the worksheet: AddTextBox() handles creation, Text and RichText manage content and styling, Fill controls the background, Inner*Margin and IsWrapText govern layout, and iteration or name-based access supports bulk maintenance.

Building on these foundations, you can combine text boxes with charts, shapes, data validation, and other features — for instance, adding annotated text boxes to charts or using text boxes to display dynamic prompts in dashboards — to make your automatically generated Excel documents both data-accurate and clearly documented.

How to Create, Edit, and Manage PDF Layers with Python

Allen Yang — Fri, 17 Jul 2026 05:57:29 +0000

In complex PDF documents, it is often necessary to organize content into logical groups: annotation lines in engineering drawings, different base maps in cartography, comment layers in technical documentation, or separate layouts for print versus screen viewing. PDF layers (OCG, Optional Content Group) are designed precisely for this purpose — they allow you to place different content into independent layers with individually controllable visibility. A PDF reader can show or hide any layer on demand without affecting the rest of the document.

Programmatically creating and managing layers is ideal for scenarios where content needs to be organized by role during document generation — for example, auto-generated drawings with toggleable annotation layers, or exports that separate "final content" from "draft markup." This article walks through how to create layers, draw content on them, control their visibility, and delete them using Python.

Why Use Layers to Manage PDF Content

Compared to drawing everything directly onto a single canvas, using layers offers several practical advantages:

On-Demand Display: Readers can individually hide annotations, grids, or background layers in their PDF viewer, focusing only on what they need.
Logical Isolation: Different types of content belong to different layers, so modifying one category later won't accidentally affect others.
Batch Control: You can hide all layers at once or keep only specific ones visible, making it easy to generate different versions for different purposes.
Clear Structure: The exported document has a well-organized layer panel, making it easier for others to understand and edit.

Setting Up the Environment

You can use Spire.PDF for Python to work with PDF layers. Install it via pip:

pip install Spire.Pdf

After installation, import the main module and the common module in your script to access the layer-related APIs:

from spire.pdf.common import *
from spire.pdf import *

Adding Layers to an Existing Document and Drawing Content

To add layers to an existing PDF document, the key method is doc.Layers.AddLayer(). It returns a new layer object. You then call layer.CreateGraphics(page.Canvas) to obtain a drawing context dedicated to that layer — all subsequent drawing operations will affect only that layer.

from spire.pdf.common import *
from spire.pdf import *

doc = PdfDocument()
doc.LoadFromFile("AddLayers.pdf")
page = doc.Pages[0]

# Add a layer named "red line" with initial visibility set to On
layer1 = doc.Layers.AddLayer("red line", PdfVisibility.On)
g1 = layer1.CreateGraphics(page.Canvas)
g1.DrawLine(PdfPen(PdfBrushes.get_Red(), 2.0), PointF(100.0, 350.0), PointF(300.0, 350.0))

# Add a layer named "blue line" (visible by default)
layer2 = doc.Layers.AddLayer("blue line")
g2 = layer2.CreateGraphics(doc.Pages[0].Canvas)
g2.DrawLine(PdfPen(PdfBrushes.get_Blue(), 2.0), PointF(100.0, 400.0), PointF(300.0, 400.0))

# Add a layer named "green line"
layer3 = doc.Layers.AddLayer("green line")
g3 = layer3.CreateGraphics(doc.Pages[0].Canvas)
g3.DrawLine(PdfPen(PdfBrushes.get_Green(), 2.0), PointF(100.0, 450.0), PointF(300.0, 450.0))

doc.SaveToFile("AddLayers.pdf")
doc.Close()

Here are a few key points:

The second parameter of AddLayer(name, visibility) is optional. PdfVisibility.On means the layer is initially visible; if omitted, the layer defaults to visible as well.
The drawing object returned by CreateGraphics() is bound to the page canvas, but its output belongs to the current layer. In other words, the red, blue, and green lines each reside in separate layers and can be toggled individually in a PDF reader.
Layer names should be distinctive throughout the document, as they are used later for looking up or deleting layers.

Output Document Preview

Organizing Multiple Layers When Creating a New Document

When starting from a blank document, you can also draw text, images, and other content onto different layers to create a well-structured final file.

from spire.pdf.common import *
from spire.pdf import *

doc = PdfDocument()
page = doc.Pages.Add()

# Draw a text layer on the default canvas
text = "Welcome to evaluate Spire.PDF for Python !"
font = PdfTrueTypeFont("Calibri", 15.0, PdfFontStyle.Regular, True)
brush = PdfSolidBrush(PdfRGBColor(Color.get_Black()))
page.Canvas.DrawString(text, font, brush, PointF(50.0, 50.0))

# Draw an image layer on the default canvas
image = PdfImage.FromFile("MultilayerImage.png")
size = font.MeasureString("Spire.PDF for Python")
page.Canvas.DrawImage(image, PointF(50.0 + 230.0, 50.0), size)

doc.SaveToFile("CreateMultilayerPDF.pdf")
doc.Close()

This example demonstrates a common practice: drawing text and images in sequence to create a layered structure on a single page. Combined with the "draw by layer" approach described earlier, you can organize multiple layers within a single page. When content has front-to-back overlapping relationships, the drawing order also determines the visual stacking order of the layers.

Controlling Layer Visibility

The primary value of layers lies in the ability to toggle them individually. Using the Visibility property, you can show or hide any layer. The following examples demonstrate two approaches: hiding all layers and hiding a specific layer.

from spire.pdf.common import *
from spire.pdf import *

doc = PdfDocument()
doc.LoadFromFile("Template_Pdf_5.pdf")

# Approach 1: Iterate through all layers and hide each one
for i in range(doc.Layers.Count):
    doc.Layers.get_Item(i).Visibility = PdfVisibility.Off

doc.SaveToFile("InvisibleAllPdfLayers.pdf")
doc.Close()

If you only want to hide certain layers, you can access them directly by index or by name:

from spire.pdf.common import *
from spire.pdf import *

doc = PdfDocument()
doc.LoadFromFile("Template_Pdf_5.pdf")

# Hide the first layer by index
doc.Layers.get_Item(0).Visibility = PdfVisibility.Off
# Hide the layer named "blue line" by name
doc.Layers.get_Item("blue line").Visibility = PdfVisibility.Off

doc.SaveToFile("InvisibleParticularPdfLayers.pdf")
doc.Close()

PdfVisibility.Off means hidden, and PdfVisibility.On means visible. The get_Item() method supports both numeric indices and layer names, making operations like "hide only this specific annotation layer" straightforward.

Deleting Layers

When a layer is no longer needed, you can remove it directly by name. After removal, all content on that layer is deleted from the document.

from spire.pdf.common import *
from spire.pdf import *

doc = PdfDocument()
doc.LoadFromFile("DeleteLayer.pdf")

# Delete the "red line" layer by name
doc.Layers.RemoveLayer("red line")

doc.SaveToFile("DeleteLayer.pdf")
doc.Close()

Note that RemoveLayer() deletes the layer along with all of its content. Therefore, it should only be called when you are certain the content is no longer needed, to avoid accidentally removing annotations or base maps that should be retained.

Practical Tips

Use Meaningful Names: Layer names appear in the PDF reader's layer panel. Semantic names like "Annotations," "Grid," or "Background" are far more informative than "layer1" or "layer2."
Visibility Is Not Deletion: Hiding a layer only makes it invisible — the content remains in the file. To permanently remove content, use RemoveLayer().
Drawing Order Affects Stacking: On the same page, later layers overlap earlier ones. Consider the visual hierarchy when planning layer order.
Release Resources Promptly: Call Close() or Dispose() when finished processing. This is especially important when batch-generating multiple documents with layers, as it prevents file handle leaks.

Conclusion

This article covered four key operations for managing PDF layers: creating new layers and drawing content on them, organizing multiple layers in a new document, controlling layer visibility by index or name, and deleting layers by name. The unified entry point for all these operations is the doc.Layers collection: AddLayer() creates a layer, CreateGraphics() obtains the layer's drawing context, Visibility controls the display state, and RemoveLayer() removes a layer.

Building on this foundation, you can further combine layer capabilities with features like forms, annotations, and digital signatures. For example, you could place all review comments in a dedicated "Annotation Layer" that can be hidden with a single click, allowing the same document to serve both "formal reading" and "collaborative review" purposes.

How to Add a Digital Signature to a PDF File Using Python

Allen Yang — Thu, 16 Jul 2026 08:54:36 +0000

When contracts, invoices, reports, and other documents are being circulated, a common concern arises: how can you confirm that a document was indeed issued by a specific signer and that its content has not been tampered with after signing? Digital signatures are the mechanism designed for exactly this purpose. Built on PKI (Public Key Infrastructure) technology, a digital signature binds the signer's identity to the document content — if the document is modified in any way after signing, signature verification will fail.

Compared to manually signing documents one by one in tools like Adobe Acrobat, programmatically processing signatures is far better suited for automated scenarios — for example, system-generated contracts, daily settlement reports, or workflow documents that need to be stamped upon approval. This article will walk you through adding visible signatures, invisible signatures, and timestamped signatures to PDF documents using Python, as well as verifying already-signed documents.

Problems Digital Signatures Solve

Programmatically handling digital signatures is typically driven by the following considerations:

Integrity verification: Once a signed document is altered in any way, verification immediately fails, allowing you to detect tampering.
Identity authentication: Signatures are based on the holder's certificate (PFX/PKCS#12 file), proving that the document was signed by a specific entity.
Batch automation: Approval systems and settlement systems can automatically sign documents at the time of generation, with no manual intervention required.
Long-term verifiability: When combined with a timestamp service, it can be proven that the signing occurred while the certificate was still valid, even if the certificate has since expired.

Environment Setup

For working with PDF signatures in Python, you can use Spire.PDF for Python, which can be installed via pip:

pip install Spire.Pdf

In addition to the library itself, you will need a digital certificate file for signing, typically in .pfx (or .p12) format, along with its corresponding password. Certificates can be issued by a Certificate Authority (CA), or you can use self-signed certificates during the testing phase.

Adding a Visible Digital Signature

A visible signature renders a signature field on the page, displaying information such as the signer, reason, location, and more. This is ideal for scenarios where readers need to clearly see that the document has been signed.

The core object is PdfOrdinarySignatureMaker: it takes the PDF document, the certificate file path, and the certificate password, and performs the signing operation. The visual appearance of the signature is configured separately through PdfSignatureAppearance.

from spire.pdf import *

# Load the PDF document
doc = PdfDocument()
doc.LoadFromFile("SampleB_1.pdf")

# Create a signature maker using the certificate file and password
signatureMaker = PdfOrdinarySignatureMaker(doc, "Jerry.pfx", "e-iceblue")

# Configure signature metadata
signature = signatureMaker.Signature
signature.Name = "Jerry"
signature.ContactInfo = "555-123-4567"
signature.Location = "New York"
signature.Reason = "Signed by the document owner"

# Configure signature appearance
appearance = PdfSignatureAppearance(signature)
appearance.NameLabel = "Signer: "
appearance.ContactInfoLabel = "Contact: "
appearance.LocationLabel = "Location: "
appearance.ReasonLabel = "Reason: "
appearance.SignatureImage = PdfImage.FromFile("logo.png")
appearance.GraphicMode = GraphicMode.SignImageAndSignDetail

# Draw the signature field at the specified position on the first page: x, y, width, height
signatureMaker.MakeSignature("Signer:", doc.Pages.get_Item(0), 90.0, 550.0, 270.0, 90.0, appearance)

doc.SaveToFile("DigitalSignature.pdf")
doc.Dispose()

There are a few key points worth noting:

The Name, Reason, Location, and other properties set on the Signature object are written into the signature object itself. PDF readers read these values when displaying signature details.
GraphicMode determines what the signature field displays. SignImageAndSignDetail means both an image (such as a company logo) and text information are shown; you can also choose to display only the image or only the text.
The coordinate parameters in MakeSignature are measured in points, with the origin at the bottom-left corner of the page. The last four numbers correspond to the left boundary, bottom boundary, width, and height of the signature field, respectively.

Adding an Invisible Signature

In some scenarios, you may not want a visible signature field on the page — you only need to sign the document at the document level to ensure integrity. In such cases, you can create an invisible signature without appearance parameters.

from spire.pdf import *

doc = PdfDocument()
doc.LoadFromFile("SampleB_1.pdf")

# Create a signature maker
signatureMaker = PdfOrdinarySignatureMaker(doc, "Jerry.pfx", "e-iceblue")

# Pass only the signature field name without setting appearance — this creates an invisible signature
signatureMaker.MakeSignature("signName")

doc.SaveToFile("AddInvisibleSignature.pdf")
doc.Dispose()

Invisible signatures provide the same tamper-proof capabilities. PDF readers can still detect them in the signature panel; they simply do not display any visible marker on the page.

Adding a Timestamped Signature

All digital certificates have an expiration date. If you need to prove that "the signing occurred while the certificate was still valid," you need to integrate a trusted Timestamp Authority (TSA). The Security_PdfSignature class provides the ConfigureTimestamp method for specifying the TSA service URL.

from spire.pdf import *

doc = PdfDocument()
doc.LoadFromFile("SampleB_1.pdf")

# Create a signature object with a page reference
signature = Security_PdfSignature(doc, doc.Pages.get_Item(0), "Jerry.pfx", "e-iceblue", "signature")

# Set the position and size of the signature field
signature.Bounds = RectangleF(PointF(90.0, 550.0), SizeF(180.0, 90.0))
signature.NameLabel = "Digitally signed by: Jerry"
signature.Reason = "Ensuring document authenticity"

# Restrict post-signing operations: allow form filling only, forbid other modifications
signature.DocumentPermissions = PdfCertificationFlags.AllowFormFill.value | PdfCertificationFlags.ForbidChanges.value

# Configure the trusted timestamp service
signature.ConfigureTimestamp("https://freetsa.org/tsr")

doc.SaveToFile("SignedByTimestamp.pdf")
doc.Dispose()

DocumentPermissions is a practical property here — it restricts post-signing operations through a certification signature. The combination in this example means "allow continued form field filling, but forbid any other changes," making it suitable for form-type documents that still need to collect information after approval while preventing content from being tampered with.

Verifying Signed Documents

Once you have a signed document, you can iterate through the signature fields in its form and verify each signature individually.

from spire.pdf import *

doc = PdfDocument()
doc.LoadFromFile("SignedDocument.pdf")

# Access the document form
formWidget = PdfFormWidget(doc.Form)

if formWidget.FieldsWidget.Count > 0:
    for i in range(formWidget.FieldsWidget.Count):
        field = formWidget.FieldsWidget.get_Item(i)
        if isinstance(field, PdfField):
            signatureField = PdfSignatureFieldWidget(field)
            # Verify the corresponding signature using the field's full name
            valid = doc.VerifySignature(signatureField.FullName)
            print("Signature is valid" if valid else "Signature is invalid")

doc.Dispose()

VerifySignature recalculates the document content digest and compares it against the value recorded in the signature — if the document has been modified after signing, the result will be invalid. This is the final step that fulfills the "tamper-proof" promise of digital signatures.

Practical Tips

Do not hardcode certificate passwords: In the examples, passwords are written directly in the code for demonstration purposes. In production, they should be read from environment variables or a key management service.
Coordinate system: Signature field coordinates use the bottom-left corner of the page as the origin, with the y-axis increasing upward — the opposite of typical screen coordinates. Keep this in mind when positioning signature fields.
Signing order: A document can contain multiple signatures. Each subsequent signature incorporates all previous signatures into its verification scope, so the signing order affects the verification logic.
Release resources promptly: After processing, call Dispose() to release file handles held by the document, preventing file-locking issues in batch tasks.

Conclusion

This article covered four common operations related to PDF digital signatures: visible signatures, invisible signatures, timestamped signatures, and signature verification. All of them are built on the binding between a certificate file and the document content — PdfOrdinarySignatureMaker handles standard signing, Security_PdfSignature extends it with timestamping and permission control, and VerifySignature completes the verification loop.

Building on this foundation, you can further integrate form fields, document permissions, and Long-Term Validation (LTV) capabilities to create automated signing solutions tailored for contract management, approval workflows, and similar scenarios.

Automating Excel Formulas and Functions Using Python

Allen Yang — Fri, 10 Jul 2026 10:47:14 +0000

In modern data processing workflows, Excel formulas and functions are essential tools for performing calculations, analyzing data, and implementing business logic. However, manually entering and managing large numbers of formulas is not only time-consuming but also prone to errors. By automating Excel formula insertion and management with Python, developers can process spreadsheets in bulk, dynamically generate calculation logic, and ensure accuracy and consistency in data processing.

This article demonstrates how to use Python to insert various types of formulas and functions into Excel worksheets, including basic arithmetic operations, built-in functions, array formulas, and named ranges. These techniques are applicable to scenarios such as financial report automation, data analysis pipeline construction, and batch data processing.

Environment Setup

First, install the Spire.XLS for Python library:

pip install Spire.XLS

This library provides a comprehensive API for Excel file manipulation, supporting formula insertion, calculation execution, and file format conversion.

Inserting Basic Formulas

Excel formulas begin with an equals sign (=) and can contain constants, cell references, operators, and function calls. When inserting formulas using Python, simply assign the formula string to the cell's Formula property.

The following example demonstrates how to insert various basic formulas into a worksheet:

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

# Create a workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]

# Set test data
sheet.Range["B2"].NumberValue = 7.3
sheet.Range["C2"].NumberValue = 5
sheet.Range["D2"].NumberValue = 8.2

# Insert string formula
sheet.Range["A4"].Text = '="hello"'
sheet.Range["B4"].Formula = '="hello"'

# Insert arithmetic formula
sheet.Range["A5"].Text = "=1+2+3+4+5-6-7+8-9"
sheet.Range["B5"].Formula = "=1+2+3+4+5-6-7+8-9"

# Insert multiplication operation
sheet.Range["A6"].Text = "=33*3/4-2+10"
sheet.Range["B6"].Formula = "=33*3/4-2+10"

# Reference other cells
sheet.Range["A7"].Text = "=Sheet1!$B$2"
sheet.Range["B7"].Formula = "=Sheet1!$B$2"

# Reference a cell range and calculate average
sheet.Range["A8"].Text = "=AVERAGE(Sheet1!$B$2:$D$2)"
sheet.Range["B8"].Formula = "=AVERAGE(Sheet1!$B$2:$D$2)"

# Save the file
workbook.SaveToFile("BasicFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

This example demonstrates:

String constants: Text enclosed in double quotes
Arithmetic operations: Support for addition, subtraction, multiplication, division, and operator precedence
Cell references: Using the $ symbol to create absolute references
Range references: Using colons to specify continuous cell ranges

Using Built-in Functions

Excel provides hundreds of built-in functions covering mathematics, statistics, date/time operations, logical operations, and text processing. Python allows flexible insertion of these functions.

Mathematical and Statistical Functions

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

currentRow = 1

# COUNT function - count the number of arguments
sheet.Range[f"A{currentRow}"].Text = "=Count(3,5,8,10,2,34)"
sheet.Range[f"B{currentRow}"].Formula = "=Count(3,5,8,10,2,34)"
currentRow += 1

# SUM function - calculate sum
sheet.Range[f"A{currentRow}"].Text = "=SUM(18,29)"
sheet.Range[f"B{currentRow}"].Formula = "=SUM(18,29)"
currentRow += 1

# AVERAGE function - calculate average
sheet.Range[f"A{currentRow}"].Text = "=AVERAGE(12,45)"
sheet.Range[f"B{currentRow}"].Formula = "=AVERAGE(12,45)"
currentRow += 1

# MAX and MIN functions
sheet.Range[f"A{currentRow}"].Text = "=MAX(10,30)"
sheet.Range[f"B{currentRow}"].Formula = "=MAX(10,30)"
currentRow += 1

sheet.Range[f"A{currentRow}"].Text = "=MIN(5,7)"
sheet.Range[f"B{currentRow}"].Formula = "=MIN(5,7)"
currentRow += 1

# ROUND function - round numbers
sheet.Range[f"A{currentRow}"].Text = "=ROUND(7,3)"
sheet.Range[f"B{currentRow}"].Formula = "=ROUND(7,3)"
currentRow += 1

# SQRT function - square root
sheet.Range[f"A{currentRow}"].Text = "=SQRT(40)"
sheet.Range[f"B{currentRow}"].Formula = "=SQRT(40)"
currentRow += 1

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

Date and Time Functions

Handling date and time data is a common requirement in Excel automation. The following code demonstrates how to use date and time functions:

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# NOW function - current date and time
sheet.Range["A1"].Text = "=NOW()"
sheet.Range["B1"].Formula = "=NOW()"
sheet.Range["B1"].Style.NumberFormat = "yyyy-MM-DD HH:mm:ss"

# DATE function - construct a date
sheet.Range["A2"].Text = "=DATE(2024,1,15)"
sheet.Range["B2"].Formula = "=DATE(2024,1,15)"
sheet.Range["B2"].Style.NumberFormat = "yyyy-MM-DD"

# TIME function - construct a time
sheet.Range["A3"].Text = "=TIME(14,30,0)"
sheet.Range["B3"].Formula = "=TIME(14,30,0)"
sheet.Range["B3"].Style.NumberFormat = "HH:mm:ss"

# YEAR, MONTH, DAY functions - extract date components
sheet.Range["A4"].Text = "=YEAR(NOW())"
sheet.Range["B4"].Formula = "=YEAR(NOW())"

sheet.Range["A5"].Text = "=MONTH(NOW())"
sheet.Range["B5"].Formula = "=MONTH(NOW())"

sheet.Range["A6"].Text = "=DAY(NOW())"
sheet.Range["B6"].Formula = "=DAY(NOW())"

# HOUR, MINUTE, SECOND functions - extract time components
sheet.Range["A7"].Text = "=HOUR(NOW())"
sheet.Range["B7"].Formula = "=HOUR(NOW())"

sheet.Range["A8"].Text = "=MINUTE(NOW())"
sheet.Range["B8"].Formula = "=MINUTE(NOW())"

sheet.Range["A9"].Text = "=SECOND(NOW())"
sheet.Range["B9"].Formula = "=SECOND(NOW())"

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

Logical and Conditional Functions

Logical functions are used to implement conditional judgments and business rules:

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# IF function - conditional judgment
sheet.Range["A1"].Text = '=IF(10>5, "Greater", "Less")'
sheet.Range["B1"].Formula = '=IF(10>5, "Greater", "Less")'

# AND function - logical AND
sheet.Range["A2"].Text = "=AND(TRUE, TRUE)"
sheet.Range["B2"].Formula = "=AND(TRUE, TRUE)"

# OR function - logical OR
sheet.Range["A3"].Text = "=OR(FALSE, TRUE)"
sheet.Range["B3"].Formula = "=OR(FALSE, TRUE)"

# NOT function - logical NOT
sheet.Range["A4"].Text = "=NOT(FALSE)"
sheet.Range["B4"].Formula = "=NOT(FALSE)"

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

Text Processing Functions

Text functions are used for string manipulation and data cleaning:

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# LEN function - string length
sheet.Range["A1"].Text = '=LEN("Hello World")'
sheet.Range["B1"].Formula = '=LEN("Hello World")'

# MID function - extract substring
sheet.Range["A2"].Text = '=MID("Hello World",7,5)'
sheet.Range["B2"].Formula = '=MID("Hello World",7,5)'

# VALUE function - convert text to number
sheet.Range["A3"].Text = '=VALUE("123")'
sheet.Range["B3"].Formula = '=VALUE("123")'

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

Using Array Formulas

Array formulas can perform calculations on multiple values and return either a single result or multiple results. This is particularly useful when executing matrix operations and batch data processing.

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Prepare data
sheet.Range["A1"].NumberValue = 1
sheet.Range["A2"].NumberValue = 2
sheet.Range["A3"].NumberValue = 3

sheet.Range["B1"].NumberValue = 4
sheet.Range["B2"].NumberValue = 5
sheet.Range["B3"].NumberValue = 6

sheet.Range["C1"].NumberValue = 7
sheet.Range["C2"].NumberValue = 8
sheet.Range["C3"].NumberValue = 9

# Insert array formula - LINEST function performs linear regression analysis
sheet.Range["A5:C6"].FormulaArray = "=LINEST(A1:A3,B1:C3,TRUE,TRUE)"

# Calculate all formula values
workbook.CalculateAllValue()

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

Key points:

Use the FormulaArray property instead of Formula to insert array formulas
Array formulas need to span multiple cell ranges
Call the CalculateAllValue() method to ensure formulas are calculated correctly

Using the SUBTOTAL Function

The SUBTOTAL function can perform aggregate calculations on data lists and ignore hidden rows, which is particularly useful when creating interactive reports.

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Prepare data
for row in range(1, 4):
    sheet.Range[f"A{row}"].NumberValue = row
    sheet.Range[f"B{row}"].NumberValue = row + 3
    sheet.Range[f"C{row}"].NumberValue = row + 6

# Insert different SUBTOTAL functions
# 1 represents AVERAGE
sheet.Range["A5"].Formula = "=SUBTOTAL(1,A1:C3)"

# 2 represents COUNT
sheet.Range["B5"].Formula = "=SUBTOTAL(2,A1:C3)"

# 5 represents MIN
sheet.Range["C5"].Formula = "=SUBTOTAL(5,A1:C3)"

# Calculate formulas
workbook.CalculateAllValue()

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

The first parameter of the SUBTOTAL function specifies the calculation type:

1: AVERAGE
2: COUNT
3: COUNTA (count non-empty cells)
4: MAX
5: MIN
9: SUM

Using Named Ranges and Formulas

Named ranges improve formula readability and maintainability. By replacing complex cell references with meaningful names, formulas become easier to understand.

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Set data
sheet.Range["A1"].Value = "10"
sheet.Range["A2"].Value = "20"

# Create a named range
namedRange = workbook.NameRanges.Add("SumRange")
namedRange.RefersToRange = sheet.Range["A1:A2"]

# Use the named range in a formula
sheet.Range["C1"].Formula = "=SUM(SumRange)"

# Alternatively, define a named formula directly
namedFormula = workbook.NameRanges.Add("TotalCalc")
namedFormula.NameLocal = "=SUM(A1+A2)"
sheet.Range["C2"].Formula = "TotalCalc"

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

The advantages of this approach include:

Improved readability: =SUM(SumRange) is clearer than =SUM(A1:A2)
Easier maintenance: Modifying the named range reference automatically updates all formulas using that name
Reduced errors: Avoids manual entry of complex cell references

Practical Tips

Formatting Formula Cells

To distinguish formula cells from data cells, you can apply special formatting:

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Insert formula
sheet.Range["A1"].Formula = "=SUM(B1:B10)"

# Set background color for formula cell
sheet.Range["A1"].Style.Color = Color.get_LightYellow()

# Add borders
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin

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

Forcing Formula Calculation

In some cases, you may need to immediately obtain the calculated result of a formula rather than keeping the formula itself:

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

sheet.Range["A1"].NumberValue = 10
sheet.Range["A2"].NumberValue = 20
sheet.Range["A3"].Formula = "=A1+A2"

# Calculate all formulas
workbook.CalculateAllValue()

# At this point, A3 value is already 30
result = sheet.Range["A3"].Value
print(f"Calculation result: {result}")

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

Handling Cross-Sheet References

When formulas need to reference data from other worksheets, use the worksheet name followed by an exclamation mark:

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

workbook = Workbook()

# Create two worksheets
sheet1 = workbook.Worksheets[0]
sheet1.Name = "Data"
sheet2 = workbook.Worksheets.Add("Calculation")

# Set data in the first worksheet
sheet1.Range["A1"].NumberValue = 100
sheet1.Range["A2"].NumberValue = 200

# Reference data from the first worksheet in the second worksheet
sheet2.Range["A1"].Formula = "=Data!A1+Data!A2"

workbook.CalculateAllValue()
workbook.SaveToFile("CrossSheetFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Conclusion

This article has demonstrated various techniques for automating Excel formulas and functions using Python, including:

Inserting basic arithmetic formulas and cell references
Using mathematical, statistical, date/time, logical, and text processing functions
Executing array formulas for complex calculations
Leveraging the SUBTOTAL function for flexible aggregate calculations
Improving formula readability and maintainability through named ranges

These techniques enable developers to:

Generate spreadsheets with complex calculation logic in bulk
Automate financial models and data analysis reports
Dynamically build condition-based calculation formulas
Improve consistency and accuracy in data processing

By combining Python's programming capabilities with Excel's calculation features, you can build powerful data processing automation solutions that significantly improve工作效率 and reduce human error.

Protect and Unprotect Excel Worksheets and Workbooks Using Python

Allen Yang — Mon, 06 Jul 2026 06:25:35 +0000

In enterprise data management and report distribution scenarios, Excel files often contain sensitive information or critical formulas that need protection from unauthorized modifications. Manually setting protection passwords on each worksheet is feasible for a small number of files, but it becomes inefficient when dealing with batch processing or automated report generation.

By implementing worksheet and workbook protection programmatically with Python, security policies can be seamlessly integrated into document generation and distribution workflows. This article covers how to password-protect worksheets, lock specific cells and rows/columns, define editable ranges, hide formulas, and remove protection using Python.

Environment Setup

This article uses the Spire.XLS for Python library to work with Excel files. Install it via pip:

pip install Spire.XLS

Import the required modules in your script:

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

Understanding Excel Protection Basics

Excel's protection mechanism operates at two levels:

Worksheet protection: Restricts editing of cell contents within a worksheet. You can set a password and selectively allow certain operations (such as sorting or filtering).
Workbook protection: Restricts modifications to the workbook structure, preventing users from adding, deleting, or renaming worksheets.

In Spire.XLS, worksheet protection is implemented through the Protect() method, while workbook protection uses the Workbook.Protect() method. Every cell has a Locked property — by default, all cells are locked, but the lock only takes effect once worksheet protection is enabled.

Protect a Worksheet

The simplest form of protection is setting a password on the entire worksheet. Once protection is enabled, all cells are locked by default and users cannot edit any content:

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

# Create a workbook and load the file
workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Protect the worksheet with a password
sheet.Protect("MyPassword", SheetProtectionType.All)

# Save the document
workbook.SaveToFile("ProtectSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

SheetProtectionType.All denies all operations. You can also choose more granular protection types, such as preventing only row insertion, column deletion, or other specific actions.

Lock Specific Cells

In most scenarios, you don't need to lock everything — only critical formula cells or summary data need protection. The key to achieving this is to first unlock all cells, then lock only the ones that need protection, and finally enable worksheet protection.

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

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

# Lock cell B3
sheet.Range["B3"].Style.Locked = True

# Unlock cell C3 (editable even when the sheet is protected)
sheet.Range["C3"].Style.Locked = False

# Enable worksheet protection
sheet.Protect("TestPassword", SheetProtectionType.All)

workbook.SaveToFile("LockSpecificCells.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

This approach is ideal for form-style documents where most fields should be user-editable, with only formulas, headers, and other key areas requiring protection.

Lock Specific Rows or Columns

When you need to protect entire rows or columns of data, you can iterate through and set the lock status accordingly. The following example unlocks all rows first, then locks only row 3:

workbook = Workbook()
workbook.CreateEmptySheet()
sheet = workbook.Worksheets[0]

# Unlock all rows
for i in range(0, 255):
    sheet.Rows[i].Style.Locked = False

# Lock row 3
sheet.Rows[2].Text = "Locked"
sheet.Rows[2].Style.Locked = True

# Enable protection
sheet.Protect("123", SheetProtectionType.All)

workbook.SaveToFile("LockSpecificRow.xlsx", ExcelVersion.Version2013)

Locking a specific column follows a similar pattern. The example below locks column D (the 4th column):

workbook = Workbook()
workbook.CreateEmptySheet()
sheet = workbook.Worksheets[0]

# Unlock all rows
for i in range(0, 255):
    sheet.Rows[i].Style.Locked = False

# Lock column D
sheet.Columns[3].Text = "Locked"
sheet.Columns[3].Style.Locked = True

# Enable protection
sheet.Protect("123", SheetProtectionType.All)

workbook.SaveToFile("LockSpecificColumn.xlsx", ExcelVersion.Version2013)

Define Editable Ranges

A more flexible approach is to specify certain areas as "allowed edit ranges" while the worksheet remains protected. Users can freely edit within these ranges while the rest of the worksheet stays locked:

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

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

# Define the range that users are allowed to edit
sheet.AddAllowEditRange("EditableRanges", sheet.Range["B4:E12"])

# Protect the worksheet
sheet.Protect("TestPassword", SheetProtectionType.All)

workbook.SaveToFile("ProtectedWithEditableRange.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The AddAllowEditRange() method takes two parameters: a range name and a range reference. This approach is more intuitive than setting the Locked property on individual cells, making it especially useful when you need to open up data entry areas within a protected spreadsheet.

Protect Workbook Structure

Beyond worksheet-level protection, you can also protect the overall workbook structure to prevent users from adding, deleting, or renaming worksheets:

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

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

# Protect the workbook structure
workbook.Protect("e-iceblue")

workbook.SaveToFile("ProtectWorkbook.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Workbook protection and worksheet protection can be applied simultaneously, creating multi-layered defense from the overall structure down to individual cell contents.

Hide Formulas

In reports containing complex formulas, you may want users to see the calculated results without being able to view or modify the formulas themselves. By setting the IsFormulaHidden property and enabling protection, all formulas in the worksheet can be hidden:

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

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

# Hide all formulas in the used range
sheet.AllocatedRange.IsFormulaHidden = True

# Enable worksheet protection for the hidden setting to take effect
sheet.Protect("e-iceblue")

workbook.SaveToFile("HideFormulas.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

It's important to note that the IsFormulaHidden property only takes effect after worksheet protection is enabled. Setting this property alone will not hide formulas.

Unprotect a Worksheet

Removing protection requires the correct password. Use the Unprotect() method and pass in the password that was set during protection:

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

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

# Remove protection using the password
sheet.Unprotect("e-iceblue")

workbook.SaveToFile("UnprotectedSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

If the worksheet was protected without a password, you can call Unprotect() without passing any arguments.

Practical Tips

Combining protection with editable ranges: When building data entry templates, use AddAllowEditRange() to open up input areas, then protect the worksheet. This prevents users from accidentally modifying headers and formulas while still allowing normal business data entry.

Batch protecting multiple worksheets: Iterate through all worksheets in a workbook and apply a uniform protection password:

for i in range(workbook.Worksheets.Count):
    sheet = workbook.Worksheets[i]
    sheet.Protect("CommonPassword", SheetProtectionType.All)

Password security best practices: In production environments, passwords should not be hardcoded in source code. Use environment variables or configuration files to manage passwords, and choose sufficiently strong passwords to guard against brute-force attacks.

Conclusion

This article covered the complete approach to protecting and unprotecting Excel worksheets and workbooks using Python, including basic protection, locking specific cells and rows/columns, defining editable ranges, hiding formulas, and removing protection. These capabilities have broad practical value in automated report generation, batch file processing, and document security management scenarios.

Combined with features like conditional formatting and data validation, you can build more secure and feature-rich Excel automation solutions.

Encrypt and Decrypt PDF Documents Using Python

Allen Yang — Fri, 03 Jul 2026 10:04:14 +0000

In enterprise document management and information security, protecting PDF documents is a critical concern. Whether it's contracts, financial reports, or internal technical documentation, encryption is needed to prevent unauthorized access and tampering. Manually setting passwords on each document is not only time-consuming but also difficult to integrate into automated workflows.

Python provides a programmatic approach to batch-encrypt and decrypt PDF documents. By controlling password settings, encryption algorithm selection, and permission assignment through code, document security policies can be standardized and embedded into existing document processing pipelines. This article covers how to implement PDF encryption, permission control, and decryption using Python.

Environment Setup

This article uses the Spire.PDF for Python library to work with PDF files. Install it via pip:

pip install Spire.PDF

Import the required modules in your script:

from spire.pdf import *

Core Concepts of PDF Encryption

Before writing any code, it's important to understand several key concepts in the PDF encryption system:

User Password: The password required to open the document. Once a user password is set, anyone without the password will be unable to view the document's contents.
Owner Password: The password that controls document permissions. Users with the owner password can modify the document's security settings and permissions.
Encryption Algorithm: PDF supports multiple encryption standards, including RC4 (40-bit and 128-bit), AES-128, and AES-256. The stronger the algorithm, the better the security.
Document Permissions: Controls whether users can perform specific operations on the document, such as printing, copying text, or filling out forms.

In Spire.PDF, all of these settings are managed through the PdfPasswordSecurityPolicy class.

Encrypt a PDF Using RC4

The most basic form of encryption involves setting both a user password and an owner password on the PDF. The following example uses the RC4-128 bit encryption algorithm and allows printing and form filling:

from spire.pdf import *

# Load the PDF document
doc = PdfDocument()
doc.LoadFromFile("input.pdf")

# Create a security policy with user and owner passwords
securityPolicy = PdfPasswordSecurityPolicy("open", "test")

# Set the encryption algorithm to RC4-128 bit
securityPolicy.EncryptionAlgorithm = PdfEncryptionAlgorithm.RC4_128

# Define document permissions: forbid all, then selectively enable
dp = PdfDocumentPrivilege.ForbidAll()
dp.AllowPrint = True
dp.AllowFillFormFields = True
securityPolicy.DocumentPrivilege = dp

# Apply the encryption policy
doc.Encrypt(securityPolicy)

# Save the document
doc.SaveToFile("Encrypted_RC4.pdf")

The key logic in this code is the permission control section. ForbidAll() first sets all permissions to denied, then specific operations are enabled by setting AllowPrint and AllowFillFormFields to True. This "deny by default, grant selectively" approach is more secure than "allow by default, deny selectively," as it avoids accidentally leaving some permissions unblocked.

Increase Security with AES Encryption

AES (Advanced Encryption Standard) is a widely recognized encryption algorithm in the industry and offers stronger security than RC4. Spire.PDF supports both AES-128 and AES-256 key lengths:

from spire.pdf import *

# Load the PDF document
pdfDocument = PdfDocument()
pdfDocument.LoadFromFile("input.pdf")

# Create a security policy
securityPolicy = PdfPasswordSecurityPolicy("123456789", "M123456789")

# Use the AES-128 encryption algorithm
securityPolicy.EncryptionAlgorithm = PdfEncryptionAlgorithm.AES_128

# Forbid all permissions, allow printing only
securityPolicy.DocumentPrivilege = PdfDocumentPrivilege.ForbidAll()
securityPolicy.DocumentPrivilege.AllowPrint = True

# Encrypt and save
pdfDocument.Encrypt(securityPolicy)
pdfDocument.SaveToFile("Encrypted_AES128.pdf")
pdfDocument.Close()

Note that AES encryption requires a user password of at least 8 characters. If the password is too short, the encryption operation may fail. In production environments, it is recommended to use strong passwords that include uppercase and lowercase letters, numbers, and special characters.

High-Level Encryption with AES-256

For documents containing sensitive business data or personal information, AES-256 provides the highest level of encryption protection:

from spire.pdf import *

doc = PdfDocument()
doc.LoadFromFile("input.pdf")

# No user password (left empty), owner password only
securityPolicy = PdfPasswordSecurityPolicy("", "test")

# Use AES-256 encryption
securityPolicy.EncryptionAlgorithm = PdfEncryptionAlgorithm.AES_256

# Set permissions: allow degraded printing and form filling
dp = PdfDocumentPrivilege.ForbidAll()
dp.AllowDegradedPrinting = True
dp.AllowFillFormFields = True
securityPolicy.DocumentPrivilege = dp

doc.Encrypt(securityPolicy)
doc.SaveToFile("Encrypted_AES256.pdf")

There is a notable detail in this code: the user password is set to an empty string. This means anyone can open the document and view its contents, but the document's operational permissions (printing, copying, etc.) are protected by the owner password. This configuration is suitable for documents that need to be widely distributed but require restrictions on editing and copying.

The difference between AllowDegradedPrinting and AllowPrint is that degraded printing produces lower-resolution output, preventing high-quality document reproduction — ideal for protecting content that contains intellectual property.

Decrypt a PDF Document

Decryption requires knowledge of the owner password (or user password). Pass the password when loading the document, then call the Decrypt() method:

from spire.pdf import *

# Load the encrypted PDF using the owner password
doc = PdfDocument()
doc.LoadFromFile("Encrypted_RC4.pdf", "test")

# Decrypt the document
doc.Decrypt()

# Save as an unencrypted PDF
doc.SaveToFile("Decrypted.pdf")

If the document was opened with the user password, the owner password must also be provided for decryption:

# Load using the user password
doc.LoadFromFile("Encrypted_RC4.pdf", "open")

# Pass the owner password to decrypt
doc.Decrypt("test")

doc.SaveToFile("Decrypted.pdf")

Once decrypted, the PDF document will no longer be protected by any passwords or permissions and can be opened and edited normally.

Available Permissions at a Glance

PdfDocumentPrivilege provides a range of permission control options. Here are the commonly used permission properties:

AllowPrint: Allows high-quality printing
AllowDegradedPrinting: Allows degraded (low-resolution) printing
AllowCopy: Allows copying document content
AllowFillFormFields: Allows filling in form fields
AllowModifyContents: Allows modifying document content
AllowComment: Allows adding comments

By combining these permissions thoughtfully, you can create differentiated security policies for different document types. For example, reports distributed to clients can allow printing but prohibit copying; internal forms can allow filling but prevent other modifications.

Practical Tips

Choosing an encryption algorithm: RC4-128 offers good compatibility and is suitable for scenarios that require backward compatibility with older PDF readers. AES-128 is sufficient for most everyday needs. AES-256 is recommended for documents containing highly sensitive information. In scenarios where security requirements are not particularly strict, AES-128 is more than adequate.

Batch encryption: When you need to apply the same encryption to multiple PDFs in a folder, combine this with Python's os or pathlib module to iterate through files and apply a uniform security policy. Owner and user passwords can be managed via configuration files or environment variables to avoid hardcoding them in scripts.

Password management best practices: In real-world projects, passwords should not be written directly in source code. Use environment variables, configuration files, or key management services to store passwords and reduce the risk of credential exposure.

Conclusion

This article covered the complete approach to encrypting and decrypting PDF documents using Python, including the use of RC4, AES-128, and AES-256 encryption algorithms, as well as the configuration of user passwords, owner passwords, and document permissions. These capabilities have broad practical value in document security management, automated report generation, and content protection scenarios.

Combined with other Spire.PDF features such as watermarking, text replacement, and form handling, you can build more comprehensive document security automation solutions.