Finding the number of Blank and Non-Blank cells in Excel table using Python

Hello everyone, with the use of excel spreadsheets in almost every day to day life, to perform calculation, analysis, visualization of data and information, keeping a record of its Blank and Non-Blank cells in your excel table is also important. Counting all the cells can be a hassle, so to help you with it, we at codespeedy, have designed this short yet simple python code to solve your problem. So here we will learn how to find the number of blank and non-blank cells in excel table in Python.
But before we move on to the code, please make sure you have ‘xlrd’ library installed in your machine.
xlrd is a python library which has been built specially, to read data from an excel file, however, the xlrd library cannot be used to input data into excel file. Python uses a different Library for that.

If you have never used this module you may have a look at¬†Reading an excel sheet using “xlrd” module in Python 3.x or earlier.

Find Empty and Non-Empty Cells of the table in an excel file in Python

import xlrd
empty=0
filled=0
path="Excel.xlsx"
wb=xlrd.open_workbook(path)
sheet=wb.sheet_by_index(0)
for row in range (sheet.nrows):
    for column in range (sheet.ncols) :
        if (sheet.cell_value(row,column)==""):
            empty+=1
        else :
            filled+=1
print(f'The number of Empty and Non-Empty cells are {empty} and {filled} respectively. ')

Output :

The number of Empty and Non-Empty cells are 6 and 22 respectively. 

*Note : Output will vary from machine to machine and sheet to sheet.

Explanation :

The code starts by importing the “xlrd” library, the one without which none of these would have been possible. We then create two counters, one for the number of empty cells and other for non-empty cells. We set the path to the address for the file (*note : the path address will differ from machine to machine). The next and obvious step is to open the workbook by setting its path. We then set up the sheet as a excel file can contain multiple sheets, we then use for loops to traverse between all the cells of your excel table, and check. If the the cell is empty, we iterate the empty counter with one else we iterate the filled counter with one. In the end we use a formatted string to print the number of empty and non empty cells.
In our program we have used a well defined path, i.e it cannot be changed, but if you want to use a user-defined path, you are free to do so, just change the path statement with :

path=input("Enter the file address using forward slash as a separator ")

and your program is good to go.

Leave a Reply

Your email address will not be published. Required fields are marked *