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.

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.

Copy
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").

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.

Copy
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.)

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.

Copy
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.