1
00:00:00,005 --> 00:00:03,004
- [Instructor] Excel tables are a common way of organizing

2
00:00:03,004 --> 00:00:05,004
and formatting data in a spreadsheet

3
00:00:05,004 --> 00:00:08,004
to make analyzing the data easier.

4
00:00:08,004 --> 00:00:09,009
And in this example, we're going to see how

5
00:00:09,009 --> 00:00:13,006
to create an Excel table using XLSXWriter.

6
00:00:13,006 --> 00:00:15,007
And we're going to build on our previous

7
00:00:15,007 --> 00:00:17,000
formatting examples.

8
00:00:17,000 --> 00:00:18,006
So if you haven't already done that one,

9
00:00:18,006 --> 00:00:21,000
I would suggest going back and doing it.

10
00:00:21,000 --> 00:00:26,001
So, let's go ahead and open up our XLSX tables file.

11
00:00:26,001 --> 00:00:29,002
Right, so here in the example file, I already have some code

12
00:00:29,002 --> 00:00:33,006
that creates the workbook and defines some formatting

13
00:00:33,006 --> 00:00:36,007
and then adds the data to the worksheet.

14
00:00:36,007 --> 00:00:39,004
So, to create an Excel table,

15
00:00:39,004 --> 00:00:42,004
I need to first define some table options

16
00:00:42,004 --> 00:00:45,008
and then create the region that represents the table.

17
00:00:45,008 --> 00:00:47,009
So first, let's create the object

18
00:00:47,009 --> 00:00:51,001
that will define the table options.

19
00:00:51,001 --> 00:00:54,009
And I'll call that "table_options".

20
00:00:54,009 --> 00:01:00,001
And for the moment, I'll just specify the table name.

21
00:01:00,001 --> 00:01:04,000
To define the table, I'll use the add table function,

22
00:01:04,000 --> 00:01:07,002
which requires me to specify the region of the worksheet

23
00:01:07,002 --> 00:01:09,004
that represents the table.

24
00:01:09,004 --> 00:01:11,006
And I'll do that right below here.

25
00:01:11,006 --> 00:01:17,001
I'll call "worksheet." and I'll call "add_table".

26
00:01:17,001 --> 00:01:19,004
And then I'm going to define the region

27
00:01:19,004 --> 00:01:21,005
that's going to represent the table

28
00:01:21,005 --> 00:01:23,003
and just for the sake of time,

29
00:01:23,003 --> 00:01:27,000
I'm just going to specify it's "A1:E6"

30
00:01:27,000 --> 00:01:29,008
because I know how big the data is.

31
00:01:29,008 --> 00:01:36,002
And then I'm going to write the table options.

32
00:01:36,002 --> 00:01:37,005
Alright, so now let's go back and add

33
00:01:37,005 --> 00:01:39,006
some more table options.

34
00:01:39,006 --> 00:01:42,005
So, after the name, I'm going to specify

35
00:01:42,005 --> 00:01:44,005
that I want some autofiltering,

36
00:01:44,005 --> 00:01:47,005
and that's going to be true.

37
00:01:47,005 --> 00:01:54,003
I'm going to specify that I want banded rows,

38
00:01:54,003 --> 00:01:57,007
and that will be false,

39
00:01:57,007 --> 00:02:00,006
and I'm going to specify that the first column

40
00:02:00,006 --> 00:02:03,009
should be highlighted.

41
00:02:03,009 --> 00:02:06,009
So that's true.

42
00:02:06,009 --> 00:02:10,006
Okay, so what we have now is a complete example

43
00:02:10,006 --> 00:02:14,000
where we write out some data, we have some formatting,

44
00:02:14,000 --> 00:02:17,002
and what we've done is defined a table.

45
00:02:17,002 --> 00:02:19,003
So let's save

46
00:02:19,003 --> 00:02:24,005
and let's run this in the terminal.

47
00:02:24,005 --> 00:02:27,008
Okay, we can see that "Tables.xlsx" has been created,

48
00:02:27,008 --> 00:02:33,003
so let's open that up in Excel.

49
00:02:33,003 --> 00:02:35,002
Alright, so when I open this in Excel,

50
00:02:35,002 --> 00:02:38,001
you can see that the table has been created,

51
00:02:38,001 --> 00:02:40,009
but, hmm, we seem to have a problem.

52
00:02:40,009 --> 00:02:43,001
The column names aren't correct.

53
00:02:43,001 --> 00:02:48,000
It just says "Column1, Column2, Column3," et cetera.

54
00:02:48,000 --> 00:02:51,004
So to fix this, I need to define the column names

55
00:02:51,004 --> 00:02:53,005
when I create the table.

56
00:02:53,005 --> 00:02:58,000
Right, let's close this and go back to our code.

57
00:02:58,000 --> 00:03:00,003
And then below the other properties,

58
00:03:00,003 --> 00:03:06,009
I'm going to specify a columns property.

59
00:03:06,009 --> 00:03:11,007
And that's going to be an array of options.

60
00:03:11,007 --> 00:03:15,006
So, I'm going to specify that the header

61
00:03:15,006 --> 00:03:19,008
for each of the columns should be the same

62
00:03:19,008 --> 00:03:24,001
as the very first item in row zero of the data.

63
00:03:24,001 --> 00:03:26,000
And remember, row zero of the data

64
00:03:26,000 --> 00:03:28,004
are these category names here.

65
00:03:28,004 --> 00:03:31,008
So I'm going to do that by specifying that

66
00:03:31,008 --> 00:03:34,007
the first row in data

67
00:03:34,007 --> 00:03:39,000
and the first item should be the item name.

68
00:03:39,000 --> 00:03:41,007
And then I'm just going to do this five times

69
00:03:41,007 --> 00:03:45,006
for each of the columns.

70
00:03:45,006 --> 00:03:50,005
So I'm just going to increment the index each time.

71
00:03:50,005 --> 00:03:52,008
Alright.

72
00:03:52,008 --> 00:03:55,009
Okay, so now let's save

73
00:03:55,009 --> 00:04:02,001
and let's run the updated code.

74
00:04:02,001 --> 00:04:07,002
Alright, and once again, let's pop over to Excel.

75
00:04:07,002 --> 00:04:08,007
Okay, and now we can see

76
00:04:08,007 --> 00:04:11,005
that the table has been properly created

77
00:04:11,005 --> 00:04:14,000
and now it has the right column names,

78
00:04:14,000 --> 00:04:16,009
and the table has automatic filters applied

79
00:04:16,009 --> 00:04:19,000
to the header row, and you can see

80
00:04:19,000 --> 00:04:20,007
that the table is not banded, right,

81
00:04:20,007 --> 00:04:22,001
because that setting is false,

82
00:04:22,001 --> 00:04:23,006
but the first column is highlighted

83
00:04:23,006 --> 00:04:26,000
because that setting is true.

84
00:04:26,000 --> 00:04:28,004
Now, of course, we're not just limited to these options.

85
00:04:28,004 --> 00:04:30,000
We can try other things.

86
00:04:30,000 --> 00:04:32,004
Let's try specifying a table style.

87
00:04:32,004 --> 00:04:33,006
So let's close this,

88
00:04:33,006 --> 00:04:37,001
and once again, let's go back to our code.

89
00:04:37,001 --> 00:04:40,001
And what I'm going to do is specify

90
00:04:40,001 --> 00:04:43,002
the table style is going to be...

91
00:04:43,002 --> 00:04:44,008
And again, you can use any one

92
00:04:44,008 --> 00:04:48,009
of the predefined Excel table styles here.

93
00:04:48,009 --> 00:04:58,000
So I'm going to specify "Table Style Light 9".

94
00:04:58,000 --> 00:04:59,008
And since I'm defining a style,

95
00:04:59,008 --> 00:05:03,002
let's comment out the banded rows and first column

96
00:05:03,002 --> 00:05:05,005
because they won't apply right now.

97
00:05:05,005 --> 00:05:13,003
So, alright, let's save and let's run this again.

98
00:05:13,003 --> 00:05:18,006
Alright, and now let's open it up in Excel again.

99
00:05:18,006 --> 00:05:20,008
Yeah, now we can see that the, sure enough...

100
00:05:20,008 --> 00:05:22,002
Where's the table style?

101
00:05:22,002 --> 00:05:25,005
If we go to table design and we click on...

102
00:05:25,005 --> 00:05:26,003
Yep, sure enough, we can see

103
00:05:26,003 --> 00:05:29,008
that's Table Style Light 9 right there.

104
00:05:29,008 --> 00:05:35,003
So, we still have our bold fruits category,

105
00:05:35,003 --> 00:05:37,006
but the table's not banded, right?

106
00:05:37,006 --> 00:05:39,001
We can try changing this.

107
00:05:39,001 --> 00:05:42,003
You can substitute any of the name table styles in Excel

108
00:05:42,003 --> 00:05:43,004
for that parameters.

109
00:05:43,004 --> 00:05:45,007
Let's go ahead and hover over the name again.

110
00:05:45,007 --> 00:05:48,003
Let's see, let's try this purple one right here.

111
00:05:48,003 --> 00:05:49,006
Actually, let's try this one.

112
00:05:49,006 --> 00:05:52,000
This is lavender...

113
00:05:52,000 --> 00:05:55,002
This is "Table Style Light 19".

114
00:05:55,002 --> 00:05:59,008
So let's go back to the code and try that.

115
00:05:59,008 --> 00:06:01,007
Table Style Light 19.

116
00:06:01,007 --> 00:06:04,006
Let's save.

117
00:06:04,006 --> 00:06:08,008
Let's run.

118
00:06:08,008 --> 00:06:11,003
Alright, let's open.

119
00:06:11,003 --> 00:06:14,000
Yep, and sure enough, the new style has been applied.

120
00:06:14,000 --> 00:06:17,005
So, Excel tables are really useful for displaying groups

121
00:06:17,005 --> 00:06:19,008
of data, and you can see that creating them

122
00:06:19,008 --> 00:06:23,000
with XLSXWriter is really easy.