DEV Community: Allen Yang

Insert and Control Audio in PowerPoint Using Python

Allen Yang — Fri, 15 May 2026 10:22:53 +0000

Audio elements significantly enhance audience engagement and information delivery in modern presentations. Whether it is background music, voice narration, or sound effects, appropriate use of audio can make PowerPoint presentations more vivid and professional. By automating audio insertion and control with Python, developers can batch-create presentations containing audio or add audio content to existing presentations.

This article demonstrates how to insert audio files into PowerPoint presentations, control audio playback behavior, and manage audio properties using Spire.Presentation for Python. These techniques are applicable to scenarios requiring dynamic generation of multimedia presentations, such as educational training materials, product demonstrations, and interactive reports.

Environment Setup

First, install the Spire.Presentation for Python library:

pip install Spire.Presentation

This library provides a complete PowerPoint document manipulation API that supports insertion and control of audio and video media.

Inserting Audio into PowerPoint Slides

Basic Audio Insertion

The basic workflow for adding audio to a PowerPoint slide includes loading or creating a presentation, specifying the audio position, inserting the audio file, and saving the document. The following code demonstrates how to insert an audio file into a slide:

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

# Create a PowerPoint document
presentation = Presentation()

# Load an existing document from disk
inputFile = "./Data/InsertAudio.pptx"
presentation.LoadFromFile(inputFile)

# Add a title text box
rec_title = RectangleF.FromLTRB(50, 240, 160+50, 50+240)
shape_title = presentation.Slides[0].Shapes.AppendShape(
    ShapeType.Rectangle, rec_title)
shape_title.ShapeStyle.LineColor.Color = Color.get_Transparent()
shape_title.Fill.FillType = FillFormatType.none

# Set title text formatting
para_title = TextParagraph()
para_title.Text = "Audio:"
para_title.Alignment = TextAlignmentType.Center
para_title.TextRanges[0].LatinFont = TextFont("Myriad Pro Light")
para_title.TextRanges[0].FontHeight = 32
para_title.TextRanges[0].IsBold = TriState.TTrue
para_title.TextRanges[0].Fill.FillType = FillFormatType.Solid
para_title.TextRanges[0].Fill.SolidColor.Color = Color.FromArgb(255, 68, 68, 68)
shape_title.TextFrame.Paragraphs.Append(para_title)

# Insert audio file at specified position
audioRect = RectangleF.FromLTRB(220, 240, 80+220, 80+240)
presentation.Slides[0].Shapes.AppendAudioMedia("./Data/Music.wav", audioRect)

# Save the document
outputFile = "InsertAudio.pptx"
presentation.SaveToFile(outputFile, FileFormat.Pptx2010)
presentation.Dispose()

Result preview:

In the above code, the AppendAudioMedia method is the key API, which accepts the audio file path and a rectangular region as parameters. The rectangular region defines the position and size of the audio icon on the slide. Common audio formats such as WAV and MP3 are supported.

Positioning and Layout Control

The positioning of audio icons is crucial for the visual effect of presentations. The RectangleF.FromLTRB method allows precise control over the left, top, right, and bottom boundary coordinates of audio icons. It is recommended to place audio icons in non-core content areas of slides to avoid obscuring important information.

Controlling Audio Playback Behavior

Setting Auto-Play Mode

By default, audio in PowerPoint requires manual clicking to play. By setting the playback mode, audio can be configured to play automatically when the slide is displayed:

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

# Load PowerPoint document
presentation = Presentation()
presentation.LoadFromFile("./Data/Template_Ppt_8.pptx")

# Iterate through all slides to find audio objects
for slide in presentation.Slides:
    for shape in slide.Shapes:
        if isinstance(shape, IAudio):
            # Set audio to auto-play mode
            (shape if isinstance(shape, IAudio) else None).PlayMode = VideoPlayMode.Auto

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

The VideoPlayMode.Auto enumeration value causes audio to start playing automatically when the slide enters. This is very useful for background music or automatic voice narration. Other available playback modes include manual play and sequential play.

Hiding Audio Icons During Presentation

In certain scenarios, it may be desirable to hide audio icons during slide shows to maintain a clean interface:

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

# Load PowerPoint document containing audio
presentation = Presentation()
presentation.LoadFromFile("./Data/audio.pptx")

# Get the first slide
slide = presentation.Slides[0]

# Hide audio icon during presentation
for shape in slide.Shapes:
    if isinstance(shape, IAudio):
        shape.HideAtShowing = True

# Save the document
presentation.SaveToFile("HideAudioDuringShow.pptx", FileFormat.Pptx2013)

By setting the HideAtShowing property to True, the audio icon remains visible in edit mode but automatically hides during slide show. This is practical in scenarios where audio is needed but player controls should not be displayed.

Extracting Audio from Slides

Sometimes it is necessary to extract audio files from existing presentations for separate processing or archiving:

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

# Load PowerPoint document
presentation = Presentation()
presentation.LoadFromFile("./Data/AudioPresentation.pptx")

# Iterate through slides to find and extract audio
slide_index = 0
for slide in presentation.Slides:
    for shape in slide.Shapes:
        if isinstance(shape, IAudio):
            # Extract audio data and save as file
            audio_data = shape.EmbeddedData
            output_path = f"Extracted_Audio_Slide{slide_index}.wav"
            with open(output_path, 'wb') as f:
                f.write(audio_data)
            print(f"Audio extracted to: {output_path}")
    slide_index += 1

presentation.Dispose()

The EmbeddedData property returns the binary data of the audio, which can be written to the file system. This method supports batch extraction of audio resources from multiple slides.

Advanced Techniques

Audio Position Optimization

When inserting audio, the overall layout of the slide should be considered. The following strategies can optimize audio positioning:

Place audio icons in corner or edge areas
Ensure audio icons do not overlap with text or other important elements
Adjust audio icon visibility based on slide background color

Multi-Audio Management

For presentations containing multiple audio files, all audio objects can be managed uniformly by iterating through the shape collection and checking types:

# Batch set playback mode for all audio
for slide in presentation.Slides:
    audio_count = 0
    for shape in slide.Shapes:
        if isinstance(shape, IAudio):
            shape.PlayMode = VideoPlayMode.Auto
            audio_count += 1
    print(f"Slide {slide.Index} contains {audio_count} audio objects")

Audio File Format Considerations

Spire.Presentation supports various audio formats including WAV, MP3, WMA, and others. When selecting audio files, consider the following:

File size: Larger audio files increase presentation volume
Compatibility: Ensure the target system supports the selected audio format
Audio quality: Choose appropriate bit rate and sampling rate based on usage scenario

Summary

This article introduced the complete workflow for inserting and controlling audio in PowerPoint presentations using Python. Through Spire.Presentation for Python, developers can easily implement audio insertion, playback mode control, icon hiding, and audio extraction functionalities. These techniques provide powerful automation capabilities for creating interactive multimedia presentations.

In practical applications, richer and more professional presentation content can be created by combining text, images, and other media elements. Audio control features are particularly suitable for producing educational courseware, product demonstrations, and corporate training materials. By appropriately utilizing these APIs, the professionalism and appeal of presentations can be significantly enhanced.

Create and Customize Pie and Doughnut Charts in PowerPoint with Python

Allen Yang — Fri, 15 May 2026 10:03:36 +0000

In data visualization scenarios, pie charts and donut charts are ideal choices for displaying proportional relationships and market share. By automating chart generation with Python, data can be efficiently transformed into intuitive visual presentations, suitable for sales reports, market analysis, and management presentations.

This article demonstrates how to create and customize pie charts and donut charts in PowerPoint presentations using Spire.Presentation for Python, covering core functionalities such as data binding, color customization, and label configuration.

Environment Setup

First, install the Spire.Presentation library:

pip install Spire.Presentation

This library provides a comprehensive PowerPoint manipulation API that supports creating various chart types with fine-grained customization options.

Creating Pie Charts

Pie charts are suitable for displaying the proportion of each part relative to the whole. The following example demonstrates how to create a pie chart showing quarterly sales figures.

Basic Implementation Steps

The core process for creating a pie chart includes: initializing the presentation document, adding a chart object, binding data sources, configuring chart properties, and finally saving the file.

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

# Create presentation document
presentation = Presentation()

# Add a pie chart to the first slide, setting position and size
rect = RectangleF.FromLTRB(40, 100, 590, 420)
chart = presentation.Slides[0].Shapes.AppendChartInit(ChartType.Pie, rect, False)

# Set chart title
chart.ChartTitle.TextProperties.Text = "Quarterly Sales"
chart.ChartTitle.TextProperties.IsCentered = True
chart.ChartTitle.Height = 30
chart.HasTitle = True

# Define data
quarters = ["Q1", "Q2", "Q3", "Q4"]
sales = [210, 320, 180, 500]

# Write data to chart data table
chart.ChartData[0, 0].Text = "Quarter"
chart.ChartData[0, 1].Text = "Sales"

for i in range(len(quarters)):
    chart.ChartData[i + 1, 0].Text = quarters[i]
    chart.ChartData[i + 1, 1].NumberValue = sales[i]

# Bind data series
chart.Series.SeriesLabel = chart.ChartData["B1", "B1"]
chart.Categories.CategoryLabels = chart.ChartData["A2", "A5"]
chart.Series[0].Values = chart.ChartData["B2", "B5"]

# Set different colors for each data point
for i in range(len(chart.Series[0].Values)):
    cdp = ChartDataPoint(chart.Series[0])
    cdp.Index = i
    chart.Series[0].DataPoints.Add(cdp)

chart.Series[0].DataPoints[0].Fill.FillType = FillFormatType.Solid
chart.Series[0].DataPoints[0].Fill.SolidColor.Color = Color.get_RosyBrown()
chart.Series[0].DataPoints[1].Fill.FillType = FillFormatType.Solid
chart.Series[0].DataPoints[1].Fill.SolidColor.Color = Color.get_LightBlue()
chart.Series[0].DataPoints[2].Fill.FillType = FillFormatType.Solid
chart.Series[0].DataPoints[2].Fill.SolidColor.Color = Color.get_LightPink()
chart.Series[0].DataPoints[3].Fill.FillType = FillFormatType.Solid
chart.Series[0].DataPoints[3].Fill.SolidColor.Color = Color.get_MediumPurple()

# Display value and percentage labels
chart.Series[0].DataLabels.LabelValueVisible = True
chart.Series[0].DataLabels.PercentValueVisible = True

# Save file
presentation.SaveToFile("PieChartExample.pptx", FileFormat.Pptx2013)
presentation.Dispose()

Result Preview:

Code Analysis

The AppendChartInit method is used to add a chart at a specified position, where the first parameter specifies the chart type and the second parameter defines the rectangular area.

Data is bound through the ChartData two-dimensional array, where [row, column] indices correspond to cell positions. SeriesLabel specifies the source of series names, CategoryLabels sets the category label range, and Values binds the actual numerical data.

Each data point is individually configured through ChartDataPoint objects, allowing precise control over colors and styles. The DataLabels property controls label display content, and displaying both values and percentages provides more complete information.

Creating Donut Charts

Donut charts are a variant of pie charts with a hollow center forming a ring structure. They offer a more modern visual appearance and are suitable for displaying comparative data such as market share.

Implementation Method

The creation process for donut charts is similar to pie charts, with the main differences being the chart type and additional ring size settings.

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

# Create presentation document
presentation = Presentation()
rect = RectangleF.FromLTRB(80, 100, 630, 420)

# Add donut chart
chart = presentation.Slides[0].Shapes.AppendChartInit(ChartType.Doughnut, rect, False)

# Set title
chart.ChartTitle.TextProperties.Text = "Market Share by Country"
chart.ChartTitle.TextProperties.IsCentered = True
chart.ChartTitle.Height = 30

# Define data
countries = ["Cuba", "Mexico", "France", "Germany"]
sales = [1800, 3000, 5100, 6200]

# Write data
chart.ChartData[0, 0].Text = "Country"
chart.ChartData[0, 1].Text = "Sales"

for i in range(len(countries)):
    chart.ChartData[i + 1, 0].Text = countries[i]
    chart.ChartData[i + 1, 1].NumberValue = sales[i]

# Bind data series
chart.Series.SeriesLabel = chart.ChartData["B1", "B1"]
chart.Categories.CategoryLabels = chart.ChartData["A2", "A5"]
chart.Series[0].Values = chart.ChartData["B2", "B5"]

# Add data points and set colors
for i in range(len(chart.Series[0].Values)):
    cdp = ChartDataPoint(chart.Series[0])
    cdp.Index = i
    chart.Series[0].DataPoints.Add(cdp)

chart.Series[0].DataPoints[0].Fill.FillType = FillFormatType.Solid
chart.Series[0].DataPoints[0].Fill.SolidColor.Color = Color.get_LightBlue()
chart.Series[0].DataPoints[1].Fill.FillType = FillFormatType.Solid
chart.Series[0].DataPoints[1].Fill.SolidColor.Color = Color.get_MediumPurple()
chart.Series[0].DataPoints[2].Fill.FillType = FillFormatType.Solid
chart.Series[0].DataPoints[2].Fill.SolidColor.Color = Color.get_DarkGray()
chart.Series[0].DataPoints[3].Fill.FillType = FillFormatType.Solid
chart.Series[0].DataPoints[3].Fill.SolidColor.Color = Color.get_DarkOrange()

# Display labels
chart.Series[0].DataLabels.LabelValueVisible = True
chart.Series[0].DataLabels.PercentValueVisible = True

# Set donut hole size (between 0-90)
chart.Series[0].DoughnutHoleSize = 60

# Save file
presentation.SaveToFile("DonutChartExample.pptx", FileFormat.Pptx2013)
presentation.Dispose()

Result Preview:

Key Features

The DoughnutHoleSize property controls the size of the hollow center area in the donut chart, with values ranging from 0 to 90. Larger values result in a larger center hole and thinner ring. The default value is typically between 50-60, achieving a balance between aesthetics and readability.

The data binding method for donut charts is identical to pie charts, allowing reuse of the same data processing logic.

Advanced Customization Techniques

Dynamic Color Assignment

In practical applications, colors may need to be dynamically assigned based on data volume. A predefined color list can be used with cyclic application:

# Predefined color list
colors = [
    Color.get_LightBlue(),
    Color.get_MediumPurple(),
    Color.get_LightPink(),
    Color.get_RosyBrown(),
    Color.get_DarkOrange(),
    Color.get_DarkGray()
]

# Cyclically apply colors
for i in range(len(chart.Series[0].Values)):
    cdp = ChartDataPoint(chart.Series[0])
    cdp.Index = i
    chart.Series[0].DataPoints.Add(cdp)
    chart.Series[0].DataPoints[i].Fill.FillType = FillFormatType.Solid
    chart.Series[0].DataPoints[i].Fill.SolidColor.Color = colors[i % len(colors)]

This approach is suitable for scenarios with uncertain numbers of data points, ensuring each data point has an appropriate color.

Label Position Adjustment

Data label positions can be further customized through the DataLabels property:

# Set label display position
chart.Series[0].DataLabels.Position = LabelPositionType.OutsideEnd

# Display only percentages
chart.Series[0].DataLabels.LabelValueVisible = False
chart.Series[0].DataLabels.PercentValueVisible = True

# Customize label format
chart.Series[0].DataLabels.NumberFormat = "0.0%"

The LabelPositionType enumeration provides multiple position options, such as OutsideEnd, InsideEnd, Center, etc. The most suitable position can be selected based on chart layout.

Chart Background Configuration

To enhance visual appeal, backgrounds can be added to slides:

# Set background image
image_file = "background.png"
bg_rect = RectangleF.FromLTRB(0, 0, presentation.SlideSize.Size.Width, 
                               presentation.SlideSize.Size.Height)
presentation.Slides[0].Shapes.AppendEmbedImageByPath(
    ShapeType.Rectangle, image_file, bg_rect)
presentation.Slides[0].Shapes[0].Line.FillFormat.SolidFillColor.Color = Color.get_FloralWhite()

Background images should coordinate with chart colors to avoid interfering with data readability.

Practical Recommendations

Choose Appropriate Chart Types: Pie charts are suitable for displaying proportional relationships among 5-7 categories; too many categories reduce readability. Donut charts offer a more modern visual appearance, and the center area can be used to place total numbers or explanatory text.

Color Coordination Principles: Use contrasting colors to distinguish different data points, avoiding overly similar tones. Consider accessibility for color-blind users and validate color schemes using online tools.

Label Clarity: Displaying both values and percentages provides complete information, but if space is limited, show only percentages and note the total in the legend.

Data Sorting: Arrange data in descending order, displaying from the largest share clockwise, which aligns with reading habits and facilitates quick identification of major contributors.

Summary

Through Spire.Presentation for Python, pie charts and donut charts can be flexibly created and customized in PowerPoint. Core APIs include AppendChartInit for adding charts, ChartData for data binding, DataPoints for color customization, and DataLabels for label control.

These techniques can be applied to various business scenarios such as sales analysis, market research, and budget allocation, helping transform complex data into intuitive visual presentations. Combined with dynamic data sources, automated report generation can be achieved, significantly improving work efficiency.

Creating and Customizing Line Charts in Excel Using Python

Allen Yang — Mon, 11 May 2026 08:43:56 +0000

Line charts are one of the most commonly used chart types for data visualization, particularly suitable for displaying trends in data over time. In data analysis, financial reporting, and market research, line charts can clearly present data fluctuation patterns and growth trends. This article introduces how to create line charts in Excel workbooks using Python and perform various customizations.

Environment Setup

First, install the Spire.XLS for Python library:

pip install Spire.XLS

This library provides a complete Excel file operation API, supporting creation, reading, and modification of Excel documents, including chart generation functionality.

Creating a Basic Line Chart

The basic process for creating a line chart includes: preparing data, adding a chart object, setting the data range, and configuring chart properties.

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

# Create workbook and worksheet
workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Line Chart"

# Prepare sample data
sheet.Range["A1"].Value = "Country"
sheet.Range["A2"].Value = "Cuba"
sheet.Range["A3"].Value = "Mexico"
sheet.Range["A4"].Value = "France"
sheet.Range["A5"].Value = "Germany"

sheet.Range["B1"].Value = "Jun"
sheet.Range["B2"].NumberValue = 3300
sheet.Range["B3"].NumberValue = 2300
sheet.Range["B4"].NumberValue = 4500
sheet.Range["B5"].NumberValue = 6700

sheet.Range["C1"].Value = "Jul"
sheet.Range["C2"].NumberValue = 7500
sheet.Range["C3"].NumberValue = 2900
sheet.Range["C4"].NumberValue = 2300
sheet.Range["C5"].NumberValue = 4200

sheet.Range["D1"].Value = "Aug"
sheet.Range["D2"].NumberValue = 7400
sheet.Range["D3"].NumberValue = 6900
sheet.Range["D4"].NumberValue = 7800
sheet.Range["D5"].NumberValue = 4200

sheet.Range["E1"].Value = "Sep"
sheet.Range["E2"].NumberValue = 8000
sheet.Range["E3"].NumberValue = 7200
sheet.Range["E4"].NumberValue = 8500
sheet.Range["E5"].NumberValue = 5600

# Set header row style
sheet.Range["A1:E1"].Style.Color = Color.get_DarkGray()
sheet.Range["A1:E1"].Style.Font.Color = Color.get_White()
sheet.Range["A1:E1"].Style.HorizontalAlignment = HorizontalAlignType.Center

# Add line chart
chart = sheet.Charts.Add()
chart.ChartType = ExcelChartType.Line

# Set chart data range
chart.DataRange = sheet.Range["A1:E5"]

# Set chart position
chart.LeftColumn = 1
chart.TopRow = 6
chart.RightColumn = 11
chart.BottomRow = 29

# Set chart title
chart.ChartTitle = "Sales Market by Country"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Configure axes
chart.PrimaryCategoryAxis.Title = "Month"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryValueAxis.Title = "Sales (in Dollars)"
chart.PrimaryValueAxis.HasMajorGridLines = False

# Display data labels
for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

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

Result document preview:

The code above first populates the worksheet with sales data for four countries over six months, then creates a chart object using the Charts.Add() method. The ChartType property is set to ExcelChartType.Line to specify a line chart. The data range is bound to the cell range A1:E5 through the DataRange property.

Adding Data Markers

In certain scenarios, markers need to be added to each data point on the line chart to more clearly identify specific value positions. This effect can be achieved by setting the MarkerStyle property.

# ...

# Change line chart to line chart with markers
chart.ChartType = ExcelChartType.LineMarkers

# ...

# Display data labels
for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

# Add circular markers to each data series
for cs1 in chart.Series:
    cs = ChartSerie(cs1)
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
    cs.DataFormat.MarkerStyle = ChartMarkerType.Circle

chart.Legend.Position = LegendPositionType.Top
workbook.SaveToFile("Line_Circle.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Result document preview:

By setting MarkerStyle to ChartMarkerType.Circle, each data point is displayed as a circular marker. In addition to circles, other marker styles such as squares, triangles, or diamonds can also be selected.

Creating 3D Line Charts

For presentation scenarios requiring stronger visual impact, 3D line charts can be used. The 3D effect enhances the立体感 and层次感 of the chart.

# ...

# Create 3D line chart
chart.ChartType = ExcelChartType.Line3D

# ...

# Set axis title rotation angle
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90
chart.PrimaryValueAxis.MinValue = 1000

for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

chart.PlotArea.Fill.Visible = False
workbook.SaveToFile("Line_3D.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Result document preview:

The creation method for 3D line charts is similar to 2D charts; simply set ChartType to ExcelChartType.Line3D. Additionally, the angle of axis titles can be rotated by adjusting the TextRotationAngle property to make the chart layout more reasonable.

Customizing Chart Styles

The appearance of line charts can be customized in various ways, including colors, fonts, gridlines, and legend positions.

# Customize chart style
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Set primary category axis
chart.PrimaryCategoryAxis.Title = "Month"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True

# Set primary value axis
chart.PrimaryValueAxis.Title = "Sales (in Dollars)"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.MinValue = 1000

# Hide plot area background
chart.PlotArea.Fill.Visible = False

# Set legend position
chart.Legend.Position = LegendPositionType.Top

Result document preview:

By accessing the PrimaryCategoryAxis and PrimaryValueAxis objects, the titles, fonts, and gridlines of the horizontal and vertical axes can be configured separately. The PlotArea.Fill.Visible property controls the visibility of the plot area background; setting it to False makes the chart cleaner.

Practical Tips

In practical applications, the following tips can enhance the usability and readability of line charts:

Data Label Display: Display specific values next to each data point by setting DataLabels.HasValue = True, making it easier for readers to quickly obtain information
Multi-Series Color Differentiation: Set IsVaryColor = True to assign different colors to different data series, improving distinguishability
Gridline Control: Decide whether to display gridlines based on data density to avoid cluttering the chart
Legend Position Adjustment: Place the legend at the top or bottom to leave more space for the data area

Summary

This article introduced the complete process of creating line charts in Excel using Python, including implementation methods for basic line charts, line charts with markers, and 3D line charts. By adjusting chart types, data markers, axis properties, and style settings, professional-grade data visualization charts that meet various requirements can be generated.

Line charts are suitable for scenarios such as displaying time series data, trend analysis, and comparative analysis. After mastering these techniques, they can be applied to practical work such as financial report generation, sales data analysis, and market trend research.

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()