Adding Conditional Formatting

00:00 One of the features that makes Excel so useful is conditional formatting, or the ability to have the style of the cell change based on the value it contains.

00:08 openpyxl lets you set up these rules and styles so that you can add them to the worksheets that you create. Let’s go to the interpreter and load up the Amazon worksheet to try it out.

00:18 Start bpython, do the import,

00:23 load the workbook, and then take the first sheet. openpyxl has a couple of built-in patterns to make some of the more common conditional formatting rules easier to apply.

00:34 These are the ColorScale, IconSet, and DataBar. Let’s try out the ColorScale first, which will allow you to set up a color gradient.

00:41 So, from openpyxl.styles import colors and then from openpyxl.formatting.rule import ColorScaleRule. So to set this up we’ll say color_scale_rule and then set this equal to a ColorScaleRule().

00:59 This is going to have a start and an end, so one end will be one color that will shift into the other color as the values go from one rule to the other rule. So to do this, we’ll say the start_type will be "min", or the minimum value, and then set the start_color to be colors.RED. Now, the end_type will be the maximum, so by looking at the values that this rule applies to, it’ll find a minimum and maximum and then set up the scale.

01:25 So, set your end_color to something like colors.GREEN. Okay. Now you’ll take your sheet and then apply the .conditional_formatting property by saying .add(), and you’ll apply this to "H2:H100" and you’ll pass in the color_scale_rule, like so. Okay.

01:43 Take your workbook, and save it, and set the filename to something like "sample_color_scale_rule.xlsx". Once you run it, you should see it pop up.

01:53 Let’s open it up and see what we get.

01:57 Head over to column H, and there you go! You can see the star_rating. 5 would be maximum and that’s this bright green, it’s all green. And then 4 is a little darker. 3 is right in the middle, kind of like a really dark yellow. And 1 is the minimum, it’s that bright red.

02:14 And we’ll see if there’s any 2s in here—there’s a 2. A little darker red, not as red. Now, since this is conditional formatting, if you were to change these values—like change this to 3—the formatting changes with it.

02:29 So that’s great because these rules are set up to update as the values update. And if you go to Manage Rules, you should be able to see the rule that you created, right there.

02:43 So, that’s pretty cool! Let’s head back and take a look at another one. openpyxl also has an IconSet that will add icons based on the values.

02:52 So, from openpyxl.formatting.rule import IconSetRule. This will be a pretty similar pattern to the last one, so you can say icon_set_rule and set this equal to a new IconSetRule.

03:06 And for this example, use the "5Arrows" (five arrows), which is one of the icon styles that’s in Excel. Pass in "num", and then within a list, I’ll say [1, 2, 3, 4, 5]. Okay. So now take this sheets.conditional_formatting, and you want to .add—and apply this to the stars as well—so from "H2:H100". And now, pass in the icon_set_rule that you just created. And do you see this? You can go to workbook.save() and call this one something like "sample_icon_set_rule".

03:40 And I forgot the extension, so I’m just going to add the ".xlsx" and make another one. And if you open that up, you can now scroll over and you should see these icons here.

03:50 So, 5 is a green arrow pointing straight up. This one’s kind of diagonal. 1 is red and straight down. And 3 is horizontal. Good deal!

04:01 And like before, if you were to change one of these values, you can see that the icons will update with the values.

04:10 And finally, we can take a look at the DataBar, which will generate a progress bar within the cell. So to clean this up, let’s just reload the workbook and just grab the "sample.xlsx", like before. And then grab the sheet again.

04:25 It helps if you grab the actual active worksheet. And now, from openpyxl.formatting.rule import DataBarRule. And like before, let’s say data_bar_rule = DataBarRule() and you’ll set a start_type, which will be "num", a start_value, which will be 1, and the end_type, which is also "num", and the end_value, which in this case will be 5. Then set a color to colors.GREEN. Cool!

04:58 So, now take your sheet, go to .conditional_formatting and then .add() for "H2:H100", like before, and now, pass in the data_bar_rule. Take the workbook and save it.

05:11 I’m going to call this one "sample_data_bar.xlsx".

05:16 Let’s see what it looks like. So, head over and—oh, there it was. Cool! There we go. There’s a little progress bar based on the values inside. So the higher the value, the further it fills up. All right.

05:31 Now, keep in mind this is only a subset of what openpyxl can do when it comes to conditional formatting, so be sure to take a look at the documentation if there’s something specific that you’re looking for.

05:43 You should have a pretty good idea of how to add conditional formatting to your workbooks, though, so in the next video, you’re going to see how to add images.