Forem: Allen Yang

Creating and Formatting Tables in PowerPoint Using Python

Allen Yang — Sat, 09 May 2026 09:40:16 +0000

Tables are essential tools for presenting structured data in business presentations. Whether displaying financial reports, project timelines, or comparative analyses, clear and well-formatted tables significantly enhance information delivery efficiency. Automating PowerPoint table generation with Python enables batch data processing, maintains formatting consistency, and substantially reduces manual operation time.

This article demonstrates how to use the Spire.Presentation for Python library to create tables in PowerPoint slides and customize table styles, borders, cell merging, and text alignment properties. This solution is suitable for scenarios requiring dynamic report generation, automated presentation creation, or batch export of data analysis results.

Environment Setup

First, install the Spire.Presentation for Python library:

pip install Spire.Presentation

This library provides a complete PowerPoint document manipulation API, supporting creation, reading, modification, and conversion of PPT/PPTX files without requiring Microsoft PowerPoint application installation.

Core Implementation

Creating Tables and Populating Data

The first step in creating a table is defining column widths and row heights, then adding it to the slide. The following example demonstrates creating a table containing country information:

from spire.presentation.common import *
from spire.presentation import *
import math

# Create PowerPoint document
presentation = Presentation()

# Define table column widths and row heights (in points)
widths = [100, 100, 150, 100, 100]
heights = [15] * 13

# Calculate table position (horizontally centered)
left = math.trunc(presentation.SlideSize.Size.Width / 2) - 275

# Add table to slide
table = presentation.Slides[0].Shapes.AppendTable(left, 90, widths, heights)

# Prepare table data
dataStr = [
    ["Name", "Capital", "Continent", "Area", "Population"],
    ["Venezuela", "Caracas", "South America", "912047", "19700000"],
    ["Bolivia", "La Paz", "South America", "1098575", "7300000"],
    ["Brazil", "Brasilia", "South America", "8511196", "150400000"],
    ["Canada", "Ottawa", "North America", "9976147", "26500000"],
    ["Chile", "Santiago", "South America", "756943", "13200000"]
]

# Populate table data
for i in range(len(dataStr)):
    for j in range(len(dataStr[i])):
        table[j, i].TextFrame.Text = dataStr[i][j]
        # Set font
        table[j, i].TextFrame.Paragraphs[0].TextRanges[0].LatinFont = TextFont("Arial Narrow")

# Save document
presentation.SaveToFile("CreateTable.pptx", FileFormat.Pptx2010)
presentation.Dispose()

Resulting document:

The above code creates a table at the specified position using the AppendTable method, which accepts four parameters: left margin, top margin, column width array, and row height array. After table creation, each cell is accessed via two-dimensional indexing [column, row], and cell content is set using the TextFrame.Text property.

Applying Preset Table Styles

Spire.Presentation provides multiple preset table styles for quick table appearance enhancement. Preset styles include different color themes and format combinations:

from spire.presentation.common import *
from spire.presentation import *

# Load existing document
ppt = Presentation()
ppt.LoadFromFile("CreateTable.pptx")

# Get table object
table = None
for shape in ppt.Slides[0].Shapes:
    if isinstance(shape, ITable):
        table = shape
        break

if table is not None:
    # Apply preset style
    table.StylePreset = TableStylePreset.MediumStyle1Accent2

    # Other commonly used preset styles:
    # TableStylePreset.LightStyle3Accent1
    # TableStylePreset.DarkStyle1Accent2
    # TableStylePreset.MediumStyle2Accent3

# Save document
ppt.SaveToFile("StyledTable.pptx", FileFormat.Pptx2010)
ppt.Dispose()

Resulting document:

The StylePreset property accepts TableStylePreset enumeration values. These preset styles have preconfigured background colors, borders, font colors, and other properties. Selecting appropriate preset styles quickly achieves professional visual effects.

Customizing Table Borders

Beyond using preset styles, table borders can be precisely controlled for color, width, and type:

from spire.presentation.common import *
from spire.presentation import *

# Create new document
presentation = Presentation()

# Define table dimensions
tableWidth = [100, 100, 100, 100, 100]
tableHeight = [20, 20]

# Iterate through all border types and set styles
for item in TableBorderType:
    # Add table to new slide
    itable = presentation.Slides.Append().Shapes.AppendTable(100, 100, tableWidth, tableHeight)

    # Add sample text
    itable.TableRows[0][0].TextFrame.Text = "Row"
    itable.TableRows[1][0].TextFrame.Text = "Column"

    # Set border: type, width (points), color
    itable.SetTableBorder(item, 1.5, Color.get_Red())

# Save document
presentation.SaveToFile("CustomBorders.pptx", FileFormat.Pptx2013)
presentation.Dispose()

The SetTableBorder method allows separate style settings for different border types (such as internal horizontal borders, internal vertical borders, outer borders, etc.). The TableBorderType enumeration includes the following commonly used values:

InsideHorizontal: Internal horizontal borders
InsideVertical: Internal vertical borders
Top: Top border
Bottom: Bottom border
Left: Left border
Right: Right border

Merging Cells

When handling complex table layouts, merging adjacent cells is often necessary to create header rows or grouping areas:

from spire.presentation.common import *
from spire.presentation import *

# Load document
presentation = Presentation()
presentation.LoadFromFile("CreateTable.pptx")

# Get table
table = None
for shape in presentation.Slides[0].Shapes:
    if isinstance(shape, ITable):
        table = shape
        break

if table is not None:
    # Vertical merge: merge rows 2 and 3 in the first column
    table.MergeCells(table[0, 1], table[0, 2], False)

    # Horizontal merge: merge columns 4 and 5 in row 5
    table.MergeCells(table[3, 4], table[4, 4], True)

# Save document
presentation.SaveToFile("MergedCells.pptx", FileFormat.Pptx2010)
presentation.Dispose()

The MergeCells method accepts three parameters: starting cell, ending cell, and merge direction flag. The third parameter set to True indicates horizontal merging, while False indicates vertical merging. Merged cells retain the content and formatting of the starting cell.

Setting Text Alignment

Text alignment in tables directly impacts readability and aesthetics. Both horizontal and vertical alignment can be set simultaneously:

from spire.presentation.common import *
from spire.presentation import *

# Load document
presentation = Presentation()
presentation.LoadFromFile("CreateTable.pptx")

# Get table
table = None
for shape in presentation.Slides[0].Shapes:
    if isinstance(shape, ITable):
        table = shape
        break

if table is not None:
    # Horizontal alignment settings
    # Left align
    table[0, 1].TextFrame.Paragraphs[0].Alignment = TextAlignmentType.Left
    # Center align
    table[0, 2].TextFrame.Paragraphs[0].Alignment = TextAlignmentType.Center
    # Right align
    table[0, 3].TextFrame.Paragraphs[0].Alignment = TextAlignmentType.Right
    # Justify
    table[0, 4].TextFrame.Paragraphs[0].Alignment = TextAlignmentType.Justify

    # Vertical alignment settings
    # Top align
    table[1, 1].TextAnchorType = TextAnchorType.Top
    # Vertically center
    table[1, 2].TextAnchorType = TextAnchorType.Center
    # Bottom align
    table[1, 3].TextAnchorType = TextAnchorType.Bottom

    # Set both horizontal and vertical alignment
    table[2, 1].TextFrame.Paragraphs[0].Alignment = TextAlignmentType.Left
    table[2, 1].TextAnchorType = TextAnchorType.Top

# Save document
presentation.SaveToFile("AlignedTable.pptx", FileFormat.Pptx2010)
presentation.Dispose()

Horizontal alignment is set through the TextFrame.Paragraphs[0].Alignment property, accepting TextAlignmentType enumeration values. Vertical alignment is controlled through the TextAnchorType property, managing text positioning in the vertical direction within cells.

Feature Demonstration

Comprehensive Example: Creating a Fully Formatted Table

Combining the above features enables creation of professionally formatted complete tables:

from spire.presentation.common import *
from spire.presentation import *
import math

# Create document
presentation = Presentation()

# Define table structure
widths = [120, 100, 100, 100]
heights = [20, 18, 18, 18, 18]

# Add table
left = math.trunc(presentation.SlideSize.Size.Width / 2) - 210
table = presentation.Slides[0].Shapes.AppendTable(left, 80, widths, heights)

# Populate data
headers = ["Product", "Q1", "Q2", "Q3"]
products = [
    ["Product A", "150", "180", "210"],
    ["Product B", "120", "140", "160"],
    ["Product C", "90", "110", "130"]
]

# Set headers
for j in range(len(headers)):
    table[j, 0].TextFrame.Text = headers[j]
    table[j, 0].TextFrame.Paragraphs[0].Alignment = TextAlignmentType.Center
    table[j, 0].TextFrame.Paragraphs[0].TextRanges[0].Fill.FillType = FillFormatType.Solid
    table[j, 0].TextFrame.Paragraphs[0].TextRanges[0].Fill.SolidColor.Color = Color.get_White()

# Set data rows
for i in range(len(products)):
    for j in range(len(products[i])):
        table[j, i + 1].TextFrame.Text = products[i][j]
        if j == 0:
            table[j, i + 1].TextFrame.Paragraphs[0].Alignment = TextAlignmentType.Left
        else:
            table[j, i + 1].TextFrame.Paragraphs[0].Alignment = TextAlignmentType.Center

# Apply style
table.StylePreset = TableStylePreset.MediumStyle2Accent3

# Set borders
table.SetTableBorder(TableBorderType.InsideHorizontal, 0.5, Color.get_Gray())
table.SetTableBorder(TableBorderType.InsideVertical, 0.5, Color.get_Gray())
table.SetTableBorder(TableBorderType.Top, 1.5, Color.get_DarkBlue())
table.SetTableBorder(TableBorderType.Bottom, 1.5, Color.get_DarkBlue())

# Save document
presentation.SaveToFile("CompleteTable.pptx", FileFormat.Pptx2010)
presentation.Dispose()

This example comprehensively applies data population, style application, border settings, and alignment control to generate a sales data table with clear hierarchical structure. Headers use white font for emphasis, data is left-aligned by category and center-aligned for numerical values, with an overall medium-intensity accent color theme.

Practical Tips

Adjusting Row Heights and Column Widths

After table creation, cell dimensions can be dynamically adjusted based on content:

# Set specific row height
table.TableRows[0].Height = 25

# Set specific column width
table.ColumnsList[0].Width = 150

# Batch set all row heights
for row in table.TableRows:
    row.Height = 20

Filling Cell Background Colors

Background colors can be set for specific rows or cells to highlight important information:

# Set background color for entire row
for cell in table.TableRows[1]:
    cell.Fill.FillType = FillFormatType.Solid
    cell.Fill.SolidColor.Color = Color.get_LightYellow()

# Set background color for single cell
table[0, 2].Fill.FillType = FillFormatType.Solid
table[0, 2].Fill.SolidColor.Color = Color.get_LightGreen()

Identifying Merged Cells

When processing existing tables, detecting which cells have been merged may be necessary:

# Iterate through all cells to identify merge status
for i in range(table.ColumnsList.Count):
    for j in range(table.TableRows.Count):
        cell = table[i, j]
        if cell.IsMergedCell:
            print(f"Cell [{i}, {j}] is a merged cell")

Summary

This article introduces the complete process of creating and formatting tables in PowerPoint using Python, covering everything from basic data population to advanced style customization. Through the Spire.Presentation library, flexible control over all aspects of tables is achievable:

Use the AppendTable method to create tables and define dimensions
Quickly apply preset styles through StylePreset
Precisely control border styles using SetTableBorder
Implement complex cell layouts through MergeCells
Optimize text layout using Alignment and TextAnchorType

These techniques can be applied to automated report generation, batch presentation creation, and data visualization scenarios. Combined with other PowerPoint automation features such as shapes, charts, and SmartArt, richer and more professional presentation content can be constructed.

Managing Word Document Bookmarks with Python

Allen Yang — Sat, 09 May 2026 09:15:35 +0000

When working with lengthy Word documents, bookmarks serve as a valuable navigation and positioning tool. Bookmarks allow developers to mark specific locations or content areas within a document, enabling quick access, content replacement, and dynamic document generation.

This article demonstrates how to perform various bookmark operations in Word documents using Python and the Spire.Doc for Python library, including creating bookmarks, extracting bookmark text, replacing bookmark content, inserting images and tables at bookmark positions, and deleting bookmarks.

Environment Setup

First, install the Spire.Doc for Python library:

pip install Spire.Doc

Once installed, you can begin using bookmark-related APIs for Word document processing.

What Are Bookmarks and Their Use Cases

Bookmarks are a marking mechanism in Word documents that allow naming specific locations or content areas. Common use cases include:

Document Navigation: Add bookmarks to important chapters or paragraphs for quick user navigation
Template Filling: Predefine bookmarks in document templates and dynamically replace them with actual content during runtime
Content Extraction: Quickly extract content from specifically marked areas in documents
Dynamic Document Generation: Insert text, images, or tables at designated positions based on business requirements

Creating Bookmarks

Creating bookmarks is the most fundamental operation. In Spire.Doc, bookmarks can be defined using the AppendBookmarkStart and AppendBookmarkEnd methods to mark the start and end positions.

The following example demonstrates how to create simple bookmarks and nested bookmarks:

from spire.doc import *
from spire.doc.common import *

def CreateBookmarkInDocument():
    # Create a Word document
    document = Document()

    # Add a new section
    section = document.AddSection()

    # Add a title paragraph
    paragraph = section.AddParagraph()
    txtRange = paragraph.AppendText("The following example demonstrates how to create bookmarks in a Word document.")
    txtRange.CharacterFormat.Italic = True

    # Add a simple bookmark
    section.AddParagraph()
    paragraph = section.AddParagraph()
    txtRange = paragraph.AppendText("Simple Bookmark Example")
    txtRange.CharacterFormat.TextColor = Color.get_CornflowerBlue()
    paragraph.ApplyStyle(BuiltinStyle.Heading2)

    section.AddParagraph()
    paragraph = section.AddParagraph()
    paragraph.AppendBookmarkStart("SimpleBookmark")
    paragraph.AppendText("This is the content of a simple bookmark.")
    paragraph.AppendBookmarkEnd("SimpleBookmark")

    # Add nested bookmarks
    section.AddParagraph()
    paragraph = section.AddParagraph()
    txtRange = paragraph.AppendText("Nested Bookmark Example")
    txtRange.CharacterFormat.TextColor = Color.get_CornflowerBlue()
    paragraph.ApplyStyle(BuiltinStyle.Heading2)

    section.AddParagraph()
    paragraph = section.AddParagraph()
    paragraph.AppendBookmarkStart("Root")
    txtRange = paragraph.AppendText(" Root level content ")
    txtRange.CharacterFormat.Italic = True

    paragraph.AppendBookmarkStart("Level1")
    txtRange = paragraph.AppendText(" First level nested content ")
    txtRange.CharacterFormat.Italic = True
    txtRange.CharacterFormat.TextColor = Color.get_DarkSlateGray()

    paragraph.AppendBookmarkStart("Level2")
    txtRange = paragraph.AppendText(" Second level nested content ")
    txtRange.CharacterFormat.Italic = True
    txtRange.CharacterFormat.TextColor = Color.get_DimGray()
    paragraph.AppendBookmarkEnd("Level2")

    paragraph.AppendBookmarkEnd("Level1")
    paragraph.AppendBookmarkEnd("Root")

    # Save the document
    document.SaveToFile("CreateBookmark.docx", FileFormat.Docx)
    document.Close()

CreateBookmarkInDocument()

Resulting document:

In the code above, the AppendBookmarkStart and AppendBookmarkEnd methods are used in pairs, identifying the bookmark range with the same bookmark name. Nested bookmarks allow other bookmarks to be contained within one bookmark, forming a hierarchical structure.

Extracting Bookmark Text

Extracting bookmark content is a common requirement, especially when retrieving specific information from large documents. The BookmarksNavigator class can be used to locate and extract bookmark content.

from spire.doc import *
from spire.doc.common import *

def ExtractBookmarkText():
    # Load the document
    doc = Document()
    doc.LoadFromFile("CreateBookmark.docx")

    # Create a bookmark navigator instance
    navigator = BookmarksNavigator(doc)

    # Move to the specified bookmark
    navigator.MoveToBookmark("SimpleBookmark")

    # Get the bookmark content
    textBodyPart = navigator.GetBookmarkContent()

    # Iterate through bookmark content items to extract text
    text = ''
    for i in range(textBodyPart.BodyItems.Count):
        item = textBodyPart.BodyItems.get_Item(i)
        if isinstance(item, Paragraph):
            for j in range(item.ChildObjects.Count):
                childObject = item.ChildObjects.get_Item(j)
                if isinstance(childObject, TextRange):
                    text += childObject.Text

    print(f"Extracted bookmark text: {text}")

    # Write text to file
    with open("ExtractedText.txt", "w", encoding="utf-8") as f:
        f.write(text)

    doc.Close()

ExtractBookmarkText()

The MoveToBookmark method of the BookmarksNavigator class is used to locate the bookmark with the specified name, and the GetBookmarkContent method returns the content object of the bookmark. By iterating through the child items in the content object, plain text content can be extracted.

Replacing Bookmark Content

In practical applications, it is often necessary to replace the original content at a bookmark position with new content. This is particularly useful in document template filling scenarios.

from spire.doc import *
from spire.doc.common import *

def ReplaceBookmarkContent():
    # Load the document
    doc = Document()
    doc.LoadFromFile("CreateBookmark.docx")

    # Navigate to the bookmark
    bookmarkNavigator = BookmarksNavigator(doc)
    bookmarkNavigator.MoveToBookmark("SimpleBookmark")

    # Replace bookmark content with new content
    bookmarkNavigator.ReplaceBookmarkContent("This is the new content after replacement.", False)

    # Save the document
    doc.SaveToFile("ReplaceBookmarkContent.docx", FileFormat.Docx)
    doc.Close()

ReplaceBookmarkContent()

The first parameter of the ReplaceBookmarkContent method is the new content to replace with, and the second parameter controls whether to preserve the original formatting. Setting it to False means not preserving the original formatting and using the default formatting of the new content.

Inserting Images at Bookmark Positions

In addition to text replacement, images can also be inserted at bookmark positions, which is useful for generating documents containing charts or logos.

from spire.doc import *
from spire.doc.common import *

def InsertImageAtBookmark():
    # Load the document
    doc = Document()
    doc.LoadFromFile("CreateBookmark.docx")

    # Create a bookmark navigator instance
    bn = BookmarksNavigator(doc)

    # Find the bookmark named "SimpleBookmark"
    bn.MoveToBookmark("SimpleBookmark", True, True)

    # Add a temporary section and paragraph
    section0 = doc.AddSection()
    paragraph = section0.AddParagraph()

    # Add an image to the paragraph
    picture = paragraph.AppendPicture("./SampleImage.png")

    # Insert the paragraph at the bookmark position
    bn.InsertParagraph(paragraph)

    # Remove the temporary section
    doc.Sections.Remove(section0)

    # Save the document
    doc.SaveToFile("InsertImageAtBookmark.docx", FileFormat.Docx)
    doc.Close()

InsertImageAtBookmark()

The core idea of this method is to first create a paragraph containing an image, then use the InsertParagraph method to insert that paragraph at the bookmark position. Finally, remove the temporarily created section to keep the document structure clean.

Inserting Tables at Bookmark Positions

Tables are commonly used elements in Word documents, and structured data can be dynamically displayed by inserting tables at bookmark positions.

from spire.doc import *
from spire.doc.common import *

def ReplaceBookmarkWithTable():
    # Load the document
    doc = Document()
    doc.LoadFromFile("CreateBookmark.docx")

    # Create a table
    table = Table(doc, True)

    # Prepare table data
    rowsCount = 4
    colsCount = 5
    data = [
        ["Name", "Capital", "Continent", "Area (km²)", "Population"],
        ["Argentina", "Buenos Aires", "South America", "2777815", "32300003"],
        ["Bolivia", "La Paz", "South America", "1098575", "7300000"],
        ["Brazil", "Brasília", "South America", "8511196", "150400000"]
    ]

    # Reset table cells
    table.ResetCells(rowsCount, colsCount)

    # Fill table data
    for i in range(rowsCount):
        for j in range(colsCount):
            table.Rows[i].Cells[j].AddParagraph().AppendText(data[i][j])

    # Navigate to the bookmark
    navigator = BookmarksNavigator(doc)
    navigator.MoveToBookmark("SimpleBookmark")

    # Create a TextBodyPart instance and add the table
    part = TextBodyPart(doc)
    part.BodyItems.Add(table)

    # Replace bookmark content with the table
    navigator.ReplaceBookmarkContent(part)

    # Save the document
    doc.SaveToFile("ReplaceWithTable.docx", FileFormat.Docx)
    doc.Close()

ReplaceBookmarkWithTable()

In this example, a Table object is first created and populated with data, then the table is added to a TextBodyPart object, and finally the ReplaceBookmarkContent method is used to insert the table at the bookmark position. This method allows flexible insertion of various complex content.

Deleting Bookmarks

When a bookmark is no longer needed, it can be removed from the document. It should be noted that deleting a bookmark does not delete its contained content; only the bookmark marker itself is removed.

from spire.doc import *
from spire.doc.common import *

def RemoveBookmark():
    # Load the document
    document = Document()
    document.LoadFromFile("CreateBookmark.docx")

    # Get the bookmark by name
    bookmark = document.Bookmarks["SimpleBookmark"]

    # Delete the bookmark (preserve content)
    document.Bookmarks.Remove(bookmark)

    # Save the document
    document.SaveToFile("RemoveBookmark.docx", FileFormat.Docx)
    document.Close()

RemoveBookmark()

If both the bookmark and its content need to be deleted, the content can first be cleared using the ReplaceBookmarkContent method, and then the bookmark marker can be deleted.

Practical Tips

Getting All Bookmarks in a Document

When processing documents with unknown structures, it may be necessary to first obtain a list of all bookmark names:

from spire.doc import *

def GetAllBookmarks():
    doc = Document()
    doc.LoadFromFile("CreateBookmark.docx")

    # Iterate through all bookmarks
    for bookmark in doc.Bookmarks:
        print(f"Bookmark name: {bookmark.Name}")

    doc.Close()

GetAllBookmarks()

Checking if a Bookmark Exists

Before operating on a bookmark, it is best to check whether the bookmark exists to avoid runtime errors:

def CheckBookmarkExists(doc, bookmarkName):
    for bookmark in doc.Bookmarks:
        if bookmark.Name == bookmarkName:
            return True
    return False

Summary

This article introduced various techniques for performing bookmark operations in Word documents using Python and the Spire.Doc for Python library, including:

Creating simple and nested bookmarks
Extracting text content from bookmarks
Replacing bookmark content with new text
Inserting images at bookmark positions
Inserting tables at bookmark positions
Deleting bookmark markers

Bookmark functionality provides powerful support for automated Word document processing, particularly suitable for scenarios such as document template filling, dynamic content generation, and information extraction. By making proper use of bookmarks, the efficiency and flexibility of document processing can be significantly improved.

In actual development, these operations can be combined according to specific requirements to build more complex document automation solutions. For example, a document template containing multiple bookmarks can be created, and then content at each bookmark position can be dynamically filled based on business data to achieve batch document generation.

Adding Data Validation in Excel Using Python

Allen Yang — Thu, 30 Apr 2026 10:07:46 +0000

When working with spreadsheet data, ensuring the accuracy and consistency of input is crucial. The data validation feature allows developers to set rules that restrict what users can enter into cells, thereby preventing invalid data entry. This article demonstrates how to add various types of data validation rules to Excel worksheets using Python.

Why Data Validation Is Needed

Data validation serves multiple purposes in practical applications:

Ensure Data Quality: Restrict input ranges to avoid invalid data
Improve User Experience: Provide clear error messages and guidance
Simplify Data Processing: Reduce the workload for subsequent data cleaning
Automate Form Control: Create standardized data entry templates

Common validation scenarios include numeric range restrictions, date validity checks, text length controls, and more.

Environment Setup

First, install the Spire.XLS for Python library:

pip install Spire.XLS

This library provides a complete API for Excel file operations, supporting the creation, modification, and formatting of Excel documents.

Basic Implementation Steps

The core process for adding data validation includes:

Load or create an Excel workbook
Get the target worksheet
Specify the cell range where validation should be applied
Configure validation rules (type, conditions, error messages)
Save the file

The following examples demonstrate how to implement different types of data validation.

Adding Numeric Range Validation

Numeric validation is the most common validation type, restricting users to enter only numbers within a specific range. The following code demonstrates how to set up a decimal number validation between 3 and 6:

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

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

# Add description label
sheet.Range["B11"].Text = "Enter a number (3-6):"

# Get target cell range
rangeNumber = sheet.Range["B12"]

# Set comparison operator to "Between"
rangeNumber.DataValidation.CompareOperator = ValidationComparisonOperator.Between

# Set minimum and maximum values
rangeNumber.DataValidation.Formula1 = "3"
rangeNumber.DataValidation.Formula2 = "6"

# Specify validation type as decimal
rangeNumber.DataValidation.AllowType = CellDataType.Decimal

# Set error message
rangeNumber.DataValidation.ErrorMessage = "Please enter a valid number."

# Enable error display
rangeNumber.DataValidation.ShowError = True

# Set cell background color to identify validation area
rangeNumber.Style.KnownColor = ExcelColors.Gray25Percent

# Auto-fit column width
sheet.AutoFitColumn(2)

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

Key API explanations:

CompareOperator: Defines the comparison method, such as Between, Greater, Less, etc.
Formula1 and Formula2: Set boundary values for validation conditions
AllowType: Specifies the data type, such as Decimal, Integer, etc.
ErrorMessage: Error message displayed when input is invalid
ShowError: Controls whether to display the error dialog box

Adding Date Validation

Date validation ensures that users enter dates within a valid range. This is particularly useful when handling schedules, deadlines, and similar scenarios:

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

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

# Add description label
sheet.Range["B14"].Text = "Enter a date"

# Get target cell
rangeDate = sheet.Range["B15"]

# Set validation type to date
rangeDate.DataValidation.AllowType = CellDataType.Date

# Set comparison operator
rangeDate.DataValidation.CompareOperator = ValidationComparisonOperator.Between

# Set date range (January 1, 1970 to December 31, 1970)
rangeDate.DataValidation.Formula1 = "1/1/1970"
rangeDate.DataValidation.Formula2 = "12/31/1970"

# Set error message
rangeDate.DataValidation.ErrorMessage = "Please enter a valid date."

# Enable error display
rangeDate.DataValidation.ShowError = True

# Set warning style (optional: Stop, Warning, Information)
rangeDate.DataValidation.AlertStyle = AlertStyleType.Warning

# Set cell background color
rangeDate.Style.KnownColor = ExcelColors.Gray25Percent

sheet.AutoFitColumn(2)

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

Date formats can use various standard representations, such as "MM/DD/YYYY" or "YYYY-MM-DD". The AlertStyleType provides three error prompt styles:

Stop: Prevents users from entering invalid data
Warning: Warns but allows continuation
Information: Provides informational prompts only

Adding Text Length Validation

Text length validation controls the maximum or minimum number of characters in a string, suitable for fields like usernames, passwords, codes, etc.:

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

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

# Add description label
sheet.Range["B17"].Text = "Enter text"

# Get target cell
rangeTextLength = sheet.Range["B18"]

# Set validation type to text length
rangeTextLength.DataValidation.AllowType = CellDataType.TextLength

# Set comparison operator to "LessOrEqual"
rangeTextLength.DataValidation.CompareOperator = ValidationComparisonOperator.LessOrEqual

# Set maximum length to 5 characters
rangeTextLength.DataValidation.Formula1 = "5"

# Set error message
rangeTextLength.DataValidation.ErrorMessage = "Please enter a valid string."

# Enable error display
rangeTextLength.DataValidation.ShowError = True

# Set stop style to strictly prevent invalid input
rangeTextLength.DataValidation.AlertStyle = AlertStyleType.Stop

# Set cell background color
rangeTextLength.Style.KnownColor = ExcelColors.Gray25Percent

sheet.AutoFitColumn(2)

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

Text length validation supports various comparison operators:

LessOrEqual: Less than or equal to the specified length
GreaterOrEqual: Greater than or equal to the specified length
Between: Between two length values
Equal: Equal to the specified length

Comprehensive Example: Adding Multiple Validations in One File

In practical applications, it's common to apply multiple validation rules within the same worksheet. Here's a complete example integrating the three validation types mentioned above into one file:

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

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

# === Numeric Validation ===
sheet.Range["B11"].Text = "Enter a number (3-6):"
rangeNumber = sheet.Range["B12"]
rangeNumber.DataValidation.CompareOperator = ValidationComparisonOperator.Between
rangeNumber.DataValidation.Formula1 = "3"
rangeNumber.DataValidation.Formula2 = "6"
rangeNumber.DataValidation.AllowType = CellDataType.Decimal
rangeNumber.DataValidation.ErrorMessage = "Please enter a valid number."
rangeNumber.DataValidation.ShowError = True
rangeNumber.Style.KnownColor = ExcelColors.Gray25Percent

# === Date Validation ===
sheet.Range["B14"].Text = "Enter a date"
rangeDate = sheet.Range["B15"]
rangeDate.DataValidation.AllowType = CellDataType.Date
rangeDate.DataValidation.CompareOperator = ValidationComparisonOperator.Between
rangeDate.DataValidation.Formula1 = "1/1/1970"
rangeDate.DataValidation.Formula2 = "12/31/1970"
rangeDate.DataValidation.ErrorMessage = "Please enter a valid date."
rangeDate.DataValidation.ShowError = True
rangeDate.DataValidation.AlertStyle = AlertStyleType.Warning
rangeDate.Style.KnownColor = ExcelColors.Gray25Percent

# === Text Length Validation ===
sheet.Range["B17"].Text = "Enter text"
rangeTextLength = sheet.Range["B18"]
rangeTextLength.DataValidation.AllowType = CellDataType.TextLength
rangeTextLength.DataValidation.CompareOperator = ValidationComparisonOperator.LessOrEqual
rangeTextLength.DataValidation.Formula1 = "5"
rangeTextLength.DataValidation.ErrorMessage = "Please enter a valid string."
rangeTextLength.DataValidation.ShowError = True
rangeTextLength.DataValidation.AlertStyle = AlertStyleType.Stop
rangeTextLength.Style.KnownColor = ExcelColors.Gray25Percent

# Auto-fit column width
sheet.AutoFitColumn(2)

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

Practical Tips

Custom Dropdown List Validation

In addition to the validation types mentioned above, dropdown lists can also be created for user selection:

# Create dropdown list validation
rangeList = sheet.Range["C5"]
rangeList.DataValidation.AllowType = CellDataType.List
rangeList.DataValidation.Formula1 = '"Option 1,Option 2,Option 3"'
rangeList.DataValidation.ShowDropDown = True

Note: Dropdown list options need to be enclosed in double quotes and separated by commas.

Referencing Validation Data from Cell Ranges

Validation data can be dynamically read from other cell ranges:

# Read list data from range A1:A5
rangeDynamic = sheet.Range["D5"]
rangeDynamic.DataValidation.AllowType = CellDataType.List
rangeDynamic.DataValidation.Formula1 = "=A1:A5"

This approach allows dynamic updates to validation options without modifying the code.

Clearing Data Validation

To remove existing validation rules:

# Clear validation for specified cells
rangeToClear.DataValidation.Clear()

Summary

This article demonstrated how to add data validation in Excel using Python, including numeric range validation, date validation, and text length validation. By properly configuring the properties of the DataValidation object, flexible data input control can be achieved, improving spreadsheet data quality and user experience.

These techniques are particularly applicable to:

Creating standardized data entry templates
Building data collection forms
Implementing data quality control processes
Developing automated reporting systems

After mastering data validation techniques, they can be combined with other Excel operations (such as conditional formatting, formula calculations, etc.) to build more comprehensive automated office solutions.

Applying Conditional Formatting in Excel Using Python

Allen Yang — Thu, 30 Apr 2026 09:46:32 +0000

In daily data processing workflows, conditional formatting is an extremely practical feature. It automatically applies specific formatting to cells based on their content, helping users quickly identify data patterns, highlight key information, or detect outliers. By automating conditional formatting with Python, data processing efficiency can be significantly improved, especially when handling large datasets or generating reports in batches.

This article demonstrates how to apply various types of conditional formatting in Excel worksheets using Python and the Spire.XLS library, including cell value-based formatting, highlighting duplicate and unique values, data bar visualization, and icon sets.

Environment Setup

First, install the Spire.XLS library:

pip install Spire.XLS

Once installed, you can start writing code to implement conditional formatting functionality.

Cell Value-Based Conditional Formatting

The most basic type of conditional formatting applies formats based on cell numeric values. For example, cells exceeding a certain threshold can be marked in red, while those below another threshold can be marked in green.

The following example demonstrates how to create a worksheet with sample data and apply cell value-based conditional formatting:

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

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

# Insert sample data into range A1:C4
sheet.Range["A1"].NumberValue = 582
sheet.Range["A2"].NumberValue = 234
sheet.Range["A3"].NumberValue = 314
sheet.Range["A4"].NumberValue = 50
sheet.Range["B1"].NumberValue = 150
sheet.Range["B2"].NumberValue = 894
sheet.Range["B3"].NumberValue = 560
sheet.Range["B4"].NumberValue = 900
sheet.Range["C1"].NumberValue = 134
sheet.Range["C2"].NumberValue = 700
sheet.Range["C3"].NumberValue = 920
sheet.Range["C4"].NumberValue = 450

# Set row height and column width
sheet.AllocatedRange.RowHeight = 15
sheet.AllocatedRange.ColumnWidth = 17

# Create the first conditional formatting rule: values greater than 800 displayed in red
xcfs1 = sheet.ConditionalFormats.Add()
xcfs1.AddRange(sheet.AllocatedRange)
format1 = xcfs1.AddCondition()
format1.FormatType = ConditionalFormatType.CellValue
format1.FirstFormula = "800"
format1.Operator = ComparisonOperatorType.Greater
format1.FontColor = Color.get_Red()
format1.BackColor = Color.get_LightSalmon()

# Create the second conditional formatting rule: values less than 300 displayed in green
xcfs2 = sheet.ConditionalFormats.Add()
xcfs2.AddRange(sheet.AllocatedRange)
format2 = xcfs2.AddCondition()
format2.FormatType = ConditionalFormatType.CellValue
format2.FirstFormula = "300"
format2.Operator = ComparisonOperatorType.Less
format2.FontColor = Color.get_Green()
format2.BackColor = Color.get_LightBlue()

# Save the file
workbook.SaveToFile("CellValueFormatting.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Result document:

In this example, ConditionalFormatType.CellValue is used to create cell value-based conditional formatting. The threshold is specified by setting FirstFormula, the comparison operator (such as Greater, Less, etc.) is defined using Operator, and then font color and background color are configured. This approach intuitively highlights data that falls outside the normal range.

Highlighting Duplicate and Unique Values

During data analysis, it is often necessary to identify duplicates or unique items within a dataset. Conditional formatting provides specialized functionality to accomplish this.

The following example demonstrates how to highlight duplicate and unique values:

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

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

# ===== 1. Write sample data =====
data = [
    ["ID", "Name", "Value"],
    [1, "A", 10],
    [2, "B", 20],
    [3, "C", 10],
    [4, "D", 30],
    [5, "E", 40],
    [6, "F", 20],
    [7, "G", 50],
    [8, "H", 60],
    [9, "I", 70],
]

for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.Range[r, c].Text = str(value)

# ===== 2. Set column width (optional, for clearer demonstration) =====
sheet.Columns[0].ColumnWidth = 15
sheet.Columns[1].ColumnWidth = 20
sheet.Columns[2].ColumnWidth = 15

# ===== 3. Conditional formatting: duplicate values in column C (Value column) =====
xcfs = sheet.ConditionalFormats.Add()
xcfs.AddRange(sheet.Range["C2:C10"])
format1 = xcfs.AddCondition()
format1.FormatType = ConditionalFormatType.DuplicateValues
format1.BackColor = Color.get_IndianRed()

# ===== 4. Conditional formatting: unique values in column C =====
xcfs1 = sheet.ConditionalFormats.Add()
xcfs1.AddRange(sheet.Range["C2:C10"])
format2 = xcfs1.AddCondition()
format2.FormatType = ConditionalFormatType.UniqueValues
format2.BackColor = Color.get_Yellow()

# ===== 5. Save =====
workbook.SaveToFile("HighlightDuplicateUnique.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Result document:

Two special conditional formatting types are used here: ConditionalFormatType.DuplicateValues to identify duplicate values and ConditionalFormatType.UniqueValues to identify unique values. This functionality is particularly useful in data cleaning and validation scenarios, enabling quick detection of data entry errors or anomalous records.

Applying Data Bar Visualization

Data bars provide an intuitive visualization method by displaying bar charts within cells to represent the relative magnitude of values. Larger values result in longer bars.

The following is an example code for applying data bars:

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

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

# Insert sample data
sheet.Range["A1"].NumberValue = 582
sheet.Range["A2"].NumberValue = 234
sheet.Range["A3"].NumberValue = 314
sheet.Range["A4"].NumberValue = 50
sheet.Range["B1"].NumberValue = 150
sheet.Range["B2"].NumberValue = 894
sheet.Range["B3"].NumberValue = 560
sheet.Range["B4"].NumberValue = 900
sheet.Range["C1"].NumberValue = 134
sheet.Range["C2"].NumberValue = 700
sheet.Range["C3"].NumberValue = 920
sheet.Range["C4"].NumberValue = 450

# Set cell dimensions
sheet.AllocatedRange.RowHeight = 15
sheet.AllocatedRange.ColumnWidth = 17

# Add data bars
xcfs = sheet.ConditionalFormats.Add()
xcfs.AddRange(sheet.AllocatedRange)
format = xcfs.AddCondition()
format.FormatType = ConditionalFormatType.DataBar
format.DataBar.BarColor = Color.get_CadetBlue()

# Save the file
workbook.SaveToFile("DataBarsFormatting.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Result document:

The data bar functionality is implemented through the ConditionalFormatType.DataBar type. The bar color can be customized via the DataBar.BarColor property. This visualization approach is particularly suitable for quickly comparing the magnitude relationships within a set of values without creating separate charts.

Applying Icon Sets

Icon sets are another powerful conditional formatting tool that uses predefined icons (such as arrows, traffic lights, flags, etc.) to represent data status or ratings.

The following example demonstrates how to apply a three-color traffic light icon set:

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

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

# Insert sample data
sheet.Range["A1"].NumberValue = 582
sheet.Range["A2"].NumberValue = 234
sheet.Range["A3"].NumberValue = 314
sheet.Range["A4"].NumberValue = 50
sheet.Range["B1"].NumberValue = 150
sheet.Range["B2"].NumberValue = 894
sheet.Range["B3"].NumberValue = 560
sheet.Range["B4"].NumberValue = 900
sheet.Range["C1"].NumberValue = 134
sheet.Range["C2"].NumberValue = 700
sheet.Range["C3"].NumberValue = 920
sheet.Range["C4"].NumberValue = 450

# Set cell dimensions
sheet.AllocatedRange.RowHeight = 15
sheet.AllocatedRange.ColumnWidth = 17

# Add icon set (three-color traffic lights)
xcfs = sheet.ConditionalFormats.Add()
xcfs.AddRange(sheet.AllocatedRange)
format = xcfs.AddCondition()
format.FormatType = ConditionalFormatType.IconSet
format.IconSet.IconSetType = IconSetType.ThreeTrafficLights1

# Save the file
workbook.SaveToFile("IconSetsFormatting.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Result document:

Icon sets are implemented through the ConditionalFormatType.IconSet type. The IconSetType enumeration provides various icon style options, such as ThreeTrafficLights1 (three-color traffic lights), ThreeArrows (three-way arrows), and more. Icon sets are commonly used to represent performance indicators, status assessments, or rating classifications, making data more intuitive and easier to understand.

Practical Tips

In practical applications, multiple conditional formatting types can be combined to achieve more complex data visualization requirements:

Combined Usage: Multiple conditional formatting rules can be applied to the same data region, with each rule targeting different conditions
Dynamic Ranges: Using AllocatedRange automatically adapts to the size of the data region, avoiding hardcoded cell ranges
Color Selection: Choose color combinations with obvious contrast to ensure formatted data remains easy to read
Performance Considerations: For large datasets, conditional formatting should be used judiciously to avoid excessive rules affecting file opening speed

Summary

This article introduced several main methods for applying conditional formatting in Excel using Python, including cell value-based formatting, duplicate and unique value highlighting, data bar visualization, and icon set application. Through these techniques, data readability and analysis efficiency can be significantly enhanced.

Conditional formatting can be applied not only to simple numeric comparisons but also extended to various data types such as dates and text. Combined with Python's automation capabilities, batch data processing and report generation can be easily achieved, providing strong support for data analysis and business decision-making.

Adding and Setting Text Boxes in PowerPoint Slides Using Python

Allen Yang — Mon, 27 Apr 2026 01:49:59 +0000

When creating PowerPoint presentations, text boxes are one of the most commonly used elements. Programmatically adding and configuring text boxes enables batch creation of presentations, automated report generation, and other tasks. This article demonstrates how to add text boxes to PowerPoint using Python and configure properties such as text content, formatting, and margins.

Environment Setup

First, install the Spire.Presentation library:

pip install Spire.Presentation

Basic Text Box Operations

Creating a Presentation with Text Boxes

In PowerPoint, text boxes are actually a special type of shape. By adding a rectangle shape and setting its TextFrame property, text boxes can be created.

from spire.presentation.common import *
from spire.presentation import *

# Create a presentation instance
ppt = Presentation()

# Add a rectangle shape as a text box
shape = ppt.Slides[0].Shapes.AppendShape(
    ShapeType.Rectangle, 
    RectangleF.FromLTRB(50, 100, 500, 250)
)

# Set text content
shape.TextFrame.Text = "This is a text box example."

# Save the document
ppt.SaveToFile("TextBox.pptx", FileFormat.Pptx2013)
ppt.Dispose()