1
00:00:00,005 --> 00:00:01,004
- [Instructor] Earlier in the course,

2
00:00:01,004 --> 00:00:04,005
we saw how to use the Openpyxl library

3
00:00:04,005 --> 00:00:08,001
to apply conditional formatting to a worksheet.

4
00:00:08,001 --> 00:00:10,006
In this example, we're going to use XlsxWriter

5
00:00:10,006 --> 00:00:12,006
to accomplish the same thing,

6
00:00:12,006 --> 00:00:16,003
as well as writing a formula to the worksheet.

7
00:00:16,003 --> 00:00:18,006
Now, conditional formatting, as you'll recall,

8
00:00:18,006 --> 00:00:21,005
is a way to specify a set of conditions

9
00:00:21,005 --> 00:00:26,000
under which to apply a format to a set of cells.

10
00:00:26,000 --> 00:00:29,002
Let's go ahead and open up our example code.

11
00:00:29,002 --> 00:00:30,000
And you can see,

12
00:00:30,000 --> 00:00:33,008
we already have some code to create the workbook

13
00:00:33,008 --> 00:00:35,001
and add some data.

14
00:00:35,001 --> 00:00:37,007
Essentially, we are reading in the contents

15
00:00:37,007 --> 00:00:40,005
of the Inventory.csv file

16
00:00:40,005 --> 00:00:46,000
and just writing it out as a Excel workbook

17
00:00:46,000 --> 00:00:49,007
named Conditional.xlsx.

18
00:00:49,007 --> 00:00:52,008
We also have some code that creates some format definitions

19
00:00:52,008 --> 00:00:55,001
and applies them to the data.

20
00:00:55,001 --> 00:00:58,000
So this is essentially the same code

21
00:00:58,000 --> 00:00:59,009
from the formatting example

22
00:00:59,009 --> 00:01:03,003
where we created a workbook from CSV data.

23
00:01:03,003 --> 00:01:07,003
So let's start by adding a new column to the data

24
00:01:07,003 --> 00:01:10,006
that calculates the profit margin for each product,

25
00:01:10,006 --> 00:01:13,001
which is the difference between the consumer price

26
00:01:13,001 --> 00:01:14,007
and the wholesale price.

27
00:01:14,007 --> 00:01:16,002
And this is what we focused on

28
00:01:16,002 --> 00:01:19,002
for the programming challenge way back in chapter one.

29
00:01:19,002 --> 00:01:21,003
So let's go ahead and do that again.

30
00:01:21,003 --> 00:01:22,001
What we're going to do

31
00:01:22,001 --> 00:01:26,006
is after we've written the header row, which is right here,

32
00:01:26,006 --> 00:01:29,004
let's add a new header for the margin.

33
00:01:29,004 --> 00:01:38,001
So I'm going to call worksheet.write at row 0, column 5.

34
00:01:38,001 --> 00:01:40,002
I'm going to put the string, "Margin," in there

35
00:01:40,002 --> 00:01:45,007
and I'm going to format it as bold.

36
00:01:45,007 --> 00:01:47,000
So to add the formula

37
00:01:47,000 --> 00:01:48,007
to calculate the margin to the worksheet,

38
00:01:48,007 --> 00:01:51,007
we're going to use the write_formula function.

39
00:01:51,007 --> 00:01:54,009
So here in the loop

40
00:01:54,009 --> 00:01:57,001
that writes out the data for each column,

41
00:01:57,001 --> 00:02:00,002
let's add the formula that calculates the profit margin

42
00:02:00,002 --> 00:02:02,006
for each product.

43
00:02:02,006 --> 00:02:10,008
So I am going to use the write_formula function

44
00:02:10,008 --> 00:02:15,000
at the current row in index 5,

45
00:02:15,000 --> 00:02:17,008
and I'll use a formatting string for this.

46
00:02:17,008 --> 00:02:22,003
So the formula is going to be equal to the value in column E

47
00:02:22,003 --> 00:02:25,008
at the current row+1,

48
00:02:25,008 --> 00:02:28,008
because remember, this is a zero-indexed operation here

49
00:02:28,008 --> 00:02:32,001
and Excel is a one-indexed thing.

50
00:02:32,001 --> 00:02:33,008
So the value in column E

51
00:02:33,008 --> 00:02:41,002
minus the value in column D at this row.

52
00:02:41,002 --> 00:02:48,006
And we're going to format that using the fmt_money format.

53
00:02:48,006 --> 00:02:51,002
All right, so that writes the formula

54
00:02:51,002 --> 00:02:53,000
and adds the new column.

55
00:02:53,000 --> 00:02:54,004
So that all looks good.

56
00:02:54,004 --> 00:02:57,003
So now let's add the conditional formatting.

57
00:02:57,003 --> 00:02:59,006
So first, we need to define the format

58
00:02:59,006 --> 00:03:02,008
to apply when the condition is met.

59
00:03:02,008 --> 00:03:07,003
So let's do that up here. I'll name that fmt_cond.

60
00:03:07,003 --> 00:03:14,003
And in my workbook, I'm going to call add_format.

61
00:03:14,003 --> 00:03:20,007
And this format is going to have a background color

62
00:03:20,007 --> 00:03:28,000
that is set to like a very light green

63
00:03:28,000 --> 00:03:34,003
and it's going to be bold.

64
00:03:34,003 --> 00:03:36,006
All right.

65
00:03:36,006 --> 00:03:38,008
Now, to apply the conditional format,

66
00:03:38,008 --> 00:03:41,004
we use the conditional_format function,

67
00:03:41,004 --> 00:03:45,000
and this function takes several arguments.

68
00:03:45,000 --> 00:03:47,007
Scroll down to where we're going to add that.

69
00:03:47,007 --> 00:03:49,009
We need to specify the full range of cells

70
00:03:49,009 --> 00:03:52,001
to apply the conditional formatting to.

71
00:03:52,001 --> 00:03:54,008
So that's the start row, start column,

72
00:03:54,008 --> 00:03:57,000
last row, and last column.

73
00:03:57,000 --> 00:03:59,007
So for this case, we'll apply the formatting

74
00:03:59,007 --> 00:04:05,009
just to the column that contains the margin.

75
00:04:05,009 --> 00:04:12,004
So I'll call the conditional_format, okay?

76
00:04:12,004 --> 00:04:17,004
So I'll specify the first row, which is row 1,

77
00:04:17,004 --> 00:04:22,005
and then the column, which is row 5.

78
00:04:22,005 --> 00:04:26,003
And then the length of whatever the inventory data is,

79
00:04:26,003 --> 00:04:28,002
that's the last row.

80
00:04:28,002 --> 00:04:32,007
And then the last column is also row 5.

81
00:04:32,007 --> 00:04:37,000
And then we specify the conditions for the format.

82
00:04:37,000 --> 00:04:38,006
Now, there's a variety of ways to do this,

83
00:04:38,006 --> 00:04:41,001
and I'm going to just demonstrate a couple of them.

84
00:04:41,001 --> 00:04:45,004
In this case, I'm going to specify a set of options.

85
00:04:45,004 --> 00:04:47,000
So the conditional_format type

86
00:04:47,000 --> 00:04:53,001
is going to be a cell operation,

87
00:04:53,001 --> 00:04:59,003
and the criteria is going to be a greater than

88
00:04:59,003 --> 00:05:02,005
or equal to criteria.

89
00:05:02,005 --> 00:05:10,002
We will be comparing that against a value of 0.75.

90
00:05:10,002 --> 00:05:13,004
And the format that we will apply

91
00:05:13,004 --> 00:05:18,001
is going to be fmt_cond.

92
00:05:18,001 --> 00:05:21,000
All right, so that's all we need to do, okay?

93
00:05:21,000 --> 00:05:23,007
So for each cell in this range,

94
00:05:23,007 --> 00:05:25,004
we're going to check to see if the sales value

95
00:05:25,004 --> 00:05:27,007
is greater than or equal 2.75.

96
00:05:27,007 --> 00:05:30,005
And if it is, we apply the conditional_format.

97
00:05:30,005 --> 00:05:39,003
So let's go ahead and save this and let's run our code.

98
00:05:39,003 --> 00:05:41,005
Oh, whoops. Little typing error there.

99
00:05:41,005 --> 00:05:44,004
It's conditional_format. There we go.

100
00:05:44,004 --> 00:05:48,007
So let's run the code.

101
00:05:48,007 --> 00:05:49,007
All right, and we can see

102
00:05:49,007 --> 00:05:54,005
that Conditional.xlsx has been created,

103
00:05:54,005 --> 00:05:56,002
going to open that up.

104
00:05:56,002 --> 00:05:59,002
And sure enough, when we look into the result,

105
00:05:59,002 --> 00:06:03,002
we can see that the Margin column is highlighted

106
00:06:03,002 --> 00:06:05,009
whenever the profit margin for a given product

107
00:06:05,009 --> 00:06:09,005
is greater than 75 cents, or equal to.

108
00:06:09,005 --> 00:06:12,009
So here, we can have some 75s.

109
00:06:12,009 --> 00:06:15,002
All right, so far so good,

110
00:06:15,002 --> 00:06:17,009
but we can get a little more fancy than this.

111
00:06:17,009 --> 00:06:20,002
Let's highlight the entire row

112
00:06:20,002 --> 00:06:24,002
instead of just the Margin cell.

113
00:06:24,002 --> 00:06:28,004
So let's close this and go back to our code.

114
00:06:28,004 --> 00:06:31,004
So I'm going to make a couple of changes.

115
00:06:31,004 --> 00:06:35,000
First, I'm going to make a change to the type.

116
00:06:35,000 --> 00:06:39,005
So instead of a cell type, I'm going to use a formula type.

117
00:06:39,005 --> 00:06:44,003
And the criteria is going to be a formula

118
00:06:44,003 --> 00:06:48,003
where I'm going to specify that the value in column F

119
00:06:48,003 --> 00:06:54,003
starting at row 2 is greater than or equal to 0.75,

120
00:06:54,003 --> 00:06:58,001
which means we no longer need the value.

121
00:06:58,001 --> 00:07:00,000
We're going to leave the format as it is,

122
00:07:00,000 --> 00:07:03,003
but remember, we want to apply this to the entire row.

123
00:07:03,003 --> 00:07:05,004
So instead of having the start column

124
00:07:05,004 --> 00:07:07,007
and end column both be 5,

125
00:07:07,007 --> 00:07:09,004
I want the start column to be 0.

126
00:07:09,004 --> 00:07:12,009
So I'm going to change that.

127
00:07:12,009 --> 00:07:14,004
And that's all we need to do.

128
00:07:14,004 --> 00:07:18,009
So let's run this again.

129
00:07:18,009 --> 00:07:22,000
Okay, appears to have worked.

130
00:07:22,000 --> 00:07:24,007
Let's open this up.

131
00:07:24,007 --> 00:07:27,009
And now, we can see that the entire row

132
00:07:27,009 --> 00:07:32,002
is being highlighted when the criteria is met.

133
00:07:32,002 --> 00:07:34,001
So for every product

134
00:07:34,001 --> 00:07:37,005
where the product margin is greater than .75,

135
00:07:37,005 --> 00:07:41,004
the entire row is being highlighted.

136
00:07:41,004 --> 00:07:43,007
So you can find the detailed documentation

137
00:07:43,007 --> 00:07:47,003
for working with conditional formatting inside the docs.

138
00:07:47,003 --> 00:07:50,003
Let me flip over to those.

139
00:07:50,003 --> 00:07:52,008
So here at this link, you can learn a lot more

140
00:07:52,008 --> 00:07:55,005
about the conditional formatting feature.

141
00:07:55,005 --> 00:08:00,004
And if I scroll down a bit to the Options documentation,

142
00:08:00,004 --> 00:08:01,008
you can see that there are a lot

143
00:08:01,008 --> 00:08:04,000
of different options to explore,

144
00:08:04,000 --> 00:08:07,005
along with a variety of examples for each.

145
00:08:07,005 --> 00:08:09,000
So before moving on here,

146
00:08:09,000 --> 00:08:10,009
I would suggest maybe taking some time

147
00:08:10,009 --> 00:08:12,009
to try out some of the ones we didn't cover.

148
00:08:12,009 --> 00:08:16,000
Try starting with some of the text-based ones, for example.