#100DaysOfCode in Python Transcripts
Chapter: Days 70-72: Excel automation with openpyxl
Lecture: Using maxrow
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
So I'm sure a few of you had a question from the last video, which was "What happens if we don't know the end cell for the range?"
0:11
So in the last video we did a sort of range check. We went from cell 2, down to 101 in our column, and we just added all of the values up.
0:23
Now what happens if we don't know that, you know that end value, that end range number - 101. You know spreadsheets are constantly growing,
0:32
so you know we can't hard code our end value in. This is where openpyxl really shines, it's got something awesome that I absolutely love,
0:44
and that is max_row. What we do is, we go ws1, we've specified our worksheet as Finances 2017. We go ws1.max_row 705.
1:03
So what this looks at is it goes into the spreadsheet, goes down here and it just pops down. I'll let it scroll,
1:12
pops down to the last active cell. Okay? It's not the last fully populated one just like that but wherever the lowest, or the highest,
1:23
rather, the highest cell happens to be. So I just popped this in here to show that, while yes, in the nicely formatted sort of
1:34
rows that we've got here. Because I've entered something here, max_row is 705. Okay? So we'll just delete that, not that it really matters.
1:44
And we'll just do a quick demonstration printing out something really quick. So we'll go for row, we can actually let's just do max_row=ws1.max_row.
1:57
That way we don't need to type it out every time. Let's grab pretty much all of the country data I reckon. Let's just double check the file here.
2:06
And yes, B is the country, so we'll go four row, in range two, 'cause we don't want the header, right? So two max row, mkay?
2:23
And we'll just create the cell quickly, so we'll go with B, 'cause that's the country, try something different rather than money.
2:31
And that's string of the row. Okay, and then we'll just print it out. So this is going to give us one heck of a list,
2:38
but for the sake of this, let's do it. So remember, we still have to specify ws1. Even though we have got the cell here,
2:47
we still need to say this is worksheet 1, otherwise he's not going to know. So ws1 cell.value. And before I hit enter, this for-loop,
2:58
it's gone row two, over to the maximum row, which we know is 705. So this is going to do the 705 times, build this cell number,
3:08
and then print out the value, okay? So ready? Here we go. And there, we now have None, but that's because, we have, no data down here, okay.
3:25
So we can expect that, that's okay. But, we see all of them, United States, Canada, Mexico, France, Germany, and so on. That's another way of accessing
3:36
all of the cells that you need, in a row you can see here, you can combine things now. You can combine that with other rows as well.
3:45
So we could obviously build in the code, we could build the B, we could build that cell, we could also build that along with A.
3:55
So we know the government in Germany, the mid-market in France, and so on. You can see I could start to build these.