Python openpyxl – Excel Formatting Cells

Sharing is caring!

Last Updated on July 14, 2022 by Jay

This tutorial will show you how to use the Python openpyxl library to customize Excel formatting such as cell color, alignment, borderlines, etc. We’ll continue with the previous example to make our monthly budget spreadsheet look prettier.

Library

The only library required is openpyxl, type the following in the command prompt window to install the library:

pip install openpyxl

Python & Excel – Cell Formatting

In openpyxl, each Cell object has a bunch of attributes we can use for formatting:

Collectively, these attributes are called styles but I still prefer calling them just formats. One thing to note is that we can apply styles to only one cell at a time. So we’ll need to do a loop in order to format a range of cells.

Color – Pattern (solid) Fill

There are two types of cell color formatting: Pattern Fill and Gradient Fill.

We use Pattern Fill to make a solid color or a pattern for the cell. A Pattern Fill refers to the following formatting in Excel:

The below code changes cells B11:C11 to a light gray background and changes cells B19:C19 to a dotted background known as 6.25% Gray (found in the Pattern Style dropbox). To make the formatting easier to see, we’ll hide all gridlines in Excel by setting ws.sheet_view.showGridLines to False.

from openpyxl.styles import PatternFill, GradientFill
ws.sheet_view.showGridLines = False

for c in ws['B11:C11'][0]:
    c.fill = PatternFill('solid', fgColor = 'F2F2F2')

for c in ws['B19:C19'][0]:
    c.fill = PatternFill('gray0625')

Color – Gradient Fill

A Gradient Fill usually takes two colors and interpolates colors between them and fills the cell background. The following code takes a cyan color (’85E4F7′) and a light blue color (‘4617F1’) for the gradient fill. You can change the two RGB hex code inside the stop argument to any color to create your own gradient fill.

ws['E7'].fill = GradientFill('linear', stop = ('85E4F7','4617F1'))
ws['E9'].fill = GradientFill('linear', stop = ('85E4F7','4617F1'))
ws['E11'].fill = GradientFill('linear', stop = ('85E4F7','4617F1'))
ws['E13'].fill = GradientFill('linear', stop = ('85E4F7','4617F1'))

Borderlines

Borderlines refer to the four sides of a cell. With openpyxl, we can control the style (solid vs dash, etc), thickness, color, and position of the borderlines. We need two objects to model a cell’s borderline: Border and Side.

A Border can contain one or multiple Sides. Then we assign the cell.border attribute to a Border object to create the borderlines.

from openpyxl.styles import Border, Side

double = Side(border_style="double", color="4617F1")
thin = Side(border_style="thin", color="4617F1")
regular = Side(border_style="medium", color="000000")

## For the title cells B2 to F2
for c in ws['B2:F2'][0]:
    c.border = Border(bottom=double, top=thin)



no_left_side = Border(top = regular,bottom=regular,right=regular)
no_right_side = Border(top = regular,bottom=regular, left=regular)
box = Border(top = regular,bottom=regular, left=regular,right=regular)

## For the "table-like" cells
for c in ws['B8:B11']+ws['B15:B19']:
    c[0].border = no_left_side
    
for c in ws['C8:C11']+ws['C15:C19']:
    c[0].border = no_right_side

Text Alignment

We use the Alignment object to model text/number alignment within a cell, then assign the cell.alignment attribute to the object.

from openpyxl.styles import Alignment

center_align = Alignment(horizontal='center', vertical='center')

for c in ws['C8:C11']+ws['C15:C19']:
    c[0].border = no_right_side
    c[0].alignment = center_align

for c in ws['F7:F14']:
    c[0].alignment = center_align

Additional Resources

Python & Excel – Number Format

Python openpyxl – How to Insert Rows, Columns in Excel

Adjust Excel Fonts using Python openpyxl

Write Data to Excel using Python