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.
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:
|Cell.font||font style, size, color, etc|
|Cell.fill||cell color and pattern|
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']: c.fill = PatternFill('solid', fgColor = 'F2F2F2') for c in ws['B19:C19']: 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 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']: 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.border = no_left_side for c in ws['C8:C11']+ws['C15:C19']: c.border = no_right_side
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.border = no_right_side c.alignment = center_align for c in ws['F7:F14']: c.alignment = center_align