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.

openpyxl-excel-formatting
python-openpyxl-formatting

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:

AttributeUsage
Cell.fontfont style, size, color, etc
Cell.number_formatnumber formats
Cell.fillcell color and pattern
Cell.bordercell border
Cell.alignmenttext alignment

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:

Excel Format Cells window

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')
openpyxl fills cell background color

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'))
openpyxl fills cell background color

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
openpyxl draws cell borderlines

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
openpyxl text alignment

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

2 comments

  1. I’m just wondering about overlaying borders. For example if I wanted to put a thick border above the title, but did not want to change other borders around the cell, how would I do that?

    To the best of my understanding a change to one-side only would blank out the borders on the other sides.

Leave a Reply

Your email address will not be published. Required fields are marked *