- [Instructor] Let's use some of the formatting features
to apply styling
to the cells in the worksheet.
So let's open up our sample file, and that's XLSX format.
And here you can see I have a subset
of the inventory.csv data.
And I've already created a workbook
and added a worksheet to it.
And I have some code
that already writes some values into the cells.
So before we add our styling code,
let's run what we already have in its existing state.
So I'm going to run this in my terminal,
and when I do that,
you can see that inventory.xlsx gets created.
So let's go ahead and open that up in Excel.
And when I open the resulting workbook,
you can see that the data is here
and it is in plain text format without any styling.
Okay, so let's go ahead and close this.
All right, let's go back to our code.
So now let's add the formatting,
and similar to OpenPyXL,
this is a two step process.
First, I have to define the styles I want to use
and then I can add them when I'm writing the data.
So what I'm going to do is define two styles,
one for bold text and one to represent currency.
So the first one, I'll call fmt_bold.
And to do this, what I do is I call the add_format function
and that takes a dictionary of properties.
So in this case,
I'll set the bold property to true.
And then for the money format,
I'll add another format.
And for these settings, I'm going to do two things.
I'm going to define a font color,
and that's going to be green.
And I'm going to define a num format.
That is going to be a currency format in Excel
that looks like this.
Okay, that looks correct.
So once I have these styles defined,
I can use them as I'm writing the data to the worksheet.
So let's update the write functions
to incorporate the styles.
So first, you can see here in the first write,
I'm calling write_row to write out the headers.
So let's use fmt_bold
to make the headers all bold.
Let's also make the category bold.
So that's going to be the second category there.
So that's going to be fmt_bold.
And then let's have the prices be formatted
as currency in the green color.
So three and four are going to be fmt_money.
So each of those will get that format.
All right, and then let's use one other function
that XlsxWriter gives us.
We're going to use the autofit function
to make the columns automatically fit the content.
This is a nice little convenience function.
And then we set the zoom and then we close the workbook.
All right, so let's save our updated example.
And now let's run the code again.
And this will overwrite the existing workbook.
Make sure that you have it closed in Excel, by the way.
So I'm going to run this again.
All right, so now our workbook has been updated,
so let's jump over to Excel and see the results.
All right, there we go.
So we can see now that the styling has been applied.
So all of the headers are bold, which is great.
The category field is also bold,
and sure enough, the price fields are green
and formatted as US dollars.
And the columns are all now autofitting the width
of the content that's in that column.
Now of course this example just scratches the surface.
So let me jump over to the documentation really quick
for the format class.
So if you go to this link in your browser,
you can see the documentation for the format class.
And I would suggest taking some time to try out
some of your own experiments.
So for example, if I scroll down to the format methods
and format properties,
you can see that there's a whole bunch
of properties that you can try out.
Now I only tried a couple,
so try out a few of these on your own.
And there's also a fair amount of number formatting options.
If I click on number format categories,
I scroll down a little bit,
you can see that there's a fair number of options here
that you can also try.
And basically what you do is you specify
the number formatting string the same way
that Excel does it.
So just go into Excel
and you can click in this dialogue to see
what the formatting string is.
Just put that into your code,
and then that's how the number format will be set in Excel.
So before moving on, I would consider taking some time here
and trying some of these out
to see the results using this example code
as a starting point.