- [Instructor] Earlier in the course,
we saw how to use the Openpyxl library
to apply conditional formatting to a worksheet.
In this example, we're going to use XlsxWriter
to accomplish the same thing,
as well as writing a formula to the worksheet.
Now, conditional formatting, as you'll recall,
is a way to specify a set of conditions
under which to apply a format to a set of cells.
Let's go ahead and open up our example code.
And you can see,
we already have some code to create the workbook
and add some data.
Essentially, we are reading in the contents
of the Inventory.csv file
and just writing it out as a Excel workbook
named Conditional.xlsx.
We also have some code that creates some format definitions
and applies them to the data.
So this is essentially the same code
from the formatting example
where we created a workbook from CSV data.
So let's start by adding a new column to the data
that calculates the profit margin for each product,
which is the difference between the consumer price
and the wholesale price.
And this is what we focused on
for the programming challenge way back in chapter one.
So let's go ahead and do that again.
What we're going to do
is after we've written the header row, which is right here,
let's add a new header for the margin.
So I'm going to call worksheet.write at row 0, column 5.
I'm going to put the string, "Margin," in there
and I'm going to format it as bold.
So to add the formula
to calculate the margin to the worksheet,
we're going to use the write_formula function.
So here in the loop
that writes out the data for each column,
let's add the formula that calculates the profit margin
for each product.
So I am going to use the write_formula function
at the current row in index 5,
and I'll use a formatting string for this.
So the formula is going to be equal to the value in column E
at the current row+1,
because remember, this is a zero-indexed operation here
and Excel is a one-indexed thing.
So the value in column E
minus the value in column D at this row.
And we're going to format that using the fmt_money format.
All right, so that writes the formula
and adds the new column.
So that all looks good.
So now let's add the conditional formatting.
So first, we need to define the format
to apply when the condition is met.
So let's do that up here. I'll name that fmt_cond.
And in my workbook, I'm going to call add_format.
And this format is going to have a background color
that is set to like a very light green
and it's going to be bold.
All right.
Now, to apply the conditional format,
we use the conditional_format function,
and this function takes several arguments.
Scroll down to where we're going to add that.
We need to specify the full range of cells
to apply the conditional formatting to.
So that's the start row, start column,
last row, and last column.
So for this case, we'll apply the formatting
just to the column that contains the margin.
So I'll call the conditional_format, okay?
So I'll specify the first row, which is row 1,
and then the column, which is row 5.
And then the length of whatever the inventory data is,
that's the last row.
And then the last column is also row 5.
And then we specify the conditions for the format.
Now, there's a variety of ways to do this,
and I'm going to just demonstrate a couple of them.
In this case, I'm going to specify a set of options.
So the conditional_format type
is going to be a cell operation,
and the criteria is going to be a greater than
or equal to criteria.
We will be comparing that against a value of 0.75.
And the format that we will apply
is going to be fmt_cond.
All right, so that's all we need to do, okay?
So for each cell in this range,
we're going to check to see if the sales value
is greater than or equal 2.75.
And if it is, we apply the conditional_format.
So let's go ahead and save this and let's run our code.
Oh, whoops. Little typing error there.
It's conditional_format. There we go.
So let's run the code.
All right, and we can see
that Conditional.xlsx has been created,
going to open that up.
And sure enough, when we look into the result,
we can see that the Margin column is highlighted
whenever the profit margin for a given product
is greater than 75 cents, or equal to.
So here, we can have some 75s.
All right, so far so good,
but we can get a little more fancy than this.
Let's highlight the entire row
instead of just the Margin cell.
So let's close this and go back to our code.
So I'm going to make a couple of changes.
First, I'm going to make a change to the type.
So instead of a cell type, I'm going to use a formula type.
And the criteria is going to be a formula
where I'm going to specify that the value in column F
starting at row 2 is greater than or equal to 0.75,
which means we no longer need the value.
We're going to leave the format as it is,
but remember, we want to apply this to the entire row.
So instead of having the start column
and end column both be 5,
I want the start column to be 0.
So I'm going to change that.
And that's all we need to do.
So let's run this again.
Okay, appears to have worked.
Let's open this up.
And now, we can see that the entire row
is being highlighted when the criteria is met.
So for every product
where the product margin is greater than .75,
the entire row is being highlighted.
So you can find the detailed documentation
for working with conditional formatting inside the docs.
Let me flip over to those.
So here at this link, you can learn a lot more
about the conditional formatting feature.
And if I scroll down a bit to the Options documentation,
you can see that there are a lot
of different options to explore,
along with a variety of examples for each.
So before moving on here,
I would suggest maybe taking some time
to try out some of the ones we didn't cover.
Try starting with some of the text-based ones, for example.