Iterating Through the Data
Now that you can load the worksheets, it’s time to put their data to use.
openpyxl has a couple of different ways to iterate through the data in your worksheets and, in some ways, should feel familiar if you’re used to doing this in Excel.
So, go ahead and open up an interpreter session and do your imports. So,
from openpyxl import load_workbook. Say that
workbook is going to equal
load_workbook with the
filename="sample.xlsx", and the
sheet is going to equal
workbook.active. In a way that’s very similar to Excel, you can slice through the rows and columns using a
: (colon) to separate the top-left and bottom-right cells. So let’s say
sheet, and you want to access
["A1:C2"], like that.
Okay. And just like in Excel, if you ever want to access an entire column, you can say
sheet and then just put in the column letter. So if you want to see everything, that’s in
"A", you can do that.
So, of the tuples that are returned, each tuple contains an entire column. If you want to grab an entire row, you can get those the same way. Let’s say you just want to grab row 5, just put in the row number as an integer, run that, and you can see
C5, and so on.
.iter_cols() can take a range of rows and columns, and then iterate through the cells. These will also return tuples, which are either entire rows or columns, depending on which one you call.
So, to try this out, say something like
for row in sheet and then call the
.iter_rows() method and say that
min_row is going to equal
max_row is going to equal
min_col is going to equal
max_col is going to equal
3, like, so. And then, just print the
There you go!
C2. This will work for columns, too. So,
for column in sheet I’m going to call
.iter_cols(), and then with the same name parameters—so,
values_only set to
True. Come back here, print the
value, and now you should see the resulting values from those cells, instead of the
Cell objects. Finally, if you want to go through the entire worksheet and you don’t have any min or max rows or columns, you can just say something like
for row in sheet and then access the
.rows property. So if you go
print(row) from here, you should see that everything prints out just like that.
And column would be the same way. So, when you do this, it’s the same as calling
.iter_cols(), but it gets rid of the minimum or maximum parameters and goes through the entire dataset. All right!
Become a Member to join the conversation.