Excel is a powerful tool for organizing and analyzing data, but the default black-and-white spreadsheet can make it difficult to interpret information at a glance. By adding fill colors, gradients, or patterns to specific cells, you can highlight important data, separate different categories of information, and make the spreadsheet more visually appealing. In this article, you will learn how to set a fill color, gradient or pattern in Excel cells using Spire.XLS for Python.

• Add Fill Color to Excel Cells with Python
• Add Gradient Fill to Excel Cells with Python
• Add Fill Pattern to Excel Cells with 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

Add Fill Color to Excel Cells with Python

The CellRange.Style.Color property provided by Spire.XLS for Python allows to add a solid background color to Excel cells. The following are detailed steps.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.Worksheets[] property.
• Get a specified cell or cell range using Worksheet.Range[] property.
• Add a background color to the cells using CellRange.Style.Color property.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "Cost.xlsx"
outputFile = "CellFillColor.xlsx"

# Create a Workbook instance
workbook = Workbook()

# Load an Excel file from disk
workbook.LoadFromFile(inputFile)

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

# Set fill color for a cell or cell range
worksheet.Range["A1:D1"].Style.Color = Color.get_Green()
worksheet.Range["A5"].Style.Color = Color.get_Yellow()

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

Add Gradient Fill to Excel Cells with Python

To apply gradient fill in Excel, you first need to set the cell fill pattern type to gradient, and then specify two colors and the shading style of the gradient fill. The following are detailed steps.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.Worksheets[] property.
• Get the cell style of a specified cell or cell range using Worksheet.Range[].Style property.
• Get the interior cell style using CellStyle.Interior property.
• Set cell fill effect to gradient through ExcelInterior.FillPattern property.
• Set the background and foreground colors of the gradient fill using ExcelInterior.Gradient.BackColor and ExcelInterior.Gradient.ForeColor properties.
• Set the gradient shading style using ExcelInterior.Gradient.GradientStyle property.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "Cost.xlsx"
outputFile = "CellGradientFill.xlsx"

# Create a Workbook instance
workbook = Workbook()

# Load an Excel file from disk
workbook.LoadFromFile(inputFile)

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

# Get the cell style of a specified range
cellStyle = worksheet.Range["A1:A12"].Style

# Set cell fill pattern type to gradient
cellStyle.Interior.FillPattern  = ExcelPatternType.Gradient

# Set the background and foreground colors of the gradient fill
cellStyle.Interior.Gradient.BackColor = Color.get_SkyBlue()
cellStyle.Interior.Gradient.ForeColor = Color.get_White()

# Set the gradient shading style
cellStyle.Interior.Gradient.GradientStyle = GradientStyleType.From_Center

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

Add Fill Pattern to Excel Cells with Python

You can also add predefined patterns such as different styles of stripes, dots, and crosshatch to specific cells through the CellRange.Style.FillPattern property. The following are the detailed steps:

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.Worksheets[] property.
• Get a specified cell or cell range using Worksheet.Range[] property.
• Add a background pattern to the cells using CellRange.Style.FillPattern property.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "Cost.xlsx"
outputFile = "CellFillPattern.xlsx"

# Create a Workbook instance
workbook = Workbook()

# Load an Excel file from disk
workbook.LoadFromFile(inputFile)

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

#Set cell fill pattern for a cell or cell range
worksheet.Range["A7:C12"].Style.FillPattern = ExcelPatternType.Percent125Gray
worksheet.Range["D5"].Style.FillPattern = ExcelPatternType.ThinDiagonalStripe

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

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.

In Microsoft Excel, freezing panes is a practical feature that allows you to lock specific rows or columns in place while navigating through a spreadsheet. When you freeze panes, the selected rows or columns remain visible on the screen, even as you scroll through the rest of the worksheet. This feature proves especially useful when dealing with large datasets where headers, titles, or other important reference points must stay visible to provide context.

Unfreezing panes, on the other hand, allows you to release the locked rows or columns. It is beneficial when you no longer need certain sections to remain fixed and want to regain the ability to navigate the entire spreadsheet without any restrictions. In this article, we will demonstrate how to freeze and unfreeze panes in Excel in Python using Spire.XLS for Python.

• Freeze Panes in Excel in Python
• Unfreeze Panes 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 commands.

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

Freeze Panes in Excel in Python

Rows and columns can be frozen by using the Worksheet.FreezePanes() method. This method takes two parameters - rowIndex (1-based) and columnIndex (1-based). The first parameter represents the index of the row above which all rows will be frozen. The second parameter represents the index of the column to the left of which all columns will be frozen.

The following steps show you how to freeze specific rows and/or columns in Excel using Spire.XLS for Python.

• Create an instance of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Freeze specific rows and/or columns in the worksheet using Worksheet.FreezePanes(rowIndex, columnIndex) method.
• Save the result Excel file using Workbook.SaveToFile() method.

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

# Specify the paths for the input and output Excel files
inputFile = "Sample.xlsx"
outputFile = "FreezePanes.xlsx"

# Create a Workbook instance
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile(inputFile)

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

# Freeze specific rows and/or columns
# Freeze the top row
sheet.FreezePanes(2, 1)
# Or freeze the first column
# sheet.FreezePanes(1, 2)
# Or freeze the top row and the first column
# sheet.FreezePanes(2, 2)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Unfreeze Panes in Excel in Python

To unfreeze rows and columns in Excel, use the Worksheet.RemovePanes() method. The detailed steps are as follows.

• Create an instance of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Unfreeze rows and columns in the worksheet using Worksheet.RemovePanes() method.
• Save the result Excel file using Workbook.SaveToFile() method.

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

# Specify the paths for the input and output Excel files
inputFile = " FreezePanes.xlsx"
outputFile = "UnfreezePanes.xlsx"

# Create a Workbook instance
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile(inputFile)

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

# Unfreeze rows and/or columns in the worksheet
sheet.RemovePanes()

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

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.

If you have additional pieces of information to include in your spreadsheet, inserting rows or columns can provide room for these new fields. In addition,
adding blank rows or columns between data sets can also help to effectively separate different categories of information, making them easier to read and analyze. This article will demonstrate how to insert rows and columns in Excel in Python using Spire.XLS for Python.

• Insert a Row and a Column in Excel in Python
• Insert Multiple Rows and Columns 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

Insert a Row and a Column in Excel in Python

Spire.XLS for Python provides the Worksheet.InsertRow(rowIndex: int) and Worksheet.InsertColumn(columnIndex: int) methods for inserting a blank row and a blank column in an Excel worksheet. The following are the detailed steps:

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
• Insert a row into the worksheet using Worksheet.InsertRow(rowIndex: int) method.
• Insert a column into the worksheet using Worksheet.InsertColumn(columnIndex: int) method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "input.xlsx"
outputFile = "InsertRowAndColumn.xlsx"

# Create a Workbook instance
workbook = Workbook()

# Load an Excel document 
workbook.LoadFromFile(inputFile)

# Get a specified worksheet
worksheet = workbook.Worksheets[0]

# Insert a blank row as the 5th row in the worksheet 
worksheet.InsertRow(5)

# Insert a blank column as the 4th column in the worksheet 
worksheet.InsertColumn(4)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

Insert Multiple Rows and Columns in Excel in Python

To insert multiple rows and columns into a worksheet, you can use the Worksheet.InsertRow(rowIndex: int, rowCount: int) and Worksheet.InsertColumn(columnIndex: int, columnCount: int) methods. The following are detailed steps.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
• Insert multiple rows into the worksheet using Worksheet.InsertRow(rowIndex: int, rowCount: int) method.
• Insert multiple columns into the worksheet using Worksheet.InsertColumn(columnIndex: int, columnCount: int) method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "input.xlsx"
outputFile = "InsertRowsAndColumns.xlsx"

# Create a Workbook instance
workbook = Workbook()

# Load an Excel document 
workbook.LoadFromFile(inputFile)

# Get a specified worksheet
worksheet = workbook.Worksheets[0]

# Insert three blank rows into the worksheet
worksheet.InsertRow(5, 3)

#Insert two blank columns into the worksheet
worksheet.InsertColumn(4, 2)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

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.

Cell borders play a crucial role in enhancing the visual clarity and organization of data in Excel spreadsheets. Adding borders to cells can help draw attention to specific data points, highlight headers, or create clear boundaries between different sections of your worksheet. On the other hand, removing borders can provide a sleek and seamless appearance, especially when you want to eliminate distractions and focus solely on the data itself.

In this comprehensive guide, we will walk you through the process of adding or removing cell borders in Excel by using the Spire.XLS for Python library.

• Add Borders to a Selected Cell or Cell Range in a Worksheet
• Add Borders to the Cell Range Containing Data in a Worksheet
• Add Left, Top, Right, Bottom, Diagonal Borders to a Cell
• Remove Borders of a Cell or Cell Range in a Worksheet

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

Add Borders to a Selected Cell or Cell Range in a Worksheet in Python

Borders can be applied to individual cells, groups of cells, or even entire ranges to create clear boundaries and make data stand out. By adding borders, you can effectively organize and structure your data, making it easier to analyze and understand.

With Spire.XLS for Python, accessing specific cells or cell ranges is made easy through the Worksheet.Range[name: str] property. Once you have obtained the desired cell or cell range, you can apply an outside border using the CellRange.BorderAround() method. Additionally, you can apply inside borders to a cell range using the CellRange.BorderInside() method.

To apply borders to a cell or cell range, follow the steps below.

• Create a Workbook object.
• Load an Excel document from the specified path.
• Get a specific worksheet from the workbook.
• Get a cell or cell range using Worksheet.Range[name: str] property.
• Apply outside borders to the cell or cell range using CellRange.BorderAround() method.
• Apply inside borders to the cell range using CellRange.BorderInside() method.
• Save the workbook to a new Excel file.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx")

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

# Get a specific cell 
cell = worksheet.Range["C11"]

# Apply borders to the cell
cell.BorderAround(LineStyleType.Medium, Color.get_Black())

# Get a cell range
cellRange = worksheet.Range["B2:C6"]

# Apply outside borders to the cell range
cellRange.BorderAround(LineStyleType.Thin, Color.get_Black())

# Apply inside borders to the cell range
cellRange.BorderInside(LineStyleType.Thin, Color.get_Black())

# Save the document
workbook.SaveToFile("Output/AddBordersToCellRange.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

Add Borders to the Cell Range Containing Data in a Worksheet in Python

The range that contains data in a worksheet is commonly referred to as the "allocated range" or "used range". It represents the rectangular area that encompasses all the cells with data, including text, numbers, formulas, and other types of content.

To retrieve the cell range having data, use the Worksheet.AllocatedRange property. Then, you can easily apply borders to this range using the BorderAround() and BorderInside() methods.

The steps to add borders to the cell range containing data are as follows.

• Create a Workbook object.
• Load an Excel document from the specified path.
• Get a specific worksheet from the workbook.
• Get the cell range that contains data using Worksheet.AllocatedRange property.
• Apply outside borders to the cell or cell range using CellRange.BorderAround() method.
• Apply inside borders to the cell range using CellRange.BorderInside() method.
• Save the workbook to a new Excel file.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx")

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

# Get the cell range that contains data
locatedRange = worksheet.AllocatedRange

# Apply outside borders to the cell range
locatedRange .BorderAround(LineStyleType.MediumDashed, Color.get_Black())

# Apply inside borders to the cell range
locatedRange .BorderInside(LineStyleType.Thin, Color.get_Black())

# Save the document
workbook.SaveToFile("Output/AddBordersToLocatedRange.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

Add Left, Top, Right, Bottom, Diagonal Borders to a Cell in Python

In addition to applying outside and inside borders, you have the option to add left, top, right, bottom, and diagonal borders to individual cells or cell ranges. This feature allows you to go beyond basic border customization and provides additional flexibility to highlight important information, separate sections within your worksheet, or provide a visual structure to your data.

Spire.XLS provides convenient access to specific borders, including the left, right, top, bottom, and diagonal borders, through properties such as CellRange.Borders[BordersLineType.EdgeLeft]. Once you have obtained the desired border, you have the flexibility to customize its appearance by utilizing the IBorder.LineStyle property and the IBorder.Color property.

To add left, top, right, bottom, diagonal borders to a cell, follow the following steps.

• Create a Workbook object.
• Load an Excel document from the specified path.
• Get a specific worksheet from the workbook.
• Get a specific cell using Worksheet.Range property.
• Get the left, top, right, bottom and diagonal borders of the cell using the properties such as CellRange.Borders[BordersLineType.EdgeLeft].
• Set the line style of the border using IBorder.LineStyle property
• Set the color of the border using IBorder.Color property.
• Save the workbook to a new Excel file.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx")

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

# Get a cell
cell = worksheet.Range["B11"]

# Get the left, top, right, bottom border of the cell 
leftBorder = cell.Borders[BordersLineType.EdgeLeft]
topBorder = cell.Borders[BordersLineType.EdgeTop]
rightBorder = cell.Borders[BordersLineType.EdgeRight]
bottomBorder = cell.Borders[BordersLineType.EdgeBottom]

# Set the border type respectively
leftBorder.LineStyle = LineStyleType.Thick
topBorder.LineStyle = LineStyleType.Dotted
rightBorder.LineStyle = LineStyleType.SlantedDashDot
bottomBorder.LineStyle = LineStyleType.Double

# Set the border color respectively
leftBorder.Color = Color.get_Red()
topBorder.Color = Color.get_Brown()
rightBorder.Color = Color.get_DarkGray()
bottomBorder.Color = Color.get_OrangeRed()

# Get a specific cell 
cell = worksheet.Range["C10"]

# Get the diagonal border of the cell
diagonalBorder = cell.Borders[BordersLineType.DiagonalDown]

# Set the border style
diagonalBorder.LineStyle = LineStyleType.Thin

# Save the document
workbook.SaveToFile("Output/BorderOfEdge.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

Remove Borders of a Cell or Cell Range in a Worksheet in Python

Borders can be removed from individual cells, groups of cells, or even entire ranges, allowing you to reduce visual noise and clutter, making your data easier to interpret and analyze. Additionally, you can choose to remove borders from specific sides of a cell, such as the left, top, right, or bottom, which can alter the visual appearance and enhance the overall presentation.

To eliminate borders surrounding or within a cell or cell range, you can easily achieve this by setting the CellRange.Borders.LineStyle property to LineStyleType.none. Similarly, if you want to remove a border on a specific side, such as the left side, you can accomplish this by setting the CellRange.Borders[BordersLineType.EdgeLeft].LineStyle property to LineStyleType.none.

The steps to remove borders of a cell or cell range as follows.

• Create a Workbook object.
• Load an Excel document from the specified path.
• Get a specific worksheet from the workbook.
• Get a specific cell or cell range using Worksheet.Range property.
• Remove all borders of the cell or cell range by setting CellRange.Borders.LineStyle property to LineStyleType.none.
• Save the workbook to a new Excel file.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\BorderExample.xlsx")

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

# Get a cell 
cell = worksheet.Range["C11"]

# Remove borders by setting line style to none
cell.Borders.LineStyle = LineStyleType.none

# Remove border on a specific side
# cell.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.none

# Get a cell range
cellRange = worksheet.Range["B2:C6"]

# Remove borders by setting line style to none
cellRange.Borders.LineStyle = LineStyleType.none

# Save the document
workbook.SaveToFile("Output/RemoveBorders.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

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.