- [Instructor] Let's start by using OpenPyXL
to load and explore some data in a workbook.
I'm going to be using this sample Excel file right here
named Financial Sample.xlsx.
Now, if you don't have Excel,
you can install an extension for VS Code
called Excel Viewer, which I already have installed.
So I'm going to go over to my extensions panel
and scroll down,
and you can see it's called Excel Viewer right here.
It's by Grape City.
You can see it has millions of downloads
and some very high ratings on the store.
You can find this extension in the marketplace
by doing a search for it,
like we did earlier for the Python extension, and it's free.
So go ahead and install it,
and you can see
that when I click on an Excel file here in code,
I actually get this nice visualization
of the contents of the workbook
without having to use the full Excel program.
And like I said, it's free.
It works really well.
So I suggest installing it if you can't use Excel.
Now, I do have Excel on my computer,
so I'm going to open this file up in Excel
and we can take a look at its structure and content.
So you can see that this example file
contains a sales data worksheet
as well as an empty worksheet,
and the sales data worksheet contains several hundred rows
of sales information for a fictional company.
If we scroll down,
you can see there's quite a lot of data in this worksheet,
about 700 rows' worth,
and there are 16 columns that represent information
such as market segment, country, sales amount,
cost of goods, profit, so on.
And like I said,
I currently have a second blank worksheet in here
just for example purposes that we'll see later on.
For now, let's write some code to open this workbook
and explore its content, right?
So I'm going to go ahead and close this.
Alright, so back here in code,
let's open up our example file.
So we're going to open up explore_workbook.py,
and you can see that I've imported the OpenPyXL library,
and I have a variable for the name of the file.
To load the workbook,
I'm going to use the load workbook function
and that's going to return a workbook object.
So I'll create a variable for that,
and I'll call open OpenPyXL,
and I'll use the load workbook function
and I'll pass the file name.
Alright, so that will load the workbook,
so let's explore some of the data that's in the workbook.
First, let's print out the number and names
of the worksheets that the workbook contains.
So we'll print out the number of worksheets,
and that's going to be the length of the workbook,
and it has a property called sheet names,
and then let's print out each worksheet name.
So I'll write for worksheet_name in
and I'll use that sheet name's variable again.
I'll assign a worksheet variable to the workbook,
and then I can use brackets to index each worksheet name
to get a reference to that particular worksheet.
So I'll get the worksheet that has that name,
and then let's print out some information about it.
So we'll say that the worksheet is,
and we'll print out the worksheet name.
Alright, that's a pretty good start so far,
so let's go ahead and save and let's run this.
So I'll just run this in the terminal.
Alright, so far so good.
We can see that there are two worksheets
and one is called SalesData and one is called Sheet2,
so those are the correct names, so, so far so good.
Let's keep going.
We can get the dimensions of a worksheet
by using the dimensions property.
So let's create a variable named dimensions,
and that's going to be equal to worksheet,
and there's a dimensions property that we can examine.
Let's go ahead and print that out.
Let's give ourselves some spacing here,
and I'll write dimensions,
and that's going to be the dimensions variable,
and we can inspect properties such as the min and max rows
and the columns that have data in them.
So let's print out that information as well.
So I'll print out the minimum row
and that's going to be the worksheet's min_row property,
and I'll do the same thing for max_row,
and then let's do the same thing for columns.
So I'll make a couple of copies there,
so this this'll be min column and max column,
and I'll just change that to min_column and max_column.
Alright.
We can also get data from the worksheet, if it has any.
So let's add some code to test if the worksheet is empty,
and if it's not, then we'll get some data.
So first, let's check to see
if the worksheet's max row is equal to one,
and the worksheet's max column is equal to one.
That will tell us that the worksheet is empty.
So we'll print out, "Worksheet is empty,"
if that's the case.
Otherwise.
Right, now, I can use either letter and number syntax
like you normally see in Excel,
or I can use row and column index numbers
via the cell function.
So let's create a cell variable,
and I'll use the worksheet bracket notation,
and I'll just get cell A1, and I'll print out its content.
So the top-left cell value is,
and I'm going to take that cell reference
and get the value out of it.
I can also do the same thing
using my worksheet cell function,
and in this case, what I'm going to do
is get the absolute last cell in the sheet
at the max row and the max column.
So I'll pass in row is equal to worksheet.max_row,
column is equal to worksheet.max_column,
and then we'll print that.
So the bottom-right cell value,
and once again, we'll write out the cell value.
Alright, so we've added a bunch of new code
to get the dimensions.
We're going to get the min and max rows and columns.
We're going to check to see if the worksheet is empty.
If not, we'll get some data out of it,
so let's go ahead and run what we have.
So I'll right click and choose run in the terminal,
and let's make this a little bit larger.
Okay, so we've got two worksheets
and the sales data worksheet,
we can see that the dimensions are A1
all the way down to P701.
So we've got 701 rows, we've got 16 columns.
The top-left cell value is segment.
Is that right?
Let's take a look.
Yep, sure enough, segment,
and then the bottom right cell value is 2014.
Alright, so the P row, we scroll all the way down.
Yep, sure enough, 2014 is correct,
and Worksheet 2, dimensions are A1 to A1,
so we can see that that sheet indeed is empty.
Alright, so that's a quick introduction
to reading and exploring a workbook using OpenPyXL.