1
00:00:00,005 --> 00:00:02,001
- [Instructor] Let's use some of the formatting features

2
00:00:02,001 --> 00:00:03,004
to apply styling

3
00:00:03,004 --> 00:00:05,007
to the cells in the worksheet.

4
00:00:05,007 --> 00:00:10,005
So let's open up our sample file, and that's XLSX format.

5
00:00:10,005 --> 00:00:12,007
And here you can see I have a subset

6
00:00:12,007 --> 00:00:16,006
of the inventory.csv data.

7
00:00:16,006 --> 00:00:19,004
And I've already created a workbook

8
00:00:19,004 --> 00:00:22,002
and added a worksheet to it.

9
00:00:22,002 --> 00:00:23,007
And I have some code

10
00:00:23,007 --> 00:00:28,002
that already writes some values into the cells.

11
00:00:28,002 --> 00:00:30,007
So before we add our styling code,

12
00:00:30,007 --> 00:00:34,005
let's run what we already have in its existing state.

13
00:00:34,005 --> 00:00:37,006
So I'm going to run this in my terminal,

14
00:00:37,006 --> 00:00:38,004
and when I do that,

15
00:00:38,004 --> 00:00:42,006
you can see that inventory.xlsx gets created.

16
00:00:42,006 --> 00:00:47,000
So let's go ahead and open that up in Excel.

17
00:00:47,000 --> 00:00:49,001
And when I open the resulting workbook,

18
00:00:49,001 --> 00:00:51,006
you can see that the data is here

19
00:00:51,006 --> 00:00:55,008
and it is in plain text format without any styling.

20
00:00:55,008 --> 00:00:58,008
Okay, so let's go ahead and close this.

21
00:00:58,008 --> 00:01:01,008
All right, let's go back to our code.

22
00:01:01,008 --> 00:01:03,003
So now let's add the formatting,

23
00:01:03,003 --> 00:01:05,001
and similar to OpenPyXL,

24
00:01:05,001 --> 00:01:06,009
this is a two step process.

25
00:01:06,009 --> 00:01:09,005
First, I have to define the styles I want to use

26
00:01:09,005 --> 00:01:13,003
and then I can add them when I'm writing the data.

27
00:01:13,003 --> 00:01:15,004
So what I'm going to do is define two styles,

28
00:01:15,004 --> 00:01:19,007
one for bold text and one to represent currency.

29
00:01:19,007 --> 00:01:23,009
So the first one, I'll call fmt_bold.

30
00:01:23,009 --> 00:01:29,001
And to do this, what I do is I call the add_format function

31
00:01:29,001 --> 00:01:32,000
and that takes a dictionary of properties.

32
00:01:32,000 --> 00:01:32,008
So in this case,

33
00:01:32,008 --> 00:01:40,008
I'll set the bold property to true.

34
00:01:40,008 --> 00:01:45,002
And then for the money format,

35
00:01:45,002 --> 00:01:49,000
I'll add another format.

36
00:01:49,000 --> 00:01:52,003
And for these settings, I'm going to do two things.

37
00:01:52,003 --> 00:01:58,006
I'm going to define a font color,

38
00:01:58,006 --> 00:02:02,000
and that's going to be green.

39
00:02:02,000 --> 00:02:09,008
And I'm going to define a num format.

40
00:02:09,008 --> 00:02:13,006
That is going to be a currency format in Excel

41
00:02:13,006 --> 00:02:18,007
that looks like this.

42
00:02:18,007 --> 00:02:21,004
Okay, that looks correct.

43
00:02:21,004 --> 00:02:23,009
So once I have these styles defined,

44
00:02:23,009 --> 00:02:27,006
I can use them as I'm writing the data to the worksheet.

45
00:02:27,006 --> 00:02:30,001
So let's update the write functions

46
00:02:30,001 --> 00:02:32,001
to incorporate the styles.

47
00:02:32,001 --> 00:02:37,001
So first, you can see here in the first write,

48
00:02:37,001 --> 00:02:39,006
I'm calling write_row to write out the headers.

49
00:02:39,006 --> 00:02:43,006
So let's use fmt_bold

50
00:02:43,006 --> 00:02:46,003
to make the headers all bold.

51
00:02:46,003 --> 00:02:49,007
Let's also make the category bold.

52
00:02:49,007 --> 00:02:52,007
So that's going to be the second category there.

53
00:02:52,007 --> 00:02:56,006
So that's going to be fmt_bold.

54
00:02:56,006 --> 00:02:59,000
And then let's have the prices be formatted

55
00:02:59,000 --> 00:03:02,009
as currency in the green color.

56
00:03:02,009 --> 00:03:09,000
So three and four are going to be fmt_money.

57
00:03:09,000 --> 00:03:15,002
So each of those will get that format.

58
00:03:15,002 --> 00:03:19,008
All right, and then let's use one other function

59
00:03:19,008 --> 00:03:22,003
that XlsxWriter gives us.

60
00:03:22,003 --> 00:03:24,001
We're going to use the autofit function

61
00:03:24,001 --> 00:03:31,000
to make the columns automatically fit the content.

62
00:03:31,000 --> 00:03:35,004
This is a nice little convenience function.

63
00:03:35,004 --> 00:03:38,006
And then we set the zoom and then we close the workbook.

64
00:03:38,006 --> 00:03:42,001
All right, so let's save our updated example.

65
00:03:42,001 --> 00:03:46,002
And now let's run the code again.

66
00:03:46,002 --> 00:03:49,002
And this will overwrite the existing workbook.

67
00:03:49,002 --> 00:03:52,008
Make sure that you have it closed in Excel, by the way.

68
00:03:52,008 --> 00:03:55,004
So I'm going to run this again.

69
00:03:55,004 --> 00:03:58,004
All right, so now our workbook has been updated,

70
00:03:58,004 --> 00:04:04,009
so let's jump over to Excel and see the results.

71
00:04:04,009 --> 00:04:05,007
All right, there we go.

72
00:04:05,007 --> 00:04:09,006
So we can see now that the styling has been applied.

73
00:04:09,006 --> 00:04:12,008
So all of the headers are bold, which is great.

74
00:04:12,008 --> 00:04:15,006
The category field is also bold,

75
00:04:15,006 --> 00:04:19,005
and sure enough, the price fields are green

76
00:04:19,005 --> 00:04:23,002
and formatted as US dollars.

77
00:04:23,002 --> 00:04:27,004
And the columns are all now autofitting the width

78
00:04:27,004 --> 00:04:30,003
of the content that's in that column.

79
00:04:30,003 --> 00:04:33,005
Now of course this example just scratches the surface.

80
00:04:33,005 --> 00:04:36,000
So let me jump over to the documentation really quick

81
00:04:36,000 --> 00:04:39,000
for the format class.

82
00:04:39,000 --> 00:04:41,001
So if you go to this link in your browser,

83
00:04:41,001 --> 00:04:44,001
you can see the documentation for the format class.

84
00:04:44,001 --> 00:04:47,000
And I would suggest taking some time to try out

85
00:04:47,000 --> 00:04:48,006
some of your own experiments.

86
00:04:48,006 --> 00:04:51,006
So for example, if I scroll down to the format methods

87
00:04:51,006 --> 00:04:53,004
and format properties,

88
00:04:53,004 --> 00:04:55,006
you can see that there's a whole bunch

89
00:04:55,006 --> 00:04:57,001
of properties that you can try out.

90
00:04:57,001 --> 00:04:58,008
Now I only tried a couple,

91
00:04:58,008 --> 00:05:02,001
so try out a few of these on your own.

92
00:05:02,001 --> 00:05:05,007
And there's also a fair amount of number formatting options.

93
00:05:05,007 --> 00:05:09,003
If I click on number format categories,

94
00:05:09,003 --> 00:05:10,005
I scroll down a little bit,

95
00:05:10,005 --> 00:05:13,000
you can see that there's a fair number of options here

96
00:05:13,000 --> 00:05:15,001
that you can also try.

97
00:05:15,001 --> 00:05:17,003
And basically what you do is you specify

98
00:05:17,003 --> 00:05:19,000
the number formatting string the same way

99
00:05:19,000 --> 00:05:20,002
that Excel does it.

100
00:05:20,002 --> 00:05:21,008
So just go into Excel

101
00:05:21,008 --> 00:05:24,001
and you can click in this dialogue to see

102
00:05:24,001 --> 00:05:25,007
what the formatting string is.

103
00:05:25,007 --> 00:05:26,008
Just put that into your code,

104
00:05:26,008 --> 00:05:30,007
and then that's how the number format will be set in Excel.

105
00:05:30,007 --> 00:05:33,008
So before moving on, I would consider taking some time here

106
00:05:33,008 --> 00:05:35,004
and trying some of these out

107
00:05:35,004 --> 00:05:37,007
to see the results using this example code

108
00:05:37,007 --> 00:05:39,000
as a starting point.