Find the first empty cell from column of an excel file using openpyxl

In this tutorial, we will see how to find the first empty cell in a column of an excel sheet using openpyxl. openpyxl is a Python library used to work on Excel files.

First, we must install the openpyxl library and import the theload_workbook() function from it in our code. It is a function used to access MS Excel files.

How to install openpyxl in Python
Note: It only works on previously created excel files.

! pip install openpyxl
from openpyxl import load_workbook

Before starting the program, create an Excel file with some data. Or use an already created file.

Example of excel file:

Pass the excel file location path to the function to access the file.

path = 'F:\Jupyter\IRIS.xlsx'
wb = load_workbook(path)

Now select an active excel sheet from the workbook. Or you can select by specifying its name.

sheet = wb.active
#Or use    sheet = wb['sheet name']

Find the first empty cell in the column

To find the first empty cell from a particular column takes the column number as input from the user.
Note: Column and Row numbers in an Excel file start from 1.

col = int(input("Enter column number: "))

Output:

Enter column number: 4

Now, using for loop iterate through the rows of the column.
for each cell using the if statement checks whether the cell value is None or not. If yes (if None), print that cell and come out of the loop.

for rowNum in range(1, sheet.max_row):
    if((sheet.cell(row=rowNum, column=col).value)==None):
        print(f"The first empty cell from column {col} is:",sheet.cell(row=rowNum, column=col))
        break

Attributes used:

max_row is an attribute of the sheet object which returns the number of maximum rows in the sheet.

cell(row=rowNum, column=col).value attribute returns the value from a particular single cell. You have to pass row and column number to the attribute.

The break statement is used to stop and terminate the loop. Statements defined after the break statement do not get executed in python.

Output:

The first empty cell from column 4 is: <Cell 'Worksheet'.D2>