Add Gridlines or Borders in Excel with Python (A Comprehensive Guide)
Gridlines and borders play a crucial role in enhancing the visual appeal and organization of data in Excel spreadsheets. Whether you want to highlight specific cells, create clear boundaries between data sets, or improve overall readability, mastering the art of adding or removing gridlines and borders is essential.
In this article, we will explore various techniques and methods to easily manipulate gridlines and borders in Excel with Python. From adding and removing gridlines and borders to adjusting line style, color and thickness, understanding these functionalities will empower you to create professional-looking and well-structured spreadsheets that effectively communicate your data. So, let’s dive in and discover the power of gridlines and borders in Excel.
- Turn On/Off Gridlines in Excel
- Change the Color of Gridlines in Excel
- Enable Printing Gridlines in Excel
- Apply Borders to Specific Cells or Ranges in Excel
- Apply Borders to the Allocated Range in Excel
- Apply Borders to Specific Sides of a Cell or Range in Excel
- Highlight Specific Cell Borders Using Conditional Formatting in Excel
- Change the Border Style and Color in Excel
- Remove Borders of Cells or Ranges in Excel
Python Library to Work with Gridlines and Borders in Excel
Spire.XLS for Python is a robust library for creating, reading and editing Excel XLS and XLSX files in Python applications. With Spire.XLS, developers can effortlessly manipulate gridlines and borders, creating visually appealing and well-structured Excel documents.
By utilizing the library’s intuitive methods and functionalities, users gain precise control over the addition and removal of gridlines and borders, as well as their properties, including line style, color, and thickness.
The library can be installed from PyPI using the following pip command.
pip install Spire.XLSDifferences Between Gridlines and Borders
Gridlines and borders are two distinct features in Excel that are used to enhance the appearance and readability of cells and worksheets. Here are the key differences between gridlines and borders:
Gridlines: Gridlines are horizontal and vertical lines that form a grid pattern on a worksheet. They are primarily used to visually separate cells and provide a reference for aligning and organizing data. Gridlines are displayed by default in Excel and are not printed unless specifically enabled. Gridlines are applied to the entire worksheet and cannot be customized for individual cells.
Borders: Borders, on the other hand, are lines that can be applied to individual cells, ranges of cells, or objects such as shapes and charts. Unlike gridlines, borders can be customized in terms of line style, color, and thickness. Borders can be applied to specific sides of a cell or to all sides, allowing for precise control over cell appearance.
Turn On/Off Gridlines in Excel Using Python
By default, gridlines are enabled to provide a visual grid pattern on the worksheet. However, if you want to remove gridlines for a cleaner look or enable them for better visibility, simply toggle the “Gridlines” checkbox.
To control the visibility of gridlines in Excel programmatically, you can set the Worksheet.GridLinesVisible property to True or False. The following code snippet loads an Excel document from a given path and hides (or shows) the gridlines of a specified worksheet within it.
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\\Input.xlsx")
# Get a specific worksheet
sheet = workbook.Worksheets[1]
# Hide the gridlines in the worksheet
sheet.GridLinesVisible = False
# Show the hidden gridlines in the worksheet
# sheet.GridLinesVisible = True
# Save the workbook to a different xlsx file
workbook.SaveToFile("output/HideGridlines.xlsx", ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()Change the Color of Gridlines in Excel Using Python
In Excel, you can customize the color of gridlines to suit your preferences and enhance the visual appeal of your worksheets. This flexibility enables you to create visually engaging worksheets with gridlines that align perfectly with your desired color scheme.
The Spire.XLS library provides the Worksheet.GridLineColor property, which allows you to effortlessly modify the gridline color for a specific worksheet. Here’s an example code snippet that demonstrates how to use this property to change the gridline color.
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\\Input.xlsx")
# Get a specific worksheet
sheet = workbook.Worksheets[0]
# Change the gridline color
sheet.GridLineColor = ExcelColors.Brown
# Save the workbook to a different xlsx file
workbook.SaveToFile("Output/ChangeGridlineColor.xlsx", ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()Enable Printing Gridlines in Excel Using Python
By default, gridlines are not printed, but you can easily enable this option. Navigate to the “Page Layout” tab, click on “Print Gridlines,” and check the box to include gridlines when printing. This ensures that the gridlines you see on the screen will also appear on the printed copy, providing clear visual boundaries between cells and improving the overall presentation of your printed Excel documents.
The PageSetup class in Spire.XLS encapsulates all page setup attributes, such as left margin, bottom margin, paper size, etc., as properties. Notably, it includes the PageSetup.IsPrintGridlines property, which allows you to specify whether or not gridlines should be printed for a worksheet. The code example provided enables printing gridlines in a specific worksheet.
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\\Input.xlsx")
# Get a specific worksheet
sheet = workbook.Worksheets[0]
# Get the PageSetup object of the first worksheet
pageSetup = sheet.PageSetup
# Allow to print gridlines
pageSetup.IsPrintGridlines = True
# Save the workbook to a different xlsx file
workbook.SaveToFile("Output/EnablePrintingGridlines.xlsx", ExcelVersion.Version2016);
# Dispose resources
workbook.Dispose()Apply Borders to Specific Cells or Ranges in Excel Using Python
In Excel, you have the ability to apply borders to individual cells, groups of cells, or entire ranges to create clear boundaries and highlight data. By incorporating borders, you can effectively organize and structure your data, making it easier to analyze and understand.
Using Spire.XLS, you can easily access specific cells or cell ranges by utilizing the Worksheet.Range[name: str] property. Once you have identified the desired cell or cell range, you can apply an outer border using the CellRange.BorderAround() method. Additionally, you can apply inner borders within a cell range using the CellRange.BorderInside() method.
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\\Input.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Get a specific cell
cell = worksheet.Range["B1"]
# Apply outside borders to the cell
cell.BorderAround(LineStyleType.Medium, Color.get_Black())
# Get a cell range
cellRange = worksheet.Range["B2:F10"]
# 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 workbook to a different xlsx file
workbook.SaveToFile("Output/AddBordersToCells.xlsx", ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()Apply Borders to the Allocated Range in Excel Using Python
The range in a worksheet that contains data, often known as the “allocated range” or “used range,” encompasses all cells with data, including text, numbers, formulas, and other content types.
To obtain the cell range that contains data, you can utilize the Worksheet.AllocatedRange property. Once you have obtained this range, applying borders becomes straightforward. You can use the CellRange.BorderAround() method to add an outer border to the range, and the CellRange.BorderInside() method to apply inner borders within the range.
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\\Input.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Get the allocated range
locatedRange = worksheet.AllocatedRange
# Apply outside borders to the cell range
locatedRange .BorderAround(LineStyleType.Medium, Color.get_Black())
# Apply inside borders to the cell range
locatedRange .BorderInside(LineStyleType.Thin, Color.get_Black())
# Save the workbook to a different xlsx file
workbook.SaveToFile("Output/AddBordersToAllocatedRange.xlsx", ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()Apply Borders to Specific Sides of a Cell or Range in Excel Using Python
Excel provides the flexibility to apply borders selectively to specific sides of cells or cell ranges, allowing for precise customization and visual impact. You can add left, top, right, bottom, or diagonal borders to individual cells or ranges, tailoring the border style to your specific needs.
With Spire.XLS, accessing and customizing specific borders, such as left, right, top, bottom, and diagonal borders, is made easy. You can conveniently access these borders through properties like CellRange.Borders[BordersLineType.EdgeLeft]. Once you have obtained the desired border, you have the flexibility to customize its appearance using the IBorder.LineStyle property and the IBorder.Color property.
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\\Input.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Get a cell or cell range, in this case, a cell range
cellRange = worksheet.Range["B2:D6"]
# Get the left, top, right, bottom border of the cell
leftBorder = cellRange.Borders[BordersLineType.EdgeLeft]
topBorder = cellRange.Borders[BordersLineType.EdgeTop]
rightBorder = cellRange.Borders[BordersLineType.EdgeRight]
bottomBorder = cellRange.Borders[BordersLineType.EdgeBottom]
# Set the border type respectively
leftBorder.LineStyle = LineStyleType.Thin
topBorder.LineStyle = LineStyleType.Thin
rightBorder.LineStyle = LineStyleType.Thin
bottomBorder.LineStyle = LineStyleType.Thick
# Set the border color respectively
leftBorder.Color = Color.get_Gray()
topBorder.Color = Color.get_Gray()
rightBorder.Color = Color.get_Gray()
bottomBorder.Color = Color.get_Black()
# Get a specific cell
cell = worksheet.Range["B8"]
# Get the diagonal border of the cell
diagonalBorder = cell.Borders[BordersLineType.DiagonalDown]
# Set the border style
diagonalBorder.LineStyle = LineStyleType.Thin
# Save the workbook to a different xlsx file
workbook.SaveToFile("Output/AddBorderOfEdge.xlsx", ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()Highlight Specific Cell Borders Using Conditional Formatting in Excel Using Python
Conditional formatting in Excel offers a powerful way to highlight specific cell borders based on predefined conditions. By utilizing conditional formatting rules, you can dynamically apply border styles to cells that meet specific criteria or contain certain values.
To add a conditional format to a worksheet using Python, you can utilize the Worksheet.ConditionalFormats.Add() method. Then, a condition is added to the conditional format using the AddCondition() method. This condition checks if the cell values are between 800 and 1000.
The border style and color of the cells that meet the condition are customized by setting the LeftBorderStyle, TopBorderStyle, RightBorderStyle, BottomBorderStyle, LeftBorderColor, TopBorderColor, RightBorderColor, and BottomBorderColor properties of the condition object.
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\\Input.xlsx")
# Get a specific 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["B2:B6"])
# Add a condition to get the cells that have the value larger than 800 but less than 1000
condition = conditionalForamt.AddCondition()
condition.FormatType = ConditionalFormatType.CellValue
condition.Operator = ComparisonOperatorType.Between
condition.FirstFormula = "800"
condition.SecondFormula = "1000"
# Set the border style and color of these cells
condition.LeftBorderStyle = LineStyleType.Thick
condition.TopBorderStyle = LineStyleType.Thick
condition.RightBorderStyle = LineStyleType.Thick
condition.BottomBorderStyle = LineStyleType.Thick
condition.LeftBorderColor = Color.get_Red()
condition.TopBorderColor = Color.get_Red()
condition.RightBorderColor = Color.get_Red()
condition.BottomBorderColor = Color.get_Red()
# Save the workbook to a different xlsx file
workbook.SaveToFile("Output/HightlightCellBorders.xlsx", ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()Change the Border Style and Color in Excel Using Python
If you read the above code examples carefully, you must already know how to customize the appearance of borders. For those of you who would skip the code examples above and go straight to this section, I list this example to show how to set the border style and color.
When calling BorderAround() method and BorderInside() method, you’ll need to pass in two parameters: LineStyleTpye and Color. The former is an enumeration of predefined border styles, such as Thin, Medium, Thick, Dashed, Dotted, DashDot and Double. The latter is used to specify a solid color for the border.
To change the style and color of the border on a specific side, you use the LineStyle property and the Color property of an IBorder object.
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\\Input.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Get a cell range
cellRange = worksheet.Range["B2:F6"]
# Change the style and color of outside borders
cellRange.BorderAround(LineStyleType.Double, Color.get_DarkBlue())
# Change the style and color of inside borders
cellRange.BorderInside(LineStyleType.Dashed, Color.get_LightBlue())
# Get a cell
cell = worksheet.Range["B9"]
# 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]
# Change the line style of the border on each side
leftBorder.LineStyle = LineStyleType.DashDotDot
topBorder.LineStyle = LineStyleType.DashDotDot
rightBorder.LineStyle = LineStyleType.DashDotDot
bottomBorder.LineStyle = LineStyleType.Double
# Change the color of the border on each slide
leftBorder.Color = Color.get_Red()
topBorder.Color = Color.get_Red()
rightBorder.Color = Color.get_Red()
bottomBorder.Color = Color.get_Orange()
# Save the workbook to a different xlsx file
workbook.SaveToFile("Output/ChangeBorderStyleAndColor.xlsx", ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()Remove Borders of a Cell or Cell Range in Excel Using Python
Removing borders in Excel reduces visual noise and distractions, creating a cleaner presentation. Excel offers flexibility to remove borders from cells, groups, or ranges, tailoring the appearance to your needs. Additionally, you can selectively remove borders from specific sides of a cell, enabling you to fine-tune the visual aesthetics and improve the overall presentation.
To remove borders surrounding or within a cell or cell range, you can simply set the CellRange.Borders.LineStyle property to LineStyleType.none. Similarly, if you wish to eliminate a border on a particular side, such as the left side, you can achieve this by setting the CellRange.Borders[BordersLineType.EdgeLeft].LineStyle property to LineStyleType.none.
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["B2"]
# 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["B3:F8"]
# Remove borders by setting line style to none
cellRange.Borders.LineStyle = LineStyleType.none
# Save the workbook to a different xlsx file
workbook.SaveToFile("Output/RemoveBorders.xlsx", ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()Conclusion
In conclusion, this blog post provides a comprehensive guide on managing gridlines and borders in Excel using Python. By leveraging Python libraries such as Spire.XLS, users can automate and customize spreadsheet manipulation, creating professional reports, analyzing data, and visualizing information with ease. Controlling gridlines and borders through Python empowers users to achieve their desired outcomes efficiently.
Hopefully, it will help someone in need. Happy coding!
Related Topics
Insert or Read Formulas in Excel in Python
