Locked lesson.
About this lesson
Use this method when you want to grab an entire row - or a range of rows - from an Excel spreadsheet.
Exercise files
Download this lesson’s related exercise files.
19 - Existing Excel - Row Range.docx57.1 KB 19 - Existing Excel - Row Range SOLUTION.docx
55.6 KB
Quick reference
Existing Excel - Row Range
We can easily grab an entire row and a range of rows from an Excel spreadsheet.
When to use
Use this method when you want to grab an entire row, or a range of rows from an Excel spreadsheet.
Instructions
Notice there are no quotation marks around 1.
#Grab row 1:
row = ws[1]
# Now loop through to grab the values:
for cell in row:
print(cell.value)
# Grab a range of rows 1 thru 2
row_range = [1:2]
# Loop through them:
for row in row_range:
for x in row:
print(x.value)
Hints & tips
- One Row: row = ws[1]
- Row Range: row_range = [1:2]
- 00:04 Okay, in this video, we want to grab an entire row and a range of rows.
- 00:08 So we can create a variable.
- 00:10 Let's call it row and we want to set it equal to ws or worksheet and
- 00:14 then whatever row we want.
- 00:15 So just like we grabbed a column by calling the letter,
- 00:20 we can just pass a number for the row.
- 00:23 And you'll notice these aren't in quotation marks.
- 00:25 I said way back at the beginning of the course,
- 00:27 Python hardly ever uses quotation marks with numbers.
- 00:29 That's just the way it goes.
- 00:30 So if we look at our worksheet really quickly,
- 00:34 we see row 1 is this guy right here at the top, so names and favorite pizza.
- 00:39 So let's go ahead and print out row and save this and see what we got.
- 00:45 You can probably guess by now.
- 00:47 We're going to get a tuple with Python objects in it so
- 00:49 we can loop through our tuple like we've learned to do many times already.
- 00:54 So let's go for cell in row, print(cell).
- 01:00 So if we save this and run it,
- 01:01 you can probably guess we're going to get a list of those objects again.
- 01:04 And to get the actual values just like always, we can call .value on this guy.
- 01:09 Save this and run it one more time, and we get Names and Favorite Pizza.
- 01:14 So we can grab any row we want.
- 01:16 If we want the second row, we just pass it to through there.
- 01:20 Run this, we see John and Pepperoni, which if we check our spreadsheet, sure enough,
- 01:25 John and Pepperoni is row 2.
- 01:26 So really easy to grab rows, and that's all there is to it.
- 01:30 Now we can also grab a range of rows, so let's go row_range.
- 01:35 And just like with other slices, we can designate what we want.
- 01:38 So we want, let's say 1 through 8, right?
- 01:43 So let's get rid of this.
- 01:44 And let's look at our spreadsheet again.
- 01:45 So instead of 1 through 8, maybe we just want 2 through 8, right?
- 01:49 We don't want the headers.
- 01:50 We just want the data.
- 01:51 So let's change that to 2 through 8.
- 01:54 And let's print this out.
- 01:58 So save this and run it.
- 02:00 Let's clear the screen here and start fresh.
- 02:03 And we get a big huge tuple again.
- 02:05 And we could see there's a couple of different tuples in here.
- 02:07 And this makes sense because we've got two columns, so
- 02:11 we expect more than one tuple inside of here.
- 02:15 And so let's start breaking this apart and looping through it.
- 02:20 So let's go for row in row_range.
- 02:24 So let's print out a row.
- 02:27 Save this and run it.
- 02:28 See what we got here.
- 02:30 So we've got another set of tuples.
- 02:34 And each one of them has, so A2, B2, A3, B3.
- 02:38 And that kind of makes sense, our rows and columns there.
- 02:41 So all right, so let's then loop through this again inside of here.
- 02:46 So let's go for x in row, print x, and
- 02:49 we can kind of probably guess what this is going to look like,
- 02:54 a bunch of Python objects, sure enough.
- 02:58 And we can get the values from each of those just by calling .value as we
- 03:03 always do.
- 03:04 So if we save this and let's clear the screen and run it.
- 03:08 We get our items and these are paired up John, Pepperoni, Mary, Cheese.
- 03:12 Now this is kind of hard to read.
- 03:13 We can create line breaks in here if we want.
- 03:16 So inside of this guy here, we can go print.
- 03:20 And then let's just go \n, that stands for line break.
- 03:26 So if we save this and run it the screen, and we get some spaces in here.
- 03:32 And it's sort of easier to read this.
- 03:33 So John Pepperoni, Mary Cheese, Jason Pineapple,
- 03:36 we can check against our worksheet here.
- 03:39 And sure enough, Jason likes pineapple.
- 03:41 Steve likes mushrooms.
- 03:43 So we can come through here and see.
- 03:45 Yep, sure enough, Steve likes mushroom, and we're good to go.
- 03:48 So that's how to grab a range of rows.
- 03:50 That's how to grab an individual row, pretty straightforward.
- 03:53 Now in the last few videos, we've been looping through these ranges and
- 03:57 it's been, it works.
- 03:59 But it's been a little bit sloppy to say the least, and a little time-consuming.
- 04:04 We have to do print a bunch of times to figure out what the thing is returning,
- 04:07 how many tuples it's returning.
- 04:09 It's kind of a hassle.
- 04:10 openpyxl actually has some functions that help us to iterate through rows and
- 04:14 columns, and we'll start to look at those in the next video.
Lesson notes are only available for subscribers.