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

In this article, we will show you how to find the count of a number of blank and non-blank (filled) cells in an excel worksheet using python.

Assume we have taken an excel file with the name demoTutorialsPoint.xlsx containing some random data with some blank cells. We will return the count of a number of blank and non-blank (filled) cells in an excel worksheet.

sampleTutorialsPoint.xlsx

Algorithm (Steps)

Following are the Algorithm/steps to be followed to perform the desired task −

• Use the import keyword, to import the xlrd module (To read data from a spreadsheet, use the xlrd module. It has the ability to read, write, and alter data. Furthermore, the user may be required to traverse numerous sheets to obtain data based on certain criteria or to alter specific rows and columns, among other things. Use the xlrd module to extract data from a spreadsheet).

pip install xlrd

• Create a variable to store the count of empty cells present in an excel sheet.

• Create another variable to store the count of Non-empty cells present in an excel sheet.

• Initialize both the count variables with 0.

• Create a variable to store the path of the input excel file.

• To create a workbook object, pass the input excel file to the xlrd module's open_workbook() function (opens a workbook).

• Using the sheet_by_index() method (opens a sheet with the specific index), open the first worksheet in the given workbook (here 0 represents first sheet).

• Traverse in all the rows of the worksheet using the for loop. The nrows attribute is used to get the total number of rows.

• Using the nested for loop, traverse all the columns of the worksheet using another nested for loop. The ncols attribute is used to get the total number of columns.

• Using the cell_value() function (gives the cell's value in the specified row and column) and the if conditional statement, determine whether the cell is blank or NOT.

• Increment the empty cells count by 1 if it is a blank cell.

• Else Increment the Non-empty cells count by 1.

• Print the count of empty cells found in a given input excel file.

• Print the count of Non-empty cells found in a given input excel file.

Example

The following program prints the count of the number of blank and Non-blank cells found in a given input excel file −

import xlrd
# storing the count of Empty cells
emptyCells=0

# storing the count of Non-empty cells
nonEmptycells=0

# input excel file path
inputExcelFile ="sampleTutorialsPoint.xlsx"

# creating a workbook
newWorkbook =xlrd.open_workbook(inputExcelFile)

# creating a first worksheet
firstWorksheet=newWorkbook.sheet_by_index(0)

# Traversing in all the rows of the worksheet

# (nrows is used to get the number of rows)
for each_row in range (firstWorksheet.nrows):

   # Traversing in all the columns of the worksheet

   # (ncols is used to get the number of columns)
   for each_column in range (firstWorksheet.ncols) :

      # Checking whether the cell is a blank cell   
      if (firstWorksheet.cell_value(each_row, each_column)==""):

         # Incrementing empty cells count by 1, if it is a blank cell
         emptyCells+=1
      else :

      # Else Incrementing Non-empty cells count by 1
      nonEmptycells+=1

# Printing the count of empty cells
print("Empty cells count = ", emptyCells)

# Printing the count of Non-empty cells
print("Non-empty cells count = ", nonEmptycells)

Output

On executing, the above program will generate the following output −

Empty cells count = 10
Non-empty cells count = 67

We used a sample excel file with some dummy data in our program. The excel file has some blank cells in it. We used two variables to count the number of empty and non-empty cells, and we used the for loop to traverse the excel file cell by cell, checking whether it was an empty cell or not, and printed the count of empty and non-empty cells.

Conclusion

We learned how to use the xlrd module to fetch the excel file as a workbook and get the specified sheet as a worksheet. We also learned how to traverse the excel file cell by cell and how to obtain the value of a cell using the cell value() function.

Vikram Chiluka

Updated on: 18-Aug-2022

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started