openpyxl Part 10 – Freezing Rows and Columns

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

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.