DEV Community: Allen Yang

Set Text Wrapping, Indentation, and AutoFit in Excel Using Python

Allen Yang — Fri, 23 Oct 2026 10:24:45 +0000

Every exported report has one or two columns that refuse to stay in their place: order notes, error logs, product descriptions. By default the text runs sideways, either spilling over the neighbouring columns or forcing the column width to something absurd. Widen only the row and the text still will not wrap — it simply gets clipped. A handful of rows can be dragged by hand; a few hundred files cannot.

Excel offers several complementary ways to deal with long text: wrapping breaks the text inside the cell, shrink-to-fit compresses it to the available width, indentation shifts short text horizontally, and auto-fitting recalculates column widths and row heights from the content. This article demonstrates each of these with Spire.XLS for Python, then shows how to combine them.

Why Handle Cell Layout in a Script

Consistent across sheets: one wrap-and-fit rule applies to every worksheet, with no column dragging.
Predictable results: width, height, and indentation are parameters instead of hidden state left by a mouse gesture.
Composable: wrap then fit, or indent then resize — the order is written down and reproducible.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

Wrapping Text

Wrapping is controlled by WrapText on the cell style. Once enabled, text folds onto the next line when it reaches the column boundary, and Excel adjusts the row height to fit:

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

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

sheet.Range["C1"].Text = "Long descriptions wrap inside the cell when the column is narrow enough"
sheet.Range["C1"].Style.WrapText = True

sheet.Range["C2"].Text = "https://example.com/a/very/long/url/that/should/not/wrap"
sheet.Range["C2"].Style.WrapText = False

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

Range.Style.WrapText applies to the whole range, so a column can be configured in one call rather than cell by cell. Content that must not break — URLs, identifiers — is better left at False, because a wrapped URL is harder to read than a clipped one.

Controlling Break Points with Line Breaks

Wrapping chooses break points from the column width, but some content should break at a specific place: the lines of an address, or a note written as a short list. Insert a newline character \n in the text and enable wrapping as before:

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

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

cell = sheet.Range["C5"]
# Set the column width first, then write text with explicit breaks
sheet.SetColumnWidth(cell.Column, 70)
cell.Value = "Line one: order details\nLine two: shipping address\nLine three: remarks"
cell.IsWrapText = True

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

The single-cell property here is cell.IsWrapText, which behaves the same as Style.WrapText. The first argument of SetColumnWidth() is the column number (1-based) and the second is the width; setting the width before writing the text keeps the breaks aligned with the column, instead of leaving two or three characters dangling on each line.

Shrinking Text to Fit

When the content has to fit a fixed-width cell and the column must not grow, let the font shrink instead. ShrinkToFit lives on the style:

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

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

# Shrink the text so it fits the fixed width of the cell
cell = sheet.Range["B13:C13"]
style = cell.Style
style.ShrinkToFit = True

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

ShrinkToFit and WrapText solve the problem in opposite directions: the first keeps one line and reduces the font size, the second keeps the font size and adds lines. For a print template whose layout must not shift, shrinking is the safer choice.

Indenting Text Inside a Cell

Indentation shifts short text horizontally within the cell, which is useful for showing hierarchy between a category and its children:

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

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

cell = sheet.Range["B5"]
cell.Text = "Sub-item"
# Indent the text two levels inside the cell
cell.Style.IndentLevel = 2

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

IndentLevel takes the number of levels, where 0 means no indentation. It changes only the horizontal position of the text inside the cell, not the alignment, so it composes with left alignment or centring.

Auto-Fitting Columns and Rows to Content

After wrapping is configured, the column width often needs recalculating from the longest entry. Range objects provide AutoFitColumns() and AutoFitRows():

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

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

cell = sheet.Range["B8"]
cell.Text = "Welcome to Spire.XLS!"
cell.Style.Font.Size = 10
cell.Style.Font.IsBold = True

# Fit the column width and row height to the cell value
cell.AutoFitColumns()
cell.AutoFitRows()

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

Called on a range, the fitting uses the longest and tallest content in that range, so calling it on sheet.AllocatedRange recalculates the whole sheet.

The worksheet also offers a column-index-based form for cases where only the number is known:

# Auto-fit column 2; the third argument adds extra width
sheet.AutoFitColumn(2, 2, 5)

The first two arguments are the first and last column, and the third adds a margin on top of the calculated width.

Combining the Steps: Wrap First, Fit Afterwards

In practice these steps run together: write the long text, enable wrapping, then let the dimensions settle around the content.

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

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

notes = [
    "Order confirmed. Payment received on 2026-10-20.",
    "Address verified. Shipping scheduled for the next business day.",
    "Customer requested gift wrapping.",
]

# Write the long text and enable wrapping
for i, note in enumerate(notes, start=1):
    cell = sheet.Range[i + 1, 2]
    cell.Value = note
    cell.IsWrapText = True

# Fit after writing, when the content is final
sheet.Range[2, 2, 4, 2].AutoFitRows()
sheet.AutoFitColumn(2, 2, 3)

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

The order matters. Fitting before wrapping computes the row height from a single line of text, and the wrapped content ends up clipped. Wrapping first and fitting afterwards lets the row height account for the extra lines.

Practical Tips

Wrapping and indentation stack: indentation sets where the text starts, wrapping decides where it breaks, and the two do not conflict.
Shrink for fixed templates: when the column widths of a print template must not change, ShrinkToFit is safer than wrapping.
Keep URLs unwrapped: they become hard to read once broken across lines, so leave WrapText at False.
Fit last: call AutoFitColumns() and AutoFitRows() after content and styles are final, or the result reflects stale measurements.
Fit only the used area: auto-fitting the whole sheet repeatedly adds processing time, so restrict batch jobs to the data region.
Release resources: call workbook.Dispose() after each workbook.

Conclusion

This article covered several ways to handle long text in Excel cells with Python: wrapping through Style.WrapText and IsWrapText, controlling break points with explicit \n characters and a matching column width, shrinking text inside a fixed cell width with ShrinkToFit, indenting with IndentLevel, and recalculating dimensions with AutoFitColumns(), AutoFitRows(), and AutoFitColumn(). Applied in the order "content, then style, then fitting", these settings keep generated reports tidy without any manual step.

Create and Customize Numbered and Bulleted Lists in Word Using Python

Allen Yang — Fri, 23 Oct 2026 10:18:22 +0000

A runbook needs numbered steps. A requirements checklist reads better with bullets. A specification document usually wants a hierarchy such as "1. Overview / 1.1 Background / 1.1.1 Scope". In Word this is a click on the numbering button, but when the document is generated by a script, and the number of items changes from run to run, clicking is not an option.

A Word list is not just a text prefix. It is a structure made of a list style plus per-paragraph list formatting. Once those two layers are clear, Python can produce lists at any depth and starting from any number. This article shows how to create numbered lists, bulleted lists, multi-level numbering, and restarted numbering in Spire.Doc for Python.

Why Build Lists in a Script

Stable structure: numbering is driven by the style, so inserting or deleting items renumbers automatically.
Consistent levels: depth is set with ListLevelNumber, so indentation no longer drifts between items.
Repeatable: the same list style applies to any number of generated documents, with the item text coming from data.

Setting Up the Environment

Install the Spire.Doc library:

pip install Spire.Doc

Then import the required modules in your script:

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

The Two Layers of List Formatting

Spire.Doc separates lists into two parts:

The list style (ListStyle): defines the appearance of each level — number format, prefix, starting value, and bullet character. Create one with document.Styles.Add(ListType.Numbered, "name").
The paragraph's list format (ListFormat): attaches the style to a paragraph and says which level that paragraph belongs to, through paragraph.ListFormat.ApplyStyle("name") and ListLevelNumber.

The style is defined once; each paragraph then joins that numbering sequence with a single ApplyStyle() call.

Using the Built-in Numbered Style

For a plain consecutive list, ApplyNumberedStyle() does the job in one call:

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

document = Document()
section = document.AddSection()

items = ["Set up the environment", "Install the library", "Run the first script"]
first = True

for item in items:
    paragraph = section.AddParagraph()
    paragraph.AppendText(item)
    if first:
        paragraph.ListFormat.ApplyNumberedStyle()
        first = False
    else:
        paragraph.ListFormat.ContinueListNumbering()

document.SaveToFile("SimpleNumberedList.docx", FileFormat.Docx2013)
document.Close()

Two details matter here. The first paragraph calls ApplyNumberedStyle() to start the list; every later paragraph calls ContinueListNumbering() to say "keep counting from the previous item". Calling ApplyNumberedStyle() on every paragraph instead produces several separate lists, each restarting at 1.

Building a Custom Numbered Style

When the number format has to be controlled — "1." or "1.1." — create a dedicated list style. Each level is configured in ListRef.Levels:

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

document = Document()
section = document.AddSection()

# Create a numbered list style
numberList = document.Styles.Add(ListType.Numbered, "numberList")

levels = numberList.ListRef.Levels
# %1 through %9 are placeholders for the counters of each level
levels[0].NumberPrefix = "%1."
levels[0].PatternType = ListPatternType.Arabic
levels[1].NumberPrefix = "%1.%2."
levels[1].PatternType = ListPatternType.Arabic
levels[2].NumberPrefix = "%1.%2.%3."
levels[2].PatternType = ListPatternType.Arabic

# Add one paragraph per level
for text, level in [("Overview", 0), ("Background", 1), ("Scope", 2)]:
    paragraph = section.AddParagraph()
    paragraph.AppendText(text)
    paragraph.ListFormat.ApplyStyle(numberList.Name)
    paragraph.ListFormat.ListLevelNumber = level

document.SaveToFile("CustomNumberedList.docx", FileFormat.Docx2013)
document.Close()

In NumberPrefix, %1, %2, and %3 stand for the counters of levels 1, 2, and 3, so a second level written as %1.%2. and a third written as %1.%2.%3. produce "1.1." and "1.1.1.". PatternType = ListPatternType.Arabic selects Western digits; the enum offers other numeral styles as well.

ListLevelNumber is zero-based: 0 is the first level, 1 the second, and so on. It is easy to confuse with the index into Levels, but the two are consistent — both start at 0.

Bulleted Lists

Bulleted lists use the same machinery, with ListType.Bulleted in place of ListType.Numbered:

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

document = Document()
section = document.AddSection()

# Create a bulleted list style
bulletList = document.Styles.Add(ListType.Bulleted, "bulletList")

items = ["Reusable template", "Batch output", "Consistent formatting"]
for item in items:
    paragraph = section.AddParagraph()
    paragraph.AppendText(item)
    paragraph.ListFormat.ApplyStyle(bulletList.Name)

document.SaveToFile("BulletedList.docx", FileFormat.Docx2013)
document.Close()

To change the symbol, set BulletCharacter on the level. A Word bullet is really a character, so any character that maps to a glyph in the right font will do:

# Use a character from the Wingdings font as the bullet
bulletList = document.Styles.Add(ListType.Bulleted, "squareBullet")
levels = bulletList.ListRef.Levels
levels[0].BulletCharacter = "\u006e"
levels[0].CharacterFormat.FontName = "Wingdings"

In Wingdings, \u006e renders as a solid square; \u0075, \u00b2, or \u00d8 yield other symbols. The essential part is that CharacterFormat.FontName must match the symbol font the character came from, or the bullet shows up as a blank box or a question mark.

Starting a List From a Given Number

Sometimes a second block of items must continue a numbering scheme, or deliberately begin at a specific number. Rather than building two styles, set the starting value:

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

document = Document()
section = document.AddSection()

numberList = document.Styles.Add(ListType.Numbered, "Numbered1")
for text in ["List Item 1", "List Item 2"]:
    paragraph = section.AddParagraph()
    paragraph.AppendText(text)
    paragraph.ListFormat.ApplyStyle(numberList.Name)

# The second list starts at 10
numberList2 = document.Styles.Add(ListType.Numbered, "Numbered2")
numberList2.ListRef.Levels[0].StartAt = 10

for text in ["List Item 3", "List Item 4"]:
    paragraph = section.AddParagraph()
    paragraph.AppendText(text)
    paragraph.ListFormat.ApplyStyle(numberList2.Name)

document.SaveToFile("RestartList.docx", FileFormat.Docx2013)
document.Close()

StartAt applies to the level it is set on; setting it on the first level restarts the whole sequence from that value. If the two blocks should belong to one continuous sequence with a gap in between, ContinueListNumbering() is the better fit — StartAt is for a deliberate restart at a chosen number.

Practical Tips

Create the style once: Styles.Add() is called a single time; every paragraph then reuses it through ApplyStyle().
Levels are zero-based: ListLevelNumber and the Levels index agree, and an off-by-one shifts both the prefix and the indentation.
Do not mix ApplyNumberedStyle() and ApplyStyle(): the former brings its own built-in numbering while the latter references a custom style, and mixing them in one block breaks the sequence.
Match the symbol font: a custom BulletCharacter needs its FontName set, and that font has to be available where the document is opened.
Release resources: call document.Close() after saving, and in batch jobs create, save, and close one document at a time.

Conclusion

This article covered several ways to build lists in Word documents with Python: quick consecutive numbering with ApplyNumberedStyle(), multi-level number formats defined through Styles.Add(ListType.Numbered, ...) and Levels[i].NumberPrefix, custom bullets with ListType.Bulleted and BulletCharacter, and control over continuation and restart through ContinueListNumbering() and StartAt. Because numbering is driven by the style, the sequence adjusts itself whenever the number of items changes, which makes these operations a good fit for template-based document generation.

Create Groups and Outlines in Excel Using Python

Allen Yang — Thu, 22 Oct 2026 10:19:13 +0000

A project plan usually carries two different levels of information at once: the phases, and the tasks inside each phase along with their owners and durations. Flattened into one list, the reader has to work out the hierarchy while scanning; in a status meeting, it is far more useful to show the phases first and expand a phase only when its tasks matter. Excel's outline feature exists for exactly this: grouped rows get a plus/minus control in the margin, level buttons labeled 1, 2, 3 appear above the row headers, and one click collapses or expands a block.

Doing it by hand means selecting rows and clicking Data → Group for every level, which is easy to get wrong once the nesting deepens and has to be redone for every new file. Python lets the structure live in code so it can be applied to any report with the same shape. This article covers creating worksheet groups, controlling their collapsed state, and removing groups with Spire.XLS for Python.

Why Group Rows in a Script

Levels defined in code: which rows belong to which level is a parameter, not a mouse drag.
Consistent across files: one rule set applies to every plan or monthly report with the same layout.
Controlled initial state: the script decides which groups start collapsed, so the file opens in its final view.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

How Grouping and Outline Levels Work

Grouping is done through the worksheet's GroupByRows() and GroupByColumns() methods:

GroupByRows(startRow, endRow, isCollapsed)
GroupByColumns(startColumn, endColumn, isCollapsed)

The first two arguments are an inclusive, 1-based range of rows or columns, and the third sets whether the group starts collapsed. Each call places the selected rows or columns into a new outline level. Calling GroupByRows() again on rows that are already inside a group creates the next level within it, which is how nested outlines are built.

Grouping Rows

The example below puts rows 1 through 5 into a single group:

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

workbook = Workbook()
workbook.LoadFromFile("GroupRowsAndColumns.xls")
sheet = workbook.Worksheets[0]

# Group rows 1 to 5 and leave the group expanded
sheet.GroupByRows(1, 5, False)

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

Open the saved file and the outline area appears to the left of the row headers, with a minus sign beside rows 1 to 5 to collapse them. Passing False as the third argument keeps the group expanded after creation; passing True stores it collapsed, so the file opens with the group already folded away.

Building Nested Outline Levels

Real reports rarely stop at one level. The following example creates a project plan, puts every task row into an outer group, and then groups the tasks of each phase separately:

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

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

# Define a heading style
style = workbook.Styles.Add("style")
style.Font.Color = Color.get_CadetBlue()
style.Font.IsBold = True

# Show summary rows above the detail rows
sheet.PageSetup.IsSummaryRowBelow = False

# Write phases and tasks
sheet.Range["A1"].Value = "Project plan for project X"
sheet.Range["A1"].CellStyleName = style.Name

sheet.Range["A3"].Value = "Set up"
sheet.Range["A3"].CellStyleName = style.Name
sheet.Range["A4"].Value = "Task 1"
sheet.Range["A5"].Value = "Task 2"
sheet.Range["A4:A5"].BorderAround(LineStyleType.Thin)
sheet.Range["A4:A5"].BorderInside(LineStyleType.Thin)

sheet.Range["A7"].Value = "Launch"
sheet.Range["A7"].CellStyleName = style.Name
sheet.Range["A8"].Value = "Task 1"
sheet.Range["A9"].Value = "Task 2"
sheet.Range["A8:A9"].BorderAround(LineStyleType.Thin)
sheet.Range["A8:A9"].BorderInside(LineStyleType.Thin)

# Create the outer group first, then the inner ones
sheet.GroupByRows(2, 9, False)   # Level 1: rows 2 to 9
sheet.GroupByRows(4, 5, False)   # Level 2: tasks of "Set up"
sheet.GroupByRows(8, 9, False)   # Level 2: tasks of "Launch"

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

The order matters: build the outer group (rows 2–9) before the inner ones (rows 4–5 and 8–9). Reversing the order still leaves the inner groups inside the outer one, but working from the widest range inward keeps the hierarchy unambiguous. After saving, the level buttons 1 2 appear above the row headers: level 1 leaves the two phase names visible, level 2 expands down to the tasks.

sheet.PageSetup.IsSummaryRowBelow = False places the summary row above its detail rows. Excel assumes summaries sit below details by default, while a phase name normally precedes its tasks, so this property has to be flipped explicitly.

Grouping Columns

Column grouping mirrors row grouping and is useful for folding away detail columns in a wide sheet:

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

workbook = Workbook()
workbook.LoadFromFile("GroupRowsAndColumns.xls")
sheet = workbook.Worksheets[0]

# Group columns 1 to 3
sheet.GroupByColumns(1, 3, False)

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

The outline controls appear above the column headers, independent of any row groups, so a single sheet can carry both kinds of grouping.

Expanding and Collapsing Existing Groups

When the groups already exist in a file, their state can be changed through a cell range instead of recreating them:

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

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

# Expand rows 16 to 19, including any parent group
sheet.Range["A16:G19"].ExpandGroup(GroupByType.ByRows, ExpandCollapseFlags.ExpandParent)

# Collapse rows 10 to 12
sheet.Range["A10:G12"].CollapseGroup(GroupByType.ByRows)

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

CollapseGroup() and ExpandGroup() both operate on a cell range, and the second argument GroupByType.ByRows specifies row-based groups (use GroupByType.ByColumns for columns). ExpandGroup() also accepts a third argument, ExpandCollapseFlags.ExpandParent: when the target group sits inside a deeper level, expanding it alone leaves it hidden behind a collapsed parent, and this flag expands the parents as well.

Removing Groups

Groups that are no longer needed can be removed with UngroupByRows(), which takes a row range:

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

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

# Remove the group on rows 10 to 12
sheet.UngroupByRows(10, 12)
# Remove the group on rows 16 to 19
sheet.UngroupByRows(16, 19)

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

Ungrouping removes only the outline structure; cell values, styles, and formulas are unaffected.

Practical Tips

Work from the outside in: call nested grouping from the widest row range down to the narrowest so the hierarchy stays readable.
Row numbers start at 1: the range passed to GroupByRows() uses Excel row numbers and includes both endpoints, unlike Python's zero-based indexing.
The collapsed state is saved: the third argument decides the initial view of the file, so pass True when readers should see the folded structure first.
Set the summary position explicitly: when phase names sit above their tasks, set PageSetup.IsSummaryRowBelow to False.
Release resources: call workbook.Dispose() after each workbook, and in batch jobs load, process, and save one file at a time.

Conclusion

This article covered creating row and column groups and outline levels in Excel with Python: grouping and nesting with GroupByRows() and GroupByColumns(), controlling the state of existing groups with CollapseGroup() and ExpandGroup() (optionally with ExpandCollapseFlags.ExpandParent), positioning summary rows through PageSetup.IsSummaryRowBelow, and removing groups with UngroupByRows(). Because grouping changes only the view and never the underlying data, the same code can turn a flat detail sheet into a collapsible structure without touching its contents.

How to Find and Highlight Duplicate and Unique Values in Excel with Python

Allen Yang — Fri, 16 Oct 2026 10:18:29 +0000

When registration forms from several channels are merged, a repeated email address usually means someone submitted twice. When an inventory list is reconciled, a duplicated product code means the ledger holds two records for the same item. And when hunting for data-entry mistakes, the interesting cells are often the unique values: an entry that appears exactly once is frequently the result of a typo or an omission. In all three cases, scanning thousands of rows by eye is not realistic.

Excel's built-in "Conditional Formatting → Duplicate Values" feature handles this, but it is an interactive workflow: every new file means re-selecting the range and redefining the color rule. Python turns that judgement into a script that can be applied to any number of worksheets. This article shows how to find and highlight duplicate and unique values in Spire.XLS for Python.

Why Check Duplicates in a Script

Batch execution: dozens of worksheets or files can be processed with the same rule set, without clicking through each dialog.
Reusable rules: the color, range, and comparison type all live in the code, so switching datasets means changing a filename.
Traceable results: the script writes the marked output to a new file and leaves the source data untouched for comparison.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

Two Dedicated Conditional Format Methods

The conditional formatting entry point is the worksheet's ConditionalFormats collection. A general rule is built by calling Add() to create the rule, AddRange() to set the area it applies to, and AddCondition() to attach a condition whose FormatType determines the comparison. Duplicate and unique values have two more explicit methods that skip the FormatType assignment altogether:

Method	What it highlights
`AddDuplicateValuesCondition()`	Values that occur two or more times in the range
`AddUniqueValuesCondition()`	Values that occur exactly once in the range

Both evaluate within the area passed to AddRange(). In other words, whether a value counts as a duplicate is decided by the selected range alone, not by anything outside it. Once the condition exists, FillPattern, BackColor, and FontColor control how the matching cells look.

Highlighting Duplicate Values

The example below flags repeated student IDs in an exam sheet with a solid fill:

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

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

# Create a conditional formatting rule and set its range
conds = sheet.ConditionalFormats.Add()
conds.AddRange(sheet.Range["C2:C10"])

# Attach the duplicate-values condition
cf = conds.AddDuplicateValuesCondition()
cf.FillPattern = ExcelPatternType.Solid
cf.BackColor = Color.get_IndianRed()

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

Output:

AddDuplicateValuesCondition() takes no arguments — the method name is the condition. FillPattern = ExcelPatternType.Solid selects a solid fill, and BackColor supplies the actual color. Setting a color without setting the fill pattern is the most common reason a conditional format appears to do nothing in Excel, so both properties belong together. To recolor the text of the duplicates as well, add cf.FontColor = Color.get_White().

Marking Duplicates and Unique Values Together

The unique-value rule is symmetric: swap in AddUniqueValuesCondition(). Applying both rules to the same range colors every non-empty cell in that range — one color for values seen two or more times, another for values seen once — which makes the distribution of a column easy to read at a glance.

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

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

# Rule 1: duplicates in Indian red
xcfs = sheet.ConditionalFormats.Add()
xcfs.AddRange(sheet.Range["C2:C10"])
dup = xcfs.AddDuplicateValuesCondition()
dup.FillPattern = ExcelPatternType.Solid
dup.BackColor = Color.get_IndianRed()

# Rule 2: unique values in yellow
xcfs1 = sheet.ConditionalFormats.Add()
xcfs1.AddRange(sheet.Range["C2:C10"])
uniq = xcfs1.AddUniqueValuesCondition()
uniq.FillPattern = ExcelPatternType.Solid
uniq.BackColor = Color.get_Yellow()

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

The important detail is that each rule needs its own ConditionalFormats.Add() call before AddRange(). Attaching the second condition to the first rule's collection compiles without complaint but produces the wrong range and color — a mistake that also appears in sample code in the wild, so it is worth checking that each AddRange() is called on the object that owns the condition.

Applying the Rules to Several Worksheets

A ledger usually holds one sheet per month with identical structure, so a loop is enough to reuse the rules:

for i in range(workbook.Worksheets.Count):
    sheet = workbook.Worksheets.get_Item(i)

    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["C2:C10"])
    cf = xcfs.AddDuplicateValuesCondition()
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_IndianRed()

Worksheets.Count returns the number of sheets and get_Item(i) fetches each one by index. When the sheets differ in width, replacing the fixed C2:C10 range with a dynamic one such as sheet.AllocatedRange is safer.

Practical Tips

Do not skip FillPattern: a color without a fill pattern may render as nothing in Excel.
The range decides the verdict: with a range of A2:B100, rows that repeat across columns also count as duplicates; restrict the range to a single column to deduplicate on that column only.
Watch rule count: larger ranges and more rules mean more work for Excel when the file opens, so cover only the area that actually holds data.
Release resources: call workbook.Dispose() after each workbook, and in batch jobs load, process, and save one file at a time.

Conclusion

This article covered how to find and highlight duplicate and unique values in Excel with Python: creating a rule with ConditionalFormats.Add(), limiting its scope with AddRange(), marking the two categories through AddDuplicateValuesCondition() and AddUniqueValuesCondition(), and controlling the appearance with FillPattern and BackColor. Because the comparison is scoped to the selected range, the same code works as a pre-cleaning duplicate check and, wrapped in a worksheet loop, covers an entire workbook.

How to Find and Replace Data in Excel with Python and Highlight Matches

Allen Yang — Fri, 16 Oct 2026 10:09:55 +0000

Reports exported from several systems rarely use the same wording. One file says "North America", another says "America"; a department was renamed last quarter; a column header oscillates between "Total" and "Sum". Fixing this by hand has a second, quieter cost: after the edits nobody knows which cells were touched, so verification becomes a line-by-line comparison. A more practical approach is to let a script perform the replacement and color every changed cell at the same time, so the affected area is visible at a glance.

In Python this can be done with Spire.XLS for Python. Beyond plain string replacement, the library supports regular expression matching, numeric and formula lookup, and replacement that applies a new style in the same call, which covers most report-cleaning scenarios.

Why Do Find and Replace in a Script

Auditable: the result is written to a new file while the original stays untouched, so you can always compare before and after.
Annotated: replacing and highlighting happen in the same loop, so there is no separate pass to work out what changed.
Repeatable: the same rules apply to dozens of worksheets or files by adding one more loop.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

Finding and Replacing Text While Highlighting It

The worksheet-level FindAllString() returns a collection of every matching cell, and iterating that collection is enough to change the text and style one cell at a time. The example below replaces every "Total" with "Sum" and fills the affected cells in yellow:

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

workbook = Workbook()
workbook.LoadFromFile("Report.xlsx")
worksheet = workbook.Worksheets[0]

# Find all matching cells
ranges = worksheet.FindAllString("Total", True, True)

for range in ranges:
    # Assigning the text performs the replacement
    range.Text = "Sum"
    # Fill the cell yellow to mark the change
    range.Style.Color = Color.get_Yellow()

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

The three arguments of FindAllString() are the text to look for, whether the search is case-sensitive, and whether it must match a whole word. Passing True, True here means only a standalone "Total" with exactly that casing is replaced, so total and Subtotal are left alone. range.Text is both a read and a write property — assigning to it performs the replacement — while range.Style.Color controls the cell fill and Color.get_Yellow() is a predefined color from the enum.

Marking Matches Without Changing the Content

When reviewing data you often want to flag suspicious cells rather than modify them. In that case, skip the assignment inside the loop:

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

workbook = Workbook()
workbook.LoadFromFile("Data.xlsx")
worksheet = workbook.Worksheets[0]

ranges = worksheet.FindAllString("Area", False, False)

for range in ranges:
    # Keep the original text, mark it with color only
    range.Style.Color = Color.get_Yellow()

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

Both flags are False here, so the search ignores case and does not require a whole-word match. "Area", "area", and "Area Code" are all highlighted.

Matching Complex Patterns with Regular Expressions

Regular expressions help when the data has no fixed literal form. FindAllString() has an overload with a fourth parameter; set it to True to enable regex matching:

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

workbook = Workbook()
workbook.LoadFromFile("FindTextByRegex.xlsx")
worksheet = workbook.Worksheets[0]

# Match every text that starts with North
ranges = worksheet.FindAllString(".*North.", False, False, True)

for range in ranges:
    range.Style.Color = Color.get_Yellow()

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

The pattern follows .NET regular expression syntax, so \d{4} locates four-digit years and ^A.* locates any text beginning with A. Combining a regex with highlighting is a quick way to circle the cells that break a naming convention in a sheet that mixes several formats.

Finding Numbers and Formulas

Non-text data has its own lookup methods:

# Find the number 100; the second argument means "match the exact value"
numberRanges = sheet.FindAllNumber(100, True)
for range in numberRanges:
    print(range.RangeAddress)

# Find cells that use a specific formula
formulaRanges = sheet.FindAll("=SUM(A11,A12)", FindType.Formula, ExcelFindOptions.none)
for range in formulaRanges:
    print(range.RangeAddress)

When the second argument of FindAllNumber() is True, only cells equal to that number match; with False, cells containing the number also match. FindAll() takes a FindType value — besides Formula it supports Text, Number, and DateTime — while ExcelFindOptions.none means no extra match options are applied. Unlike the earlier examples, these results are usually used for locating rather than editing: read range.RangeAddress inside the loop to get the cell reference, then act on it. A common follow-up is deleting the whole row:

cr = sheet.FindString("Address", False, False)
sheet.DeleteRow(cr.Row)

FindString() returns only the first match, which suits "handle it once" cases. Read the Row property from the result and pass it to DeleteRow() to remove the row that carries the keyword.

Applying a New Style During Replacement

When a replacement also has to update appearance, ReplaceAll() performs the content and style swap in a single call. It takes four arguments: the old text, the old style, the new text, and the new style.

newStyle = workbook.Styles.Add("newStyle")
newStyle.Font.FontName = "Arial Black"
newStyle.Font.Size = 14

cellRange = sheet.Range["D9"]
oldStyle = cellRange.Style
sheet.ReplaceAll("North America", oldStyle, "America", newStyle)

This replaces "North America" with "America" only in cells whose style matches cell D9, and applies newStyle (Arial Black, size 14) at the same time. Styles created with workbook.Styles.Add() can be reused, so for consistently formatted reports you can take the style from one sample cell and let ReplaceAll() filter by it, which avoids rewriting identical text elsewhere in the sheet.

Practical Tips

Choose the match strength deliberately: start with False, False to see the full set of matches, then tighten to a whole-word match once the data is confirmed clean.
Log the addresses: collect range.RangeAddress into a list or a log file during the loop to produce a change list after the replacement finishes.
Keep a backup: even though the script writes a new file, retain the original so the diff between the two versions stays available.
Release resources: call workbook.Dispose() after each workbook, and in batch jobs load, process, and release one file at a time.
Work across worksheets: FindAllString() operates on one worksheet, so for multi-sheet workbooks loop with Worksheets.Count and get_Item(i).

Conclusion

This article covered several ways to find and replace data in Excel with Python: replacing text through FindAllString() and marking the results with Style.Color, matching complex patterns through the regex overload, locating numbers and formulas with FindAllNumber() and FindAll(), and applying a new style during replacement with ReplaceAll(). Combined, these operations form an auditable report-cleaning routine in which replacement and annotation happen in one pass.

Converting PDF Documents to PowerPoint Presentations Using Python

Allen Yang — Thu, 15 Oct 2026 10:01:59 +0000

When you need to reuse an existing PDF report, product manual, or contract as an editable slide deck, moving every page into PowerPoint by hand is slow and tends to lose formatting. For a single file that is merely tedious; for a folder of PDFs it is not practical at all. Python can automate the task: load the PDF and save it as PPTX, where each PDF page becomes a slide, with text, images, and vector graphics placed as close to the original layout as possible.

This article uses Spire.PDF for Python to show how a few lines of code convert a PDF into a PowerPoint presentation, and it covers password-protected files, batch conversion, and converting to other formats through the same API.

Why Convert PDF to PowerPoint with Python

Scripted conversion has several practical benefits:

Batch processing: a single script can process an entire folder of PDFs, with output names and locations fully controlled by code.
Layout retention: text, images, and vector graphics are written to the slide in their original positions, which reduces manual re-layout.
Consistent pipeline: conversion, password checks, and file naming can all live in one automated workflow.

Environment Setup

Install the Spire.PDF library:

pip install Spire.PDF

Import the modules in your script:

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

Converting a PDF to PowerPoint

The conversion takes three steps: create a PdfDocument, load the PDF, and save it with SaveToFile() using FileFormat.PPTX.

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

pdf = PdfDocument()
pdf.LoadFromFile("Input.pdf")
pdf.SaveToFile("Output.pptx", FileFormat.PPTX)
pdf.Close()

PdfDocument represents a PDF document, and LoadFromFile() reads it from disk. The second argument of SaveToFile() is the target format; passing FileFormat.PPTX writes the content into slides page by page, so each PDF page becomes one slide. Call Close() when you are done to release the resources held by the document.

Converting a Password-Protected PDF

If the PDF requires an open password, pass it as an extra argument to LoadFromFile(); the remaining steps stay the same:

pdf = PdfDocument()
pdf.LoadFromFile("Encrypted.pdf", "password")
pdf.SaveToFile("Output.pptx", FileFormat.PPTX)
pdf.Close()

When you are not sure whether a file is encrypted, check it first with the static method PdfDocument.IsPasswordProtected(), then decide whether to supply a password:

from spire.pdf import *

pdf = PdfDocument()
if PdfDocument.IsPasswordProtected("Input.pdf"):
    pdf.LoadFromFile("Input.pdf", "password")
else:
    pdf.LoadFromFile("Input.pdf")

pdf.SaveToFile("Output.pptx", FileFormat.PPTX)
pdf.Close()

IsPasswordProtected() takes a file path and returns a boolean, so a batch job can separate encrypted files from plain ones up front instead of failing partway through.

Reusing the Same API for Other Formats

The format argument of SaveToFile() is generic: replace FileFormat.PPTX with another value to convert the same PDF into a different target format.

pdf.SaveToFile("Output.docx", FileFormat.DOCX)   # to Word
pdf.SaveToFile("Output.xlsx", FileFormat.XLSX)   # to Excel
pdf.SaveToFile("Output.html", FileFormat.HTML)   # to HTML
pdf.SaveToFile("Output.svg", FileFormat.SVG)     # to SVG

If you only need the PDF as static material for a deck, you can also render each page as an image and insert it into a presentation. This path does not rebuild the layout, so the image looks closest to the original page:

for i in range(pdf.Pages.Count):
    with pdf.SaveAsImage(i) as image:
        image.Save("slide-{0}.png".format(i))

pdf.Pages.Count returns the number of pages, and SaveAsImage(i) renders page i as an image object; the with statement manages the resource and Save() writes it to disk.

Practical Tips

Check before converting: for PDFs of unknown origin, call IsPasswordProtected() first so an encrypted file does not break a batch task.
Loop for batches: put loading, conversion, and naming inside a loop over a directory to process many PDFs at once, and call Close() after each file.
Pick the right target format: use PPTX when you need editable slides; render to images when only a fixed appearance matters.
Release resources: a PdfDocument holds a file handle once opened, so call Close() or Dispose() after processing, one file at a time in batch runs.

Conclusion

This article showed how to convert a PDF into a PowerPoint presentation with Python: load the file with PdfDocument.LoadFromFile(), output slides with SaveToFile(path, FileFormat.PPTX), and handle encrypted PDFs through the password argument. The same API also converts a PDF to Word, Excel, HTML, and SVG, or renders pages as images when that is enough. Combined with file iteration, these steps fit directly into automated workflows for document archiving and report preparation.

How to Sort Data in Excel Using Python

Allen Yang — Sat, 10 Oct 2026 10:11:19 +0000

Whenever a sales report, inventory list, or signup sheet lands on your desk, sorting is usually the first thing you do: dates from oldest to newest, revenue from highest to lowest, or grouped by department and then ranked within each group. With large datasets, clicking through Excel's sort dialog is easy to get wrong on range selection, and doing it across dozens of files is not realistic at all. With Python you can define one-column or multi-column sort rules for any worksheet, and even sort by cell background color, font color, or conditional formatting icons. This article demonstrates these common sorting scenarios using Spire.XLS for Python.

Why Sort Excel Data with Python

Sorting in a script has a few concrete advantages:

Batch processing: the same sort rules apply to many files or worksheets once written.
Rich sort dimensions: beyond values, rows can be ordered by background color, font color, and icons.
Composable rules: multi-level sorts apply in order, giving precise control over "sort by this first, then that".

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

Sorting by One or More Columns

Sorting goes through workbook.DataSorter: add sort columns to the SortColumns collection, then call Sort() on a range. The example below sorts a five-column range by column 3 ascending, then column 4 ascending:

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

workbook = Workbook()
workbook.LoadFromFile("Data.xlsx")
worksheet = workbook.Worksheets[0]

# Add sort columns: column 3 ascending, column 4 ascending (0-based index)
workbook.DataSorter.SortColumns.Add(2, OrderBy.Ascending)
workbook.DataSorter.SortColumns.Add(3, OrderBy.Ascending)

# Apply sorting to the specified range
workbook.DataSorter.Sort(worksheet["A1:E19"])

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

The first argument of SortColumns.Add() is the column index (0-based, so 2 means column C), and the second is an OrderBy value — Ascending or Descending. Sort() accepts a cell range and sorts only within it; you can pass a string range like worksheet["A1:E19"] or worksheet.AllocatedRange for the entire used area. With multiple columns, the earlier ones are the primary keys and later ones break ties.

Multi-Level Sorting: Values, Colors, and Icons Together

Real-world spreadsheets often carry visual markers — background colors marking priority, font colors distinguishing status, conditional formatting icons showing trends. The SortComparsionType enum extends sorting beyond values. Here is a four-level sort: values, background color, font color, and icon:

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

workbook = Workbook()
workbook.LoadFromFile("Data.xlsx")
worksheet = workbook.Worksheets[0]

# Clear previous rules to avoid accumulation
workbook.DataSorter.SortColumns.Clear()

# Level 1: column A by values, ascending
workbook.DataSorter.SortColumns.Add(0, SortComparsionType.Values, OrderBy.Ascending)
# Level 2: column C by background color, dark colors to the bottom
workbook.DataSorter.SortColumns.Add(2, SortComparsionType.BackgroundColor, OrderBy.Bottom)
# Level 3: column D by font color, dark colors to the bottom
workbook.DataSorter.SortColumns.Add(3, SortComparsionType.FontColor, OrderBy.Bottom)
# Level 4: column F by icon, high-value icons to the top
workbook.DataSorter.SortColumns.Add(5, SortComparsionType.Icon, OrderBy.Top)

# Apply sorting to the entire used range
workbook.DataSorter.Sort(worksheet.AllocatedRange)

workbook.SaveToFile("MultiSorted.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

The overload that takes a SortComparsionType handles visual sorting: Values sorts by cell value, BackgroundColor by fill, FontColor by font, and Icon by conditional formatting icon. For these types, OrderBy uses Top/Bottom to decide direction — Top moves the attribute toward the top of the range, Bottom toward the bottom. Levels apply in order; later keys only matter when earlier keys tie.

Applying Sorting to Multiple Worksheets

Once the rules are defined, applying them across worksheets is just another loop. Clear SortColumns before defining a new rule set, or rules from the previous sheet will accumulate:

for i in range(0, workbook.Worksheets.Count):
    worksheet = workbook.Worksheets.get_Item(i)
    workbook.DataSorter.SortColumns.Clear()
    workbook.DataSorter.SortColumns.Add(0, SortComparsionType.Values, OrderBy.Ascending)
    workbook.DataSorter.Sort(worksheet.AllocatedRange)

Worksheets.Count gives the sheet total; get_Item(i) fetches each sheet, after which the rules are cleared, redefined, and applied — so every worksheet in one file gets processed under its own rules.

Practical Tips

0-based column index: the first argument of SortColumns.Add() is the worksheet column index — column A is 0, not 1.
Clear before redefining: DataSorter is workbook-level; call SortColumns.Clear() before setting up a new rule set, otherwise leftover rules stack up and produce confusing results.
Two sets of OrderBy values: value sorts use Ascending/Descending; color and icon sorts use Top/Bottom. Do not mix them.
Range selection: pass a string range like worksheet["A1:E19"] when you know the data bounds; use AllocatedRange to cover the whole used area when you do not.
Resource cleanup: call workbook.Dispose() after each file, and in batch scenarios process and release files one at a time.

Conclusion

This article covered the common ways to sort data in Excel using Python: defining single- and multi-column rules through DataSorter.SortColumns and applying them with Sort(), performing multi-level sorts by background color, font color, and conditional formatting icons via SortComparsionType, and applying sort rules across every worksheet in a workbook. Combined with file iteration, these operations fit directly into report cleanup and data preparation pipelines.

How to Merge PowerPoint Files Using Python

Allen Yang — Sat, 10 Oct 2026 10:04:34 +0000

When several people prepare slides for the same report, each of them submits a separate deck that someone eventually has to stitch into one presentation. The same happens when building training material: you pick a few slides from different files and assemble them into a new deck. Copy-pasting slides by hand is error-prone once the page count grows — slides get missed or end up out of order. With Python you can merge an entire presentation into another, assemble selected slides across files, or duplicate slides within a single deck. This article demonstrates these operations using Spire.Presentation for Python.

Why Merge Presentations with Python

Doing the job in a script has a few concrete advantages:

Batch processing: dozens of submitted decks can be merged in order in one run.
Flexible assembly: merge whole files or pick individual slides across multiple sources.
Easy integration: merging is one step in a pipeline that may also generate slides from templates or export to PDF.

Setting Up the Environment

Install the Spire.Presentation library:

pip install Spire.Presentation

Then import the required modules in your script:

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

Merging an Entire Presentation into Another

The most common case is appending every slide of a source deck to the end of a destination deck. Load both files, loop over the source slides, append them one by one, and save the destination:

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

# Load the destination document
destPPT = Presentation()
destPPT.LoadFromFile("Dest.pptx")

# Load the source document
sourcePPT = Presentation()
sourcePPT.LoadFromFile("Source.pptx")

# Append every slide of the source document to the end of the destination
for slide in sourcePPT.Slides:
    destPPT.Slides.AppendBySlide(slide)

# Save the destination document
destPPT.SaveToFile("Merged.pptx", FileFormat.Pptx2013)
destPPT.Dispose()
sourcePPT.Dispose()

Slides.AppendBySlide() is the core method here: it copies the given slide — content and styling included — to the end of the target slide collection, preserving the traversal order. For multiple source documents, load each one and repeat the same loop.

Merging Selected Slides from Multiple Presentations

Often you do not want a full merge — just a few slides from each file assembled into a new deck. One detail matters here: a freshly created Presentation object already contains one blank slide, so remove it first, or the final deck starts with an empty page:

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

# Create the target deck and remove its default blank slide
ppt = Presentation()
ppt.Slides.RemoveAt(0)

# Load two source documents
ppt1 = Presentation()
ppt1.LoadFromFile("TemplateA.pptx")
ppt2 = Presentation()
ppt2.LoadFromFile("TemplateB.pptx")

# Append all slides from the first document
for i, unusedItem in enumerate(ppt1.Slides):
    ppt.Slides.AppendBySlide(ppt1.Slides[i])

# Append only the second slide of the second document (0-based index)
ppt.Slides.AppendBySlide(ppt2.Slides[1])

# Save the result
ppt.SaveToFile("Merged.pptx", FileFormat.Pptx2013)
ppt.Dispose()
ppt1.Dispose()
ppt2.Dispose()

After Slides.RemoveAt(0) clears the default blank page, appended slides start from page 1. Because AppendBySlide() accepts a slide object, the same call handles both bulk appends and single-slide picks by index, letting any number of sources be combined into any page order.

Duplicating a Slide Within a Presentation

A related operation is copying one slide to the end of the same deck — useful for report pages that share the same layout. Since the slide being appended belongs to the document itself, the code is identical to cross-file merging:

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

presentation = Presentation()
presentation.LoadFromFile("Source.pptx")

# Take the first slide and append a copy to the end
slide = presentation.Slides[0]
presentation.Slides.AppendBySlide(slide)

presentation.SaveToFile("CloneSlide.pptx", FileFormat.Pptx2013)
presentation.Dispose()

Slides[0] fetches the first slide by index, and AppendBySlide() appends it to the end. Combined with a loop and index arithmetic, any slide can be duplicated to extend a deck with same-layout pages quickly.

Practical Tips

Blank slide trap: a new Presentation ships with one blank slide — always call RemoveAt(0) before assembling a new deck, or the output starts with an empty page.
Layouts travel with slides: AppendBySlide() copies the slide itself, so the layout it uses comes along; slides keep their styling even when the two decks use different masters.
Save format: the examples use FileFormat.Pptx2013 for good compatibility with modern PowerPoint; swap in another FileFormat value if needed.
Resource cleanup: call Dispose() on every loaded deck, and in batch scenarios load and release documents one at a time to keep memory usage flat.

Conclusion

This article covered the common ways to merge PowerPoint presentations in Python: appending an entire deck with AppendBySlide(), assembling selected slides across files after removing the new deck's default blank slide, and duplicating slides inside one presentation to extend it. Combined with simple file iteration, these operations handle team slide consolidation and course-material assembly without any manual copy-pasting.

How to Split PDF Documents with Python

Allen Yang — Wed, 30 Sep 2026 06:46:54 +0000

Large PDFs are everywhere in day-to-day work — consolidated quarterly reports, scanned contracts, batches of exported invoices. The recipient usually needs only a few pages: individual pages split out as separate files, or a specific page range handed to a different team. Doing this by hand in a PDF editor is slow and impossible to scale. With Python you can split a document page by page, extract selected pages, or even divide one oversized page into several standard pages — all of which slot naturally into automated workflows. This article demonstrates these three common splitting scenarios using Spire.PDF for Python.

Why Split PDFs with Python

Handling the job in a script has a few concrete advantages:

Batch processing: one script can process hundreds of files without manual clicks.
Flexible granularity: split a whole document page by page, extract only chosen pages, or subdivide a single long page.
Easy integration: splitting is just one step in a pipeline that may also download, archive, or email the results.

Setting Up the Environment

Install the Spire.PDF library:

pip install Spire.PDF

Then import the required modules in your script:

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

Splitting a PDF into Single-Page Documents

The most direct approach is turning every page into its own PDF. PdfDocument.Split() does this with a single filename pattern — no per-page handling required:

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

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

# Split by filename pattern; each page becomes an individual PDF
doc.Split("SplitDocument-{0}.pdf")

doc.Close()

Split() takes a filename template containing the {0} placeholder, which is replaced with the page index starting from 0. A 10-page document produces ten single-page files, from SplitDocument-0.pdf through SplitDocument-9.pdf. Call Close() afterwards to release the document's resources.

Extracting Specific Pages into a New PDF

More often you do not need every page — just a selection combined into a new document. The approach: create a new PdfDocument, add a same-sized page for each source page you want, then draw the source page's content into it:

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

# Load the source document
oldPdf = PdfDocument()
oldPdf.LoadFromFile("Sample.pdf")

# Create the target document
newPdf = PdfDocument()
page = None

# Extract pages at index 1 and 2 (0-based)
for i in range(1, 3):
    # Add a page with the same size as the source page
    page = newPdf.Pages.Add(oldPdf.Pages[i].Size, PdfMargins(0.0))
    # Draw the source page content onto the new page
    oldPdf.Pages[i].CreateTemplate().Draw(page, PointF(0.0, 0.0))

newPdf.SaveToFile("ExtractPages.pdf")
newPdf.Close()
oldPdf.Close()

The first argument to Pages.Add() receives the source page's Size, so the new page matches the original dimensions, and PdfMargins(0.0) zeroes out the margins to keep the content aligned. CreateTemplate() produces a drawing template of the source page, and Draw() renders it onto the new page with layout fully preserved. The loop range range(1, 3) extracts pages 2 and 3 — adjust the range to pull any combination of pages.

Splitting One Long Page into Multiple Pages

A special case: the source PDF contains a very tall page (a long screenshot, a continuous table) and you want it cut into standard-height pages. The trick is to build a document whose page height is half the source page's height, then draw the source content with automatic pagination enabled:

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

# Load the source document and get the first page
doc = PdfDocument()
doc.LoadFromFile("LongPage.pdf")
page = doc.Pages[0]

# New document: zero margins, same width, half the source height
newPdf = PdfDocument()
newPdf.PageSettings.Margins.All = 0.0
newPdf.PageSettings.Width = page.Size.Width
newPdf.PageSettings.Height = page.Size.Height / float(2)

# Add a new page and configure automatic pagination
newPage = newPdf.Pages.Add()
format = PdfTextLayout()
format.Break = PdfLayoutBreakType.FitPage
format.Layout = PdfLayoutType.Paginate

# Draw the source content; it flows onto extra pages automatically
page.CreateTemplate().Draw(newPage, PointF(0.0, 0.0), format)

newPdf.SaveToFile("SplitOnePage.pdf")
newPdf.Close()
doc.Close()

Two properties of PdfTextLayout do the work: Layout = PdfLayoutType.Paginate makes the drawing flow onto additional pages when it exceeds the page, and Break = PdfLayoutBreakType.FitPage cuts at the page boundary. Since the new page is half the height of the source, one long page spreads across two standard pages; decrease the height divisor to cut it into more pieces.

Practical Tips

Output location: the filename pattern in Split() can include a path prefix (for example "output/SplitDocument-{0}.pdf") to write the results into a specific directory.
0-based indexing: check doc.Pages.Count before extracting pages and choose the loop range accordingly to avoid out-of-range errors.
Granularity: for long-page splitting, a smaller divisor for Height yields more pages — tune it to the actual content height.
Resource cleanup: call Close() on both the source and target documents; this matters even more in batch processing.
Free edition: the free edition of Spire.PDF limits the number of pages processed, which is worth checking for large documents.

Conclusion

This article covered three common ways to split PDF documents in Python: using Split() to break an entire document into single-page files, using CreateTemplate().Draw() to extract selected pages into a new document, and leveraging PdfLayoutType.Paginate to divide one long page across multiple standard pages. Combined with file iteration and simple page math, these operations fit cleanly into workflows such as batch splitting and per-recipient document distribution.

How to Insert and Manage Images in PowerPoint with Python

Allen Yang — Wed, 30 Sep 2026 06:28:46 +0000

Images are almost indispensable in presentations: a company logo, a product screenshot, a flow diagram, an illustrative photo all carry information that text alone cannot. Placing one image on a slide looks simple, but precisely controlling its position, size, border, and transparency — or batch-replacing an old logo across many decks — is more than manual editing can handle.

This article uses Spire.Presentation for Python to show how to insert an image onto a slide, scale image dimensions, set the image frame's border and rotation, apply transparency, replace an existing image, and delete images from a slide in bulk.

Setting Up the Environment

Install the Spire.Presentation library:

pip install Spire.Presentation

Then import the required modules in your script:

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

Inserting an Image onto a Slide

AppendEmbedImageByPath() embeds an image into a rectangular region on a specified slide and returns an image shape object:

import math

presentation = Presentation()
presentation.LoadFromFile("presentation.pptx")

imagePath = "logo.png"
# Compute a centered position
left = math.trunc(presentation.SlideSize.Size.Width / float(2)) - 280
rect = RectangleF.FromLTRB(left, 140, 120 + left, 260)

# Insert theE image on the first slide
image = presentation.Slides[0].Shapes.AppendEmbedImageByPath(
    ShapeType.Rectangle, imagePath, rect)
# Remove the default border
image.Line.FillType = FillFormatType.none

presentation.SaveToFile("InsertImage.pptx", FileFormat.Pptx2010)
presentation.Dispose()

RectangleF.FromLTRB(left, top, right, bottom) defines the image's position and size on the slide, in pixels. ShapeType.Rectangle specifies the container shape type. The inserted image has a border by default; setting Line.FillType to FillFormatType.none removes it.

Scaling Images on a Slide

Existing images can be found by iterating the Shapes collection and then resizing them via Width and Height:

presentation = Presentation()
presentation.LoadFromFile("presentation.pptx")

scale = 0.5
for slide in presentation.Slides:
    for shape in slide.Shapes:
        if isinstance(shape, IEmbedImage):
            shape.Width = shape.Width * scale
            shape.Height = shape.Height * scale

presentation.SaveToFile("ScaleImage.pptx", FileFormat.Pptx2013)
presentation.Dispose()

isinstance(shape, IEmbedImage) filters out image shapes and skips text boxes, tables, and other shapes. scale = 0.5 shrinks every image to half its original size. Scaling by a single factor preserves the aspect ratio.

Setting the Image Frame Border and Rotation

The object returned when inserting an image is itself a shape, so you can set its border line style, width, and rotation angle:

import math

presentation = Presentation()

stream = Stream("logo.png")
imageData = presentation.Images.AppendStream(stream)
stream.Close()

# Insert at half the image's original dimensions
rect = RectangleF.FromLTRB(
    100, 100,
<parameter name="newString">    math.trunc(imageData.Width / float(2)) + 100,
    math.trunc(imageData.Height / float(2)) + 100)
pptImage = presentation.Slides[0].Shapes.AppendEmbedImageByImageData(
    ShapeType.Rectangle, imageData, rect)

# Border: solid, light blue, width 5
pptImage.Line.FillFormat.FillType = FillFormatType.Solid
pptImage.Line.FillFormat.SolidFillColor.Color = Color.get_LightBlue()
pptImage.Line.Width = 5
# Rotate -45 degrees
pptImage.Rotation = -45

presentation.SaveToFile("ImageFrame.pptx", FileFormat.Pptx2013)
presentation.Dispose()

AppendEmbedImageByImageData() inserts using already-loaded image data, which is useful when the same image is needed multiple times — load it once with Images.AppendStream() and reference it repeatedly. Line.FillFormat controls the border fill, and Rotation controls the angle (positive is clockwise, negative is counter-clockwise).

Applying Transparency to an Image

Transparency must be achieved through a shape fill: first load the image into the presentation's image collection, then draw a rectangle shape and fill it with that image, and finally set the transparency:

from spire.presentation import *

presentation = Presentation()
stream = Stream("logo.png")
imageData = presentation.Images.AppendStream(stream)
stream.Close()

# Read the dimensions from the image data
width, height = imageData.Width, imageData.Height
rect = RectangleF.FromLTRB(200, 100, width + 200, height + 100)
shape = presentation.Slides[0].Shapes.AppendShape(ShapeType.Rectangle, rect)
shape.Line.FillType = FillFormatType.none

# Fill the shape with the image
shape.Fill.FillType = FillFormatType.Picture
shape.Fill.PictureFill.Picture.EmbedImage = presentation.Images[0]
shape.Fill.PictureFill.FillType = PictureFillType.Stretch

# Set transparency to 50%
shape.Fill.PictureFill.Picture.Transparency = 50

presentation.SaveToFile("TransparentImage.pptx", FileFormat.Pptx2013)
presentation.Dispose()

Images.AppendStream() adds the image to the presentation's resource pool and returns the image data, from which Width and Height can be read directly — no Pillow needed. FillType = FillFormatType.Picture switches the shape fill to an image, EmbedImage points to the image object in the resource pool, and PictureFillType.Stretch stretches the image to fill the shape. Transparency ranges from 0 to 100; a larger value means more transparent. This approach is suitable when the image is used as a background or watermark.

Replacing an Existing Image

When updating a logo or swapping an image, first load the new image into the presentation's image collection, then assign it to the target image shape's EmbedImage:

ppt = Presentation()
ppt.LoadFromFile("presentation.pptx")
slide = ppt.Slides[0]

# Load the new image
stream = Stream("new_logo.png")
newImage = ppt.Images.AppendStream(stream)
stream.Close()

# Find the image titled "image1" and replace it
for shape in slide.Shapes:
    if isinstance(shape, SlidePicture):
        if shape.AlternativeTitle == "image1":
            shape.PictureFill.Picture.EmbedImage = newImage

ppt.SaveToFile("ReplaceImage.pptx", FileFormat.Pptx2013)
ppt.Dispose()

SlidePicture is the type of slide picture shapes. AlternativeTitle locates the image to replace, and assigning the new image data to PictureFill.Picture.EmbedImage completes the swap. AppendStream() adds the new image to the presentation's resource pool; the replacement only repoints the shape to the new resource.

Deleting Images from a Slide

To clean up unwanted images, iterate Shapes and remove them in reverse order:

ppt = Presentation()
ppt.LoadFromFile("presentation.pptx")
slide = ppt.Slides[0]

for i in range(slide.Shapes.Count - 1, -1, -1):
    if isinstance(slide.Shapes[i], SlidePicture):
        slide.Shapes.RemoveAt(i)

ppt.SaveToFile("RemoveImages.pptx", FileFormat.Pptx2013)
ppt.Dispose()

isinstance(slide.Shapes[i], SlidePicture) checks whether the shape is an image, and RemoveAt(i) deletes it by index. Iterating in reverse avoids index shifting after removal.

Practical Tips

The four arguments of RectangleF.FromLTRB are left, top, right, and bottom coordinates — not width and height — and are easy to mix up.
Inserted images have a border by default; set Line.FillType to none for a clean image with no frame.
When the same image is used multiple times, load it once with Images.AppendStream() and reference it repeatedly, which keeps the file smaller than calling AppendEmbedImageByPath() each time.
Transparency must be set through a shape fill; setting Transparency directly on an IEmbedImage has no effect.
Call presentation.Dispose() when finished to release resources.

Conclusion

This article covered the common operations for working with images in PowerPoint using Python: inserting an image onto a slide with AppendEmbedImageByPath(), scaling dimensions by iterating IEmbedImage, setting the image frame's border and rotation, achieving transparency through a shape fill, replacing an existing image via EmbedImage, and deleting images in bulk by iterating SlidePicture in reverse. Combined inside a presentation-generation script, image insertion and maintenance can be fully automated.

Insert and Manipulate Images in Excel Using Python

Allen Yang — Thu, 24 Sep 2026 09:59:37 +0000

Excel reports often need images: a company logo, a product render, an electronic signature, or a scanned document thumbnail. An image must land in the right cell, at the right size, without crowding the surrounding data. When a batch of reports all need the same logo, or a template must be cleared of old images before new ones are inserted, doing it by hand is slow and error-prone.

This article uses Spire.XLS for Python to show how to insert an image at a specific cell, adjust its offset within the cell, read its position and size, compress it to reduce file size, and delete images from a worksheet in bulk.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

Inserting an Image at a Specific Cell

Worksheet.Pictures.Add() inserts an image at a cell location. The first two arguments are the row and column numbers, and the third is the image path:

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

# Insert an image at row 14, column 5 (i.e. E14)
sheet.Pictures.Add(14, 5, "logo.png")

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

The image is inserted at its original size, with its top-left corner aligned to the specified cell. If the image is larger than the cell, it overlaps neighboring cells. To avoid overlap, adjust the target column width and row height before inserting the image.

Adjusting the Image Offset Within a Cell

After insertion, LeftColumnOffset and TopRowOffset fine-tune the image position inside the cell, leaving a gap between the image and the cell borders:

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

sheet.Range["A1"].Text = "Align picture within a cell:"
sheet.Range["A1"].Style.VerticalAlignment = VerticalAlignType.Top

picture = sheet.Pictures.Add(1, 1, "logo.png")

# Widen the column and heighten the row to fit the image
sheet.Columns[0].ColumnWidth = 40
sheet.Rows[0].RowHeight = 200

# Offset the image inside the cell
picture.LeftColumnOffset = 100
picture.TopRowOffset = 25

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

LeftColumnOffset is the horizontal offset from the cell's left edge, and TopRowOffset is the vertical offset from the top edge, both in pixels. Combined with ColumnWidth and RowHeight to enlarge the cell first, then offsetting the image into place, you can center the image or leave whitespace around it.

Reading the Image Position and Size

An image already in a worksheet can be retrieved by index from the Pictures collection, and its position and dimensions read back:

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

picture = sheet.Pictures[0]
left = picture.Left
top = picture.Top
width = picture.Width
height = picture.Height

print(f"Left={left}, Top={top}, Width={width}, Height={height}")

workbook.Dispose()

Left and Top are the absolute coordinates of the image in the worksheet, and Width and Height are its displayed dimensions. These values are useful when aligning an image to a region or checking whether it falls outside the print area.

Compressing Images

Inserting high-resolution images quickly inflates the Excel file size. The Compress() method compresses images in a worksheet at a specified quality. The argument is an integer from 0 to 100; a smaller value means higher compression:

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

for sheet in workbook.Worksheets:
    for picture in sheet.Pictures:
        picture.Compress(50)

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

The outer loop iterates all worksheets, the inner loop all images on each sheet. Compress(50) reduces every image to 50% quality. For reports viewed only on screen, 50 is usually enough; for reports that will be printed, 75 preserves more detail.

Deleting Images from a Worksheet

To clean old images out of a template or remove unwanted illustrations, iterate the Pictures collection and remove each one:

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

# Iterate in reverse to avoid index shifting after removal
for i in range(sheet.Pictures.Count - 1, -1, -1):
    sheet.Pictures[i].Remove()

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

Iterate from the end when deleting. Each Remove() shortens the collection by one, so a forward loop would skip images or go out of bounds. A reverse loop safely clears every image on the sheet.

Practical Tips

Set the target cell's column width and row height before inserting an image, so it does not overlap neighboring data.
LeftColumnOffset and TopRowOffset are in pixels, while ColumnWidth is in character units — do not mix them.
Compress() modifies the image data itself and is lossy; back up the original image first if it must be preserved.
To insert the same logo into multiple worksheets, put Pictures.Add() inside a for sheet in workbook.Worksheets loop.
Call workbook.Dispose() when finished to release resources.

Conclusion

This article covered the common operations for working with images in Excel using Python: inserting an image at a specific cell with Pictures.Add(), fine-tuning its position with LeftColumnOffset and TopRowOffset, reading its position and size through Left/Top/Width/Height, reducing file size with Compress(), and deleting images in bulk by iterating the Pictures collection in reverse. Combined inside a report-generation script, image insertion and maintenance can be fully automated.

Set Cell Borders and Styles in Excel Using Python

Allen Yang — Thu, 24 Sep 2026 07:19:28 +0000

A report needs more than accurate data — it needs clear layout. Borders separate rows and columns, font styles highlight key figures, and alignment keeps numbers and text in their proper places. These are the basic techniques that make an Excel report readable. When reports must be generated in batch or refreshed on a schedule, setting formats through a script is more reliable than adjusting them by hand.

This article uses Spire.XLS for Python to show how to apply borders to a cell range, create a custom style and apply it to entire rows or columns, apply Excel built-in styles, and set font properties directly.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

Applying Borders to a Cell Range

The most common need is to add uniform borders to a data range. After obtaining a CellRange, use the Borders property to set them in bulk:

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

# Get the entire data range from the first row/column to the last
cr = sheet.Range[sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn]

# Set border line style and color at once
cr.Borders.LineStyle = LineStyleType.Double
cr.Borders.Color = Color.get_CadetBlue()

# Turn off diagonal borders
cr.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.none
cr.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.none

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

Borders.LineStyle and Borders.Color apply to every border inside the range at once. Diagonal borders are set by default; if they are not wanted, explicitly set DiagonalDown and DiagonalUp to LineStyleType.none. LineStyleType also includes common styles such as Thin, Medium, Dotted, and Dashed.

Creating a Custom Style and Applying It to a Row or Column

When several rows or columns share the same format, setting each cell individually is verbose. A named style can be created once and then applied to whole rows or columns:

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

# Create a named style
style = workbook.Styles.Add("headerStyle")
style.VerticalAlignment = VerticalAlignType.Center
style.HorizontalAlignment = HorizontalAlignType.Center
style.Font.Color = Color.get_Blue()
style.ShrinkToFit = True

# Define a bottom border inside the style
style.Borders[BordersLineType.EdgeBottom].Color = Color.get_OrangeRed()
style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Dotted

# Apply it to the first column
sheet.Columns[0].CellStyleName = style.Name
sheet.Columns[0].Text = "Test"

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

The style object returned by workbook.Styles.Add() supports the full set of properties: font, alignment, borders, fill, and more. Once defined, apply it through CellStyleName = style.Name to sheet.Columns[i] or sheet.Rows[i], and a single line formats the entire column or row. Replacing sheet.Columns[0] with sheet.Rows[1] applies the same style to the second row.

Applying Excel Built-in Styles

Excel ships with a set of built-in styles (such as Title, Heading, Good, Bad). They can be applied directly, saving the effort of choosing colors manually:

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

# Apply the Title style to the first row
sheet.Range["A1:E1"].BuiltInStyle = BuiltInStyles.Title

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

The BuiltInStyles enum corresponds to the predefined styles in the "Cell Styles" gallery on Excel's Home tab, which is useful when the report should match Excel's native look.

Setting Font Properties Directly

When only the font of a specific range needs to change, there is no need to create a style object — work through Range.Style.Font directly:

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

# Font name and size
sheet.Range["B1"].Style.Font.FontName = "Comic Sans MS"
sheet.Range["B1"].Style.Font.Size = 45

# Bold, italic, underline
sheet.Range["B2:D2"].Style.Font.IsBold = True
sheet.Range["B3:D7"].Style.Font.IsItalic = True
sheet.Range["B3:B7"].Style.Font.Underline = FontUnderlineType.Single

# Font color and strikethrough
sheet.Range["B1"].Style.Font.Color = Color.get_CornflowerBlue()
sheet.Range["D3"].Style.Font.IsStrikethrough = True

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

The Font object exposes properties such as FontName, Size, IsBold, IsItalic, Underline, Color, and IsStrikethrough, each settable independently. Underline takes a FontUnderlineType enum value (such as Single, Double, AccountingSingle).

Practical Tips

Borders.LineStyle applies to all borders in the range; to set only one side, use Borders[BordersLineType.EdgeBottom] and similar to target it individually.
A custom style defined once can be reused in many places, which is more efficient than per-cell formatting and easier to revise later.
ShrinkToFit = True shrinks text to fit the column width, suitable for columns whose content length is unpredictable.
Named colors like Color.get_CadetBlue() are easy to remember; Color.FromArgb(r, g, b) can specify any color.
Call workbook.Dispose() when finished to release resources.

Conclusion

This article covered several ways to format Excel cells with Python: adding borders through Borders, creating a named style with workbook.Styles.Add() and applying it to whole rows or columns, applying Excel built-in styles through BuiltInStyle, and setting font properties directly through Range.Style.Font. Combined inside a report-generation script, these operations let formatting be fully automated.