How to Add or Remove Cell Borders in Excel with Python [Comprehensive Tutorial]

To make Excel sheets more visually organized, adding borders is a simple yet effective solution. Borders not only enhance the structure of the data but also help differentiate between sections or topics. However, manually setting and adjusting cell borders can be tedious, especially when working with large datasets. This article introduces how to add or remove cell borders in Excel using Python, providing a quick and efficient way to streamline your workflow and save time.

---

Python Libraries to Add or Remove Cell Borders in Excel

To complete this task efficiently, several Python libraries can help. Enterprise-level tools like Spire.XLS and Aspose.Cells offer robust features and ease of use, making them ideal for handling complex Excel operations and ensuring compatibility across different file versions. Open-source libraries like OpenPyXL, XlsxWriter, and Pandas are lightweight alternatives, suitable for simpler tasks but may lack support for advanced features.

For a comprehensive solution, Spire.XLS for Python is recommended. It simplifies adding or removing cell borders and supports other advanced operations, such as format conversion (e.g., Word, PDF, HTML) and managing cells or worksheets. This makes it a versatile choice for a wide range of Excel-related tasks. You can install it using the pip command: pip install Spire.XLS.

Add Borders to Specified Cells or Cell Ranges in Excel with Python

If you want to highlight specific information in a cell or range of cells, adding borders is an effective option. Using Spire.XLS for Python, you can easily achieve this with the CellRange.BorderAround() and CellRange.BorderInside() methods. These methods allow you to add outer and inner borders to cells or regions, making the data more organized and easier to analyze.

Steps to add borders to a specified cell or cell range in Excel:

• Create an instance of the Workbook class and read an Excel file from the storage.

• Get a worksheet.

• Get a specified cell or a cell range using the Worksheet.Range[] method.

• Add the outside borders with the CellRange.BorderAround() method, and add the inside borders with the CellRange.BorderInside() method.

• Save the modified Excel file through the Workbook.SaveToFile() method.

Here is a code example demonstrating how to add borders to a single cell and a cell range in the first worksheet of an Excel file:

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("/population.xlsx")

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

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

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

# Get a cell range
cellRange = worksheet.Range["A2:B7"]

# 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("/AddBordersToCellRange.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

Python Add Borders to Cells with Data in Excel Worksheets

Adding borders to specific cells or cell ranges is a common task in Excel, but there are also times when you need to apply borders to all the used cells in a worksheet. To achieve this, you can use the Worksheet.AllocatedRange property to get the entire used range. Then, by calling the CellRange.BorderAround() and CellRange.BorderInside() methods, you can apply both outer and inner borders to the selected range. Here's a step-by-step guide on how to accomplish this.

Steps to add borders to all used cell ranges with Python:

• Create an object of the Workbook class and read an Excel document from the file path.

• Get a certain worksheet.

• Get the used range of cells in the worksheet using the Worksheet.AllocatedRange property.

• Apply outside border to the used range with the CellRange.BorderAround() method, and add the inside borders with the CellRange.BorderInside() method.

• Save the updated Excel file as a new one through the Workbook.SaveToFile() method.

Below is a Python code example demonstrating how to add a dashed border to the used cell range in the first worksheet of an Excel file:

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("/population.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("/AddBordersToLocatedRange.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

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

In addition to adding complete borders to cells, you can use Python to apply specific borders to the left, top, right, bottom, or even diagonal sides of a cell. By customizing the style and color of the borders, you can create personalized designs to highlight important information in your table, such as headers or titles.

Steps to customize cell borders in Excel with Python:

• Create a Workbook instance and read an Excel document from files.

• Retrieve a worksheet.

• Get a cell using the Worksheet.Range[] method.

• Get the left, top, right, bottom, and diagonal borders of the cell through the CellRange.Borders[] property.

• Customize the line style of the cell border with the IBorder.LineStyle property. Set the color of the cell border with the IBorder.Color property.

• Save the resulting Excel file.

Here is the Python example of setting the border style of specified cells in Excel:

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("/population.xlsx")

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


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

# 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["C3"]

# 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("/BorderOfEdge.xlsx", ExcelVersion.Version2016)


# Dispose resources
workbook.Dispose()

How to Remove Cell Borders in Excel with Python

After exploring how to add cell borders, let’s shift our focus to removing them. Removing borders can make a worksheet look cleaner and more suitable for data analysis. With Spire.XLS for Python, you can easily remove borders from specific cells or ranges and even target a particular side of a cell, such as the left side. Below are the detailed steps—let’s dive in!

Steps to remove cell borders from Excel:

• Create an object of the Workbook class and load an Excel file.

• Get the specified worksheet.

• Get a cell or cell range with borders using the Worksheet.Range[] method.

• Remove borders by setting the CellRange.Borders.LineStyle property to LineStyleType.none.

• Save the resulting Excel file.

Here’s a Python code example demonstrating how to remove the borders from cell B5 and the cell range B2:C6 in the first worksheet of an Excel file:

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("/population.xlsx")

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


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

# 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("/RemoveBorders.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

The Bottom Line

This guide covers how to add and remove cell borders in Excel, including applying borders to cells, ranges, and used areas, as well as customizing styles. With clear steps and code examples, you’ll be ready to manage Excel cell borders effortlessly.