How to Find the number of Blank and Non-Blank cells in Excel table using Python

How to Find the number of Blank and Non-Blank cells in Excel table using Python?

by

In this article let us see how to count/find the number of blank and Non-Blank cells in an Excel table using Python

Along with the use of excel spreadsheets in nearly every aspect of daily life to perform calculations, analysis, and visualization of data and information, keeping track of the blank and non-blank cells in your excel table is essential. Counting all the cells can be difficult, thus python has a very short and simple code for this task which makes life easier.

sampleExcelFile:

blank cells in excel file

Python xlrd Module:

The xlrd module can be used to get data from a spreadsheet. It can be used to read, write, or modify data. In addition, the user may have to navigate through several sheets and get data based on some criteria, or edit some rows and columns, among other things.

To extract data from a spreadsheet, use the xlrd module.

NOTE:

 Support for reading xlsx sheets has been explicitly removed from xlrd module.

Installation:

Type the below command to install the xlrd module:

pip install xlrd

Functions Used:

  • open_workbook(): It is used to open a workbook.
  • sheet_by_index(): This function is used to access/open the sheet with a specific index number.
  • nrows: It is used to give the total number of rows.
  • ncols: It is used to give the total number of rows.

Python Program to Find the number of Blank and Non-Blank cells in Excel table

Step#1: Importing the module

Import xlrd module using the import keyword to work with the excel file.

# Import xlrd module using the import keyword
import xlrd

Step#2: Input the excel File

Here we input an excel file on which the count of Non-empty/filled cells must be performed by giving the path of the excel file.

# Take a variable that stores the count of all empty cells in an excel sheet
empty_cells=0
# Take another variable that stores the count of all Non-empty/filled cells in an excel sheet
filled_cells=0
# Give the path of the excel file and store it in a variable
filepath="sampleExcelFile.xlsx"

Step#3: Creating a workbook and a worksheet

Here we create a workbook for the given excel file using the open_workbook() function and also access the required worksheet using the sheet_by_index() method.

# Pass the above file path to the open_workbook() function of the xlrd module
# to create a workbook object 
workbook=xlrd.open_workbook(filepath)
# Create a first worksheet in the above workbook using the sheet_by_index() function
worksheet=workbook.sheet_by_index(0)

Step#4: Finding the number of blank and Non-blank cells 

Now, let us calculate the number of blank and non-blank cells using the below part of the code.

# Iterate through each row of the worksheet using the nrows attribute
# The nrows attribute is used to extract the number of rows
for excelrow in range (worksheet.nrows):
    # Iterate through each column of the worksheet using the ncols attribute
    # using the nested for loop
    # The ncols attribute is used to extract the number of columns
    for excelcolumn in range (worksheet.ncols) :
        # Check if the cell is a blank cell using the cell_value() function and 
        # if conditional statement
        if (worksheet.cell_value(excelrow,excelcolumn)==""):
            # If it is true, then increment the count value of above empty_cells 
            # by 1
            empty_cells+=1
        else :
            # Else in increment the count value of above filled_cells by 1
            filled_cells+=1
# Print the count of empty cells in a given excel sheet 
print("The count of empty cells in a given excel sheet = ", empty_cells)
# Print the count of Non-empty/filled cells in a given excel sheet 
print("The count of Non-empty/filled cells in a given excel sheet = ", filled_cells)

Complete Code

Here is the complete code for you.

Approach:

  • Import xlrd module using the import keyword.
  • Take a variable that stores the count of all empty cells in an excel sheet.
  • Take another variable that stores the count of all Non-empty/filled cells in an excel sheet
  • Give the path of the excel file and store it in a variable..
  • Pass the above file path to the open_workbook() function of the xlrd module
    to create a workbook object.
  • Create the first worksheet in the above workbook using the sheet_by_index() function.
  • Iterate through each row of the worksheet using the nrows attribute. The nrows attribute is used to extract the number of rows
  • Iterate through each column of the worksheet using the ncols attribute using the nested for loop. The ncols attribute is used to extract the number of columns
  • Check if the cell is a blank cell using the cell_value() function and if conditional statement
  • If it is true, then increment the count value of the above empty cells by 1.
  • Else in increment the count value of above filled cells by 1.
  • Print the count of empty cells in a given excel sheet.
  • Print the count of Non-empty/filled cells in a given excel sheet.
  • The Exit of the Program.

Below is the implementation:

# Import xlrd module using the import keyword
import xlrd
# Take a variable that stores the count of all empty cells in an excel sheet
empty_cells=0
# Take another variable that stores the count of all Non-empty/filled cells in an excel sheet
filled_cells=0
# Give the path of the excel file and store it in a variable
filepath="sampleExcelFile.xlsx"
# Pass the above file path to the open_workbook() function of the xlrd module
# to create a workbook object 
workbook=xlrd.open_workbook(filepath)
# Create a first worksheet in the above workbook using the sheet_by_index() function
worksheet=workbook.sheet_by_index(0)
# Iterate through each row of the worksheet using the nrows attribute
# The nrows attribute is used to extract the number of rows
for excelrow in range (worksheet.nrows):
    # Iterate through each column of the worksheet using the ncols attribute
    # using the nested for loop
    # The ncols attribute is used to extract the number of columns
    for excelcolumn in range (worksheet.ncols) :
        # Check if the cell is a blank cell using the cell_value() function and 
        # if conditional statement
        if (worksheet.cell_value(excelrow,excelcolumn)==""):
            # If it is true, then increment the count value of above empty_cells 
            # by 1
            empty_cells+=1
        else :
            # Else in increment the count value of above filled_cells by 1
            filled_cells+=1
# Print the count of empty cells in a given excel sheet 
print("The count of empty cells in a given excel sheet = ", empty_cells)
# Print the count of Non-empty/filled cells in a given excel sheet 
print("The count of Non-empty/filled cells in a given excel sheet = ", filled_cells)

Output:

The count of empty cells in a given excel sheet = 29 
The count of Non-empty/filled cells in a given excel sheet = 101

Leave a Comment