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