Formatting Fonts, Background Colors, and Borders

To make data visually clear, it's essential to use appropriate cell formatting.

In this lesson, we will learn how to format cells in an Excel file using Python's openpyxl library.

Formatting Cell Fonts

How can we make the cell font bold or change the color of the text using Python code?

In openpyxl, you can use the Font class to specify such formatting.

Formatting Fonts

from openpyxl import Workbook
from openpyxl.styles import Font

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Enter data into the cell
ws['A1'] = "Hello"

# Apply font formatting (bold, italic, blue)
ws['A1'].font = Font(bold=True, italic=True, color="0000FF")

# Save the file
wb.save("output_file.xlsx")

The above code uses the Font class to make the font in cell A1 bold (bold=True), italic (italic=True), and blue (color="0000FF").

Setting Background Colors for Cells

Changing the background color of cells can make data stand out more effectively.

Especially when applying conditional formatting, background colors can help highlight data.

In openpyxl, you can use the PatternFill class to set the background color of cells.

Setting Background Colors

from openpyxl import Workbook
from openpyxl.styles import PatternFill

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Enter values from 1 to 10 in the 3rd column (C)
for i in range(1, 11):
    ws.cell(row=i, column=3, value=i)

# Set background color of cells with values greater than 5 to green
for i in range(1, 11):
    if ws[f'C{i}'].value > 5:
        ws[f'C{i}'].fill = PatternFill(bgColor="00FF00", fill_type="solid")

# Save the file
wb.save("output_file.xlsx")

The bgColor property of PatternFill specifies the background color, and the fill_type property specifies the fill type (e.g., solid, darkDown, lightUp, etc.)

Setting Borders for Cells

Adding borders to cells can make highlighted data even clearer.

In such cases, you can use the Border and Side classes to set cell borders.

The Border class specifies the borders of the cell, and the Side class specifies the style of the border.

Setting Borders

from openpyxl import Workbook
from openpyxl.styles import Border, Side

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Enter data into the cell
ws['E2'] = "Border"

# Set the border (thick solid line)
border = Border(left=Side(style='thick'),
                right=Side(style='thick'),
                top=Side(style='thick'),
                bottom=Side(style='thick'))

ws['E2'].border = border

# Save the file
wb.save("output_file.xlsx")

In the above code, the Border class is used to specify the border of cell E2.

The left, right, top, and bottom properties of the Border class specify the borders on the left, right, top, and bottom sides of the cell, respectively.

Side(style='thick') specifies a thick solid line border.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In