1
00:00:00,005 --> 00:00:02,000
- [Instructor] One of the great features of Excel

2
00:00:02,000 --> 00:00:04,005
is the ability to apply conditional formatting

3
00:00:04,005 --> 00:00:08,000
to cell content based on certain conditions.

4
00:00:08,000 --> 00:00:10,009
This really helps identify specific data conditions

5
00:00:10,009 --> 00:00:14,005
when you have a large amount of data in a worksheet

6
00:00:14,005 --> 00:00:18,003
because it's much easier to process visual information.

7
00:00:18,003 --> 00:00:21,001
Openpyxl lets you add conditional formatting

8
00:00:21,001 --> 00:00:24,006
to your Excel data with some simple Python code.

9
00:00:24,006 --> 00:00:25,005
So for this example,

10
00:00:25,005 --> 00:00:28,004
we're going to use the FinancialSample Excel file

11
00:00:28,004 --> 00:00:32,003
and the data that's in the SalesData worksheet.

12
00:00:32,003 --> 00:00:36,003
Let's go ahead and open up our sample code.

13
00:00:36,003 --> 00:00:38,008
All right, so you can see here in my sample code,

14
00:00:38,008 --> 00:00:41,007
I've already got some code that loads the workbook

15
00:00:41,007 --> 00:00:44,003
and gets the SalesData worksheet.

16
00:00:44,003 --> 00:00:47,005
So first what we need to do is define some styles

17
00:00:47,005 --> 00:00:50,008
to represent the formats that will be applied

18
00:00:50,008 --> 00:00:53,008
by the conditions that we specify later.

19
00:00:53,008 --> 00:00:57,003
And we do this the same way that we do define styles

20
00:00:57,003 --> 00:00:59,000
as we saw earlier in the chapter,

21
00:00:59,000 --> 00:01:02,009
when we did the example on creating cell styles.

22
00:01:02,009 --> 00:01:06,004
So I'm going to define some styles for red bold text

23
00:01:06,004 --> 00:01:08,008
and a red pattern fill.

24
00:01:08,008 --> 00:01:10,005
And you can see that I've imported

25
00:01:10,005 --> 00:01:13,007
the Font and PatternFill classes here.

26
00:01:13,007 --> 00:01:18,004
So first let's create our red_color,

27
00:01:18,004 --> 00:01:24,005
and that's going to be the value ffd2d2.

28
00:01:24,005 --> 00:01:27,009
And then I'll make some bold_text,

29
00:01:27,009 --> 00:01:30,007
and I need to make a font object for that.

30
00:01:30,007 --> 00:01:32,009
So we'll set bold to true

31
00:01:32,009 --> 00:01:37,007
and the color equal to, in this case it's going to be red.

32
00:01:37,007 --> 00:01:45,006
So 00, it's going to be bright red for the text.

33
00:01:45,006 --> 00:01:51,002
And then for the red_fill, I'm going to create a PatternFill.

34
00:01:51,002 --> 00:01:53,000
And the background color for that

35
00:01:53,000 --> 00:01:56,008
is going to be the red color that we just defined.

36
00:01:56,008 --> 00:01:59,009
And the fill_type

37
00:01:59,009 --> 00:02:04,003
is going to be a solid fill.

38
00:02:04,003 --> 00:02:06,002
All right, so now I've got my styles

39
00:02:06,002 --> 00:02:08,000
and what I need to do now

40
00:02:08,000 --> 00:02:11,002
is create what's called a DifferentialStyle.

41
00:02:11,002 --> 00:02:13,005
And you can see that I've imported the DifferentialStyle

42
00:02:13,005 --> 00:02:16,008
from the appropriate openpyxl module here.

43
00:02:16,008 --> 00:02:19,002
This is the style that's going to be applied

44
00:02:19,002 --> 00:02:21,002
by the conditional formatter

45
00:02:21,002 --> 00:02:24,006
based upon conditional format rules, right?

46
00:02:24,006 --> 00:02:26,004
You can't use named styles

47
00:02:26,004 --> 00:02:29,001
when working with conditional formatting, right?

48
00:02:29,001 --> 00:02:33,003
The differential style is basically purpose built for this.

49
00:02:33,003 --> 00:02:36,003
So I'm going to create

50
00:02:36,003 --> 00:02:41,003
a diff_style

51
00:02:41,003 --> 00:02:45,000
and I'm going to build a DifferentialStyle,

52
00:02:45,000 --> 00:02:50,000
and the font is going to be the bold_text

53
00:02:50,000 --> 00:02:56,003
and the fill is going to be the red_fill.

54
00:02:56,003 --> 00:02:57,006
All right, so now I've got the styles

55
00:02:57,006 --> 00:03:02,006
that I want to use in my conditional style,

56
00:03:02,006 --> 00:03:04,004
I've created the DifferentialStyle

57
00:03:04,004 --> 00:03:07,008
that will be applied by the conditional formatting engine.

58
00:03:07,008 --> 00:03:10,005
So now I have to create the rule

59
00:03:10,005 --> 00:03:13,001
for the actual conditional format.

60
00:03:13,001 --> 00:03:17,003
And you can see that I've imported the Rule object

61
00:03:17,003 --> 00:03:21,000
from the formatting module.

62
00:03:21,000 --> 00:03:24,007
So this is how we define rules for conditional formatting.

63
00:03:24,007 --> 00:03:27,000
All right, I'm going to create a rule

64
00:03:27,000 --> 00:03:33,000
that triggers when the Profit column is less than $10,000.

65
00:03:33,000 --> 00:03:36,001
So my rule is going to be a new rule

66
00:03:36,001 --> 00:03:42,002
and the type of that rule is going to be an expression.

67
00:03:42,002 --> 00:03:44,001
And then we pass our DifferentialStyle

68
00:03:44,001 --> 00:03:46,006
in the dxf parameter,

69
00:03:46,006 --> 00:03:50,002
so that's going to be my diff_style.

70
00:03:50,002 --> 00:03:53,001
And then I'm going to specify the formula.

71
00:03:53,001 --> 00:03:57,007
So the formula is going to be inside some brackets

72
00:03:57,007 --> 00:03:59,007
and I'm going to pass a string,

73
00:03:59,007 --> 00:04:02,002
and the string is going to be in Excel format.

74
00:04:02,002 --> 00:04:05,007
So I'm going to say $L1

75
00:04:05,007 --> 00:04:10,000
is less than 10,000.

76
00:04:10,000 --> 00:04:13,002
All right, I want to apply the rule

77
00:04:13,002 --> 00:04:16,008
so that the entire row has the style applied

78
00:04:16,008 --> 00:04:18,004
when the condition is met.

79
00:04:18,004 --> 00:04:21,000
Okay, I don't want to just apply this rule

80
00:04:21,000 --> 00:04:22,009
just to the cell itself.

81
00:04:22,009 --> 00:04:25,002
What I'm going to do is specify that the rule

82
00:04:25,002 --> 00:04:29,002
implies to the entire dimensions of the worksheet data.

83
00:04:29,002 --> 00:04:33,005
So to do that I'm going to create a variable called dimensions

84
00:04:33,005 --> 00:04:38,001
and we're going to set that to be the sheet.dimensions for now.

85
00:04:38,001 --> 00:04:40,005
And then on the sheet itself,

86
00:04:40,005 --> 00:04:44,003
I'm going to use the conditional_formatting property

87
00:04:44,003 --> 00:04:47,001
and I'm going to call the add function,

88
00:04:47,001 --> 00:04:54,000
and on those dimensions I'm going to add my rule.

89
00:04:54,000 --> 00:04:56,000
All right, so that's pretty much all I need to do,

90
00:04:56,000 --> 00:04:57,003
and you probably noticed that I'm using

91
00:04:57,003 --> 00:04:59,005
the absolute addressing here in Excel,

92
00:04:59,005 --> 00:05:01,004
but only on the column.

93
00:05:01,004 --> 00:05:04,007
And that's because I want the row to auto increment

94
00:05:04,007 --> 00:05:08,007
and apply this rule to each one of the rows

95
00:05:08,007 --> 00:05:10,006
in this particular column.

96
00:05:10,006 --> 00:05:12,002
So the column is never going to change,

97
00:05:12,002 --> 00:05:14,004
but the row will.

98
00:05:14,004 --> 00:05:15,009
Right, so that's why I'm not putting the dollar sign

99
00:05:15,009 --> 00:05:17,004
in front of the 1.

100
00:05:17,004 --> 00:05:23,002
So let's go ahead and save our code.

101
00:05:23,002 --> 00:05:26,000
All right, and then let's run this.

102
00:05:26,000 --> 00:05:30,003
So I'll run it in the terminal.

103
00:05:30,003 --> 00:05:32,000
And when I do, we can see

104
00:05:32,000 --> 00:05:35,004
that the CondFormat XLS file has been created,

105
00:05:35,004 --> 00:05:44,003
so let's hop on over to Excel and open that up.

106
00:05:44,003 --> 00:05:45,007
All right, so sure enough

107
00:05:45,007 --> 00:05:49,008
we can see that in the case where the Profit column,

108
00:05:49,008 --> 00:05:50,006
where is that?

109
00:05:50,006 --> 00:05:55,003
That is right here where it's column L, here's the profit.

110
00:05:55,003 --> 00:05:59,009
So in all the cases where is less than 10,000,

111
00:05:59,009 --> 00:06:03,002
right, we have the entire row for that case

112
00:06:03,002 --> 00:06:08,007
is highlighted with the sort of a subdued red pattern fill

113
00:06:08,007 --> 00:06:12,003
and the bright red bold text, right?

114
00:06:12,003 --> 00:06:13,004
So as we scroll down

115
00:06:13,004 --> 00:06:16,007
you can see all of the rows that are being affected.

116
00:06:16,007 --> 00:06:18,004
Now, of course, if I wanted to just apply

117
00:06:18,004 --> 00:06:21,000
the conditional format to only one column,

118
00:06:21,000 --> 00:06:23,003
I could just specify those dimensions.

119
00:06:23,003 --> 00:06:26,007
So let's just close this

120
00:06:26,007 --> 00:06:29,004
and let's go back to our code.

121
00:06:29,004 --> 00:06:32,006
All right, and let's change dimensions

122
00:06:32,006 --> 00:06:36,004
to be L1

123
00:06:36,004 --> 00:06:41,006
:L701, right?

124
00:06:41,006 --> 00:06:44,005
That will cover all the rows.

125
00:06:44,005 --> 00:06:48,007
So let's go ahead and run this again.

126
00:06:48,007 --> 00:06:51,000
All right, so now we've updated our file,

127
00:06:51,000 --> 00:06:55,005
let's go back out to Excel and reopen.

128
00:06:55,005 --> 00:06:58,000
And sure enough, when I scroll over to the Profit column,

129
00:06:58,000 --> 00:07:01,003
now we can see that only column L

130
00:07:01,003 --> 00:07:04,001
is having the style applied

131
00:07:04,001 --> 00:07:07,000
when the value is less than 10,000.