Adding Conditional Formatting
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.
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: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.
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
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.
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
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
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.
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.
Become a Member to join the conversation.