- [Instructor] In this example,
we're going to look at some different ways
to access the content within a worksheet.
So let's open the example file workbook_content.
And you can see I already have some code
that loads the FinancialSample Excel file
that we've been using.
And it gets the first worksheet.
We've seen some simple examples of how
to access worksheet content already,
and I'll expand on those methods here.
So first, let's take a look at how to get access
to a range of cells.
You can get an entire column
or row of cells by using either a letter
or numeric index respectively.
So to get all the cells in column C
or all the cells in row 10,
I would write some code like this.
I can col sheet
and just pass in the letter C for the entire column,
or I can get an entire row by using a numerical index.
And then once we've done that,
let's just print out some information about
that column and row.
So we'll print out the length of column,
cells in the column,
and then let's do the same thing for row,
and that will be cells in the row.
All right, so let's go ahead and try that out.
Let's save this and let's run it.
And sure enough, there are 700 cells in the column
and 16 cells in the row.
I can also get a range of cells using the range syntax.
So let's take a look at that.
So for example, I can declare a variable named range
and I will ask the sheet to give me a range of cells.
So I'll get the range from A2
and I'm going to use a colon to specify
the second part of the range, which will be B7.
And then once again, let's go ahead
and print out some similar information.
So I'll print out the length of that range.
All right, and then let's go ahead
and print out the range itself.
All right, so let's save that.
Let's comment out our previous example.
I'm going to type control slash
to comment all those lines at once.
And let's run this.
All right, now you can see that when I do that,
I get the number of cells in the range.
So there's six cells in the range
along with the actual range of cells themselves.
So each of these cell objects contains the data, formatting,
and other related information for that particular cell.
But there are going to be times when you don't know
how many cells or rows
or columns you're going to be dealing with.
And what you need is a way to iterate over those items
that are in the worksheet.
To do this, you can use the iter_rows
and iter_cols functions.
So let's go back to the code.
All right, so let's take one example.
I can iterate over a range of columns like this.
I can write for column in
and then I'm going to call on the sheet object.
I'm going to use the iter_cols function
and that takes some optional arguments.
So I can specify the starting and ending row
and column for the iteration.
So I can say start at the min_row and we'll set that to two.
And then I'll set max_row equal to three
and we'll set min_col equal to two
and max_col equal to five.
And then for each cell that is in that column,
I will print the cell's value.
All right, so here in this example I'm starting at row two
and ending at row three,
and I'm looking at all the cells
from column two to column five.
So let's comment out the previous example,
and let's save,
and let's go ahead and run this.
And now we can see the actual values in the output.
Canada, Germany, product names, right, some values.
If we look at the actual sheet,
you'll see that that's, you have Canada, Germany, right?
Product names, right, values and so on.
So it seems to be working fine. Right.
So I could do the same thing
using the iterate rows function.
And in this case, let's try a useful example.
Suppose I wanted to count the number of each product type
that appears within the product column
of the example worksheet.
So to do this, I'm going to use a defaultdict collection
to keep track of each count.
So I'll create a defaultdict
and I'll have that be based on integers.
And then I'm going to iterate over the entire set of rows
for that one column.
So in this case, I'm going to iterate for each row in
sheet dot, and I'm going to call iter_rows this time,
instead of iter_cols.
I'll set the min_row equal to two to skip the header,
and I'll set the min_col
equal to three,
'cause that's the product column.
I will leave max_col also equal to three.
So we only operate on that one column.
And then for the cell in that row,
my counter, indexed by the cell value,
'cause that's going to be each product name.
I'll just increment that count by one.
And then when I'm done, I'll print out the counter.
All right, so let's go ahead and comment that out.
And if we take a quick look at the data, right?
Column three, that's this column right here.
So we have all these product names in here, right?
So we're going to use each one of these words as a key
into that default dictionary.
And I should end up with a count
of all the different products, right?
So let's go ahead and save, and let's run this.
Yep, and sure enough, we've got 93 Carrateras, 93 Montanas,
couple of hundred Paseos, 109 Velos, and so on.
All right, so that's pretty cool. Okay, let's keep going.
Let's try one more example.
It's also possible to work with other types of cell content.
So for example, I can add a comment to a cell
by using the comment class.
Let's comment out this previous example,
and I'll get a reference to the first cell in the sheet.
And then I'll set the comment property equal to
a new instance of a comment object,
and that takes two strings.
I'm going to write "This is a comment"
and then the author is also a string.
And I'll just put my name in there.
And then of course I have to save the workbook
for that comment to show up.
So I'll call wb.save,
and let's just give it a different name
so we don't mess up our example file.
So we'll save this as Content.xlsx.
All right, let's go ahead and save, and let's run this.
All right, sure enough, Content shows up,
and of course we have to go and open Excel to see this
because I don't think that extension shows this information,
but you can see that sure enough up there in cell ones,
that little red chip,
when I hover over it, oh, there's my comment.
All right, so these utility methods
in the openpyxl library
make it really easy to process the contents
of workbooks and worksheets.