In the previous part of the openpyxl series we were adding and removing sheets. We didn’t save the changes, though, so the workbook remained unchanged. Today we’ll be talking about freezing rows and columns. This is what we do if our workbook is too large to see all the data at once, but still we want to see some data, like a particular row or column, even when we scroll it horizontally or vertically. We’ll be still working on the wb2.xlsx spreadsheet. Although it’s not too large to be see all at once, we can still use it to demonstrate how freezing rows and columns works.
Here’s the data our workbook contains:
| company | city | category | income | number_of_employees |
| Roses and Pansies | Toronto | gardening | 456845.00 | 26 |
| Reader’s Paradise | Sydney | publisher | 911500.00 | 44 |
| Dogs and Cats | Madrid | pets | 458784.00 | 36 |
| Enjoy Your Meal | New Orleans | restaurant | 447884.00 | 18 |
| Bloom Store | London | gardening | 1254654.00 | 84 |
| Tastes of the South | Miami | restaurant | 587454.00 | 42 |
| Pets for Everyone | Athens | pets | 987887.00 | 21 |
| Eat ‘Em Up | Helsinki | restaurant | 1254477.00 | 53 |
| Mr. Bunny | Vienna | pets | 254478.00 | 12 |
| Bookworms Rule | Rome | publisher | 124474 | 40 |
As usual, let’s start by loading the workbook:
>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb2.xlsx")
>>> sheet = workbook.active
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)
To freeze a row or column, we use the freeze_panes attribute. We set it to the first cell that is not supposed to be frozen. So, in our example we want to freeze the first row, which is the one with the headings, and the first column, which is the one with the company names. So, the first column that should not be frozen is B and the first row that should not be frozen is 2, so we must set the freeze_panes attribute to ‘B2’. This time I’m going to save the workbook so that we can then open it and see how it works. Here’s the code:
>>> sheet.freeze_panes = 'B2'
>>> workbook.save('wb2.xlsx')
Now let’s open the wb2.xlsx file and try scrolling the sheet both horizontally and vertically:
As you can see, the first row and the first column are not scrolled and remain always visible.
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.