Freeze Header Rows in OpenPyXL – Python

Hey fellow Python coders! In this tutorial, we will be learning how to freeze header rows in an Excel file using the OpenPyXL library in Python programming language. Let’s first understand how freezing the rows helps and why it might be necessary at times to implement.

Freezing rows in Excel is a feature that allows you to lock certain rows or columns that will remain in their place even while scrolling through a spreadsheet. Usually, the first row in an Excel file contains headers that describe the data in each column. Freezing the Header (Top) row in the Excel file makes it easier for the user to understand which data belongs to which category or header even after scrolling through the spreadsheets.

Python Code Implementation of Freezing Header Rows

For the code implementation, we will follow the following steps:

1. Create a New Excel file with the Data present
2. Freeze the Header Rows Using freeze_panes

Step 1 – Create a New Excel file with the Data present

Let’s start by creating a general database using simple openpyxl functions and the random library as displayed in the code snippet below.  would make use of the Faker library to add meaningful datasets to the Excel files. For this tutorial, let’s create an employee database with multiple columns with random values.

import openpyxl
from faker import Faker
import random

fake = Faker()
headers = ['Name', 'Age', 'Email', 'Address', 'Phone Number', 'Designation',
           'CTC', 'Location', 'Marital Status', 'Number of Children', 'Hobbies',
           'Favorite Food', 'Pet']

workbook = openpyxl.Workbook()
sheet = workbook.active
for col_num, header in enumerate(headers, start=1):
    sheet.cell(row=1, column=col_num, value=header)
num_rows = 100
for row_num in range(2, num_rows + 2):
    sheet.cell(row=row_num, column=1, value=fake.name())
    sheet.cell(row=row_num, column=2, value=random.randint(20, 65))
    sheet.cell(row=row_num, column=3, value=fake.email())
    sheet.cell(row=row_num, column=4, value=fake.address())
    sheet.cell(row=row_num, column=5, value=fake.phone_number())
    sheet.cell(row=row_num, column=6, value=fake.job())
    sheet.cell(row=row_num, column=7, value=random.randint(40000, 150000))
    sheet.cell(row=row_num, column=8, value=fake.city())
    sheet.cell(row=row_num, column=9, value=random.choice(['Single', 'Married', 'Divorced']))
    sheet.cell(row=row_num, column=10, value=random.randint(0, 3))
    sheet.cell(row=row_num, column=11, value=', '.join(fake.words(nb=random.randint(1, 5))))
    sheet.cell(row=row_num, column=12, value=random.choice(['Pizza', 'Pasta', 'Sushi', 'Burger', 'Salad']))
    sheet.cell(row=row_num, column=13, value=random.choice(['Dog', 'Cat', 'Fish', 'Bird', 'Hamster']))
workbook.save("OriginalExcelFile.xlsx")

After execution, the original dataset looks as shown in the video below:

p

Step 2 – Implementation of Freezing the Header Rows in Excel File Using freeze_panes

In this section, we will be learning how to freeze the header rows in the Excel file that we just created in the step above. To achieve the freezing of the cells, we will make use of the freeze_panes function. To understand the results properly, we will be loading the Excel sheet in the code and then making changes to this new copy of the data. Finally, we will save all the method outputs in a separate file.

file_path = 'OriginalExcelFile.xlsx'
workbook = openpyxl.load_workbook(file_path)
worksheet = workbook.active

The first approach makes use of the freeze_panes method in openpyxl  which allows you to freeze rows and columns in an Excel file. As shown in the code snippet below, we are freezing the A2 row which is the header row in the Excel file.

worksheet.freeze_panes = 'A2' 
workbook.save('FreezeHeader_freezePanes.xlsx')

The video output after code execution is displayed below:

So that’s it for this tutorial, you must be wondering what can we do next with the same method. Well, you can use the same method to freeze columns or a combination of both rows and columns as well. If you liked this tutorial, have a read at the following tutorials as well:

1. Openpyxl – Iterate through all Rows in a Specific Column – Python
2. Trigonometric Operations on Excel file in Python using openpyxl
3. How to Lock Cells in OpenPyXL Python

Happy Learning!