Excel Format Borders (Python Programming)

Why This Matters

Formatting borders in Excel is crucial for organizing data, making it more readable, and improving overall presentation. With Python, you can automate the process of formatting borders across multiple cells or entire worksheets, saving time and ensuring consistency. Mastering this skill can be particularly beneficial when dealing with large datasets or repetitive tasks.

Prerequisites

To follow along with this lesson, you should have a basic understanding of Python programming, including functions, modules, and data structures like lists and dictionaries. Familiarity with the openpyxl library is also helpful but not required, as we will cover its essentials in this lesson. If you're new to Python or need a refresher, check out our Python for Beginners tutorial.

Core Concept

The openpyxl library is a popular Python package for reading and writing Excel files (.xlsx). To use it, first install the library by running:

pip install openpyxl

Once installed, you can create an Excel workbook, access its worksheets, and manipulate their contents. To format borders, we will primarily focus on the Cell and Border classes.

Creating a Workbook and Accessing Worksheets

To begin, let's create a new workbook with one worksheet:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

Now, we can access the active worksheet (ws) and manipulate its cells.

Formatting Borders

To format a cell's border, create a Border object with the desired properties (e.g., color, style) and apply it to the cell using the border property:

from openpyxl.styles import Border, Side

Define border style

border = Border(left=Side(style='thin', color='000000'), # black

right=Side(style='thin', color='000000'), # black

top=Side(style='thin', color='000000'), # black

bottom=Side(style='thin', color='000000')) # black

Apply border to cell A1

ws['A1'].border = border

You can also format multiple cells at once by looping through a range:

for row in ws.iter_rows('A1':'D5'):

for cell in row:

cell.border = border

### Formatting Borders with Predefined Styles

Instead of defining borders manually, you can use predefined styles from the `openpyxl.styles` module:

from openpyxl.styles import Border, Side, Font, PatternFill

Define a style for thin black borders and bold red text

border = Border(left=Side(style='thin', color='000000'),

right=Side(style='thin', color='000000'),

top=Side(style='thin', color='000000'),

bottom=Side(style='thick', color='000000'))

fill = PatternFill(start_color='FFD7CE', end_color='FFBDBDB', fill_type='gradient')

font = Font(bold=True, color='FF0000')

style = ws.cell(row=1, column=1).style

style.border = border

style.fill = fill

style.font = font

### Adding Border Styles to Cell Properties

You can also add border styles directly to a cell's properties:

from openpyxl.styles import PatternFill, Font

Define a style for thin black borders and bold red text

fill = PatternFill(start_color='FFD7CE', end_color='FFBDBDB', fill_type='gradient')

font = Font(bold=True, color='FF0000')

Apply style to cell A1

ws['A1'].style.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thick'))

ws['A1'].fill = fill

ws['A1'].font = font

### Customizing Border Styles

You can customize border styles by adjusting the `border_style`, `border_color`, and `border_width` properties of the `Side` class:

from openpyxl.styles import Side

Define a style for dashed red borders

dashed_red = Side(border_style='dash', color='FF0000')

Apply dashed red border to cell A1

ws['A1'].border = Border(left=dashed_red, right=dashed_red, top=dashed_red, bottom=dashed_red)

### Formatting Diagonal Borders

To format diagonal borders, you can use the `diagonal` property of the `Border` class:

from openpyxl.styles import Diagonal

Define a style for diagonal red border with 45-degree angle

diagonal = Diagonal(style='slantdashed', color='FF0000')

Apply diagonal red border to cell A1

ws['A1'].border = Border(left=diagonal, right=diagonal, top=diagonal, bottom=diagonal)

Worked Example

Let's create a simple Excel file with formatted borders:

from openpyxl import Workbook
from openpyxl.styles import Border, Side, Font, PatternFill, Diagonal

wb = Workbook()
ws = wb.active

Define data

data = [['Apples', 'Oranges', 'Bananas'],

['5', '3', '7'],

['10', '6', '4']]

Write data to worksheet

for row in data:

ws.append(row)

Define border style for thin black borders and bold red text

border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thick'))

fill = PatternFill(start_color='FFD7CE', end_color='FFBDBDB', fill_type='gradient')

font = Font(bold=True, color='FF0000')

Apply border style to header row

for cell in ws[1]:

cell.border = border

cell.fill = fill

cell.font = font

Define diagonal red border for cell A1

diagonal = Diagonal(style='slantdashed', color='FF0000')

ws['A1'].border = Border(left=diagonal, right=diagonal, top=diagonal, bottom=diagonal)

Save the workbook as an Excel file

wb.save('formatted_borders.xlsx')

This script creates a new Excel file named `formatted_borders.xlsx` with data formatted with thin black borders, bold red text for the header row, and a diagonal red border for cell A1.

Common Mistakes

1. Forgetting to import necessary modules: Make sure you have imported the required modules, such as openpyxl, Side, Border, Font, PatternFill, and Diagonal.
2. Incorrectly defining border styles: Ensure that you set the correct properties for each side (left, right, top, bottom) when defining a border style.
3. Applying borders to the wrong cells: Verify that you are applying borders to the intended cells by using the correct cell references or looping through the desired range.
4. Not saving the workbook: Don't forget to save the workbook after making changes with the wb.save() method.
5. Using deprecated functions or properties: Some functions and properties in openpyxl have been deprecated; ensure that you are using the latest versions of the library and referencing up-to-date documentation.

Practice Questions

1. Write a script that formats all cells in column A of an Excel file named data.xlsx with thin black borders and bold red text.
2. Create an Excel file named sales_report.xlsx containing sales data (products, quantities, prices) formatted with thick green borders for the header row and thin blue borders for the rest of the cells.
3. Write a script that reads an Excel file named expenses.xlsx, calculates the total expenses, and formats the total cell with a red background and bold text.
4. Modify the worked example to format diagonal borders for all cells in the range B2:D3 with a dashed orange border.
5. Write a script that creates an Excel file named grades.xlsx containing student grades (names, exam1, exam2, exam3) formatted as follows:

• Header row with thin black borders and bold red text
• Grades cells with thin blue borders
• Total average cell with a green background and bold text

FAQ

Q: How can I format borders on multiple worksheets at once?

A: You can loop through all worksheets in the workbook using wb.worksheets and apply the desired border formatting to each one.

Q: Can I use different border styles for different cells or ranges?

A: Yes, you can create multiple Border objects with varying properties and apply them to different cells or ranges as needed.

Q: How do I save the workbook in a different format (e.g., CSV)?

A: To save the workbook as a CSV file, use the save() method with the desired filename extension: wb.save('formatted_borders.csv'). However, keep in mind that Excel files contain more formatting options than CSV files can support.

Q: How do I apply conditional formatting to cells based on their values?

A: To apply conditional formatting, use the ConditionalFormat class from the openpyxl.worksheet.worksheet module. You can set rules for formatting based on specific cell values or ranges. For example:

from openpyxl.worksheet.worksheet import ConditionalFormatRule

Set up a rule to format cells with grades above 80 as green

rule = ConditionalFormatRule(type='cell_is', operator='greaterthan', value=80, format='green')

ws.conditional_formatting.append(rule)

5. Q: How do I create a macro in Excel using Python and openpyxl?
A: To create a macro (VBA code) in an Excel file using Python and `openpyxl`, you can use the `macro_name`, `code`, and `description` properties of the `Macro` class from the `openpyxl.workbook.macros` module. Here's an example:

from openpyxl.workbook.macros import Macro

Define a simple macro that multiplies two numbers

macro = Macro(name='Multiply', code="Sub Multiply()\n MsgBox Application.WorksheetFunction.Product(Range(\"A1\", \"B1\"))\nEnd Sub", description="Multiplies the values in cells A1 and B1")

Add the macro to the workbook

wb.macros.append(macro)