Conditional formatting is a powerful feature in Microsoft Excel that allows users to apply formatting rules to cells based on specific conditions or criteria. It provides a quick and efficient way to visually highlight and analyze data, making it easier to identify trends, patterns, and outliers. With conditional formatting, users can customize the appearance of cells, such as font color, cell background, and borders, to draw attention to specific data points or results. In this article, you will learn how to programmatically apply conditional formatting in Excel in Python using Spire.XLS for Python.

• Alternate Row Colors in Excel Using Conditional Formatting in Python
• Highlight Top or Bottom Ranked Values in Excel in Python
• Highlight Values Below or Above Average in Excel in Python
• Highlight Values Between Two Numbers in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Alternate Row Colors in Excel Using Conditional Formatting in Python

Apply alternating row colors enhances the readability and visual appeal of a spreadsheet. By using different background colors for adjacent rows, it makes it easier to distinguish and follow individual rows of data. The following are the steps to alternate row colors in Excel using conditional formatting with Spire.XLS for Python.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet from the workbook through Workbook.Worsheets[index] property.
• Add a conditional format to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
• Specify the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add a condition using XlsConditionalFormats.AddCondition() method to change the color of even rows.
• Add another condition to change the color of odd rows.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("C:/Users/Administrator/Desktop/input.xlsx")

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

# Add a conditional format to the worksheet
conditionalForamt = sheet.ConditionalFormats.Add()

# Specify the range where the conditional format will be applied
conditionalForamt .AddRange(sheet.Range[2, 1, sheet.LastRow, sheet.LastColumn])

# Specify the first condition and format the cells that match the condition
condition1 = conditionalForamt .AddCondition()
condition1.FirstFormula = "=MOD(ROW(),2)=0"
condition1.FormatType = ConditionalFormatType.Formula
condition1.BackColor = Color.get_White()

# Specify the second condition and format the cells that match the condition
condition2 = conditionalForamt.AddCondition()
condition2.FirstFormula = "=MOD(ROW(),2)=1"
condition2.FormatType = ConditionalFormatType.Formula
condition2.BackColor = Color.get_LightGray()

# Save to file
workbook.SaveToFile("output/AlternateRowColors.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Highlight Top or Bottom Ranked Values in Excel

Highlighting top-ranked or bottom-ranked values in Excel can quickly identify the highest or the lowest values within a range or dataset, making it easier to analyze and interpret the data. The following are the steps to highlight top or bottom ranked values in Excel using conditional formatting with Spire.XLS for Python.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet from the workbook through Workbook.Worsheets[index] property.
• Add a conditional format to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
• Specify the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add a condition using XlsConditionalFormats.AddCondition() method to change the color of the cells that have top ranked or bottom ranked values.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("C:/Users/Administrator/Desktop/input.xlsx")

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

# Add a conditional format to the worksheet
conditionalForamt = sheet.ConditionalFormats.Add()

# Specify the range where the conditional format will be applied
conditionalForamt.AddRange(sheet.Range["I2:I15"])

# Add a condition to highlight the top 3 ranked values
condition1 = conditionalForamt.AddTopBottomCondition(TopBottomType.Top, 3)
condition1.BackColor = Color.get_Red()

# Add a condition to highlight the bottom 3 ranked values
# condition2 = conditionalForamt.AddTopBottomCondition(TopBottomType.Bottom, 3)
# condition2.BackColor = Color.get_Green()

# Save the workbook to an Excel file
workbook.SaveToFile("output/TopOrBottomValues.xlsx", ExcelVersion.Version2016)

Highlight Values Below or Above Average in Excel

To quickly identify data points that are significantly higher or lower than the average, you can use conditional formatting as well. Here are the detailed steps.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet from the workbook through Workbook.Worsheets[index] property.
• Add a conditional format to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
• Specify the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add a condition using XlsConditionalFormats.AddCondition() method to change the color of the cells with values above or below the average.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("C:/Users/Administrator/Desktop/input.xlsx")

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

# Add a conditional format to the worksheet
conditionalForamt = sheet.ConditionalFormats.Add()

# Specify the range where the conditional format will be applied
conditionalForamt.AddRange(sheet.Range["I2:I15"])

# Add a condition to highlight the values above average
condition1 = conditionalForamt.AddAverageCondition(AverageType.Above)
condition1.BackColor = Color.get_Yellow()

# Add a condition to highlight the values below average
# condition2 = conditionalForamt.AddAverageCondition(AverageType.Below)
# condition2.BackColor = Color.get_DarkGray()

# Save the workbook to an Excel file
workbook.SaveToFile("output/AboveOrBelowAverage.xlsx", ExcelVersion.Version2016)

Highlight Values Between Two Numbers in Excel

Highlighting values between a specific range helps you quickly identify and focus on the relevant data points that fall within the range. The following are the steps to highlight values between two numbers using conditional formatting with Spire.XLS for Python.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet from the workbook through Workbook.Worsheets[index] property.
• Add a conditional format to the worksheet using Worksheet.ConditionalFormats.Add() method and return an object of XlsConditionalFormats class.
• Specify the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
• Add a condition using XlsConditionalFormats.AddCondition() method to change the color of the cells with values between two specific numbers.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load a file from disk
workbook.LoadFromFile("C:/Users/Administrator/Desktop/input.xlsx")

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

# Add a conditional format to the worksheet
conditionalForamt = sheet.ConditionalFormats.Add()

# Specify the range where the conditional format will be applied
conditionalForamt.AddRange(sheet.Range["I2:I15"])

# Create a condition and format the cells that meet the condition
condition = conditionalForamt.AddCondition()
condition.FormatType = ConditionalFormatType.CellValue
condition.Operator = ComparisonOperatorType.Between
condition.FirstFormula = "25000"
condition.SecondFormula = "27000"
condition.BackColor = Color.get_Red()

# Save the workbook to an Excel file
workbook.SaveToFile("output/ValuesBetweenTwoNumbers.xlsx", ExcelVersion.Version2016)

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Footnotes are a valuable tool in Microsoft Word that allows you to enhance the content of your documents by providing additional information, references, or citations at the bottom of a page. For example, you can use footnotes to provide in-depth explanations of complex concepts, cite sources to support your arguments, or offer tangential information that might be interesting to your readers. Whether you're working on an academic paper, a book, or any document that requires citations or explanations, footnotes offer a convenient way to maintain a clean and organized layout while presenting supplementary details. In this article, we will explain how to insert or remove footnotes in a Word document in Python using Spire.Doc for Python.

• Insert a Footnote for a Specific Paragraph in Word in Python
• Insert a Footnote for a Specific Text in Word in Python
• Remove Footnotes in a Word Document in Python

Install Spire.Doc for Python

This scenario requires Spire.Doc for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.Doc

If you are unsure how to install, please refer to this tutorial: How to Install Spire.Doc for Python on Windows

Insert a Footnote for a Specific Paragraph in Word in Python

You can use the Paragraph.AppendFootnote(FootnoteType.Footnote) method provided by Spire.Doc for Python to easily add a footnote for a specific paragraph. The detailed steps are as follows.

• Create an object of the Document class.
• Load a Word document using Document.LoadFromFile() method.
• Get a specific section in the document using Document.Section[int] property and then get a specific paragraph of the section using Section.Paragraphs[int] property.
• Add a footnote at the end of the paragraph using Paragraph.AppendFootnote(FootnoteType.Footnote) method.
• Set the text content of the footnote, and then set the font and color for the footnote text and the footnote reference mark.
• Save the result document using Document.SaveToFile() method.

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

# Create a Document instance
document = Document()
# Load a sample Word document
document.LoadFromFile("Sample.docx")

# Get the first section
section = document.Sections[0]

# Get a specified paragraph in the section
paragraph = section.Paragraphs[3]

# Add a footnote at the end of the paragraph
footnote = paragraph.AppendFootnote(FootnoteType.Footnote)

# Set the text content of the footnote
text = footnote.TextBody.AddParagraph().AppendText("The industry code list is available online.")
            
# Set the text font and color
text.CharacterFormat.FontName = "Arial"
text.CharacterFormat.FontSize = 12
text.CharacterFormat.TextColor = Color.get_DarkBlue()

# Set the font and color of the footnote reference mark
footnote.MarkerCharacterFormat.FontName = "Calibri"
footnote.MarkerCharacterFormat.FontSize = 15
footnote.MarkerCharacterFormat.Bold = True
footnote.MarkerCharacterFormat.TextColor = Color.get_DarkCyan()

# Save the result document
document.SaveToFile("AddFootnoteForParagraph.docx", FileFormat.Docx2016)
document.Close()

Insert a Footnote for a Specific Text in Word in Python

To add a footnote for a specific text, you need to find the text in the document, get the location of the text in its owner paragraph, and then insert the footnote after the text. The detailed steps are as follows.

• Create an object of the Document class.
• Load a Word document using Document.LoadFromFile() method.
• Find a specific text using Document.FindString() method.
• Get the found text as a single text range using TextSelection.GetAsOneRange() method.
• Get the paragraph where the text range is located using TextRange.OwnerParagraph property.
• Get the index position of the text range in the paragraph using Paragraph.ChildObjects.IndexOf() method.
• Add a footnote to the paragraph using Paragraph.AppendFootnote(FootnoteType.Footnote) method, and then insert the footnote after the specific text using Paragraph.ChildObjects.Insert() method.
• Set the text content of the footnote, and then set the font and color for the footnote text and the footnote reference mark.
• Save the result document using Document.SaveToFile() method.

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

# Create a Document instance
document = Document()
# Load a sample Word document
document.LoadFromFile("Sample.docx")

# Find a specific text
selection = document.FindString("major players", False, True)

# Get the found text as a single text range
textRange = selection.GetAsOneRange()

# Get the paragraph where the text range is located
paragraph = textRange.OwnerParagraph

# Get the index position of the text range in the paragraph
index = paragraph.ChildObjects.IndexOf(textRange)

# Add a footnote to the paragraph
footnote = paragraph.AppendFootnote(FootnoteType.Footnote)

# Insert the footnote after the text range
paragraph.ChildObjects.Insert(index + 1, footnote)

# Set the text content of the footnote
text = footnote.TextBody.AddParagraph().AppendText("Including suppliers, competitors, and customers")

# Set the text font and color
text.CharacterFormat.FontName = "Arial"
text.CharacterFormat.FontSize = 12
text.CharacterFormat.TextColor = Color.get_DarkBlue()

# Set the font and color of the footnote reference mark
footnote.MarkerCharacterFormat.FontName = "Calibri"
footnote.MarkerCharacterFormat.FontSize = 15
footnote.MarkerCharacterFormat.Bold = True
footnote.MarkerCharacterFormat.TextColor = Color.get_DarkGreen()

# Save the result document
document.SaveToFile("AddFootnoteForText.docx", FileFormat.Docx2016)
document.Close()

Remove Footnotes in a Word Document in Python

When the footnotes of a Word document are no longer needed, you can remove them to make the document neater. The detailed steps are as follows.

• Create an object of the Document class.
• Load a Word document using Document.LoadFromFile() method.
• Get a specific section using Document.Sections[int] property.
• Loop through each paragraph in the section to find the footnotes.
• Remove the footnotes using Paragraph.ChildObjects.RemoveAt() method.
• Save the result document using Document.SaveToFile() method.

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

# Create a Document instance
document = Document()
# Load a sample Word document
document.LoadFromFile("AddFootnoteForParagraph.docx")

# Get the first section of the document
section = document.Sections[0]

# Loop through the paragraphs in the section
for y in range(section.Paragraphs.Count):
    para = section.Paragraphs.get_Item(y)
    index = -1
    i = 0
    cnt = para.ChildObjects.Count
    while i < cnt:
        pBase = para.ChildObjects[i] if isinstance(para.ChildObjects[i], ParagraphBase) else None
        if isinstance(pBase, Footnote):
            index = i
            break
        i += 1
    if index > -1:
        # Remove the footnotes from the paragraph
        para.ChildObjects.RemoveAt(index)

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

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

PDF properties refer to the information embedded within the document that provides detailed information about the documents, such as author, creation date, last modification date, etc. Users can check the properties of a PDF document in PDF viewers to quickly grasp the key information of the document. Apart from the built-in properties, PDF documents also offer the feature of customizing properties to help provide additional information about the document. Understanding how to specify and access this document information facilitates the creation of user-friendly documents and the processing of documents in large quantities. In this article, we will explore how to set and retrieve PDF properties through Python programs using Spire.PDF for Python.

• Set PDF Properties with Python
• Retrieve PDF Properties with Python

Install Spire.PDF for Python

This scenario requires Spire.PDF for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip commands.

pip install Spire.PDF

If you are unsure how to install, please refer to this tutorial: How to Install Spire.PDF for Python on Windows

Set PDF Properties with Python

Spire.PDF for Python provides several properties under the PdfDocumentInformation class for setting built-in document properties, such as author, subject, keywords. Besides, it also provides the PdfDocumentInformation.SetCustomProperty() method to set custom properties. The following are the detailed steps to set PDF properties:

• Create an object of PdfDocument class and load a PDF document using PdfDocument.LoadFromFile() method.
• Get the properties of the document through PdfDocument.DocumentInformation property.
• Set the built-in properties through properties under PdfDocumentInformation class.
• Set custom properties using PdfDocumentInformation.SetCustomProperty() method.
• Save the document using PdfDocument.SaveToFile() method.

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

# Create an object of PdfDocument class and load a PDF document
pdf = PdfDocument()
pdf.LoadFromFile("Sample.pdf")

# Get the properties of the document
properties = pdf.DocumentInformation

# Set built-in properties
properties.Author = "Tim Taylor"
properties.Creator = "Spire.PDF"
properties.Keywords = "cloud service; digital business"
properties.Subject = "The introduction of cloud service and its advantages"
properties.Title = "The Power of Cloud Services: Empowering Businesses in the Digital Age"
properties.Producer = "Spire.PDF for Python"

# Set custom properties
properties.SetCustomProperty("Company", "E-iceblue")
properties.SetCustomProperty("Tags", "Cloud; Business; Server")

# Save the document
pdf.SaveToFile("output/SetPDFProperties.pdf")
pdf.Close()

Retrieve PDF Properties with Python

Information in built-in PDF properties can be obtained using the properties under the PdfDocumentInformation class, while that in custom PDF properties can be obtained using PdfDocumentInformation.GetCustomProperty() method. The detailed steps are as follows:

• Create an object of PdfDocument class and load a PDF document using PdfDocument.LoadFromFile() method.
• Get the properties of the document through PdfDocument.DocumentInformation property.
• Retrieve the built-in properties through properties under PdfDocumentInformation class and custom properties using PdfDocumentInformation.GetCustomProperty() method and print them.

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

# Create an object of PdfDocument class and load a PDF document
pdf = PdfDocument()
pdf.LoadFromFile("output\SetPDFProperties.pdf")

# Get the properties of the document
properties = pdf.DocumentInformation

# Create a StringBuilder object
information = ""

# Retrieve the built-in properties
information += "Author: " + properties.Author
information += "\nTitle: " + properties.Title
information += "\nSubject: " + properties.Subject
information += "\nKeywords: " + properties.Keywords
information += "\nCreator: " + properties.Creator
information += "\nProducer: " + properties.Producer

# Retrieve the custom properties
information += "\nCompany: " + properties.GetCustomProperty("Company")
information += "\nTags: " + properties.GetCustomProperty("Tags")

# Print the document properties
print(information)
pdf.Close()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.