- [Instructor] Excel tables are a common way of organizing
and formatting data in a spreadsheet
to make analyzing the data easier.
And in this example, we're going to see how
to create an Excel table using XLSXWriter.
And we're going to build on our previous
formatting examples.
So if you haven't already done that one,
I would suggest going back and doing it.
So, let's go ahead and open up our XLSX tables file.
Right, so here in the example file, I already have some code
that creates the workbook and defines some formatting
and then adds the data to the worksheet.
So, to create an Excel table,
I need to first define some table options
and then create the region that represents the table.
So first, let's create the object
that will define the table options.
And I'll call that "table_options".
And for the moment, I'll just specify the table name.
To define the table, I'll use the add table function,
which requires me to specify the region of the worksheet
that represents the table.
And I'll do that right below here.
I'll call "worksheet." and I'll call "add_table".
And then I'm going to define the region
that's going to represent the table
and just for the sake of time,
I'm just going to specify it's "A1:E6"
because I know how big the data is.
And then I'm going to write the table options.
Alright, so now let's go back and add
some more table options.
So, after the name, I'm going to specify
that I want some autofiltering,
and that's going to be true.
I'm going to specify that I want banded rows,
and that will be false,
and I'm going to specify that the first column
should be highlighted.
So that's true.
Okay, so what we have now is a complete example
where we write out some data, we have some formatting,
and what we've done is defined a table.
So let's save
and let's run this in the terminal.
Okay, we can see that "Tables.xlsx" has been created,
so let's open that up in Excel.
Alright, so when I open this in Excel,
you can see that the table has been created,
but, hmm, we seem to have a problem.
The column names aren't correct.
It just says "Column1, Column2, Column3," et cetera.
So to fix this, I need to define the column names
when I create the table.
Right, let's close this and go back to our code.
And then below the other properties,
I'm going to specify a columns property.
And that's going to be an array of options.
So, I'm going to specify that the header
for each of the columns should be the same
as the very first item in row zero of the data.
And remember, row zero of the data
are these category names here.
So I'm going to do that by specifying that
the first row in data
and the first item should be the item name.
And then I'm just going to do this five times
for each of the columns.
So I'm just going to increment the index each time.
Alright.
Okay, so now let's save
and let's run the updated code.
Alright, and once again, let's pop over to Excel.
Okay, and now we can see
that the table has been properly created
and now it has the right column names,
and the table has automatic filters applied
to the header row, and you can see
that the table is not banded, right,
because that setting is false,
but the first column is highlighted
because that setting is true.
Now, of course, we're not just limited to these options.
We can try other things.
Let's try specifying a table style.
So let's close this,
and once again, let's go back to our code.
And what I'm going to do is specify
the table style is going to be...
And again, you can use any one
of the predefined Excel table styles here.
So I'm going to specify "Table Style Light 9".
And since I'm defining a style,
let's comment out the banded rows and first column
because they won't apply right now.
So, alright, let's save and let's run this again.
Alright, and now let's open it up in Excel again.
Yeah, now we can see that the, sure enough...
Where's the table style?
If we go to table design and we click on...
Yep, sure enough, we can see
that's Table Style Light 9 right there.
So, we still have our bold fruits category,
but the table's not banded, right?
We can try changing this.
You can substitute any of the name table styles in Excel
for that parameters.
Let's go ahead and hover over the name again.
Let's see, let's try this purple one right here.
Actually, let's try this one.
This is lavender...
This is "Table Style Light 19".
So let's go back to the code and try that.
Table Style Light 19.
Let's save.
Let's run.
Alright, let's open.
Yep, and sure enough, the new style has been applied.
So, Excel tables are really useful for displaying groups
of data, and you can see that creating them
with XLSXWriter is really easy.