openpyxl Part 13 – Conditional Styling

In the previous part of the openpyxl series we were talking about styles. We applied some styles to the wb2.xlsx file. Here’s the raw data again, without any styling:

We also used some named styles. After all the modifications, the spreadsheet now looks like this:

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

Now, before we move on, let’s remove all the styles. We’re going to do it manually by opening the spreadsheet and choose the Clear… option to clear all the formatting. It depends on the version of Excel or any other program that you’re using where to find this option, but it definitely should be there. It’s the fastest way to get rid of formatting. You may also need to adjust the height of the row – the best option is to select automatic heights. Anyway, the workbook should look like so:

And we’ll be saving the workbook as separate files so that we still can quickly access the file without any formatting. Make also sure to save the wb2.xlsx file.

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

Conditional Formatting

Anyway, today we’ll see how to apply styles dynamically, depending on how the data in the cells changes. Let’s start by importing the workbook:

 >>> from openpyxl import load_workbook
 >>> workbook = load_workbook(filename="wb2.xlsx")
 >>> sheet = workbook.active 

In this article I’m also going to use three other classes:

– DifferentialStyle – used to store multiple styles like fonts, alignment, borders and others,

– Rule – used to apply styles to cells where a given condition is met,

– PatternFill – used to fill the background.

So, let’s import these three classes as well:

 >>> from openpyxl.styles.differential import DifferentialStyle
 >>> from openpyxl.formatting.rule import Rule
 >>> from openpyxl.styles import colors, PatternFill 

As you can see, I also imported colors, which we used in the previous part of the series. Now, let’s add a yellow background to all the rows where the category is ‘restaurant’. There are three such rows. So, let’s define the style:

 >>> bg = PatternFill(bgColor=colors.YELLOW)
 >>> style = DifferentialStyle(fill=bg) 

If the formatting should apply to the whole row, we must use an expression rule:

 >>> rule = Rule(type="expression", dxf=style)

Here’s the formula:

>>> rule.formula = ['$C1="restaurant"']

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

And now let’s add the formatting and save the file:

>>> sheet.conditional_formatting.add("A1:E11", rule)
>>> workbook.save('wb2a.xlsx') 

If you now open the wb2a.xlsx file, you will see the yellow background in the three rows:

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.

If you want to add styles to single cells, you can use the CellIsRule class. Let’s now add a green background to those cells in column E where the number of employees is greater than 50. There are two such rows. Here’s the code:

 >>> from openpyxl.formatting.rule import CellIsRule
 >>> bg = PatternFill(bgColor=colors.GREEN)
 >>> rule = CellIsRule(operator='greaterThan', formula=['50'], fill=bg)
 >>> sheet.conditional_formatting.add('E2:E11', rule)
 >>> workbook.save('wb2b.xlsx') 

And here’s the wb2b.xlsx workbook when you open it:

There are naturally lots of other options as far as conditional styling is concerned, but I’ll leave it for you to explore. However, in the following three parts of this series I’d like to briefly discuss three built-in formats that you can use to conditionally format your workbook. In the next part I’ll be talking about ColorScale, and then, in the following parts about IconSet and DataBar.