Existing Excel - Iterate Thru Rows

Locked lesson.

Upgrade

About this lesson

Let's discuss another method to iterate through rows in an Excel spreadsheet when you need a little more control.

Quick reference

Existing Excel - Iterate Thru Rows

This is another way to Iterate through rows that gives you a little more control.

When to use

Use this method to iterate through rows in an Excel spreadsheet when you need a little more control.

Instructions

for row in ws.iter_rows(min_row=1, max_row=8, min_col=1, max_col=2):
     for cell in row:
          print(cell.value)

 

You can use this without the .value method by setting a values_only flag:

for row in ws.iter_rows(min_row=1, max_row=8, min_col=1, max_col=2, values_only=True):
     for cell in row:
          print(cell)

Hints & tips

  • for row in ws.iter_rows(min_row=1, max_row=8, min_col=1, max_col=2):
  • for row in ws.iter_rows(min_row=1, max_row=8, min_col=1, max_col=2, values_only=True):
Login to download
  • 00:04 Okay, in this video, I want to show you another way to iterate through rows that
  • 00:08 gives you a little bit more control over exactly what you're returning.
  • 00:11 So let's create a for loop.
  • 00:13 So let's go for row in, and it's called our worksheet dot.
  • 00:17 And we want to call the iter_rows function.
  • 00:21 And this will allow us to iterate through our rows and
  • 00:25 we can set exactly what we want to iterate through.
  • 00:28 Let's go min_row and let's say 1, we want to return at least the first row.
  • 00:34 And we can also set the max_col, so
  • 00:37 the maximum number of columns we want to return, let's say 2 for now.
  • 00:42 And then we can also designate the max)row, and let's say we want 8.
  • 00:49 And if we look at our worksheet, we've got 1 through 8 rows.
  • 00:53 So okay, now this is a for loop, so we need a colon there and
  • 00:57 let's loop through here.
  • 00:59 So let's go for cell in row and let's print out our cell.
  • 01:06 Let's go ahead and save this and run this and see what this return.
  • 01:10 And you can see it's printing out A1, B1 which is a row,
  • 01:15 A2, B2, which is a row A3, B3, which is a row, etc.
  • 01:20 If we want the actual values as always, we can call .value, and save this.
  • 01:29 And then we get all of our data.
  • 01:31 So you see here we're getting our headers, maybe we don't want the headers.
  • 01:35 So we could designate we don't want min_row=1, we want min_row=2.
  • 01:40 And instead of value, here's a neat little trick I can show you.
  • 01:44 We can set a value flag inside of here by going values_only=True.
  • 01:53 So if we say this, come back here, let's clear the screen and run this again.
  • 01:57 We know how to get the headers and we still get our values, so john, Pepperoni,
  • 02:01 Mary, Cheese, Steve, Mushroom, etc.
  • 02:04 So this gives you a little bit more control over exactly what you're
  • 02:08 returning.
  • 02:08 If we only want one column, we can change this max_col=1.
  • 02:13 If we save this, and then run this guy again, let's clear the screen.
  • 02:16 And we just get John, Mary, Steve, Tina, Wes, Amy, and Jason.
  • 02:23 We can also designate a min_col as well, so
  • 02:27 we can go min_col=2 and let's say the max_col=2.
  • 02:32 So if we want the second column only, we can call a min and a max on it.
  • 02:36 So if we save this and run it, we should just get the pizza toppings, right,
  • 02:41 there we go, Pepperoni, Cheese, Mushroom.
  • 02:44 Notice we're not getting the header because we didn't designate that row,
  • 02:48 if we did want the header, we could change that back to one.
  • 02:52 Save this and run it, and we get our header.
  • 02:57 So that's another way to iterate through rows.
  • 02:59 In the next video, we'll look at another way to iterate through columns.

Lesson notes are only available for subscribers.

Gift this course