In the previous part of the series we saw how to access single cells of the spreadsheet and the values they contain. In this part we’ll see how to iterate over whole rows and columns and how to access the cells from a range.
We’ll be still working on the worksheet from the previous two parts, so let’s get ready:
>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb1.xlsx")
>>> sheet = workbook.active
Let’s start by getting ranges of cells in a given row or column. Here how you can get all the cells from a row, let’s say the first row:
>>> sheet[1]
(<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>)
The cells are printed in a tuple.
Learn how to make beautiful GUI apps
in Python using the Kivy framework.
Comprehensive, for Kivy beginners, easy to follow.
Get the book here (PDF) or on Amazon:
ebook / paperback (black and white) / paperback (full color)
How about all the cells from a column? Let’s get all the cells from column D:
>>> sheet['D']
(<Cell 'Sheet'.D1>, <Cell 'Sheet'.D2>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.D4>, <Cell 'Sheet'.D5>, <Cell 'Sheet'.D6>, <Cell 'Sheet'.D7>, <Cell 'Sheet'.D8>, <Cell 'Sheet'.D9>, <Cell 'Sheet'.D10>, <Cell 'Sheet'.D11>, <Cell 'Sheet'.D12>, <Cell 'Sheet'.D13>, <Cell 'Sheet'.D14>, <Cell 'Sheet'.D15>, <Cell 'Sheet'.D16>, <Cell 'Sheet'.D17>, <Cell 'Sheet'.D18>, <Cell 'Sheet'.D19>, <Cell 'Sheet'.D20>)
You can also get all the cells from a range. How about the cells from rows 3-5?
>>> sheet[3:5]
((<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>), (<Cell 'Sheet'.A4>, <Cell 'Sheet'.B4>, <Cell 'Sheet'.C4>, <Cell 'Sheet'.D4>), (<Cell 'Sheet'.A5>, <Cell 'Sheet'.B5>, <Cell 'Sheet'.C5>, <Cell 'Sheet'.D5>))As you can see, you get the cells row by row, each in a separate tuple and all the tuples in an outer tuple.
And now let’s get the cells from the columns C-E:
>>> sheet['C:E']
((<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.C4>, <Cell 'Sheet'.C5>, <Cell 'Sheet'.C6>, <Cell 'Sheet'.C7>, <Cell 'Sheet'.C8>, <Cell 'Sheet'.C9>, <Cell 'Sheet'.C10>, <Cell 'Sheet'.C11>, <Cell 'Sheet'.C12>, <Cell 'Sheet'.C13>, <Cell 'Sheet'.C14>, <Cell 'Sheet'.C15>, <Cell 'Sheet'.C16>, <Cell 'Sheet'.C17>, <Cell 'Sheet'.C18>, <Cell 'Sheet'.C19>, <Cell 'Sheet'.C20>), (<Cell 'Sheet'.D1>, <Cell 'Sheet'.D2>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.D4>, <Cell 'Sheet'.D5>, <Cell 'Sheet'.D6>, <Cell 'Sheet'.D7>, <Cell 'Sheet'.D8>, <Cell 'Sheet'.D9>, <Cell 'Sheet'.D10>, <Cell 'Sheet'.D11>, <Cell 'Sheet'.D12>, <Cell 'Sheet'.D13>, <Cell 'Sheet'.D14>, <Cell 'Sheet'.D15>, <Cell 'Sheet'.D16>, <Cell 'Sheet'.D17>, <Cell 'Sheet'.D18>, <Cell 'Sheet'.D19>, <Cell 'Sheet'.D20>), (<Cell 'Sheet'.E1>, <Cell 'Sheet'.E2>, <Cell 'Sheet'.E3>, <Cell 'Sheet'.E4>, <Cell 'Sheet'.E5>, <Cell 'Sheet'.E6>, <Cell 'Sheet'.E7>, <Cell 'Sheet'.E8>, <Cell 'Sheet'.E9>, <Cell 'Sheet'.E10>, <Cell 'Sheet'.E11>, <Cell 'Sheet'.E12>, <Cell 'Sheet'.E13>, <Cell 'Sheet'.E14>, <Cell 'Sheet'.E15>, <Cell 'Sheet'.E16>, <Cell 'Sheet'.E17>, <Cell 'Sheet'.E18>, <Cell 'Sheet'.E19>, <Cell 'Sheet'.E20>))
It works just like with the rows.
You can also get the cells from a specific range of both rows and columns. For example here’s how you can get the cells between B2 and D6:
>>> sheet['B2:D6']
((<Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.D2>), (<Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>), (<Cell 'Sheet'.B4>, <Cell 'Sheet'.C4>, <Cell 'Sheet'.D4>), (<Cell 'Sheet'.B5>, <Cell 'Sheet'.C5>, <Cell 'Sheet'.D5>), (<Cell 'Sheet'.B6>, <Cell 'Sheet'.C6>, <Cell 'Sheet'.D6>))
You can also use Python generators to iterate over the rows and columns. I’ll demonstrate this in the next part of the series.
Make Awesome Games and Other 3D Apps
with Panda3D and Blender using Python.
Cool stuff, easy to follow articles.
Get the magazine here (PDF).
Learn the basics of Python, including OOP.
with lots of exercises, easy to follow
The course is available on Udemy.
Learn the basics of 3D modeling in Blender.
step-by-step, easy to follow, visually rich
The course is available on Udemy and on Skillshare.