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:

company	city	category	income	number_of_employees
Roses and Pansies	Toronto	gardening	456845.00	26
Reader’s Paradise	Sydney	publisher	911500.00	44
Dogs and Cats	Madrid	pets	458784.00	36
Enjoy Your Meal	New Orleans	restaurant	447884.00	18
Bloom Store	London	gardening	1254654.00	84
Tastes of the South	Miami	restaurant	587454.00	42
Pets for Everyone	Athens	pets	987887.00	21
Eat ‘Em Up	Helsinki	restaurant	1254477.00	53
Mr. Bunny	Vienna	pets	254478.00	12
Bookworms Rule	Rome	publisher	124474.00	40
SUM				376
MIN				12
MAX				84
AVERAGE				37.6
BIG COMPANIES				4

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

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.

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"']

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:

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.