- [Instructor] One of the great features of Excel
is the ability to apply conditional formatting
to cell content based on certain conditions.
This really helps identify specific data conditions
when you have a large amount of data in a worksheet
because it's much easier to process visual information.
Openpyxl lets you add conditional formatting
to your Excel data with some simple Python code.
So for this example,
we're going to use the FinancialSample Excel file
and the data that's in the SalesData worksheet.
Let's go ahead and open up our sample code.
All right, so you can see here in my sample code,
I've already got some code that loads the workbook
and gets the SalesData worksheet.
So first what we need to do is define some styles
to represent the formats that will be applied
by the conditions that we specify later.
And we do this the same way that we do define styles
as we saw earlier in the chapter,
when we did the example on creating cell styles.
So I'm going to define some styles for red bold text
and a red pattern fill.
And you can see that I've imported
the Font and PatternFill classes here.
So first let's create our red_color,
and that's going to be the value ffd2d2.
And then I'll make some bold_text,
and I need to make a font object for that.
So we'll set bold to true
and the color equal to, in this case it's going to be red.
So 00, it's going to be bright red for the text.
And then for the red_fill, I'm going to create a PatternFill.
And the background color for that
is going to be the red color that we just defined.
And the fill_type
is going to be a solid fill.
All right, so now I've got my styles
and what I need to do now
is create what's called a DifferentialStyle.
And you can see that I've imported the DifferentialStyle
from the appropriate openpyxl module here.
This is the style that's going to be applied
by the conditional formatter
based upon conditional format rules, right?
You can't use named styles
when working with conditional formatting, right?
The differential style is basically purpose built for this.
So I'm going to create
a diff_style
and I'm going to build a DifferentialStyle,
and the font is going to be the bold_text
and the fill is going to be the red_fill.
All right, so now I've got the styles
that I want to use in my conditional style,
I've created the DifferentialStyle
that will be applied by the conditional formatting engine.
So now I have to create the rule
for the actual conditional format.
And you can see that I've imported the Rule object
from the formatting module.
So this is how we define rules for conditional formatting.
All right, I'm going to create a rule
that triggers when the Profit column is less than $10,000.
So my rule is going to be a new rule
and the type of that rule is going to be an expression.
And then we pass our DifferentialStyle
in the dxf parameter,
so that's going to be my diff_style.
And then I'm going to specify the formula.
So the formula is going to be inside some brackets
and I'm going to pass a string,
and the string is going to be in Excel format.
So I'm going to say $L1
is less than 10,000.
All right, I want to apply the rule
so that the entire row has the style applied
when the condition is met.
Okay, I don't want to just apply this rule
just to the cell itself.
What I'm going to do is specify that the rule
implies to the entire dimensions of the worksheet data.
So to do that I'm going to create a variable called dimensions
and we're going to set that to be the sheet.dimensions for now.
And then on the sheet itself,
I'm going to use the conditional_formatting property
and I'm going to call the add function,
and on those dimensions I'm going to add my rule.
All right, so that's pretty much all I need to do,
and you probably noticed that I'm using
the absolute addressing here in Excel,
but only on the column.
And that's because I want the row to auto increment
and apply this rule to each one of the rows
in this particular column.
So the column is never going to change,
but the row will.
Right, so that's why I'm not putting the dollar sign
in front of the 1.
So let's go ahead and save our code.
All right, and then let's run this.
So I'll run it in the terminal.
And when I do, we can see
that the CondFormat XLS file has been created,
so let's hop on over to Excel and open that up.
All right, so sure enough
we can see that in the case where the Profit column,
where is that?
That is right here where it's column L, here's the profit.
So in all the cases where is less than 10,000,
right, we have the entire row for that case
is highlighted with the sort of a subdued red pattern fill
and the bright red bold text, right?
So as we scroll down
you can see all of the rows that are being affected.
Now, of course, if I wanted to just apply
the conditional format to only one column,
I could just specify those dimensions.
So let's just close this
and let's go back to our code.
All right, and let's change dimensions
to be L1
:L701, right?
That will cover all the rows.
So let's go ahead and run this again.
All right, so now we've updated our file,
let's go back out to Excel and reopen.
And sure enough, when I scroll over to the Profit column,
now we can see that only column L
is having the style applied
when the value is less than 10,000.